MySQL Forums
Forum List  »  Partitioning

Re: Partition or not ?
Posted by: Rick James
Date: August 09, 2012 08:04PM

> PRIMARY KEY (`userID`), -- INT
> KEY `id_idx` (`id`), -- AUTO_INCREMENT INT

Do not create an AUTO_INCREMENT when you have a perfectly good "natural" PK.
DROP COLUMN id

> which most of our select queries are based i.e. userID and userName columns.
To vague. Please provide actual sample queries.

> NOTE: There are also couple of INSERT and UPDATE triggers for the above table.

They could be costly. Especially at 1000 per second. Think about getting rid of the TRIGGERs.

Back to your question.... PARTITIONing, by itself, does not provide any performance gain. Certain categories of SELECTs will run a lot faster with PARTITIONing; others won't. Let's see the SELECTs. Also, the UPDATES, since they will have a WHERE clause that may benefit from PARTITIONing.

> we face a bottleneck during inserts

You have 3 keys. That means 3 things are updated for each row inserted. I suggest how to get rid of one of them. I assume the user_name and the userid are both jumping around? That would mean two 'random' updates to disk.

Normal disk drives can sustain about 100 disk hits per second. That limits your INSERT rate to 50 rows/sec. Is that good enough?

If it is not good enough, or you are not getting 50, then
* How big (GB) will the table become?
* How much RAM do you have?
* Have you tuned innodb_buffer_pool_size (see http://mysql.rjweb.org/doc.php/memory)
* How big is your hardware budget? (RAID striping or SSDs)

Back to PARTITIONing... Assuming the buffer_pool cannot be big enough, then we may have to go back to your original 3-key design. And PARTITION BY RANGE(id). This would lead to all new rows going into the 'last' partition. Then the 2 'random' indexes for that PARTITION might fit into the available buffer_pool.

You would need a maintenance script to periodically add a new partition.

The drawback is that any WHERE clause on userid or user_name would have to check _every_ partition. That is, the SELECTs and UPDATEs would be slowed down noticeably. And, alas, put stress on the buffer_pool.

So, we need to look at the big picture of when the INSERTs, SELECTs, etc, come. (For example, a daily batch of INSERTs is quite different than continual INSERTs.)

Options: ReplyQuote


Subject
Views
Written By
Posted
2379
August 07, 2012 12:58AM
Re: Partition or not ?
1478
August 09, 2012 08:04PM


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.