I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections inserting data in the same table at the same time.
In one of my table, I'm doing requests like : INSERT INTO ... VALUES <1000 tuples> ON DUPLICATE KEY UPDATE < do some basic operation on columns which are not in the PK> . This table does not have any index, it only has on PK on 2 columns. The row format is fixed (only fixed size column). The table is using MyISAM, and has about 3 million rows (The total size of the table is about 400Mb, and the PK takes about 40Mb.)
Most of the time, the UPDATE part of the query is applied, since we may have a lot of lines already existing in the DB in our queries.
My problem is that doing an insert for 10000 rows take about 10s, which is a real problem for my application.
I tried to OPTIMIZE the table but it didn't change anything.
Here are the config parameters I changed:
key_buffer_size = 2G
max_allowed_packet = 64M
table_open_cache = 256
Binary logs are disabled (it's not
I put this big key_buffer_size since other tables might be using it too.
So, do you guys have any suggestion about how to fix that problem ?
If you need more infos, plz tell me :)
Thanks !
Edit: I'm using JDBC to run my queries, is there something specific to my case to enable in jdbc parameters ?