You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 3 years 9 months ago
Updated: 3 years 9 months ago
Update every: 2 hours

Percona's Data performance and scalability blog
Subscribe to MySQL Performance Blog

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

PLAIN TEXT SQL:
  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 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.

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

Wanted: Better memory profiling for MySQL

MySQL Performance Blog - Mon, 19/05/2008 - 5:40am

Quite frequently I would log in to customers system and find MySQL using too much memory. I would look at memory consumed by Innodb (it is often higher than innodb_buffer_pool_size) substract memory used by other global buffers such as query_cache_size and key_buffer and will in many cases see some mysterous memory which I can’t really explain. It can be several Gigabytes accounting for over 50% of memory usage of MySQL in some cases, though typically it is much smaller fraction.

Concurrent inserts on MyISAM and the binary log

MySQL Performance Blog - Thu, 15/05/2008 - 12:22am

Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status.

MySQL Is back to Open Source Camp ?

MySQL Performance Blog - Wed, 14/05/2008 - 6:11am

Looking at Kaj’s Blog Annoucement MySQL has pulled back on the plans to release portions of the servers as Open Source only.

I am extremely happy to hear these news ! This is good for MySQL as a company, MySQL customers and MySQL users.

I’m hoping Community feedback was serious contributer to this decision, though I know there were a lot of Internal discussions as well. In any case this sends a great message to community - Speak up and you may be heard.

MySQL Performance Engineer opening

MySQL Performance Blog - Tue, 13/05/2008 - 10:15pm

As you know we love to analyze performance of various MySQL features, benchmark, compare, analyze things and post our findings on MySQL Performance Blog. However recently we got too busy with serving out customers and the backlog of things to take a look and write about is just growing larger and larger. So we decided to hire someone who could focus on such tasks.

What are we expecting from MySQL Performance Engineer ?

Quickly preloading Innodb tables in the buffer pool

MySQL Performance Blog - Fri, 02/05/2008 - 6:13am

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information.

Learning about MySQL Table Fragmentation

MySQL Performance Blog - Fri, 02/05/2008 - 5:57am

Recently I was working with the customer who need quick warmup - to get Innodb table fetched in memory as fast as possible to get good in memory access performance.

To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query because it will do full table scan - running count(*) without where clause may pick to scan some small index instead.

Pages

Subscribe to MySQL Performance Blog