What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

Question

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating partitions (dropping old ones and creating new ones).

What server configuration options would you put in your .my.conf file to ensure that the fastest possible insertion into this table was possible at all times?

Any other tips would also be appreciated.

EDIT: By 'fastest possible' i mean best possible insert speed and being sustained once it contains 100's of millions of rows.

Answer 1

There are two major things you can change. One involves the table and one involves a config setting.

TABLE CHANGE

You can increase SELECT and INSERT speed by changing the MyISAM table's row format to FIXED. In order to do do, you would run

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

There is a drawback to this: This could potentially increase the table size 80-100%

I have discussed changing MyISAM row format before

• May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
• Jul 06, 2012 : MySQL table with 100,000 records queried often
• Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

CONFIG SETTING

MyISAM supports a special protocol called a concurrent insert. It works by appending new rows to a MyISAM as long as there are no gaps as a result of DELETEs.

Here is some good news: you can disable the gap check for concurrent insert

[mysqld]
concurrent_insert = 2

According to the MySQL Documentation on concurrent_insert, here is what happens for value 2:

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

If the table is being heavily read, new INSERTs just get appended. When you are the only DB connection with a file handle to the table, then a normal gap check is done.

See the MySQL Documentation on Concurrent Inserts

EPILOGUE

These two changes combined may produce surprising performance results. Why? Using ROW_FORMAT=Dynamic would trigger a search for a gap large enough to hold a new row. If the table has ROW_FORMAT=Fixed, all gaps are the same size. A new row insertion would be as quick as possible because the first gap row searched is used immediately for the insertion.