… should normally set innodb_log_file_size large. We however usually recommend caution as it may significantly increase recovery time if Innodb needs to do… if logs are of reasonable size and wikipedia has 40 minutes or so with innodb_log_file_size=256MB. In other cases I know same log file time… the disk and undo phase – rolling back uncommitted transactions. As MySQL 5.0 these seems to be done in the background…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
…, and innodb_log_file_size do not matter, and changing these settings won’t help. Ironically, these settings are most important for tuning MySQL 5… for MySQL 5.1/InnoDB-plugin or MySQL 5.5 the setting of innodb_adaptive_flushing, innodb_max_dirty_pages_pct, innodb_io_capacity, and innodb_log_file_size are…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data… on hardware log file size workload and even when crash happen (how many unflushed changes we had) Repeating test with innodb_open_files=16384 I…
Post: Adaptive flushing in MySQL 5.6
September 4, 2012 By 8 Comments
… card. InnoDB log file size 2x2000M for MySQL 5.5, and 2x4GB, 2x8GB for MySQL 5.6…innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method = O_DIRECT innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 2 innodb_buffer_pool_size = 150G innodb_log_file_size = 4G innodb_log_files…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…39-98 log_slave_updates innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=400M innodb_log_file_size=64M You…mysql –mysql-engine-trx=yes –mysql-table-engine=innodb –mysql-host=127.0.0.1 –mysql-port=3307 –mysql-user=sbtest –mysql-password=sbpass –oltp-table-size…
Post: Disaster: MySQL 5.5 Flushing
September 18, 2011 By 27 Comments
…InnoDB configured for this hardware innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 1900M innodb_log_files…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask… much memory Innodb allocates for misc needs. innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
…wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file…using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 so your Innodb log and binary log are synchronized. In MySQL…
Post: Binary log file size matters (sometimes)
May 24, 2012 By 6 Comments
… while I was really puzzled, looking for answers in MySQL status counters, innodb status, memory info and what not. It seemed that…_binlog_size in size, new binary log is created and, if expire_logs_days is not zeroish, log files older than expire_logs_days are removed. &LOCK_log…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…size=24G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb…
