Partition or not ?

[Srikanth Mamidi]

Hi ,

I have a user table for my application with large data , currently around 70 million entries . We want to optimize the queries,particularly INSERTs for the table under the following assumptions:

- Consider the scalability i.e. growing of number of users
- Frequent updates to the table (inserts or updates) at rate of about 1000/second.

The following is the table DDL
CREATE TABLE `user_data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userID` int(10) unsigned NOT NULL,
`userName` varchar(45) DEFAULT NULL,
...............................
...............................
PRIMARY KEY (`userID`),
KEY `id_idx` (`id`),
KEY `userName_idx` (`userName`) USING BTREE) ENGINE = InnoDB;

As can be seen above, we already have primary key and indexes for the columns on which most of our select queries are based i.e. userID and userName columns.

Our main pain point is INSERTs as we face a bottleneck during inserts and I want to know if partitioning would help in optimizing the INSERTS/UPDATES, also we feel partitioning would be helpful in future to manage the large data , if so what type of partitioning would be helpful?

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

Regards
Srikanth



Edited 1 time(s). Last edit at 08/07/2012 11:10PM by Srikanth Mamidi.