… Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty pages per flush. That means that under a heavy write load, a new flush… way to get good performance is to set innodb_flush_log_at_trx_commit=2, that is, to flush the log to the disk only once per second…
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
… frequently than innodb_thread_concurrency setting. if you have innodb_flush_logs_at_trx_commit=1 and do not have battery backed up cache on your log volume… innodb_log_flush_at_trx_commit=0 or 2 smaller values may make sense. If you have binary log enabled it often does not really matter as Innodb…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 19 Comments
…: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=4G innodb_adaptive_checkpoint=keep_average innodb_thread_concurrency=0 innodb_flush_method…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… you do not need such strong guarantees and can at least change innodb_flush_log_at_trx_commit=2. It still will be much more secure than… are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are a lot more options for fine tuning but make sure at least…
Post: Adaptive flushing in MySQL 5.6
September 4, 2012 By 8 Comments
… 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_in_group = 2 #####plugin options innodb…
Post: Disaster: MySQL 5.5 Flushing
September 18, 2011 By 27 Comments
…, which 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… means InnoDB flushes more than your IO system can afford. Second, you need to a little math. (“Log flushed up to” – “Last checkpoint at“) this…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…/ innodb_checksums = 0 innodb_doublewrite = 0 innodb_buffer_pool_size = 5000m innodb_additional_mem_pool_size = 20M innodb_log_file_size = 1600M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 300 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 40 innodb_locks…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs… unless you’re piping large blobs to Innodb in this case increase it a bit. innodb_flush_log_at_trx_commit=2 If you’re not concern… your application and default which is 8 is decent start innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… about 180sec first time and about 260sec second time (with innodb_flush_log_at_trx_commit=0) go giving 550 and 380 updates/sec appropriately. Why… were a lot of dirty pages in innodb buffer pool which had to be flushed before recycling. First ran however was done… doublewrite InnoDB: buffer… InnoDB: Doing recovery: scanned up to log sequence number 12682768136 091118 18:47:44 InnoDB: Starting an apply batch of log records…
Post: Impact of logging on MySQL’s performance
February 10, 2009 By 8 Comments
…; logging every query; MySQL was run with default settings except following: [mysqld] user=root 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 used…
