How does mysql decide if concurrent insert is possible for a MyISAM table?

Question

For "concurrent inserts", MySQL reference manual has the following explanation:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table.

http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html

Let's say our database "concurrent insert" parameter is set to "Auto" (1).
And we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Or do we need to run "OPTIMIZE" before the table knows there are no gaps?

Answer 1

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'

Answer 2

You should probably set concurrent_insert to 2.

First, add this to /etc/my.cnf

[mysqld]
concurrent_insert=2

then restart mysqld. If you cannot restart mysqld, wait until a off-peak time and then run

SET GLOBAL concurrent_insert = 2;

Doing this leaves no doubt that concurrent inserts are in operation.

You can always do OPTIMIZE TABLE during a real off-peak window.

If you prefer not to tweek concurrent_insert, you can speed things up for MyISAM at a cost. What cost?

By default, whenever a MyISAM table is created, the row format is Dynamic. If you run

ALTER TABLE mytable ROW_FORMAT=Fixed;

on every MyISAM table, this will increase read/write I/O 20-25% on each MyISAM without changing anything else. Again, at what cost? The table will double in size in most cases. I wrote about this MyISAM performance enhancement/tradeoff before :

• What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• Optimized my.cnf for high-end and busy server
• Which is faster, InnoDB or MyISAM?