Inserting many rows (more then 10000rows/s)

MySQL & MariaDB Other MySQL® Questions
1

Hello,
I created application that continuosly store about 10000 rows/sec. Each row has fixed length (12xinteger). I don’t use
indexes. My server load is about 1.6
(2xIntel Xeon CPU 3.20GHz, 2GB RAM, SCSI disks). Application rotate tables every hour (start storing to the new table). Old tables are removed. I have performance problem with this type of application.
Now, I’m using prepared statement with multivalues delayed inserts
(about 2000 values per 1 insert). If I compared prepared statement and full insert query it is almost same. Can you suggest me some tips, how better/quickly store many rows?

My MySQL configuration:
key_buffer = 550M
bulk_insert_buffer_size = 32M
max_allowed_packet = 16M
table_cache = 2048
sort_buffer = 32M
record_buffer = 16M
read_buffer = 16M
read_rnd_buffer = 16M
tmp_table_size = 150M
max_heap_table_size = 150M

I’m not using log or log-bin…

Many thanks,
Jan

2

with high amount of inserts/sec youĺl be limited by the disc I/O speed, this will also cause load to increase.

Setting innodb_flush_method to O_DIRECT made a big difference to me plus you could also try innodb_flush_log_at_trx_commit=0