… a good note about MyISAM concurrent-insert features, though I should mention concurrent-insert can be cause of scalablity and peformance problems…concurrent-insert with –skip-concurrent-insert can improve MyISAM performance. As I said the reason of bad scalability is rw-locks and currently MySQL…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…concurrent…myisam_recoverblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_repair_threadsblogpercona.commanual myisam_sort_buffer_sizeblogpercona.commanual myisam_stats_methodblogpercona.commanual myisam…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking… MySQL manual actually says this, but not in the clearest way. It just says If you are using the binary log, concurrent inserts…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
…items you need to keep into account while using MyISAM tables. Recovery. MySQL was running stable for us, giving us false …ones. Concurrency. MyISAM uses table locks and has concurrent inserts which can go concurrently with selects. This is sometimes presented as great concurrency for inserts but in…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… MyISAM variables are not important ? Not really. You still have some tables in MyISAM format in “mysql” database…inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. concurrent_insert – Enables concurrent insert (while Selects are running) for MyISAM…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
… like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck was clearly the lack of concurrency on… was easy to add more concurrency so I kicked off, from a script, insertions into 16 identical MyISAM files for distinct parts of… then, back in mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select * from test…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes ? Will be UNIQUE… statement duration for Inserts with auto-increment column. This starts giving you problems if you have a lot of concurrent inserts happening to the same table. This was born same as bunch of others due to statement level MySQL Replication…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had… of similar structure with needed index and populate it with insert… select statement as you need it. One little gotcha which… MyISAM, disable concurrent inserts and run long reporting query on the log file query execution is still proceeds. I have not checked if concurrent insert…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… done in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is – replication. In MySQL before…/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO OUTFILE does not have to set extra… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… tables and does specified number of inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64) and set… from these results – Concurrent growth of many tables causes data fragmentation and affects table scan performance nadly – MyISAM suffers worse than Innodb…
