MySQL Forums
Forum List  »  Performance

Re: Need mysql Configuration Parameters that affects Insertion rate
Posted by: Rick James
Date: January 07, 2014 12:53PM

Inserting 1000 rows/second is easily achievable. 10K/sec is more challenging. 100K may be possible with significant constraints (on indexing, how you batch, tunables, etc). What rate do you need?

You say "batch"; elaborate. An INSERT statement with 100-1000 rows is about optimal. If you mean LOAD DATA, how many rows at a time? One client? Many clients at the same time?

innodb_buffer_pool_size should be about 70% of available RAM.

Please provide SHOW CREATE TABLE. Lots of indexes is usually the 'wrong' thing to do. "Random" indexes (UUIDs, MD5s, etc) are especially bad, and can effectively limit the insertion rate to 100/sec when that index becomes significantly bigger than the buffer_pool.

How big will the table eventually be? I ask because your "high insertion rate" implies a Data Warehouse application. That implies this may be the first of many performance issues. Let's discuss them all at once.

What are the values of sync_binlog and innodb_file_per_table ?

What is your disk subsystem like? RAID with some form of striping, and with a hardware write cache is highly desirable.

Is replication involved? There is things to watch for to avoid the Slave(s) from bogging down. What is the binlog_format?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need mysql Configuration Parameters that affects Insertion rate
858
January 07, 2014 12:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.