As many of you are no doubt aware ;) drupal.org has been pretty slow to respond lately. And while there has been a lot of work going on to resolve this problem by various members of the infrastructure team, there has not been a lot of information in terms of forum posts and such to keep the community at large informed.

So here's to changing all of that. :)

NOTE: Most of this content comes from a recent e-mail to the infrastructure mailing list (requires registration) from Eric Searcy of OSU Open Source Lab (OSL), drupal.org's generous hosting provider.

What's the deal?

The root of the problem that our database server is overloaded. This is causing table locks:

Table locking is also disadvantageous under the following scenario:
* A client issues a SELECT that takes a long time to run.
* Another client then issues an UPDATE on the same table.
This client waits until the SELECT is finished.
* Another client issues another SELECT statement on the same
table. Because UPDATE has higher priority than SELECT, this
SELECT waits for the UPDATE to finish, and for the first
SELECT to finish.

This is due, at least in part, to the massive size of the drupal.org's database, which houses over 6 years' worth of content. The most expensive query is the "my recent posts" page, which is also (of course) one of our most popular pages.

What's been done so far?

1. OSL has performed some database server optimizations, which have helped decrease the load some.
2. A caching proxy (Squid) has been installed to help divert some read-only DB traffic by caching frequently accessed pages like the home page and RSS feeds. This saves about half the hits to the database.
3. Installed a variation of the move users.access into its own table patch, to remove user table locking.

What are we doing about it?

1. OSL is working on moving drupal.org over to a new database cluster with more RAM that can run more concurrent threads. Currently, there are some issues with character encoding and performance.
2. We are pursuing changing over drupal.org's tables to use InnoDB rather than MyISAM. This allows us to do *row* level locking rather than *table* level locking.
3. Working on trying to improve the tracker query. See below.

How can I help?

1. Donate to the Drupal Association, which gives us more resources to throw hardware at the problem.
2. Help test the new database setup on scratch.drupal.org by poking around, doing some of your normal stuff, and reporting any errors to the infrastructure issue queue (remember to search first!).
3. Join efforts to try to improve tracker query performance and help get a patch into core for Drupal 6.
4. Join efforts to move users.access into its own table and help get a patch into core for Drupal 6.
5. If you know a lot about tuning servers and databases and whatnot, join the infrastructure mailing list and lend your expertise.