Someone decided to put
Dez's latest blog post on
reddit. Unfortunatly the mysql server was having trouble keeping up, resulting in a slow enough response for visitors. This has happened twice before (its a popular blog). Anyway the problem is from a bit of sloppy coding in
serendipity, and one quick change later the site is back running at full speed.
The problem results from a table which holds session IDs and statistical information called
serendipity_visitors. Every action a vistor took was recorded in this table, and the row relivant to the session ID is updated and a new row is created for every page view. Problem is though, that the session IDs were not indexed in the MySQL database. This forces the mysql server to do a full table scan, and look at every single row in the table. This is the absolute worst case when it comes to databases. Worse still, if your doing an insert or update with the MyISAM database engine, it locks the table until the operation is finished (blocking everyone else). So when a site gets very popular, these locks and full table scans compound themselves to slow it down to a crawl. Also lots of visitors creates lots more rows, making the time for a full table scan longer (400,000 rows in this case).
Simply creating an index on the session ID field allowed for a more optimised search algorithm to be used by the mysql server, so full table scans are avoided and the time a table is locked is kept to a minimium. The effect of creating the index was instantaneous and the site came back to full speed again.
So the moral of the story is: When your creating a table structure, think carefully about when to use indexes.
