…’ve been puzzling over concurrent inserts for a while while using v5.0.15 and we’ve discovered that concurrent inserts don’t seem to be allowed with a spatial key defined on our spatial column. (waiting for write ‘concurrent insert lock…) Removing just the spatial key frees up concurrent inserts to work as they should by default (concurrent_insert=1) Although I may have missed…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… 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… you now have very nice value of 2 which allows concurrent insert to work also if tables have “holes” (deleted rows) which…
Comment: MyISAM concurrent insert
September 24, 2006 By 12 Comments
… negative effect – your INSERTS will be a bit longer. I don’t know any bugs with disabled concurrent insert. 2. If you use only point selects, or selects that retieve only several rows – I don’t think concurrent=off… patched query log and analyze execution time of queries with concurrent insert = off and on.
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… was this done, being pretty bad for MySQL Performance and concurrency ? The reason is – replication. In MySQL before 5.1 replication… 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…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… been several MySQL bugs opened about multi-core scalability (concurrent queries, autoincrement, concurrent inserts, instrumentation, etc.). Rather than treat each defect as an… are the limits of different operations for different levels of concurrency, specifically around mutexes and contention? How does this fit into…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… statement duration for Inserts with auto-increment column. This starts giving you problems if you have a lot of concurrent inserts happening to… code simple and same for all insert cases – in fact if the insert is single value insert or the number of values in… many other cases. Gap or next-key locks which limit concurrency in many cases can be removed for READ-COMMITED isolation…
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
… lot of INSERT threads) thread thrashing could be still observed even with limited innodb_thread_concurrency. This is why innodb_commit_concurrency variable was added in MySQL 5.0 innodb_commit_concurrency limits number of…
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…_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…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
…=24M read_buffer_size=9M max_connect_errors=10 thread_concurrency=4 myisam_sort_buffer_size=950M character-set-server=utf8…_slow_queries=/var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1 The problem was that at… the transaction record as mentioned above (a small fraction of INSERTs and UPDATEs)? We explored a bunch of issues including questioning…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
… to simulate 5 concurrent connections writing to the table, with each instance of iiBench writing 200 million single row inserts, for a… avg. insert rate of ~25k INSERTs Per Second, while with the table purchases_index, the avg. insert rate reduced to ~9k INSERTs Per Second… to ~12.5k INSERTs per second, with noticeable spikes after every 100 million rows inserted. I noticed that the insert rate drop was…
