Improved eval monitor sql queries

Review Request #24 - Created Nov. 14, 2008 and discarded

Information
Valentin Stanciu
infoarena
Reviewers
hackers 
Optimized monitor queries when there are are only filters related to `ia_job` table.
Did this by doing a simple query on the job table with filters and limits and then joining it with the other tables. This is a major improvement, as mysql will parse all entities in a table until it reaches the limits when there are left joins.
Going to last page of the eval monitor led to parsing the whole table, even if it's indexed or there's a limit in the query
We should test this with combinations of queries with limits and filters related only to job table or any table.
I only did tests to check if it works correctly, some statistics with improvements would be nice.
Valentin Stanciu
Valentin Stanciu
Bogdan-Cristian Tătăroiu
Valentin Stanciu
Valentin Stanciu
Bogdan-Cristian Tătăroiu
Valentin Stanciu
Review request changed

Change Summary:

Whitespace and fix

Diff:

Revision 5 (+160 -83)

Show changes

/trunk/common/db/job.php
Bogdan-Cristian Tătăroiu
Bogdan-Cristian Tătăroiu
Code is ok... but the bad news is at home I see no performance improvement :(

  1. "SELECT id, user_id, round_id, task_id, submit_time, compiler_id, status, score FROM ia_job ORDER BY id DESC LIMIT 200000,25" takes 4 seconds even though we have an index.

If you remove ORDER BY id DESC it takes 0.63...
  2. id is sorted ascending. We could do a select for elements and then reverse the result. Bad part is that we have to calculate the new limits.
So, "select ... order by id desc limit x, y" would transform to "select * from (select ... limit new_x, y) order by id desc". All we have to do is calculate new_x. I think we could even to an SQL function for this.
  3. We could also play around with sort buffers and see how they affect performance.
On your machine, can you please try increasing or decreasing mysql sort_buffer_size and see if it has any change in query time? Something to read on the subject: http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/ and we could also check out this http://wiki.mysqltuner.com/MySQLTuner
  4. When I was searching for this this morning I found this link: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

At comment 61 he says it's best to do a select of only the `id`s with all the order and limit clauses and then select all other data with a WHERE `id` IN(...). I tried this and it does work A LOT better for stuff like monitor?first_entry=100000 (0.35 to 3.84 reqs/s for a single thread).

I think if we also optimize the round=something the same way we did with user=something, this query will be faster for monitor?first_entry=100000&round=arhiva too.
  5. Yes, but these are only particular cases. Yes they cover a great range of queries, but the code would be even more if-spaghetti.
I'll try to do the fix I suggested without ordering and if it's still not fast enough we can also add the round special-treatment. Problem is I currently don't have time to do this. I think I'll do it on Thursday. Someone else (you :P) can implement it if we need this faster than that.

I also suggest limiting first element to 10000. Seriously now, who looks at eval monitor to see what is the 10000+ entry? We could let only admins access those ranges.
Loading...