MySQL Performance Blog

OpenSQLCamp is coming close and it is your last change to register.

I’m very excited about this event as it looks to be marketing free community gathering, having much fewer attendees but many of them are well known MySQL Professionals. Besides hopefully in depth sessions this promises a lot of good informal chats.

When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command -- SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large spike in number of connections to MySQL, which often happens when several transactions kill the database performance resulting in very long execution times for even simplest queries, or a huge deadlock.

November 3rd, I’m speaking at San Francisco MySQL Meetup - The talk will be about Scaling MySQL driven Web Sites by Sharding and Replication. This is Free event and you’re surely most welcome to join. At this point I see there are some 70 people signed up and 59 spots left. BTW: This will not be exactly same sessions as on Velocity I will be adding few more slides to get into more details.

We have written before about the importance of using numeric types as keys, but maybe you've inherited a schema that you can't change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries.

Take the following example, using the InnoDB storage engine:

Percona’s patches are now available to a wider audience via OurDelta, a community effort to provide  builds with features (Percona patches, Google patches, etc) and storage engines (PBXT, Sphinx, etc) that aren’t in the main MySQL server. Arjen Lentz is really the brainchild behind this.

The trouble with slave lag is that you often can't see it coming. Especially if the slave's load is pretty uniform, a slave that's at 90% of its capacity to keep up with the master can be indistinguishable from one that's at 5% of its capacity.

So how can you tell when your slave is nearing its capacity to keep up with the master? Here are three ways:

Recently I was tasked with investigating slippage between master and slave in a standard replication setup.

The client was using Maatkit's mk-table-checksum to check his slave data was indeed a fair copy of that of the master.

mk-table-checksum --algorithm=BIT_XOR h=hostname.local,u=root,p=xxx --replicate=checksum.checksum --emptyrepltbl --chunksize=500000 --databases mydb --sleep 1

He could then examine the checksum.checksum table and see all was well, however there were various tables with different crc values.

A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a "next" and "previous" link. And usually, show how many items are in the whole list and how many pages of results there are.

Rendering such a display can consume more resources than the entire rest of the site!

Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

show global status like 'Created_tmp%'

| Created_tmp_disk_tables           | 91970        |
| Created_tmp_files                 | 19624        |
| Created_tmp_tables                | 1617031      |

Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

mysqladmin ext -ri60

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like “sometimes my slave clone script does not work” may hang on for years.