3

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?

6

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'
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 :

  • Thank you for the answer. I didn't mean to ask this for what "concurrent_insert" should be. I want to understand what is the behavior once the gaps are filled. Does the table go back to concurrent insert mode immediately. Or do we need to run an "optimize" command before concurrent inserts can start kicking in. – Haluk Jul 11 '12 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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