Speed trouble found, perhaps?

[Twin Rose]

I think I found what it is that causes occasional slowdown of EN World.

This, of course, is the search feature if more than 1 or 2 people are doing searches at the same time. If I do a search for "All time" in "All categories" and run benchmarks they are 5 to 20 times slower until my search is complete if multiples are doing it.

I'm seeing what I can do to optimize the database for searches.

Otherwise, the randomness of it has me really stumped. The combination of factors - multiple people doing searches simultaneously - could account for it, and why it sometimes happens with few people on and sometimes doesn't happen when lots are on. Investigation continues, but wanted to keep everyone informed

 

[Nyaricus]

I've found En World to be really fast sometimes, and at others to be abomidably (sp?) slow to load. The last 24 hours have been the most trying in months, perhaps for me.

Just saying Thansk for all the hard work TR

 

[Twin Rose]

I've found En World to be really fast sometimes, and at others to be abomidably (sp?) slow to load. The last 24 hours have been the most trying in months, perhaps for me.

Just saying Thansk for all the hard work TR

The last 24 hours? There's been a couple laggy bursts but for the most part it's been okay. What seems to be the problem WAS related to searches, but very difficult to explain. I think I got it.

 

[nerfherder]

If you are searching for all data and then ordering it by date, so you may be doing a table scan. I believe you have a pair of disks in a RAID 1 configuration (i.e. mirrored) so that will hit one of the disks hard. This may then be followed by a sort on disk (if a lot of data is retrieved), this time hitting both disks hard. During a table scan, other queries will be able to use the other disk, but during a disk sort both disks will be being written to, slowing other queries dramatically.

You might want to do EXPLAIN on the SQL to get the query execution plan to see whether it is using any indexes (especially on date - you ideally want to be able to just retrieve the most recent n number, not all of them), and whether it is doing filesort (i.e. sorting on disk).

There's a bit more info here: http://mysql.com/doc/refman/5.1/en/order-by-optimization.html

Cheers,
Liam

P.S. feel free to send me the SQL, table and index definitions and SQL EXPLAIN output by email to nerfherder67 at hotmail dot com. I'm more an Oracle expert, but I'm sure MySQL can't be too different.