ansaurus

Question

Mysql query staying in 'SENDING DATA' state for long time when using LEFT JOIN

Answer 1

A:

Does filtering the left join tables help to speed up the query?

John M 2010-09-16 19:34:28

Answer 2

A:

Main Idea

Are you using InnoDB? It looks like your buffer pool is only 8MB. That could easily be the problem, you're dealing with a lot of data and InnoDB doesn't have much memory. Can you bump the innodb_buffer_pool_size up? You'll have to restart MySQL, but I'm betting that would make a HUGE difference, even if you only give it 256 or 512MB.

Update: I see your storage engine and table format seem to default to MyISAM, so unless you specified otherwise this wouldn't apply. I wonder if the myisam_sort_buffer_size would help? We don't use MyISAM so I'm not familiar with tuning it.

Random Thought

I wonder if having having the primary key be alphanumeric (especially VARCHAR) has anything to do with it. I remember we had problems with performance on non-numeric primary keys, but that database dated from 4.0 or 4.1, so that may not apply (or ever have been true).

Secondary Idea

After the memory thing above, my best guess would be to give MySQL more hints. When I have a query that's running slow, I often find giving it more information helps it out. You have messageId/time indexes on each table. Maybe something more like this would work better:

select a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,
b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus
from inserted_history a left join submitted_history b on b.msgId = a.msgId
left join delivered_history c on a.msgId = c.msgId
where a.inTime between '2010-08-10 00:00:00' and '2010-08-010 23:59:59'
and a.systemId='ND_arber' AND c.inTime between b.inTime >= a.inTime
and c.inTime >= b.inTime

I'm guessing things get inserted into A, then B, then C. If you have better limits (say when something goes in A, it's always sent out and submitted within one day) add that information could help.

I wonder about this both because I've seen it help my query performance in some situations, but also because you have the data partitioned on the datetime. That may help the optimizer.

My other suggestion would be to run your query for a short amount of time, say 10 minutes instead of a full day, and make sure the results are right. Then try 30. Increase it and see when it falls off into "come back tomorrow" territory. That may tell you something.

MBCook 2010-09-16 19:50:33

Answer 3

A:

Would it be possible to alter the index of inserted_history,

systemId (systemId)

to be

systemId (systemId, inTime). Or add an additional index

My logic being that this should help to speed up the selection of the inserted_history (a) rows which forms the basis of the join.

The where clause "where a.inTime between '2010-08-10 00:00:00' and '2010-08-010 23:59:59' and a.systemId='ND_arber'" would all be selectable by index. At present, rows are selectable by systemId but then all those rows need to be scanned for the time.

Just as a matter of interest, how many records would there be (on average) for each system id. Also as msgid is not unique on its own, how many records (on average) in the other tables will have teh same msgid.

Jaydee 2010-09-21 12:39:03

Answer 4

A:

Your explain plan that you gave:

id , select_type , table , type , possible_keys      , key      , key_len  , ref                 , rows , Extra
1  , SIMPLE      , a     , ref  , systemId idx_time) , systemId , 14       , const              , 735310 , Using where
1  , SIMPLE      , b     , ref  , PRIMARY            , PRIMARY  , 66       , gwreports2.a.msgId , 2270405 ,
1  , SIMPLE      , c     , ref  , PRIMARY            , PRIMARY  , 66       , gwreports2.a.msgId , 2238701 ,

shows that you are hitting: 735310 * 2270405 * 2238701 = 3T rows!!!!!! Effectively your not using your indexes to their fullest potential.

How to interpret your 'explain plan': For every row in table 'a' (735310 ), you hit table 'b' 2270405 times. For every row you hit in table 'b', you hit table 'c' 2238701 times. As you can see, this is an exponential problem.

Yes, the 8MB of InnoDb Buffer space is small, but getting your explain plan down to xxxx * 1 * 1 will result in incredible speeds, even for 8MB of Buffer Space.

Given your Query:

SELECT a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus 
  FROM inserted_history a
  LEFT JOIN submitted_history b ON b.msgId = a.msgId  -- USES 1 column of PK
  LEFT JOIN delivered_history c ON a.msgId = c.msgId  -- USES 1 column of PK 
 WHERE a.inTime BETWEEN '2010-08-10 00:00:00' AND '2010-08-010 23:59:59' -- NO key 
   AND a.systemId='ND_arber' -- Uses non-unique PK

Here are the problems I see: A) Your _history tables are partitioned on the columns with 'Timestamp' datatype, YET you are NOT those columns in your JOIN/WHERE criteria. The engine must hit EVERY partition without that information. B) Access to submitted_history and delivered_history is using only 1 column of a 2-column PK. You are only getting partial benefit of the PK. Can you get more columns to be part of the JOIN? You must get the # of rows found for this table as close to '1' as possible.
C) msgID = varchar(64) and this is the 1st column of the PK for each table. Your Keys on each table are ** HUGE **!!
- Try to reduce the size of columns for the PK, or use different columns.

Your data patterns of the other keys shows that you have LOTS of disk/ram space tied up in non-PK keys.

Question 1) What does "Show Indexes FROM " (Link) for each of the tables report?? The column 'Cardinality' will show you how effective each of your keys really are. The smaller the cardinality is, the WORST/Less effective that index is. You want cardinality as close to "total rows" as possible for ideal performance.

Question 2) Can you re-factor the SQL such that the JOIN'd columns of each table are those with the highest cardinality for that table?

Question 3) Is the columns of 'timestamp' datatype really the best column for the partitioning? If your access patterns always use 'msgId', and msgId is the 1st column of the PK, then .

Question 4) Is msgId unique? My guess is yes, and the 2nd column of the PK is not really necessary.

Read up on Optimizing SQL (Link) and have the index cardinality reports of your tables. This is the path to figure out how to optimize an query. You want the 'rows' of the explain plan to be N * 1 * 1.

SIDE NOTE:InnoDb & MyISAM engines does NOT automatically update table cardinality for non-unique columns, the DBA needs to manually run 'Analyze Table' periodically to ensure its accuracy.

Good Luck.

J Jorgenson 2010-09-22 15:54:35

ansaurus

tags:

views:

answers:

Mysql query staying in 'SENDING DATA' state for long time when using LEFT JOIN

related questions