Re: How to improve performance in a table with 70 million rows

[Rick James]

> RAM size is 1.7GB
Yikes; that's tiny these days.
buffer_pool of 1G might be too big.
Don't let mysql swap -- that would be worse than having a smaller buffer_pool.

See also
http://forums.mysql.com/read.php?106,564229,564557

> `friends` int(10) unsigned NOT NULL,
Some people have a billion friends? Consider MEDIUMINT UNSIGNED (16M max) or...
That would shrink the data some. Smaller --> Faster (when I/O is involved).

How big is a "transaction"? (BEGIN ... COMMIT)

> select * from user_data where user_name in(<uname1>,<uname2>...);
> select * from user_data where userID =<userid>;
You can use partition pruning for one or the other, but not both. PARTITINOing buys you no performance for either of them.

> Whenever you insert a record, the partitioning engine locks the entire table.
Aftab, he is using InnoDB. So, I think it will _open_ each partition, but not _lock_ each one.

Are you doing INSERTs in multiple threads? If so, locking is an issue. Replication is involved, too? There is no parallelism on the Slave.

> bulk inserts in batches of 1000
Good number. Do you then COMMIT? Are multiple threads doing such?

innodb_log_file size matters if the transactions are large, or if you have big blobs (which you don't seem to have).

> we have 2 java programs
Just 2? 2 might not be much better than 1, and is possibly not as good as several.

Batch inserts with multiple keys are likely to get deadlocks. A partial fix is to sort by a key before building the INSERT. Preferably, use a PRIMARY or UNIQUE key.

> might be both programs trying to update the table for same userID
More likely it is "gap" locking.

> like changing the batch size of bulk updates
Changing to 1 would work. But performance would be 10 times as slow.

> does partitioning help again
No.