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

[Srikanth Mamidi]

Thanks again.

>The gain from updates typically comes from having smaller BTREE on the active partition(s) which allows for a lot better fit in memory.

Yes, we do bulk inserts in batches of 1000 and in theory as you have said the gain should be because of smaller BTREE to update for a partition, but is it possible that in case of bulk updates if the updates cover more or less all the partitions(say we decided 10 partitions), then wouldn't it be an additional overhead for mysql to figure out the apt partition for each new entry and then do the inserts across all the partitions? This could actually be a pretty usual scenario as we dont have control on the 'userID' of the updates to be done.

Also, I have tried increasing the innodb_log_file size to 100M from 10M earlier.
In our application, we have 2 java programs which do the insert/updates to the table in parallel, we need 2 programs because the data sources are different. So, in this case after increasing the log size, I see frequent occurrences of deadlock while trying to insert , I suppose the reason for might be both programs trying to
update the table for same userID which could actually be a valid case for my application.

Is there any strategy to avoid these deadlocks ? like changing the batch size of bulk updates of the queries or etc.? And in this case of deadlocks , does partitioning help again as locks would be on smaller sized partitions rather on the whole table and we can decrease the probability of deadlocks. Is this explanation actually true because I have not come across anywhere from the mysql documentation which talks about avoiding deadlocks as one of the advantages of partitioning?