MySQL Forums
Forum List  »  MyISAM

Re: Randomly slow Insert / Update on small table
Posted by: Rick James
Date: October 30, 2009 10:07AM

innodb_log_files are not the "binlog"; rather they are for transactions -- hanging onto pending changes, being able to ROLLBACK, etc. InnoDB pingpongs between 2 innodb_log_files (normally); it needs each one to be big enough to hold something (I don't know the details) related to the pending transactions. By pingpong, I mean that it is busy flushing one while loading the other. It seems like having 3 could better handle bursty situations. Now, it seems like having 48 is carrying that idea too far.

700 qps -- that is in the range of "busy". Depending on a lot of other things, you may or may not be pushing the limits of your hardware and application. In particular, having 6 indexes, many of them "random", involves up to 6 I/O operations for each 1 row inserted. Caching helps, but as the table gets too big, those I/Os become real, and you will have trouble maintaining 700 qps.

I can think of only one query for which an index on an ENUM is useful:
SELECT mail_route_type, COUNT(*) AS ct
      FROM domain
      GROUP BY mail_route_type;
This would run entirely in the index and be much faster than without the index. But it is probably not worth it.

Lack of selectivity on indexes -- that is not necessarily a good reason to get rid of an INDEX. Show us some of the other SELECTs/UPDATEs.

Let's look, for example, at mail_route_type. It is an ENUM (hopefully not a SET) with only 4 values. INDEX(mail_route_type) is probably totally useless. But
INDEX(mail_route_type, obsolete)
would be an excellent index for the query
SELECT ... WHERE mail_route_type = 'uucp' AND obsolete > 100
This would be less useful for that query:
INDEX(obsolete, mail_route_type)
In fact, that would possibly be no better than
INDEX(obsolete)
Why? Well, it has to do with doing an equality test (=) versus a range (<) test -- fields with = should come first in the index.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Randomly slow Insert / Update on small table
3860
October 30, 2009 10:07AM


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.