MySQL Performance Blog

Percona has a strong team of MySQL developers and consultants on board, so we decided to prepare MySQL releases with our patches and third-party patches which we think are very useful. We actually use these internally and for our customers.

Current releases include:

• microslow patch (enables microsecond resolution in slow logs)
• execution plan (show info about query execution in slow log)
• InnoDB statistic (show extended InnoDB usage during query execution in slow log)
• Google’s patches with user statistics, and statistics about tables and indexes usage

You can find more info about some of our patches here:
http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/
Some features were sponsored by customers and we are allowed to publish them under GPL and some we developed for internal needs.

We prepared 5.0.62 release (based on MySQL Enterprise 5.0.62 source, taken from http://mirror.provenscaling.com/mysql/enterprise/source/5.0/) and 5.1.26-rc release, and they are currently available only as source .tar.gz, but we will provide public access to our YUM repository with binaries, RedHat/CentOS and Suse RPMS / SRPMS when they’re available. Here are links to the source releases:

mysql-5.0.62-percona.tar.bz2 md5:0d8a37e09eca93cde6170866293a82ff
mysql-5.1.26-percona.tar.bz2 md5:0574b38755786a97bc476058051cbe82

You can find the individual patches here:
http://www.mysqlperformanceblog.com/mysql-patches/

Besides standard 5.1, we are going to make releases with the InnoDB plugin version compiled in (not as a plugin), as the current system with independent InnoDB binaries does not seem handy for us.

We have couple of ideas what else we can develop and include in our releases, but also your comments are very welcome.

We are still investigating Launchpad features, there is our -percona branch, but it is outdated at this moment. Probably our next releases will be based on Launchpad branches, we still need to decide how it fits into our development model.

Disclaimer: Although we use tested releases as best as we can, and use them internally, we do not claim they are bug-free, and you should use them only if you know what you are doing.

One of our customers was setting up box for MySQL LVM backups. The box had 2 RAID volumes (internal drives and enclosure) with database being stored on enclosure drives and internal drives used pretty much for OS and logs (database grew too large and so was moved to enclosure completely).

As database was large and active I thought it would make sense to have LVM undo space located on the internal drives so LVM and backup has less backup on the running system.

As the system was configured creating snapshot volume with 100G of undo space failed complaining there is not enough space available on the volume….
[read more...]

Baron recently wrote about very helpful but often forgotten about “Pager” feature of command line client. There is another one which falls into the same list - the –tee option.

Specifying –tee=/path/to/file.log you can get all session content (everything typed in and printed out) stored in the log file. Quite handy for example to keep track of changes done on production.

Moreover you can put tee=/logs/mysql.log in “mysql” section in my.cnf to have logging enabled automatically when you start the client.

If you’re looking to log session beyond MySQL command line client you can check out “script” tool.

I have been helping customer today to resolve his lockups in production by downgrading from MySQL 5.1.25 to 5.0 We have other customers (and our own projects as well) which run on MySQL 5.1 successfully but I can’t it is on par with MySQL 5.0 stability yet.

This given customer was running MyISAM on FreeBSD with quite basic queries, so may be there are some issues with FreeBSD and MySQL 5.1 still ?

Why one may wonder, it’s just Swedish beer (State of Doplhin, MySQL UC 2006).
One week ago Jim Starkey sent message http://www.firebirdnews.org/?p=1742 so he will not work for MySQL anymore and starting new project. While that’s fully Jim Starkey’s personal decision, I expected some comments about Falcon future development from MySQL / Sun side. Jim was not just ordinary developer, but lead of project and main architect of Falcon and his leaving may change a lot. For now MySQL’s calm seems stunned or indifferent to Falcon’s destiny.
Falcon has being developed for about 2.5 years, and despite it named “beta” stage, it sill crashes in our quite simple benchmarks, so it’s very optimistic “beta”. That’s why I wonder if this project will be ever finished, especially when main architect left it.

We often recommend to set lighttpd in front of apache to handle http requests (more about http://www.mysqlperformanceblog.com/2006/05/21/speedup-your-lamp-stack-with-lighttpd/ ) , redirect dynamic requests to apache and handle static files by itself. I just gathered step-by-step instruction how to do that in 10 minutes, as it may be not so obvious.
[read more...]

We know MySQL Backups using LVM are pretty cool (check out mylvmbackup) or MMM though it is quite typical LVM is not configurable properly to be usable for MySQL Backups.

Quite frequently I find LVM installed on the system but no free space left to be used as snapshot undo space, which means LVM is pretty much unusable for backups or required space is very small - created without good understanding on how much space do you need for undo.
[read more...]

Backing up MySQL Database most people compress them - which can make a good sense in terms of backup and recovery speed as well as space needed or be a serious bottleneck depending on circumstances and approach used.

First I should mention this question mainly arises for medium and large size databases - for databases below 100GB in size compression performance is usually not the problem (though backup impact on server performance may well be).

We also assume backup is done on physical level here (cold backup, slave backup, innodb hot backup or snapshot backup) as this is only way practical at this point for databases of decent size.

Two important compression questions you need to decide for backup is where to do compression (on the source or target server if you backup over network) and which compression software to use.
[read more...]

I was working with the customer today investigating MySQL over DRBD performance issues. His basic question was why there is so much overhead with DRBD in my case, while it is said there should be no more than 30% overhead when DRBD is used.

The truth is - because how DRBD works it does not adds static overhead which could be told as 10% or 80% and you really need to understand how DRBD works as well as how IO system is utilized to understand how much overhead you should expect.
[read more...]

MySQL Server does not require you to specify name of the index if you’re running ALTER TABLE statement - it is optional. Though what might be good practical reasons to specify the key name or omit ?

Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining the indexes.
[read more...]