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 35 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: 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 21 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 139 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: Innodb Performance Optimization Basics
April 25, 2008 By 70 Comments
… user MyISAM most likely will win because of Transactional overhead in Innodb, however if you have large system and many concurrent inserts or parallel long running queries Innodb is likely to be faster because MyISAM has table level locks…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… statements. I wanted to see how fast MyISAM vs. MonetDB would interpret really large INSERT statements, the kind produced by mysqldump. But… size of the resulting table on disk (smaller is better): MyISAM is 787MB, MonetDB is 791MB, and Infobright is 317MB. Next… “general-purpose” database use — there are a number of limitations (concurrency, for one) and it looks like it’s still fairly…
