… 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 buffer size being unflushed you may want to increase it. You also can see number of pending normal log writes and…
Post: InnoDB memory usage
May 30, 2006 By 8 Comments
… OS_THREADS) So InnoDB uses: innodb_buffer_pool innodb_additional_mem_pool_size innodb_log_buffer_size adaptive index hash, size= innodb_buffer_pool / 64 system dictionary hash, size = 6 * innodb_buffer_pool_size / 512 memory… for innodb: innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812 / 16384 * innodb_buffer_pool_size + OS_THREADS * 368 For simplicity we can use: 812 / 16384 * innodb_buffer_pool_size ~~ innodb_buffer_pool_size…
Post: Analyzing the distribution of InnoDB log file writes
July 16, 2010 By 5 Comments
… So, in sum, we see that about 3/4ths of InnoDB log file writes on this workload are 512 or 1024 bytes… necessary log buffer size? The largest write above is just under 40k, which might seem to indicate that only 64k or so of log buffer…, a small log buffer size wouldn’t be good and could cause additional lock contention. (There is a single mutex around the log buffer.) We…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… swap out MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80% of memory is a… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs but 256M… and good performance innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case…
Post: How long is recovery from 8G innodb_log_file
December 22, 2010 By 11 Comments
… of innodb_log_file_size > 4G. This test was done using Percona Server 5.5.7, but the same performance expected for InnoDB-plugin… is: for 52GB innodb_buffer_pool_size: 101220 21:54:31 InnoDB: Database was not shut down normally! .. 101220 22:02:40 InnoDB: Rollback of… 51sec for 144GB innodb_buffer_pool_size: 101220 22:45:37 InnoDB: Database was not shut down normally! .. 101220 22:55:00 InnoDB: Rollback of…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…: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_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb…
Post: Fix of InnoDB/XtraDB scalability of rollback segment
January 18, 2009 By 9 Comments
…=MYISAM innodb_buffer_pool_size=6G innodb_data_file_path=ibdata1:10M:autoextend 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=128M innodb_thread_concurrency=0 innodb…
Post: How long Innodb Shutdown may take
September 2, 2010 By 9 Comments
… dirty buffers. The number of dirty buffers in the buffer pool varies depending on innodb_max_dirty_pages_pct as well as workload and innodb_log_buffer_size and can be anywhere from 10 to 90% in the real life workloads. Innodb_buffer_pool_pages_dirty…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual innodb_log_buffer_sizeblogpercona.commanual innodb_log_file_sizeblogpercona.commanual innodb_log_files_in_groupblogpercona.commanual innodb_log_group_home_dirblogpercona.commanual Innodb_log_waitsblogpercona.commanual Innodb_log_write_requestsblogpercona.commanual Innodb_log…
