mChat

Slow queries spam: UPDATE phpbb_sessions SET session_time - mChat

Slow queries spam: UPDATE phpbb_sessions SET session_time

by NicoBlog » Wed Mar 07, 2018 4:51 pm

Hello i noticed my slow queries log is full of queries created by this plugin.
bug report here: viewtopic.php?f=556&t=2461861&sid=ca71b ... a20446464b

Since then i disabled it and all is good again. But i pretty much like to have the chat back.
The thing is, it can't allow a plugin to do thousands of slow UPDATE all the time. I have very few users and this is a problem that doesn't scale at all.

Is there any chance to stop doing this query? or not doing it all the time?

Thank you.
Last edited by NicoBlog on Fri Mar 09, 2018 12:15 am
NicoBlog
Registered User
Posts: 65
Joined: Sun Dec 01, 2013 5:18 pm
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by kasimi » Thu Mar 08, 2018 9:48 am

Sessions are updated whenever a user adds, edits or deletes a message. I just manually ran such a query on one of my boards:

Code: Select all

1 row affected. (Query took 0.0015 seconds.)
As such an UPDATE query takes 0.4 seconds for you it seems there's a problem with your database. I haven't heard about anyone else having this issue so far, either.

You can disable all session updates done by mChat by setting this value to false: https://github.com/kasimi/mChat/blob/2. ... s.php#L841 There are side-effects to be expected though.
User avatar
kasimi
Extension Customisations
Extension Customisations
Posts: 3800
Joined: Sat Sep 10, 2011 7:12 pm
Location: Germany
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by NicoBlog » Fri Mar 09, 2018 12:08 am

I don't know why it takes so long on my database. Probably the result of many migrations (5 years of them). I also used the old mchat plugin before. Or maybe my server is just not working properly.

If i were to set it to false, what side effect can i expect? Why does the session need to be updated when chatting?
NicoBlog
Registered User
Posts: 65
Joined: Sun Dec 01, 2013 5:18 pm
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by kasimi » Fri Mar 09, 2018 4:57 pm

NicoBlog wrote: Probably the result of many migrations (5 years of them).
This is unlikely the reason. There are boards running much longer that don't have this issue. The problem is in your database or its configuration.

Setting $update_session_infos to false will stop updating the user session. If your user session length is set to 30 minutes, a user will have to login again after 30 minutes of chatting without browsing any page. It might have other effects but I would have to test it myself to find out.

The session isn't updated only by mChat. Every user calls the session update function on every page request. Doing that edit won't fix your issue. Think of it as turning the pain down to a degree that you might find bearable. I'd recommend looking for a cure instead. :P
User avatar
kasimi
Extension Customisations
Extension Customisations
Posts: 3800
Joined: Sat Sep 10, 2011 7:12 pm
Location: Germany
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by NicoBlog » Fri Mar 09, 2018 9:16 pm

While i agree my server shouldn't take that long to do the query, i don't think it's efficient to do UPDATE every single time a message is sent on a chatroom.
What board has a 30 minute session timeout? That would annoy anyone.
Personally i use 12 hs, i think i can safely disable this, is unlikely a user will chat 12hs without clicking anything.

Thank you!
NicoBlog
Registered User
Posts: 65
Joined: Sun Dec 01, 2013 5:18 pm
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by kasimi » Fri Mar 09, 2018 9:52 pm

Actually it updates the session not more than once a minute. The phpBB core session management takes care of that.

As I said, the session running out is the obvious effect this edit will have. There might be other unforeseeable things, so have an eye on it.
User avatar
kasimi
Extension Customisations
Extension Customisations
Posts: 3800
Joined: Sat Sep 10, 2011 7:12 pm
Location: Germany
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by kasimi » Sat Mar 10, 2018 10:08 am

There's a bug in phpBB that causes the session time to be updated every single AJAX request (in case of mChat we're talking about add/edit/delete requests) instead of just once a minute. Apply this fix before changing mChat code and see if that helps: https://github.com/phpbb/phpbb/pull/5157/files
User avatar
kasimi
Extension Customisations
Extension Customisations
Posts: 3800
Joined: Sat Sep 10, 2011 7:12 pm
Location: Germany
Contact:

Re: Slow queries spam: UPDATE phpbb_sessions SET session_tim

by NicoBlog » Sat Mar 10, 2018 1:31 pm

Wow thanks for looking further into it, the core itself was bugged. I knew something was up because there couldn't possible be so many UPDATE with so few users online. I'm using your modified session.php now.
NicoBlog
Registered User
Posts: 65
Joined: Sun Dec 01, 2013 5:18 pm
Contact:


cron