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 quite easy to collect statistics about index usage in Percona Server (and others) using the User Statistics patch.  By enabling ‘userstat_running’, we start to get information in the INFORMATION_SCHEMA.INDEX_STATISTICS table.  This data collection does add some overhead to your running server, but it’s important to leave this running for a good long while to get a good dataset that is representative of as much of your workload as possible.

If you miss collecting index stats while some …

Notice the result of the NOW() function in the following query. The query was run on a real database server and I didn’t change the clock of the server or change anything in the database configuration settings.

mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 1999-01-01 00:00:00 | 2012-11-29 05:50:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)

You may proceed to party like it is 1999.

How can the NOW() function return a value in the past?

The “secret” is the TIMESTAMP variable, which is a special MySQL variable that can be set by the MySQL client. MySQL adds special events in the binary log which set the TIMESTAMP and INSERT_ID (which is used for AUTO_INCREMENT replication) to the correct values to ensure that statements replicate properly.

Here is …

Earlier this week, Cédric blogged about how easy we can get confused between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according to the documentation the 2nd worst possible execution plan after a full table scan.
If it is obvious that a full table scan is not good for performance, how much can we expect if we can switch to a full index scan? In other terms, is a full table scan always the worst possible execution and should it be avoided at all costs?

Let’s take the employees database, and slightly modify the employees tables:

mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB;

One of my favorite tools in the Percona Toolkit is pt-query-digest.  This tool is indispensable for identifying your top SQL queries, and analyzing which queries are accounting for your database load.

But the report you get from pt-query-digest is only as good as the log of queries you give it as input.  You need a large enough sample of query logs, collected over a period of time when you have representative traffic on your database.

You also need the log to include all the queries, not just those that take more than N seconds.  The reason is that some queries are individually quick, and would not be logged if you set the long_query_time configuration variable to 1 or more seconds. …

I’ve got the great response to my two previous polls (1,2) so lets continue learning about how we all use MySQL. The question of this week is What is the maximum number of tables per MySQL instance do you use ?

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Thank you for participating!

My previous poll got a great response so I thought I should continue these series.
The question of the day today is How much data do your store in your largest MySQL instance ? I’m interested about single instance not the total amount of data you have in MySQL in your Sharded replicated environment. Feel free to share details in comments – are you using MyISAM or Innodb ? Is it working out well for you or are there any challenges you’re seeing ?

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Thank you for your participation !

We have seen number of issues with MySQL Server related to amount of memory you have in the system – these range from problems with large size Query Cache to bad drop table performance with large Innodb Buffer Pool size. As such I wonder how much memory do we really use to run MySQL Server these days ?

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Feel free to select multiple sizes if you have multiple kinds of instances in production and for boundary numbers please select the highest range which applies, ie for 16GB system select 16-64GB range. Thank you for your feedback ! I will keep the pool open until the end of the year when it will be frozen so we do not get results from multiple years mixed.

MySQL command line utilities have an interesting property – if you only use the prefix of the option or command it will go over the list of available command and if there is only one command with matching prefix it will execute it with no warnings or any kind, otherwise it will report the error. For example mysqladmin e works as there is only one command “extended-status” which starts with “e” mysqladmin f however does not work because there are multiple commands which start with “f”.

This behavior can cause all kings of issues especially if you make a typo. I made one today (thankfully on the test box). MySQL init Scripts use “stop” and “start” commands to start and stop the server while mysqladmin uses “shutdown”. I mixed this while trying to stop Percona Server:

root@smt2:/var/lib/mysql# mysqladmin stop …

A few weeks back, I was working on a customer issue wherein they were observing database performance that dropped through the floor (to the point of an outage) roughly every 4 weeks or so. Nothing special about the environment, the hardware, or the queries; really, the majority of the database was a single table with an auto-incrementing integer PK and a secondary UNIQUE KEY.

The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding to the columns in the secondary index, and they were coming in at a rate of approximately 1500 to 2000 per second, sustained, 24h per day. The mathematically-astute among you may already be able to see where this is going.

For purposes of discussion, we can use the following table to illustrate the situation:

CREATE TABLE update_test (
  id INT UNSIGNED NOT NULL …

If you are planning to upgrade or make any configuration change on your MySQL database the first advice usually is:

- Benchmark!

How should we do that benchmark? People usually run generic benchmark tools like sysbench, tpcc or mysqlslap that are good to know the number of transactions per seconds that a database can do but it doesn’t care about your workload, data set or queries. Those tools just run random queries against random generated data.

The best way to run a benchmark is replaying the load of your production server against a different database server and here is where Percona Playback can help us. Percona Playback is a new tool that can replay the data captured from the production server in another different server. It can replay queries from tcpdump or slow query logs. With this tool you can measure how a database upgrade, change on …