To begin: I’ve been a developer for quite some time, and although I wouldn’t consider myself a “MySQL Expert” on a resume, I am very confident in my abilities to do decent MySQL performance tuning.
In this case, however, either I’m over-stressed and looking over something, or perhaps I’m truly out of my league and just don’t realize it.
A company I’m working with has an installation of SugarCRM, which in the past has had some “custom” tabs/features/modules" added to it. Those custom additions are the source of queries that tend to run between 8 and 30 seconds a peice.
The queries, unfortunately, are built dynamically - so I’m having a hard time tracking down the best way to handle this situation, although they are mostly consistent.
If I’m not mistaken, the version of MySQL is a stable 4.1, on SuSE linux.
I’ll upload the whole my.cnf if needed, but don’t have it at the moment. Though I will mention that:
The Query Cache is enabled, and running as effectively as possible. Since the slowest queries are dynamic, with changing search terms, the Query Cache doesn’t keep them around anyway, since they’re mostly One-offs.
The key-buffers are being used consistently, especially during these SELECT queries.
The query times are not being inflated by LOCKs… these are all SELECT Queries, and I’ve watched their status live several times, and there have been no LOCKs.
Also: YES, I am aware that there are some OBVIOUSLY poor structural issues. These are in place in a system that has been running for a very long time, and the point of my question is to see if there is anyone with a knowledge beyond my own that can help me make this work better within my current contraints. Knowledgeable answers that say “It cannot be done” are perfectly acceptable!
I’ll be glad to supply any other information.
Aside from that, here’s one particular problem query example:
SELECT users.user_name assigned_user_name, registration.FIELD001 parent_name, registration_task.status status,registration_task.date_modified date_modified,registration_task.date_due date_due, registration.FIELD240 assigned_wf,if(LENGTH(registration_task.description)>0,1,0) has_description,registration_task.* FROM registration_task LEFT JOIN users ON registration_task.assigned_user_id=users.id LEFT JOIN registration ON registration_task.parent_id=registration.id where (registration.FIELD001 LIKE ‘schreckengost%’) AND registration_task.deleted=0 ORDER BY date_due asc LIMIT 0,20;
It’s corresponding info from the Slow Query Log:
Query_time: 31 Lock_time: 0 Rows_sent: 20 Rows_examined: 1904929
an EXPLAIN Reveals:
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE registration_task ref idx_reg_task_p idx_reg_task_p 1 const 464926 Using where; Using filesort1 SIMPLE users eq_ref PRIMARY PRIMARY 36 sugarcrm401.registration_task.assigned_user_id 1 1 SIMPLE registration eq_ref PRIMARY PRIMARY 8 sugarcrm401.registration_task.parent_id 1 Using where
The table structures/create tables statements are probably longer than allowable in the forum…
They are available at pastebin: http://pastebin.com/m284e3e79
If not obvious, I should add that what I’d like to see are Faster Query Times.
The machine is a Quadcore server with 6 Gigs of RAM. I don’t have exact specs, but that should say something, at the least.