…_size=8M innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… a lot of short write transactions. innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… me start with most popular transactional storage engine for MySQL – Innodb. Innodb Storage Engine was covered in a lot of talks, many… starts giving you problems if you have a lot of concurrent inserts happening to the same table. This was born same… many other cases. Gap or next-key locks which limit concurrency in many cases can be removed for READ-COMMITED isolation…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an… / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8…. Run each query for 1, 4, 16, 64, 128, 256 concurrent threads. 3. For each thread perform a warm-up run…
Post: Fix of InnoDB/XtraDB scalability of rollback segment
January 18, 2009 By 9 Comments
… is in concurrency on rollback segment, which by default is single and all transactions are serialized accessing to segment. Fortunately InnoDB internally… innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=128M innodb_thread_concurrency=0 innodb_extra_rsegments…
Post: Internals of InnoDB mutexes
September 7, 2006 By 4 Comments
… on condition_variable in reserved cell; innodb_spin_locks is configurable via system variable innodb_sync_spin_loops (default value is 20… some loops it is reasonable to give CPU resource to concurrent threads. 2. Wait on condition variable using cells from synchronization… test it if you have multi-CPU boxes and high-concurrency load on InnoDB.
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which generally gives better concurrency. However as the name says it makes locks unsafe fore replication and point in time recovery, so use innodb_locks…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… have lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different…
Post: MySQL 5.0, 5.1 and Innodb Plugin CPU Efficiency
December 3, 2008 By 15 Comments
… execution path. Another thing to note – if you’re using Innodb Plugin consider using new Barracuda format, though do this only… testing as this format will not be recognized by older Innodb versions. Note: These are completely CPU bound test conditions, data… say CPU usage drop means concurrency issues it does not mean CPU increase does not mean concurrency issues too. You can have…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… 121 16 138 As you see running 2-4 threads concurrently we get result by 30% worse than with 1 thread… than 2x in peak which happens to be at 4 concurrent threads. So there is still work to do in addition… on system with 4 Cores. Here is comparison of MyISAM Innodb and Falcon results in the graphical form: I’ve created…
