You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 2 hours 57 min ago
Updated: 2 hours 57 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog


MySQL Performance Blog - Tue, 08/07/2008 - 3:28am

As you probably know MySQL Replication (statement based) works by fetching statements from MASTERs binary log and executing them on the SLAVE. Since MySQL 4.0 this process is a bit more involved having events passing via relay logs on the Slave which also means there are two replication threads "IO Thread" and "SQL Thread" used in the process but idea remains the same.

Speaking on OSCON 2008

MySQL Performance Blog - Sun, 06/07/2008 - 7:09pm

It looks like I never blogged about it but I’m going to be speaking on OSCON 2008. Similar to the last year presentation I’ll do overview of MySQL Open Source storage engines. I’ll do brief overview of different storage engines available out there as well as share benchmark results for some of them.

Entry posted by peter | No comment

Recovering Innodb table Corruption

MySQL Performance Blog - Fri, 04/07/2008 - 8:27am

Assume you're running MySQL with Innodb tables and you've got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this:

How to load large files safely into InnoDB with LOAD DATA INFILE

MySQL Performance Blog - Thu, 03/07/2008 - 7:23pm

Recently I had a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary log. While this is generally a fast way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several problems with the very large transaction caused by the single statement. We didn't want to split the file into pieces for the load for various reasons.

Should we proclaim MySQL Community Edition Dead ?

MySQL Performance Blog - Wed, 02/07/2008 - 2:41am

We were chatting with Jeremy Cole today and he brought to my attention last version of MySQL Community Eddition (5.0.51) was released in November 2007 - over 7 months ago. MySQL 5.0.51a and MySQL 5.0.51b security fixes were released but these can't be considered proper releases.

MySQL 5.1 Stability

MySQL Performance Blog - Mon, 30/06/2008 - 9:08pm

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 ?

Entry posted by peter | 11 comments

Resyncing table on MySQL Slave

MySQL Performance Blog - Mon, 30/06/2008 - 4:41am

Sometimes MySQL Replication may run out of sync - because of its own buts or operational limitations or because of application mistake, such as writing to the slave when you should be only writing to the master. In any case you need slave to be synced with Master.

To discover the difference between Master and Slave you can use excellent Maatkit tool though if you you just need to resync small single table it may be easy to do it other way:

Assuming your table schema does not change you can do something like this on the master:

Web Site Optimization: FrontEnd and BackEnd

MySQL Performance Blog - Thu, 26/06/2008 - 7:04pm

I spent Monday and Tuesday this week on Velocity Conference It was
quite interesting event worth attending and it was very good to see
the problems in this are going beyond Apache, PHP, Memcache and MySQL.

Percona is looking for a Perl and Python expert

MySQL Performance Blog - Tue, 24/06/2008 - 3:01pm

If you’re a Perl and/or Python expert (preferably both), Percona may have a job for you.

We’re looking to either hire or sponsor significant development efforts for some open-source tools for MySQL, including but not limited to Maatkit and the Master-Master Replication Manager. This could be full-time or part-time, depending on the person and what seems to make the most sense. You can work remotely.

Neat tricks for the MySQL command-line pager

MySQL Performance Blog - Tue, 24/06/2008 - 2:57am

How many of you use the mysql command-line client?  And did you know about the pager command you can give it?  It's pretty useful.  It tells mysql to pipe the output of your commands through the specified program before displaying it to you.

Here's the most basic thing I can think of to do with it: use it as a pager.  (It's scary how predictable I am sometimes, isn't it?)

  1. mysql> pager less
  2. mysql> SHOW innodb STATUS\G

Will Falcon fly?

MySQL Performance Blog - Mon, 23/06/2008 - 4:02am

Why one may wonder, it’s just Swedish beer (State of Doplhin, MySQL UC 2006).

Lighttpd as reverse proxy

MySQL Performance Blog - Wed, 18/06/2008 - 2:28am

We often recommend to set lighttpd in front of apache to handle http requests (more about ) , 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.

Speaking on Velocity and Book Signing

MySQL Performance Blog - Fri, 13/06/2008 - 7:59am

I should have written about it a while ago but I never had a change.
I’m speaking at Velocity conference taking place in the Bay Area 23-24 of June.

Estimating Undo Space needed for LVM Snapshot

MySQL Performance Blog - Tue, 10/06/2008 - 1:02am

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.

How would you compress your MySQL Backup

MySQL Performance Blog - Fri, 06/06/2008 - 3:54am

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).

How much overhead DRDB could cause ?

MySQL Performance Blog - Tue, 03/06/2008 - 6:17am

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.

Tools to use for MySQL Performance Review

MySQL Performance Blog - Sun, 01/06/2008 - 12:34am

There are some tools we commonly use doing performance review and optimization and we often ask each other where that particular stuff is located on the web or what is exactly name of the command what does that.

Initially I thought creating internal Percona Wiki page, but thought there is no reason this information should not be public instead.

So now you can find our favorite MySQL Performance Review Tools on the site.

Is DNS the Achilles heel in your MySQL installation?

MySQL Performance Blog - Sun, 01/06/2008 - 12:12am

Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn't. Don't let it catch you off guard.

Should you name indexes while doing ALTER TABLE ?

MySQL Performance Blog - Thu, 29/05/2008 - 3:54am

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.

Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will add numeric prefix to it, for example:

Can MySQL temporary tables be made safe for statement-based replication?

MySQL Performance Blog - Tue, 27/05/2008 - 1:17am

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn't exist anymore, and the slave will have problems trying to replay any further statements that refer to these tables.

INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

MySQL Performance Blog - Sat, 24/05/2008 - 1:14pm

Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month.

Using flow control functions for performance monitoring queries

MySQL Performance Blog - Sat, 24/05/2008 - 8:43am

I'm not big fan on flow control functions like IF or CASE used in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer.

One way I find IF statement very useful is computing multiple aggregates over different set of rows in the single query sweep.

Apache PHP MySQL and Runaway Scripts

MySQL Performance Blog - Wed, 21/05/2008 - 5:55am

Sometimes due to programming error or due to very complex query you can get your PHP script running too long, well after user stopped waiting for the page to render and went browsing other sites.
Looking at Server-Status I've seen scripts executing for hours sometimes which is obviously the problem - they take Apache Slot, MySQL Connection and other resources.

I had discussion today who was thinking Apache would kill the script after "Timeout" specified in Apache configuration is reached - this was not my experience so I decided to run couple of tests to check it.

Dangerous command

MySQL Performance Blog - Wed, 21/05/2008 - 5:08am

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with "Syntax error".
So RTMF and documentation says
"This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23."


Subscribe to MySQL Performance Blog