… updated heavily “hotspots”. Examples of hotspots Example 1: Your application is an online game and you keep track of global achievement statistics… a player makes an achievement, your application updates this table with a statement like this: UPDATE achievements SET count = count + 1 where…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… after the upgrade was completed regardless of its state before updating. This caused some problems for some users, especially if automatic… used. Bug fixed #1107539 (Sergei Glushchenko). Suboptimal code for User Statistics feature has been optimized to make sure no additional work… documentation. Bugs can be reported on the launchpad bug tracker. UPDATE[18-04-2013]: There was a RPM packaging regression introduced…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… used. Bug fixed #1107539 (Sergei Glushchenko). Suboptimal code for User Statistics feature has been optimized to make sure no additional work… documentation. Bugs can be reported on the launchpad bug tracker. UPDATE[18-04-2013]: There was a RPM packaging regression introduced…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of…_SCHEMA.STATISTICS) When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics… seconds to update all the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from…
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
…STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS Access protection – USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS…593836 ROWS_UPDATED: 0 TABLE_ROWS_READ: 549073 SELECT_COMMANDS: 214801 UPDATE_COMMANDS:…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…, root page 3 estimated statistics in dictionary: key vals: 25265338, leaf pages 497839, size pages 498304 real statistics: level 2 pages: pages… it was expected, as table is really mostly inserted in, updates and deletes are rare). And let’s take index domain…, root page 49160 estimated statistics in dictionary: key vals: 5750565, leaf pages 34383, size pages 34496 real statistics: level 2 pages: pages…
Comment: Solving INFORMATION_SCHEMA slowness
December 27, 2011 By 25 Comments
… about basic statistics such as number of rows per key value. Check out “SHOW INDEX FROM table” to see what statistics MySQL gathers. By default MySQL does update statistics on selects to information schema. This does… is done. Fully thing too SHOW INDEX FROM table also updates statistics so you really can’t see what stats optimizer is…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… information for each table, it calls dict_update_statistics and appears to force table statistics to be regenerated for each table It’s possible I’m mistaken, but it sure looks like dict_update_statistics is… 4883 So, the io-wait spike is explained by table statistics being regenerated, and the SHOW PROCESSLIST connections stuck in ‘Opening…
Comment: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
…/fts0fts.cc fts_get_total_document_count 2384 dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT, FALSE); 4 storage/innobase/handler…_stats_update(ib_table, stats_upd_option,FALSE); 5 storage/innobase/row/row0mysql.cc row_update_statistics_if_needed 980 dict_stats_update(table…/row0mysql.cc row_truncate_table_for_mysql 3318 dict_stats_update(table, DICT_STATS_RECALC_PERSISTENT_SILENT,FALSE) FALSE == where caller…
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 …: query timings, execution plan details and InnoDB engine per-query statistics. With this option you may choose which of those you… log. For example to have microsecond query timing and InnoDB statistics you would need to set this option to “microtime…
