…InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush_log…Per Second” on the y-axis. The reason why I have chosen to show “millions of …
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it… what happens on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find log records which only have…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… pages is less than innodb_max_dirty_pages_pct and checkpoint age is less than 3/4 of innodb_log_file_size * innodb_log_files_in_group. If you have these conditions and observe intensive writes, InnoDB is using LRU list flushing. So why is…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… 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… will be in the buffer pool. I think the reason why YouTube had so long recovery time is because their application…
Post: InnoDB Flushing: Theory and solutions
April 4, 2011 By 23 Comments
…into theory. The two most important parameters for InnoDB performance are innodb_buffer_pool_size and innodb_log_file_size. InnoDB works with data in memory, and all…question is: Why can’t we simply have a gigantic InnoDB transaction log? The answer is that the size of the transaction log affects …
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… second time (with innodb_flush_log_at_trx_commit=0) go giving 550 and 380 updates/sec appropriately. Why was second time slower… files and applying logs took about 9 minutes in this setup. This number of course can change a lot depending 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: 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… why innodb_adaptive_flushing is not able to keep up. What if we disable flushing of neighbor pages (we have the option innodb…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…that can enter InnoDB is limited by innodb_thread_concurrency (which is why these two variables …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: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… 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. The tables were sized so they… of rows read performance for 10000 tables would be close. Innodb with innodb_file_per_table=1 had the following results: [root@DB10… when better again for 10000 tables. I can’t explain why it is a bit worse for 1000 tables thought as…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-…why we see bad performance. We hope the performance of LIMIT queries will be fixed before release. MyISAM shows stable result. InnoDB…
