…innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load … +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 FOR UPDATE; +——+ | …
Comment: InnoDB's gap locks
April 4, 2012 By 14 Comments
Andy, With READ COMMITTED InnoDB doesn’t take additional locks on the gap, improving the concurrence. You would avoid all “Lock wait timeout…> commit; session2> commit; session1> select * from t; +——+ | i | +——+ | 21 | | 26 | | 30 | +——+ No locks on the gap, so no wait timeout and more concurrence.
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
… innodb_thread_concurrency. This is why innodb_commit_concurrency variable was added in MySQL 5.0 innodb_commit_concurrency limits number of threads which can be active inside Innodb kernel at commit stage…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
…_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_concurrency, PLUGIN_VAR_RQCMDARG, “Helps in performance tuning in heavily concurrent… from 5.1, then InnoDB‘s group commit works again. PZ: You can disable binary logging to get Group Commit back. Though this…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb_data_home_dirblogpercona.commanual Innodb_data…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… inside Innodb kernel and still has 400 tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads to run inside Innodb… your innodb_flush_log_at_trx_commit value your log writes may be more or less expensive. If innodb_flush_logs_at_trx_commit…
Comment: MySQL on Amazon RDS part 2: Determining Peak Throughput
March 28, 2011 By 4 Comments
… 2097152 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_size 55050240000 innodb_change_buffering inserts innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir /rdsdbdata/db/innodb innodb_doublewrite ON innodb_fast…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed…’re piping large blobs to Innodb in this case increase it a bit. innodb_flush_log_at_trx_commit=2 If you’re… a lot of short write transactions. innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may…
