… log every query. (See part two for handling massive slow log volume.) The slow logs are served, via HTTP, by mysql_slowlogd…-query command-line option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You will need Percona Server in order to use innodb_fake…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
…. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout because that now conflicts, or …-upgrade was written once long ago and not updated since. Now that we have four base versions …. It also reads every type of log now (slow, general, binary, and tcpdump), so you shouldn’t …
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
For a couple of months there have been no updates to our msl patch, however recently I managed some time … messages to your slow log will be. Multiple flags allowed in a comma-separated string. [microtime, query_plan, innodb] msl patch currently… you want to have in your slow log. For example to have microsecond query timing and InnoDB statistics you would need to…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… time object will be free already. OS Waits are relatively slow, and if you get tens of thousands of OS waits… to keep up with update rate, in this case difference between these values will also grow and innodb_max_purge_lag will… be “fetching rows”, “updating” and couple of other values. “Thread declared inside InnoDB 400″ means thread is running inside Innodb kernel and still…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…data into a temporary table, secondary indexes are updated by inserting individual rows rather than sorting; …” part will not be affected by a slow tmpdir, but rebuilding the indexes will obviously take…TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is just a special …
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first… seconds to update all the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5.1.17 a similar variable exist called innodb_stats_on_metadata…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… impact server performance dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved…. The solution is simple, just set innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema. Most likely you do not want it anyway. This will not make Innodb to…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
… Percona Server the following can be helpful: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_size | page_no | max… is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records when they… this problem first you can use innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 10 Comments
… into Innodb updates buffer pool pages in memory and records page operations in the transaction (redo) log. Behind the scenes those updated (dirty) buffer pool pages are flushed down the to the tablespace. If Innodb stops (read: crashes) with dirty buffer pool pages, Innodb… to the tablespaces. Ultimately this mechanism was an optimization for slow drives: if you can sequentially write all the changes into…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 12 Comments
…. Innodb uses insert buffer to “cheat” and not to update index leaf pages when at once but “buffer” such updates so several updates to… before page is made available. This means insert buffer can slow down read operations. The other way insert buffer is merged…. This can be very surprising. Stats about Innodb Insert Merge Buffer are available in SHOW INNODB STATUS output: ————————————- INSERT BUFFER AND ADAPTIVE…
