One of the most common causes of a poor Sphinx search performance I find our customers face is misuse of search filters. In this article I will cover how Sphinx attributes (which are normally used for filtering) work, when they are a good idea to use and what to do when they are not, but you still want to take advantage of otherwise superb Sphinx performance.

The Problem

While Sphinx is great for full text search, you can certainly go beyond full text search, but before you go there, it is a good idea to make sure you’re doing it the right way.

In Sphinx, columns are basically one of two kinds:

a) full text
b) attributes

Speaking in MySQL terms, Full text columns are always indexed and using the very …

There are times where you need to know exactly how much memory the mysqld server (or any other program) is using, where (i.e. for what function) it was allocated, how it got there (a backtrace, please!), and at what point in time the allocation happened.

For example; you may have noticed a sharp memory increase after executing a particular query. Or, maybe mysqld is seemingly using too much memory overall. Or again, maybe you noticed mysqld’s memory profile slowly growing overtime, indicating a possible memory bug.

Whatever the reason, there is a simple but powerful way to profile MySQL memory usage; the Massif tool from Valgrind. An excerpt from the Massif manual page (Heap memory being simply the allotted pool of memory for use by programs);

Massif tells you not only how much heap memory your program is using, it also …

With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage.

I can’t count how many time I had to scratch my head with system configured to consume only few GBs in global buffers growing to consume much more for some unknown needs leaving me puzzled whenever it is user variables, complex stored procedures temporary tables or something else. Not only such connection related allocations are invisible but many global allocations are poorly visible too. Sure, we know how much memory was allocated for Innodb Buffer Pool or Query Cache but amount of memory used for …

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 show N possible index choices but only one index actually used.

To some extent, MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer plan. The basic idea behind index merge is that for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as we will see below) scan the indexes on …

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;

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 …

Did you just run an UPDATE against your 10 million row users table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement?  This is possible if you are running in binlog_format=ROW !

Imagine this scenario:

CREATE TABLE `t1` (
 `c1` int(11) NOT NULL AUTO_INCREMENT,
 `c2` varchar(10) NOT NULL,
 PRIMARY KEY (`c1`)
) ENGINE=InnoDB;
INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina');

We run an accidental UPDATE statement that changes a row:

UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2;

If we examine this UPDATE using the Binary Logging format of STATEMENT the entry would look like:

# at 464
#121019 16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677442/*!*/;
BEGIN
/*!*/;
# at 532
#121019 16:10:42 server id 1 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0
SET …

I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording.

I’d like to invite folks who are interested in tools for query optimization to attend the new Percona online virtual training Analyzing SQL Queries with Percona Toolkit, October 29 – November 2, 2012. See http://store.percona.com/catalog.php?category=17 for details on this training class and how to register to attend.

During my webinar, I got a number of good questions. Here are the questions (paraphrased), with my answers.

Q: Can you link to the …

The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only the latest one and that is not very helpful when you are debugging your application code. Continuing my series of blog posts about Percona Toolkit, in this case I’m going to talk about pt-deadlock-logger.

pt-deadlock-logger

The tool is very simple and useful. It monitors the output of SHOW ENGINE INNODB STATUS and log the new deadlocks to a file or to a table that we can later review. Let’s see how it works with an example.

…

Ever ran into a situation where you saw “some important variable you really needed to know about=<optimized out>” while debugging? Let’s look at an example:

[Roel@qaserver master-data]$ gdb /percona-server/Percona-Server-5.5.25a-rel27.1-285.Linux.x86_64/bin/mysqld ./core.3200 
[...]
(gdb) bt
[...]
#20 handle_select (thd=0x33acd30, lex=0x33ae900, result=0x7f3e840058d0, setup_tables_done_option=1073741824)
    at /percona-server/5.5/Percona-Server-5.5.25a-rel27.1/sql/sql_select.cc:312
#21 mysql_execute_command (thd=thd@entry=0x33acd30)
    at /percona-server/5.5/Percona-Server-5.5.25a-rel27.1/sql/sql_parse.cc:3138
#22 mysql_parse (thd=thd@entry=0x33acd30, rawbuf=<optimized out>, length=72, parser_state=parser_state@entry=0x7f3ed013f810)
    at /percona-server/5.5/Percona-Server-5.5.25a-rel27.1/sql/sql_parse.cc:5809
#23 dispatch_command (command=COM_QUERY, thd=0x33acd30, packet=<optimized out>, …