Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.