MySQL Performance Blog

It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost non-blocking way ( true for InnoDB setups) for cloned server. Here is command

When it finished on destination server you run

And you have ready database directory, just copy my.cnf from original server and start mysqld.

Playing with last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter wrote about it some time ago, but I wanted to review that data having some new information. In current multi-core word the compression utility should utilize several CPU to speedup operation, and another my requirement was the ability to work with stdin / stdout, so I could do scripting something like: innobackupex --stream | compressor | network_copy.

My research gave me next list: pigz (parallel gzip), pbzip2 (parallel bzip2), qpress ( command line utility for QuickLZ) and I wanted to try LZO (as lzop 1.03 command line + LZO 2 libraries). Actually lzop does not support parallel operations, but it is know to have good decompression speed even with 1 thread. UPDATE 17-Mar-2009: I added lzma results also by request from comments.

[read more...]

One question which comes up very often is when one should use SAN with MySQL, which is especially popular among people got used to Oracle or other Enterprise database systems which are quite commonly deployed on SAN.

My question in such case is always what exactly are you trying to get by using SAN ?
[read more...]

We finally managed to get Intel X25-E SSD drive into our lab. I attached it to our Dell PowerEdge R900. The story making it running is worth separate mentioning - along with Intel X25-E I got HighPoint 2300 controller and CentOS 5.2 just could not start with two RAID controllers (Perc/6i and HighPoint 2300). The problem was solved by installing Ubuntu 8.10 which is currently running all this system. Originally I wanted to publish some nice benchmarks where InnoDB on SSD outperforms RAID 10, but recently I faced issue which can make previous results inconsistent.
[read more...]

There are few terms you need to have a good understanding if you're working with high volume systems. I commonly see these mixed and people not understanding the difference between them.

Performance - The performance comes down to performing up to users expectations (or expectations of your boss) which drills down to two important metrics - response time also sometimes called latency and throughput also called capacity. Response time applies to even single user and mostly critical in applications when a complex task needs to be performed such as report generation or search. It is just unacceptable if search takes a minute even if system has a capacity running for 100 of them at the same time. The response time is the most important aspect of performance because when we define system capacity we also define conditions on response time. For example system can handle 100 transactions a second with 95 percentile response time of 1 second.
[read more...]

When I visit customers quite often they tell me about number of creative techniques they heard on the conferences, read on the blogs, forums and Internet articles and they ask me if they should use them. My advice is frequently - do not. It is fun to be creative but creative solutions also means unproven and people who had to become creative with their system often did that because they had no choice. Of course when they came to the bunch of conferences and told their story which resonated across the Internet sticking to the people mind as a good practice.

There are 2 things you should ask yourself. First is the scale comparable - the recipes from Facebook, YouTube, Yahoo, are not good for like 99.9% of the applications because they are not even remotely close in size and so capacity requirements. Second if this "smart thing" was truly thought out architecture choice in beginning or it was the choice within code base constrains they had, and so you might not have.

[read more...]

For long time as main backup solution for MySQL on Linux we have been using LVM snapshots. Performance concerns from performance critical envinronment caused us to do LVM performance research which showed horrible results forcing us to look at more performing alternatives.

Innodb Hot Backup is a good working solution but it is not Open Source and so we can't ensure it will support all XtraDB features - extra undo slots, rollback segments etc.

After considering all alternatives we decided to develop Percona Xtrabackup tool, which will use same backup approach as InnoDB Hot Backup, that is taking online copy of InnoDB table files and transactional logs. This is going to be fully OpenSource, GPL licensed backup solution for MySQL (MyISAM and InnoDB for now).

We gathered basic ideas here http://www.percona.com/docs/wiki/percona-xtrabackup:start, and for the first stage it will be just full backup of files. For next stages we are looking to implement advanced functionality like incremental backup (copy only changes from last backup),table-level backup (copy only specified tables), streaming and parallel copying. Source code will be posted to Xtrabackup Launchpad project (https://launchpad.net/percona-xtrabackup/)

We call for more ideas you would like to see implemented (please leave them in comments). We got sponsorship for the base implementation work, but we're still looking for sponsorship for additional features, contact us http://www.percona.com/contacts.html if you are interested. Percona will also significantly invest in this project.

Here is nice gotcha which I've seen many times and which can cause just a minefield for many reasons.
Lets say you had a system storing articles and you use article_id as unsigned int. As the time goes and you see you may get over 4 billions of articles you change the type for article_id to bigint unsigned but forget linked tables.
[read more...]

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.
[read more...]