…, InnoDB has been moving towards the parallelized purge: in MySQL 5.5 there is an option to have a single separate dedicated purge thread and in MySQL 5.6.2 one can have multiple dedicated purge threads. Percona Server 5.1 supports multiple purge threads too… the number of dedicated purge threads. The workload is single-table, and the InnoDB team advises using just one thread for such workloads. On…
Post: Purge Thread Spiral of Death
June 10, 2010 By 2 Comments
… frequently you have purge not being the problem at all and when out of no where you can see purge thread being unable… is when problems often starts to happen. Now instead of purge thread simply operating in memory it has to perform IO which… as it can be controlled), using innodb_max_purge_lag or enable separate purge thread (or threads) via innodb_use_purge_thread if you’re running Percona Server…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… InnoDB 400″ means thread is running inside Innodb kernel and still has 400 tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads… currently active, state of Innodb main thread which controls scheduling of number of system operations – flushing dirty pages, checkpointing, purging, flusing logs, doing…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_page_sizeblogpercona.commanual Innodb_pages_createdblogpercona.commanual Innodb_pages_readblogpercona.commanual Innodb_pages_writtenblogpercona.commanual innodb_purge_batch_sizeblogpercona.commanual innodb_purge_threadsblogpercona.commanual innodb…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
… changes. Purge Thread Falling Behind This is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records… problem first you can use innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can’t keep up. This… you’re running XtraDB you can also use innodb_use_purge_thread to use dedicated purge thread, which works a bit faster as it does…
Comment: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
… know master thread does the adaptive_flushing check every second in a loop. In InnoDB plugin for 5.1 if master thread ends… don’t think purge should be a concern. But to keep master thread free of getting stuck in purge maybe it is a good idea to use innodb_purge_threads = 1.
Post: Percona Server 5.1.57-12.8 Stable Release
June 8, 2011 By 10 Comments
… maximum value for innodb_use_purge_threads has been corrected to 32 (maximum number of parallel threads in a parallelized operation). The innodb_purge_thread patch accepted a value up to 64 for the innodb_use_purge_thread variable, leading…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
… innodb_buffer_pool_instances=16 innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_purge_threads=1 innodb… innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity=500 innodb_max_dirty_pages_pct = 60 innodb_purge_threads=1 innodb_adaptive_flushing=0 innodb_doublewrite=1 innodb…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… the load — but from other transactions’ changes too; the purge thread cannot purge them, so everything gets bloated and slow. Even simple SELECT… not-yet-purged, row versions. Later, the purge thread will have to clean these up. This is how you make InnoDB behave like PostgreSQL… machine from another, which takes about 10 or 12 hours. InnoDB is not optimized for rollbacks, it’s optimized for transactions…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=128M innodb-open-files=1000 innodb_fast_checksum innodb-purge-threads=1 innodb-read-ahead=linear innodb-read-ahead-threshold=8 innodb-read-io-threads=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb…
