… type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance. The query patterns will include…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…’s ability to optimize queries for a star schema. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each set is known as a “flight”. I have labeled each query… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to… SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But there…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… like this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0…, depending on the query optimizer and the MySQL version, we always recommend to check manually the optimizations proposed by Percona Toolkit. The MySQL server, of…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… nothing wrong with a virtual-machine approach, but for the optimal production deployment you want two identical physical boxes with at… queries in the output of SHOW PROCESSLIST that might be a sign that trouble is brewing. Or if you watch your MySQL… simple query, but it’s always possible to build something more involved if that’s what you need. Q: What MySQL HA…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server for MySQL was built with YaSSL which could… query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix for bug #1070856 introduced a regression in Percona Server for MySQL…
Post: 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar
March 22, 2013 By Leave a Comment
… the following tools: pt-query-digest, to select the queries you should try to improve to get optimal response times pt-archiver, to… solve typical, real-world MySQL database administration challenges, such as: Selecting which queries you should try to optimize to get better response times…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions (TPS… the perfect case for read-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by definition…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
…-only server nor rewriting application queries. Furthermore, it can be combined with the read-only optimization in MySQL 5.6 to achieve even….e. all queries are single-statement SELECT statements in autocommit. Which is the perfect case for read-only optimization in MySQL 5.6…-only optimization in MySQL 5.6 does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server for MySQL was built with YaSSL which could… query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix for bug #1070856 introduced a regression in Percona Server for MySQL…
