MySQL Performance: MySQL => Inserting many rows (more then 10000rows/s)

Members

Help

Home

Home » Performance » MySQL » Inserting many rows (more then 10000rows/s)

Show: Today's Messages :: Show Polls :: Message Navigator

Inserting many rows (more then 10000rows/s) [message #468]

Fri, 15 December 2006 08:29

nejman
Messages: 1
Registered: December 2006

Junior Member

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

Report message to a moderator

Re: Inserting many rows (more then 10000rows/s) [message #470 is a reply to message #468 ]

Sat, 16 December 2006 21:59

garyis
Messages: 2
Registered: December 2006

Junior Member

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

Report message to a moderator

Previous Topic:	Performance problem with > 1GB table and some queries
Next Topic:	Choosing InnoDB or NDB Engine?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 6 21:28:58 EDT 2009

Total time taken to generate the page: 0.00979 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.