… a table‘s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query… Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you can see db1 has about 294 tables…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… memory consuming, so ideally you would want to download the “slow query log” to another machine and run it there. Analyzing… # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `wp_blog_one ` LIKE ‘wp_terms’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_terms`\G # SHOW TABLE STATUS FROM `wp_blog_one…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS …. OS Waits are relatively slow, and if you get …TABLES was used. In addition to Innodb specific information, there is generic statement information which is visible in SHOW PROCESSLIST showed in SHOW INNODB STATUS…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
… This lets us gather queries from servers that don’t have a slow query log enabled, at …# 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE ‘dual’\G # SHOW CREATE TABLE `dual`\G # EXPLAIN select 1 from ( select sleep(.1) from dual ) …
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… 10mil row table which would look as following in terms of data and index size: mysql> show table status like “… inserting data in random order can become very slow when index does not fit to memory any …clearly seen from the graphs but with index built by sort enabled the performance of ALTER TABLE …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…TABLE By temporarily dropping secondary indexes from the new table before copying the data, and then recreating them later, ALTER TABLE…SHOW TABLE STATUS versus 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…’s take a look at the tables. mysql> show table status like ‘fact’\G *************************** 1. row *************************** … the dimension tables doesn’t slow the query perceptibly, because these tables are … the fact table. The LRU policy evicts old blocks from the fact table instead, which …
Post: Visualization tools for pt-query-digest tables
August 31, 2012 By 6 Comments
…a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a …show; * filtering queries by hosts; * filtering reports by different query conditions; * graphing reports; * explaining queries; * searching and displaying samples; * displaying table status…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…, future performance, scalability, ability to recover from disasters? We also want to know …status values for Created_tmp_tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table….com/scripts/mysqlsla perl mysqlsla -lt slow /path/to/slow.log By default, this outputs …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… tables | 0.000003 | | freeing items | 0.000007 | | logging slow …show status like ‘Open%tables‘; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0 | +—————+——-+ 2 rows in set (0.00 sec) session10 > select * from…
