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.

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?

share|improve this question
2  
Just convert your tables to innodb. There is no point in causing yourself the inevitable pain that will come from using myisam tables. –  Aaron Brown Jul 12 '12 at 2:23
    
Thank you for the advice. We're at mysql 5.0. We tried converting several tables to innodb and had many problems and had to revert back. Migrating to 5.1 or 5.5, and then trying innodb is on our roadmap. –  Haluk Jul 12 '12 at 11:04
    
From the number of questions I see from you, I suspect that you are spending far more time trying to "fix" MyISAM than it would take you to simply install MySQL 5.5, set some basic InnoDB settings, and convert everything. Just my 2 cents, but MyISAM is a disaster of a storage engine and its only redeeming quality is that it has full text built in. –  Aaron Brown Jul 12 '12 at 21:52
    
Thank you for the advice. –  Haluk Jul 12 '12 at 23:51
    
Looking at our show processlist output. "select count(*)" still seems to locks the MyISAM table and block "concurrent inserts", even when all gaps are filled. I cannot find any documentation about this but I can see this almost anytime I check the show processlist. –  Haluk Jul 30 '12 at 16:14
add comment

2 Answers 2

up vote 5 down vote accepted

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'
share|improve this answer
    
Great answer, thank you. We happen to have a table with fixed-length rows. So a new insert should fill a previous hole perfectly. Recently we turned on "deletes" for some queries working on this particular table and the behavior of inserts was interesting. A low number of inserts would create longer average insert times. However when we do many inserts, average individual insert time is much less (about x100 less, but our table is 500M+ rows, so this might not be a benchmark for a more moderate size table). This fits your answer. Once the holes are filled, concurrent insert is back in action. –  Haluk Jul 11 '12 at 23:17
    
+1 for directly answering the question and giving a cleaner explanation !!! –  RolandoMySQLDBA Jul 12 '12 at 2:42
add comment

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 :

share|improve this answer
    
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
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.