… server. Mysqldump works well for this, but can quite slow on larger schemas. One of the downsides to mysqldump is the need… TABLE `’, table_name, ‘` IMPORT TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF For full disclosure, here…
Post: Webinar: MySQL 5.6 Performance Schema
May 13, 2013 By 5 Comments
… be leading a Webinar titled, “Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks.” In this Webinar I will… most time showing how you can use the wealth of information Performance Schema gathers to understand some of the typical performance bottlenecks… excessive temporary tables and external sorts Excessive internal mutex contention Slow queries due to waits on InnoDB locks and Meta Data…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… server’s caches hot. (See my Fosdem 2013 slides for information about discarded designs and benchmarks.) First, we set long_query…://master_server:3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
Many of us find INFORMATION_SCHEMA painfully slow to work it when it comes to retrieving table meta …. Besides being just slow accessing information_schema can often impact server performance dramatically. The cause of majority of this slowness is not opening and…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated … 8 rows in set (0.11 sec) mysql> select * from information_schema.innodb_buffer_pool_stats\G *************************** 1. row *************************** POOL_ID: …
Comment: Solving INFORMATION_SCHEMA slowness
April 20, 2012 By 25 Comments
I’ve just hit the INFORMATION_SCHEMA slowness today when I began the tests to integrate the pt-online-schema-change tool into our… one hour just to finish this query: “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …” After setting innodb_stats… the rest of the percona tools that use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata=0…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… server is not heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so… exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA query will surely trigger errors about this.) There are a… has the Percona patches for microsecond logging and additional information in the slow query log output. The stock MySQL server’s limitation…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source… task of finding unused indexes we’re going to use INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql…:/var/log# pt-index-usage slow.log slow.log: 11% 03:58 remain slow.log: 21% 03:43 remain slow.log: 32% 03:09…
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
…runtime. Next variables were added: | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/slow_query.log | for compatibility with …io_pattern.patch | Information schema table of InnoDB IO counts for each datafile pages | 1.0 | Percona | GPL | INFORMATION_SCHEMA.INNODB_IO_PATTERN | | microsec…
Post: Percona Server 5.1.53-12.4
December 29, 2010 By Leave a Comment
… cause a crash or undefined behavior: log_slow_timestamp_every, log_slow_sp_statements, slow_query_log_microseconds_timestamp, use_global_… Bug #677407 – The innodb.innodb_information_schema test could fail sporadically due to flawed logic in the INFORMATION_SCHEMA.INNODB_LOCKS caching mechanism. (…
