On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to… variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But there’s a finite amount of…_types` table by primary key, the result was a query that took half the time. An important conclusion of my presentation today is…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… a simple query and receive a response back in a reasonable (what is reasonable is determined by your environment), it’s a safe bet that MySQL… might say that MySQL/NDB Cluster is also somewhat similar to Oracle RAC; all of the SQL nodes in a MySQL Cluster installation…, and went to try to install MySQL. It failed miserably on the mysql_install_db process. Every time I tried, I got some…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…working with a set of customers on a longer term basis which has given me time to… process a lot more effectively when I know the relationships between tables versus observing queries only. …a Java driver for your database — I’m using the MySQL Connector/J Install the graphiz package Creating a mysql…
Post: Experiences with the McAfee MySQL Audit Plugin
May 23, 2013 By 4 Comments
… one is undocumented on the wiki at the time of writing, and is a list of users for which you do not…”,”cmd”:”Connect”,”query“:”Connect”} In conclusion, the plugin API seems to be opening new possibilities of extending MySQL‘s behavior in a way that, once set up, is transparent to users, and the McAfee MySQL Audit Plugin…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… known as a “flight”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, each flight examines different time periods or…. All OS caches are dropped at this time as well. These set of queries were tested on the SSB at SCALE FACTOR… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… did not have time to analyze it in-depth. If you try to do something like this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0…-, but we will get a warning (note severity, to be exact): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… described a solution for keeping the caches of a MySQL standby server hot using MySQL slow logs with long_query_time set to 0. Here are a some lessons we learned when logging a high volume of queries to…
Post: High-load problems? Investigate them with 'pt-query-digest'
April 23, 2013 By 2 Comments
…’d share what I think is a little known goodie from Percona Toolkit for MySQL called pt-query-digest. One customer was suffering… is no run time data available, but at least you get a nice ranking of queries sorted by the number of times they appear… with MySQL databases, getting familiar with Percona Toolkit for MySQL will let you do more work, in a safer way, and in less time…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
….g. pt-query-digest, pt-upgrade, and pt-online-schema-change). Here are some highlights: Official support for MySQL 5.6 and… simpler pt-query-digest 2.2 has fewer options now. Basically, we re-focused it on its primary objective: analyzing MySQL query logs… options a little differently. This magic is gone now: both tools run forever by default, so specify –iterations or –run-time to…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed …Results: First of all, we used a smaller dataset for this benchmarks …time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql…
