… basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but… in this case you can calculate a reasonable value, believe it or not. Run these … you can set innodb_log_file_size=64M Does that look surprisingly small? It might. I commonly see log file sizes in the…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…INNODB_LOG_FILE_SIZE | 5242880 | 50331648 | | BASEDIR | /mnt/nfs/dist/5.5.30 | /mnt/nfs/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES… where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row *************************** VARIABLE_NAME: OPTIMIZER_SWITCH VARIABLE_VALUE: index…
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…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… terms of optimizations in this …InnoDB: allowed value would be 64. InnoDB: You may need to raise the value of innodb_max_files_open in InnoDB: my.cnf. InnoDB: fil_sys open file…log file size workload and even when crash happen (how many unflushed changes we had) Repeating test with innodb_open_files…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
…optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal…Innodb database size Memory is paramount. 16G-32G is the cost efficient value… on tuning innodb buffer pool innodb_log_file_size – This …
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…=128 MyISAM / 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 …value from PK. The results for InnoDB and MyISAM are comparable and I think this shows both engines are maximally optimized…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… has “n” rows, and its size is three times the value of join_buffer_size, then 3 hash tables would need …size=32M optimizer_switch=’batched_key_access=on’ join_buffer_size=32M MariaDB 5.5.20 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=512M innodb_log_files…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…files. In fact my.cnf files are usually one of the smallest levers we can pull for server optimization. Schema and query optimization…innodb_log_file_size. I won’t give you a full rundown over every option in the file…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
… innodb_log_file corresponds to changed pages in the buffer pool. You can compute this value as Log…optimized mode: innodb_buffer_pool_size = 26G innodb_buffer_pool_instances=1 innodb_log_file_size = 8000M innodb_log_files_in_group = 2 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… in 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 is optimal – if you see more than 30% of log…
