MySQL Forums
Forum List  »  MyISAM

Re: is read block write?
Posted by: Rick James
Date: October 12, 2011 06:05AM

The _current_ villain is probably the query that is not "Locked":
> select t.id,t.pubDate,t.status,t.type,t.product,t.price,t.company,t.pic_name1 from trade as t where

Let's see the rest of it (SHOW FULL PROCESSLIST), plus
* SHOW CREATE TABLE t\G -- engine, indexes
* SHOW TABLE STATUS LIKE 't'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

The hope is that we can optimize that query so that it does not cause the sluggishness. The solution may be as simple as adding a particular INDEX. But I need all of the above information to see which of several solutions to apply.

Why so much locking?
With MyISAM (and this smells like MyISAM), an INSERT (from the replication stream in your case) will wait for a long-running SELECT to finish. If it did not wait, the SELECT might stumble over the changes that are happening in the data file because of the INSERT. Other SELECTs then see that the INSERT is waiting to get at the table, so they wait for it to finish.

The replication stream is exempt from the "readonly" restriction.

Some notes about the PROCESSLIST:
* The two "system_user" threads have to do with replication.
* 26717 seconds on the Locked INSERT -- this is the number of seconds since the "sql thread" started, not how long the INSERT has been locked. (That information is not available.)
* All the relevant threads are hitting the same table: trade_lead.trade
* The blocking SELECT has been running only 1 second, but that does not prove anything. Maybe you grabbed the PROCESSLIST early in the SELECT. Maybe this SELECT is not the real villain. Maybe this SELECT is run thousands of times a minute.

If possible, "batch" your INSERTs; that will cut down significantly on contention.
INSERT INTO trade (...) VALUES (...), (...), (...), ...;
I recommend (when practical) to do 100 rows at a time. This may speed up the INSERTs by 10x and eliminate 99% of the locking that interferes with the SELECTs. Going beyond 100 rows is getting into 'diminishing returns', and can even be counter-productive in a replication environment.

Options: ReplyQuote


Subject
Views
Written By
Posted
3592
October 10, 2011 01:02AM
Re: is read block write?
2142
October 12, 2011 06:05AM
1684
October 18, 2011 11:50PM
1718
October 20, 2011 10:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.