MySQL Performance Blog

After some pause we are going to announce bunch of patches we made and ported for last period.

Ported patches (ported from Google V2 patch):

I had an interesting tuning case few days ago. The system serving high traffic using Innodb tables would be stalling every so often causing even very simple queries both reads and writes taking long time to complete, with progress almost paused (dropping from thousands to tens of queries per second).

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log (or --general-log in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by --log-slow-queries parameter (MySQL 5.1 requires also --slow-query-log), which was designed to store poorly performing queries that run at least 2 seconds.

Today was epoch day in American history. Maybe even most important day this year, but it’s not what I’d like to write about here. What does it mean for US citizens and all other people around the world? We know, but what does it mean for us - IT professionals and/or internet portals serving news for end users?

Increased number of requests to webservers, high network utilization, and huge number of queries against your databases (while on duty last night, I saw customer servers handling 10 times larger traffic than usual)..

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!