General developer forum

Hi Jason, thanks for the reply.

Our db server is shared too (virtually on VMware, but we're seeing performace improvements), but only between Moodle 2 and a bespoke ILP project. All our other web systems use another shared db server.

Likewise, our servers have never, ever seemed slow, although with the first full week of teaching nearly over we're just starting to see it being used properly.

Today I saw that the mysql-slow.log was logging these fairly concerning results, and constantly too:

# Query_time: 95.918481  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1316162439;
SELECT GET_LOCK('moodle2-mdl_-session-164029',120);

# Query_time: 96.041455  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1316162439;
SELECT GET_LOCK('moodle2-mdl_-session-164029',120);

96 seconds to perform any kind of db operation is a startlingly large time, although like I said earlier, nobody seems to be complaining of slowness or a lack of responsiveness. Guess I need to read the MySQL manual.

I'm still seeing major issues with locking.

Database: PostgreSQL v9.1
Moodle: 2.2.4+ (Build: 20120719)

I turned on statement logging for postgresql on a single forum page hit (attached the sql statement log with identifiable info removed). Between the time that the pg_advisory_lock is obtained and when it is released, there are 59 database queries (not counting postgresql metadata queries).

There is a 2 second window in which lock contention can easily happen. I've been able to do it via holding down refresh on a web browser. I've also been able to do it by clicking on a link to the forum repeatedly (simulating a frustrated user who might keep clicking on a link thinking that this will somehow get them to the page faster – it may seem absurd to a web dev to do that, but in real life, I've seen people do that far too often to simply discount it).

In production, we've seen this stop service to the entire website for up to the lock period. Shortening the lock timeout helps, but only turns one bigger (2-3 minute) outage into several mini-outages (10-15 seconds). Although it's easy to produce lock contention for a single user, I'm still unsure as to how it's translating to brining down the entire site (other than database connections getting filled). However, every one of the site wide outages will show several pg_advisory_lock timeouts immediately preceeding the recovery. I've looked to as many other causes as possible and keep coming back to this.

There's only so much we can solve at a systems level, making queries more efficient, trying to make the system handle lock contention somewhat gracefully, etc. But it keeps coming back to the fact that there are a lot of queries being executed during a lock. Do all of these really need to be during that phase?

I've scoured the forums for information on this, and it feels like it's not considered a very high priority problem (and/or that it's too hard to solve). I'm not a moodle developer myself, but I'm in charge of making sure the system is running properly, so I get the call when it goes down. That's why I've been looking into it. I've looked at later releases, and it doesn't look like many changes have gone into the session locking code. Is this being addressed in other ways? I see that later releases allow you to turn off session locking for guests and non-logged in users, which will certainly help, but we're seeing it happen with links that are only accessible once logged in.

One suggestion if there's absolutely no way to reduce the number of queries that take place during a lock would be to implement some sort of rate limiting within the framework. This is hard to do outside of the moodle code, as all the available tools I've seen can only really rate limit based on IP address, and if students are accessing the site from within an institution, most hits will come from a small number of IP addresses - rate limiting in that case would be too harsh. But once logged in, the site itself could effectively throw up an error page if it detects a single student going to a particular URL at a rate that would cause locking problems. Better to give them an error right away than to have them frustrated, clicking away, and making the problem worse.

So here's something that's admittedly a total hack... but I'm interested in knowing whether (besides the ugliness and location of the code) there's something fundamentally wrong with it. The assumptions are:

1. The majority of GET requests aren't going to need to write specialized data to the session.
2. Web users are far less likely to try and submit multiple POST requests at the same time than multiple GET requests (it's quite natural, for example to open a few tabs at once for a site). Of course someone actually trying to be malicious wouldn't restrict themselves to GET requests, but the problem I'm seeing is non-malicious use causing things to lock up.

Here's the code, which can be put in config.php (hey, I did warn that it was an ugly location):

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
  if (!preg_match('|^/lib/.*|', $_SERVER['REQUEST_URI']) && !preg_match('|^/theme/.*|', $_SERVER['REQUEST_URI']) && !preg_match('|^/login/.*|', $_SERVER['REQUEST_URI'])) {
    session_get_instance()->write_close();
  }
}

If you watch a statement log on a normal page with this in place, you'll see the update to mdl_sessions as the only query happening during the lock. All other queries execute after.

Now, I'd think a more integrated solution could use some sort of opt-out declaration at the page level instead of the ugly if-statement filtering. Even making it an opt-in sort of system where basic functionality is unchanged, but you could declare your page to be a "safe read" page, would be nice. I suppose you could argue that there's already an opt-in system: i.e. just use "session_get_instance()->write_close()". I'd argue that the declarative approach might encourage more widespread use and make it easier to manage.

I know that it's near impossible to ensure that independently developed plugins adhere to every "best practice", but is there a specific reason that the write_close() function isn't being used more liberally in the standard codebase? I can't see a reason not to do a write_close(), for example, on a simple forum view. The difference in responsiveness is pretty dramatic in my tests (I certainly can't lock it up using the same simple methods of holding down refresh or rapidly clicking a link).

I'm not trying to suggest that there's a trivial solution, and it would be great to hear specific examples of where the above breaks down (I'm sure it does). I am trying to spark a little more discussion on the basic problem (given the potential impact of site outages, I'm surprised I've only found a few forum posts and bug tickets about it), as I just don't think the fact that a perfect solution may be hard or impossible should prevent workable solutions, even if they're just provided as options.