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

[Rick James]

> Found in some articles that its good to have buffer_pool size around 70% of RAM.
That is "70% of _available_ RAM". In 1.7GB, a bunch is taken up by other things.

> it would take around half a day which cant be afforded
You may be forced to do this or something equally painful.

> INSERT ... ON DUPLICATE KEY
On the surface, this is a good statement, even for batching INSERTs. However, there are several subtle things under the covers.
For one thing, it will "burn" AUTO_INCREMENT values. That is, if you have 1000 rows in the statement, but 200 are duplicates, all 1000 AUTO_INCREMENT values will be allocated, not just 800.
Please provide more details; perhaps I can see other issues.

Another issue...
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
(I don't know whether it applies in your case.)

Are the two Java threads hitting the same table? If not, I don't understand how there could be deadlocks.