MySQL Forums
Forum List  »  Performance

Re: How to get acceptable/repeatable performance on huge table
Posted by: Rick James
Date: September 01, 2012 06:39PM

> the table is frequently updated (>1000 times/second)

That means that the Query Cache is _useless_ because it would be purged >1000 times/second. You would be better off turning it off:
query_cache_size=0
query_cache_type=OFF

PRIMARY KEY(userUid, aid) -- This is "clustered" with the data in InnoDB. That is, to use that index, the query must read the data blocks.

A secondary INDEX(aid, userUid) (or even simply INDEX(aid), which happens to be identical in InnoDB)):
* would be stored separately
* would be smaller -- I would guess 8GB.
* would let that particular query run faster
* would slow down inserts -- because of that index would need updating (eventually). If userUid is "random" during the 1000 inserts/sec, this could be a serious I/O performance problem.

What is innodb_buffer_pool_size? How much RAM do you have? If you have at least 16GB of RAM and have the buffer_pool set to 11G, then the INSERTs would not slow down much -- just a little CPU, very little extra I/O.

You mentioned adId < 25000 -- is it always that value? What percentage of the table matches that value?

I expect _no_ difference between
select adId, count(userUId) from UserVisits where adId < 25000 group by adId;
and
select adId, count(*) from UserVisits where adId < 25000 group by adId;
I prefer the latter. If you need "count(distinct userUid), that's a different matter.

InnoDB, under heavy use, is known for having periodic slowdowns -- it seemingly freezes to flush the buffer pool (or something). Xtradb fixes that. (Maybe 5.6 also fixes it??)

>> >Mysql uses only the index anyway, so the other column sizes should be irrelevant(?)
Aftab, this statement is false for a PRIMARY KEY because it is clustered. The statement is true for secondary keys. EXPLAIN is being less than precise when it says "Using index" on an InnoDB PRIMARY KEY. Logically it only needs the KEY, but physically the entire row is in that key.

Are you INSERTing >1000/sec? Or UPDATEing? If you are inserting that fast, are you purging data ever? If so, I highly recommend PARTITION. If not, when will you run out of disk space? (And my comment about 16GB of ram is only temporary!)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to get acceptable/repeatable performance on huge table
1005
September 01, 2012 06:39PM


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.