Home » Performance » MySQL » Performance Problem, Not Basic, I'm knowledgeable
 Performance Problem, Not Basic, I'm knowledgeable [message #3324] |
Tue, 22 July 2008 09:46  |
 anon_login_001
Messages: 4
Registered: July 2008
Location: Memphis |
Junior Member |
|
|
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 Extra
1 SIMPLE registration_task ref idx_reg_task_p idx_reg_task_p 1 const 464926 Using where; Using filesort
1 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.
|
|
| | | |
| Re: Performance Problem, Not Basic, I'm knowledgeable [message #3338 is a reply to message #3324 ] |
Wed, 23 July 2008 09:37  |
anon_login_001
Messages: 4
Registered: July 2008
Location: Memphis |
Junior Member |
|
|
FIELD001 is second in an index called 'mbr_name'.
---
I have since indexed FIELD001 by itself, as well as swapped the WHERE around, as suggested by malonso.
EXPLAIN says:
id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |registration_task |ref |idx_reg_task_p,parent_id |idx_reg_task_p |1 |const |157693 |Using where; Using filesort
1 |SIMPLE |registration |eq_ref |PRIMARY,mbr_name,FIELD001 |PRIMARY |8 |sugarcrm401.registration_task.parent_id |1 |Using where
1 |SIMPLE |users |eq_ref |PRIMARY |PRIMARY |36 |sugarcrm401.registration_task.assigned_user_id |1 |
I think, and please suggest if I'm on/off the target:
The problem lies in the fact that I'm initially selecting FROM the *registration_task* table, but the only condition in place to round that down is *registration_task.deleted=0* (for that particular table), of which there are so many records.
So then MySQL *must* load those records and then filter down from there, correct? So the big slowdown is that scanning (even with an index) through all of those results, and then the SORT, depending on what column they chose to sort by
(Using filesort).
Parts of Sort By are normally done on *registration* table, which is joined by an ID to users, which is joined by an ID itself... as shown in the EXPLAIN. So that to me looks like a fairly effecient join... I just have no way of getting down the number of records that *registration_task* looks at, it seems.
Do I still look stuck?
|
|
| |
| Re: Performance Problem, Not Basic, I'm knowledgeable [message #3346 is a reply to message #3338 ] |
Fri, 25 July 2008 00:44 |
tom.krouper
Messages: 2
Registered: July 2008
Location: St. Louis |
Junior Member |
|
|
|
My guess is that deleted is a 1 or a 0. You are going to run into an issue if that is more than MySQL's breaking point to not use the index. (I think I heard 30% at some point.) So, if you have 100 records and 35 (or more) of them are 0, and you do a select for 0, the system is just going to do a full table scan. That could certainly slow down the select. I'll keep looking to see if I can figure out anything else from the information.
|
|
|
Goto Forum:
Current Time: Sun Sep 7 14:45:25 EDT 2008
Total time taken to generate the page: 0.01157 seconds |
Members
Search
Help
Register
Login
Home
