Next week I’m teaching an online Percona Training class, called Analyzing SQL Queries with Percona Toolkit. This is a guided tour of best practices for pt-query-digest, the best tool for evaluating where your database response time is being spent. This month we saw the GA release of MySQL 5.6, and I wanted to check if any [...]
Adventures in archiving
February 11, 2013 By 16 Comments
One of our Remote DBA service clients recently had an issue with size on disk for a particular table; in short this table was some 25 million rows of application audit data with an on disk size of 345GB recorded solely for the purposes of debugging which may or may not occur. Faced with the task of [...]
Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not [...]
Fun with the MySQL pager command
January 21, 2013 By 6 Comments
Last time I wrote about a few tips that can make you more efficient when using the command line on Unix. Today I want to focus more on pager. The most common usage of pager is to set it to a Unix pager such as less. It can be very useful to view the result [...]
Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
This is a recurrent question made by our MySQL Support customers: How can I audit the login attempts in MySQL? Logging all the attempts or just the failed ones is a very important task on some scenarios. Unfortunately there are not too many audit capabilities in MySQL Community so the first option to audit MySQL’s [...]
Percona Toolkit Webinar followup Q&A
December 19, 2012 By 1 Comment
First, a thank you to everyone who attended the webinar Today, I appreciate your time and nice comments. As promised, here are answers to questions that couldn’t be answered during the talk: Q: How do you install the tools? The manual has full details, but it’s important to know that the latest release for [...]
Percona XtraDB Cluster (PXC): what about GRA_*.log files ?
December 19, 2012 By Leave a Comment
How easy is it to identify and debug Percona XtraDB Cluster replication problem ? If you are using PXC, you may have already seen in your datadirectory several log files starting with GRA_ Those files correspond to a replication failure. That means the slave thread was not able to apply one transaction. For each of [...]
The Optimization That (Often) Isn’t: Index Merge Intersection
December 14, 2012 By 6 Comments
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didn’t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and they’d wonder why the EXPLAIN plan for a given SELECT would [...]
Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
I had a customer recently who needed to reduce their database size on disk quickly without a lot of messy schema redesign and application recoding. They didn’t want to drop any actual data, and their index usage was fairly high, so we decided to look for unused indexes that could be removed. Collecting data It’s [...]
Concatenating MyISAM files
November 19, 2012 By 5 Comments
Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take [...]
