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… INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something more elaborate and application…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… (storing billions of rows in tens of thousands tables) Innodb was better choice mainly because of thouse other behaviors … …. 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 …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…most popular transactional storage engine for MySQL – Innodb. Innodb Storage Engine was covered in a lot …INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes …Inserts with auto-increment column. This starts giving you problems if you have a lot of concurrent inserts…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… with MyISAM and Innodb is huge for read only workload it is caused by the fact Innodb …compression. Insert buffer for delete. For Insert operation Innodb uses nice tecnique called Insert Buffer which speeds up inserts for …see it is too bad even with 4 concurrent queries). The problem seems to be at …
Post: Fix of InnoDB/XtraDB scalability of rollback segment
January 18, 2009 By 9 Comments
… sysbench write workload (benchmark emulates intensive insert/delete queries). By our results the problem is in concurrency on rollback segment, which by…=MYISAM innodb_buffer_pool_size=6G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… 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 storage engine. Other Storage engine ether always have it (ie Innodb) or…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency… enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which generally gives better concurrency. However…/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
…buffer_size=9M max_connect_errors=10 thread_concurrency=4 myisam_sort_buffer_size=950M character-set-server…var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1 The problem was… for instance) and eking performance out of an InnoDB table for raw SELECT speed will take …
Comment: Innodb Performance Optimization Basics
April 25, 2008 By 70 Comments
… single 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…
