…Besides being just slow accessing information_…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: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM … := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM… | 0.000002 | 0.00 | 1 | 0.0000020000 | | logging slow query | 0.000001 | 0.00 | 1 | 0.0000010000 …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length… good tools to detect memory leaks like valgrind are too slow to run in production. So the best thing to do…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
… finds queries (or groups of queries) that are slow and provide a bad user experience by the…the system by grouping queries into classes and summing the execution time together. Then it prints …query executed for, which has been available in MySQL for years. This is necessary for determining whether…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… than data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated runs test… in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from lineorder join…=0; mysql> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql> select straight_join sum(lo…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… you were using the vanilla MySQL server, you would see an entry like this in the slow query log: # Time: 111229 3… queries and shows the top queries with largest impact – longest sum of run time which typically (not always) shows queries causing…
Post: Hidden columns of query_review_history table
August 28, 2012 By Leave a Comment
You can use pt-query-digest to process a MySQL slow query log and store historical values for review trend analysis … any of these MAGIC_history_cols values: pct|avt|cnt|sum|min|max|pct_95|stddev|median|rank If the column…, there are some that don’t exist in the slow log, and slow logs may actually include different kinds of attributes (for…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… are usually interested in queries that are consuming most MySQL time and that’s how mk-query-digest groups…time=0 and all the eXtra benefits from Percona slow query log patch), I knew some particular pages …digest –group-by=Thread_id –order-by=Query_time:sum in > out Spot on, the session I needed …
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…I used a variation on MySQL Sandbox to set up a little throw-away MySQL instance — that took ten seconds…h 127.0.0.1 -P 9999 -p XXXX slow_query.log \ –save-results-database h=127.0…AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o… for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL 5.5 and MySQL 5.6. For…
