On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register… SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But…: Bill, back to the query where you were trying to determine the last episode of each TV show. Couldn’t you have used…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… last webinar, but did not have time to analyze it in-depth. If you try to do something like this in MySQL… mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE… ADD INDEX (col2); Query OK, 0 rows affected, 1 warning (0.56 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
… error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default database: ‘test’. Query: ‘…_Set and Executed_Gtid_Set from the SHOW SLAVE OUTPUT as we can see in the…MySQL. Last week I gave a talk at Percona MySQL University @Toronto about GTID. It includes an overview of MySQL…
Post: High-load problems? Investigate them with 'pt-query-digest'
April 23, 2013 By 2 Comments
…last week, and I thought I’d share what I think is a little known goodie from Percona Toolkit for MySQL called pt-query-digest. One customer was suffering from … it scale poorly. This is when the ‘SHOW FULL PROCESSLIST’ captures generated by pt-stalk …
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
…last 5.5 series release of Percona Server,…MySQL will now be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL…query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix for bug #1070856 introduced a regression in Percona Server for MySQL…
Post: Percona Toolkit 2.2.2 released; bug fixes include pt-heartbeat & pt-archiver
May 6, 2013 By 2 Comments
During the Percona Live MySQL Conference & Expo 2013 the week before last, we quietly released Percona Toolkit 2.2.2 with a… corrupt data pt-heartbeat –utc –check always returns 0 pt-query-digest 2.2 prints unwanted debug info on tcpdump parsing… upgrade. Users may note the revival of the –show-all option in pt-query-digest. This had been removed in 2.2…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…last_insert_idblogpercona.commanual Last_query…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…query: session10 > show profile for query 11; +——————————–+———-+ | Status | Duration | +——————————–+———-+ | starting | 0.000018 | | Waiting for query cache lock | 0.000003 | | checking query cache for query…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… ‘dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr… for querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK, …views together. The complete method examples show how to create a complete refresh…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
Last time I wrote …lines (for instance SHOW ENGINE INNODB STATUS): mysql> pager less PAGER set to ‘less’ mysql> show engine innodb …mysql> pager md5sum PAGER set to ‘md5sum’ # Original query mysql> SELECT … 32a1894d773c9b85172969c659175d2d – 1 row in set (0.40 sec) # Rewritten query – wrong mysql…
