Database server slows down because of moodle session renewals and checks

Database server slows down because of moodle session renewals and checks

by jerrett fowler -
Number of replies: 12

Hello,

I made a previous post about a query that runs a couple of times every hour dealing with 'siteidentifier' slowing down the mySQL server rendering all moodle installs unable to communicate with the DB. This brings down all of our sites.

We have 130+ websites that communicate with a very large mySQL database (32 cores, 128 gigs ram, 5TB storage), and our DB settings are perfect for a server of that size. It should not have any issues with handling any set of queries that are being thrown at it and most of the time things are great. 

However, after investigating quite a bit I noticed a 3 or 4 stage set of queries dealing with mdl_sessions. First is a request for a user lock that looks like this:

<pid> <dbusername> <connection source ip> <dbname> Query <time elapsed> User lock SELECT GET_LOCK('<dbname>-mdl_-session-68804', 120)

Then it will do this:

<pid> <dbusername> <connection source ip> <dbname> Query <time elapsed> Opening Tables SELECT id FROM mdl_sessions WHERE sid = '<sid>';

Then this:

<pid> <dbusername> <connection source ip> <dbname> Query <time elapsed> query end UPDATE mdl_sessions SET state = '0',sid = '<sid>',userid = '<uid>',timecreated = '1412669850',timemodified = '1412674008',firstip = '<user ip>',lastip = '<user ip>',sessdata = '' WHERE id='<session id pulled from previous query>'

BTW, the "sessdata" this query can be like 1000-2000 lines, which is massive and I'm unsure if this is actually necessary.

Anyway, these queries will take about 5 minutes to perform, where the database is flooded with these requests (hundreds of them) and then the database becomes unresponsive. These are all very slow queries.

Things I've done to fix:

  1. reschedule crons to give reasonable time for completion and no overlap and ensure cronlocks are being used
  2. move intensive crons/queries to run during night instead of during business hours
  3. moved sessions handling from DB to moodledata (I thought this would help but it didn't, and it still talks to the DB for the rest of the session data even though it's no longer doing the "USER LOCK" request)
  4. Confirmed that the database settings are ok.

Even after all this, it's still coming down from 02-07 after the hour and 27-32 after the hour.

Any help would be appreciated. This is a beast of a problem.

Average of ratings: Useful (2)
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -

Anyone got any ideas?

Average of ratings: -
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by Darko Miletić -

Most likely it is time to start scaling your db server. One is no longer enough. I would strongly suggest to contact Percona as they are good at optimizing mysql setups.

Average of ratings: -
In reply to Darko Miletić

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -

I've had DBA's look at it and they are blaming the application (Moodle) for the issues we're seeing. They tell me that there is no reason why our DB can't handle this sort of traffic. 

I'm getting really conflicting information.

Average of ratings: -
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by James McLean -

Move your sessions to Memcached (but not the same memcached instance that stores MUC data).

Average of ratings: -
In reply to James McLean

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -

Hypothetically, if I build a memcached server for handling sessions, what would be the system requirements for this? Is there a moodle page outlining the process?

Average of ratings: -
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by Russell Smith -

Hi,

More RAM is better, you need enough memory to handle all your sessions.  And if it overflows, people get logged out.  Maybe sum(sessdata) from mdl_session * 2 or 3 would be a good ram size for memcache.

Basically all you do is install memcache, configure the amount of RAM it uses and start it.  You can get fancier configurations but that's the place to start.

Regards

Russell

Average of ratings: -
In reply to Russell Smith

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -
Currently we have 4 web servers talking to this very large DB/NAS server. It will hold moodledata in a separate directory that's being shared to the web servers. Could I install memcached on the database server? or would it be best to have a separate server altogether for memcached?
Average of ratings: -
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by Russell Smith -

I would recommend a different server as db is already under load.  I say that as I work with VM's and spinning up another is easy.

Memcache will put your sockets and network bandwidth under pressure, cpu will sit idle and memory usage will be fixed.


So you are best to decide, maybe the db server is a good place for a trial and measure the tradeoffs of leaving it there. You can then scale to a separate server.

Average of ratings: Useful (1)
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by Russell Smith -

Hi,

There has been a reasonable amount of change in the session handling in the last few versions.  The advice that could be offered is vastly different depending on the version of Moodle you are running.  What version is it?

Thanks

Russell

Average of ratings: -
In reply to Russell Smith

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -

The vast majority of them are 2.6.2

Average of ratings: -
In reply to jerrett fowler

Re: Database server slows down because of moodle session renewals and checks

by Russell Smith -

I'm still trying to find out the implications of adjusting this setting, but I suspect it's relatively safe.


$CFG->session_update_timemodified_frequency = 20; 

That is the default in seconds to update the table with session update information.  If your session is 2 hours, my current understanding is the above is the amount of error you could have in session overrun if the user continues to use the system after their session is supposed to have expired.  If that is of little concern to you, you could try a figure of 300 or 600 and see what it does to the load on the server.  Depending on the timeout risk, you could bump it up to 30-45 minutes.  If a user gets to 1.5 hours, then doesn't update the session even though the user starts using it.  So the longer update time basically puts the session end window into a state of real session_timeout - session_modified_update_frequency.  Depending on your use-case you may wish to just increase the update frequency and the session timeout by the same amount.  It will only produce a small set of cases where the user would stay logged in with the longer session timeout.  If I thought harder about it, I could probably work that out.  But that's not for now.

Looking at the code says that even though mdl_session is updated in this context it shouldn't be obtaining locks or writing session data.

How did you go about changing the session, I think it needs to be done in config.php now?  But I'm not sure as I'm not currently running a production 2.6 or 2.7.

Average of ratings: -
In reply to Russell Smith

Re: Database server slows down because of moodle session renewals and checks

by jerrett fowler -

There's a setting under Site Administration > Server > Session Handling

It allows you to pick where sessions are managed, either filesystem or DB, however if you have memcached configured, then memcached would be an option there.

Average of ratings: -