… queries in “SHOW PROCESSLIST” stuck in “end” stage or have “commit” queries in the processlist for multiple statement transactions. Looking at SHOW INNODB STATUS you…. Use innodb_flush_log_at_trx_commit=2 This is probably best solution. In many cases this would be good change to do with MySQL 4 also…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…MySQL 5.1 Q15: How frequently does Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty pages per flush…(lots of threads waiting for semaphores in SHOW INNODB STATUS), you can try to …performance is to set innodb_flush_log_at_trx_commit=2, that is, to flush the log to the disk…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
…innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 64M innodb_buffer_pool_size = 26G innodb_buffer_pool_instances=16 innodb_log_file_size = 2000M innodb_log…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 19 Comments
…innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=4G innodb_adaptive_checkpoint=keep_average innodb_thread_concurrency=0 innodb_flush…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
…/mysql socket=/var/run/mysqld/mysqld.sock innodb_file_per_table = true innodb_data_file_path = ibdata1:10M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log…the same and, WT -vs- WB cache of course showed no difference at all. Here are the results: As you can see…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M innodb-file-format=barracuda innodb-file-per-table innodb-flush-log-at-trx-commit=1 innodb-flush…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
…Innodb. Defaults You need to know two things about defaults for Innodb. First Depending on MySQL…innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are a lot more options for fine tuning but make sure at…
Post: MySQL 5.5.8 and Percona Server on Fast Flash card (Virident tachIOn)
December 21, 2010 By 11 Comments
… MySQL 5.5.8. For both servers I used: innodb_flush_log_at_trx_commit=2 ibdata1 and innodb_log_files located on separate RAID10 partitions, InnoDB…innodb_buffer_pool_size: 144G innodb_buffer_pool_size: The final graph shows the difference between different settings of innodb_io_capacity for MySQL…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…MySQL settings for MyISAM (table_cache=64) and set innodb_buffer_pool_size=8G innodb_flush_logs_at_trx_commit=2 innodb_log_file_size=256M innodb_flush_method=O_DIRECT for Innodb…are enough to drop performance 20 times. Innodb in single tablespace mode showed following results: [root@DB10 ~]# for i in…
Post: Replaying database load with Percona Playback
October 25, 2012 By 9 Comments
… show how to meassure the impact of my.cnf changes replaying a slow query log… –mysql-host=127.0.0.1 –mysql-user=root –mysql-schema=my_db –query-log-file=slow.log …go further. Last change, flush logs every second instead of every commit (innodb_flush_log_at_trx_commit=2): Executed 429467 queries …
