Arjen posted a good note about MyISAM concurrent-insert features, though I should mention concurrent-insert can be cause of scalablity and peformance problems on… are using range-queries and doing INSERT not often than disabling concurrent-insert with –skip-concurrent-insert can improve MyISAM performance. As I said the reason…
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… But on INSERT…SELECT, you’ll see this: Read lock without concurrent inserts That read lock is what’s blocking the concurrent inserts from happening…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… 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 reality it means only one INSERT…
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: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… MyISAM tables only to optimize bulk inserts (multiple value 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… MyISAM. The server has many dozen drives so it was easy to add more concurrency so I kicked off, from a script, insertions into 16 identical MyISAM files for distinct parts of…
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… many other cases. Gap or next-key locks which limit concurrency in many cases can be removed for READ-COMMITED isolation…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… writes are done in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is… which Innodb sets on statement execution, which generally gives better concurrency. However as the name says it makes locks unsafe fore…/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO OUTFILE does not have to set extra…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
… system. It has exactly one table. MYISAM table with the following activity: Per day: 1. INSERTS: 1,000 2. UPDATES: 200 3…=24M read_buffer_size=9M max_connect_errors=10 thread_concurrency=4 myisam_sort_buffer_size=950M character-set-server=utf8 default…_slow_queries=/var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1 The problem was that at…
Comment: New Forum Categories: Help Wanted, For Hire
March 5, 2013 By 1 Comment
…_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb innodb_additional_mem_pool…_trx_commit=1 innodb_flush_method=O_DIRECT innodb_thread_concurrency=16 innodb_buffer_pool_size = 4500M innodb_lock_wait_timeout…_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb innodb_additional_mem_pool…
