… server. Mysqldump works well for this, but can quite slow on larger schemas. One of the downsides to mysqldump is the need…_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF mysql -N -B <<'EOF' > import-ddl.sql…
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_…- http://master_server:3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –…
Post: Webinar: MySQL 5.6 Performance Schema
May 13, 2013 By 5 Comments
…, “Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks.” In this Webinar I will offer an overview of Performance Schema, focusing… in MySQL 5.6, go over the configuration and spend most time showing how you can use the wealth of information Performance Schema… excessive temporary tables and external sorts Excessive internal mutex contention Slow queries due to waits on InnoDB locks and Meta Data…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
…mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set (1.08 sec) mysql…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema… in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my… good tools to detect memory leaks like valgrind are too slow to run in production. So the best thing to do…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0 | 1048576 | | SLOW_QUERY_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30…_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER…_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema queries! innodb_log_file_size – default has…
Post: Percona Toolkit 2.1.8 released today with beta support for MySQL 5.6
January 9, 2013 By Leave a Comment
…newer: pt-table-chcecksum pt-online-schema-change pt-archive pt-mysql-summary pt-heartbeat pt-variable-…but no query–which can happen if using slow_query_log_timestamp_always in Percona Server–were…have foreign keys, and the tool uses the information_schema to find child tables, this could cause …
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> …INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/Call” FROM INFORMATION_SCHEMA… | 0.00 | 1 | 0.0000020000 | | logging slow query | 0.000001 | 0.00 | 1 …
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 9 Comments
… as increasing buffer pool size could cause performance problems. MySQL Restart MySQL Restarts may include recovery time but what is the most… with mk-query-digest (Percona Server marks Slave Thread in slow query log so it is easy to filter out events… system which queries mysql. I’ve seen monitoring updates which would add excessively expensive and frequent query on information_schema which caused a…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA query will surely trigger errors about this.) There are a… Percona patches for microsecond logging and additional information in the slow query log output. The stock MySQL server’s limitation of one-second…
