What if the calculation above shows that I should have an innodb log file size of 1-2 MB? I’m running Percona server on an ec2 small instance with the XFS filesystem for the mysql data directory.
Post: How to calculate a good InnoDB log file size
November 21, 2008 By 33 Comments
…. Since there are two log files by default, divide that in half, and now you can set innodb_log_file_size=64M Does that look surprisingly small? It might. I commonly see log file sizes in the gigabyte ranges… with a big log. However, most of the time when I run this calculation, I end up finding that the log file size needs to…
Post: How to change innodb_log_file_size safely
July 9, 2011 By 14 Comments
… MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can’t just change the parameter in the my.cnf file and restart the server. If you do, InnoDB…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…If you have a very simple workload, you can calculate these values by hand. But for most workloads…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…
Post: Impact of logging on MySQL’s performance
February 10, 2009 By 7 Comments
… max_connections=3000 innodb_log_file_size=128M innodb_flush_log_at_trx_commit=1 innodb_file_per_table table_cache=2000 Depending on workload different InnoDB buffer was… measurement error was calculated. As it is seen from the chart above the performance almost doesn’t depend on logging – the…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… benchmark (100W ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default)). The problem… makes calculating how many pages we need to flush extremely hard (read “impossible”), and this is one of the reasons why innodb…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… Innodb will print: ===================================== 060717 3:07:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated…log files. By monitoring log sequence number and value up to which logs have been flushed you can check if your innodb_log_buffer_size…
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_files…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=4G innodb_log_block_size=4096 innodb_thread_concurrency=0 innodb…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… is the absence of innodb_log_file_size. I won’t give you a full rundown over every option in the file. But again, you… the error log is interesting and needs to be investigated. You might find that there are InnoDB tables whose .frm files exist but… be possible to improve a lot, and a simple mental calculation can then tell me how much total gain I can…
