You are here

MySQL Performance Blog

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

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

Living with backups

MySQL Performance Blog - Fri, 07/11/2008 - 12:47am

Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right now. Either way the data is out and ready to save someone’s life (or job at least). Unfortunately taking backup does not come free of any cost.

Election night

MySQL Performance Blog - Thu, 06/11/2008 - 3:19am

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

MySQL, AIX5L and malloc()

MySQL Performance Blog - Mon, 03/11/2008 - 10:52pm

Some time ago I get brand new IBM POWER6 server as the replacement for “old” P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is not much documentation all around the web, I tried to set-up system and database traditional way. The first problem I hit was memory allocation and I think it is the best place share my remarks. Let’s start from the beginning..

OpenSQLCamp is comming close

MySQL Performance Blog - Sun, 02/11/2008 - 12:28pm

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.

Yasufumi Kinoshita joins Percona

MySQL Performance Blog - Sat, 01/11/2008 - 8:42am

I am happy to announce Yasufumi Kinoshita joins our team as Performance Engineer. Yasufumi is known as InnoDB hacking expert, and there is bunch of patches he made we include in our releases: innodb buffer pool scalability fix, innodb rw_lock fix, control InnoDB IO etc. Actually there is one more patch -
“adaptive flush” in InnoDB, which makes flushing process more uniform and predictable. Yasufumi will post about this patch soon.

A quest for the full InnoDB status

MySQL Performance Blog - Fri, 31/10/2008 - 10:11pm

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.

How expensive is a WHERE clause in MySQL?

MySQL Performance Blog - Fri, 31/10/2008 - 12:42pm

This is a fun question I've been wanting to test for some time.  How much overhead does a trivial WHERE clause add to a MySQL query?  To find out, I set my InnoDB buffer pool to 256MB and created a table that's large enough to test, but small enough to fit wholly in memory:

PLAIN TEXT SQL:
  1. CREATE TABLE `t` (
  2. `a` date NOT NULL
  3. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  4.  
  5. INSERT INTO t(a) VALUES(current_date);
  6. INSERT INTO t SELECT * FROM t;

Speaking on MySQL SF Meetup (Nov 3)

MySQL Performance Blog - Mon, 27/10/2008 - 8:28am

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.

Mirrored Binlogs patch

MySQL Performance Blog - Sat, 25/10/2008 - 3:31pm

Google patches V1 contain interesting patch to mirror binary log on slave. Although Google is preparing GlobalTransactionId patch, which going to be replacement for MirroredBinlogs, we still think mirroring binary logs is very useful for:

JOIN Performance & Charsets

MySQL Performance Blog - Tue, 21/10/2008 - 5:49pm

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:

Improved InnoDB rw_lock patch

MySQL Performance Blog - Mon, 20/10/2008 - 4:22pm

There is patch from Google to improve SMP performance , but for some workloads it showed for us reverse scalability.
E.g. update_key benchmark from sysbench. There are also results with Yasufumi’s rw_locks (http://bugs.mysql.com/bug.php?id=26442)

Percona’s patches spread to a wider audience

MySQL Performance Blog - Tue, 14/10/2008 - 3:50am

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.

Announce: Front End Performance Optimization

MySQL Performance Blog - Fri, 10/10/2008 - 11:41pm

I guess many of you know us and so our company for MySQL related services. It is true this is majority of our business at this point but it is far from everything.

Our goal in reality is to help people to build and operate quality systems, typically web sites, which means we help customers with performance, scalability, high availability as the whole, not just MySQL related issues.

Three ways to know when a MySQL slave is about to start lagging

MySQL Performance Blog - Thu, 09/10/2008 - 12:56am

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:

Percona welcomes Ewen Fortune and Piotr Biel

MySQL Performance Blog - Wed, 01/10/2008 - 5:03pm

Although we haven’t announced any new members in a while, the Percona team has continued to grow steadily behind the scenes. Our hiring policy is to have a few months’ provisional period to ensure the absolute highest quality of service and consistency of results provided to our clients. Today I’m proud to officially welcome Ewen Fortune and Piotr Biel, who have been working with us for quite a few months.

Why audit logging with triggers in MySQL is bad for replication

MySQL Performance Blog - Mon, 29/09/2008 - 12:44pm

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.

What is stored InnoDB buffer pool

MySQL Performance Blog - Mon, 29/09/2008 - 2:35am

Ever wonder what is stored in InnoDB buffer pool at the moment ?

It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content

Four ways to optimize paginated displays

MySQL Performance Blog - Wed, 24/09/2008 - 2:34pm

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!

Fighting MySQL Replication Lag

MySQL Performance Blog - Tue, 23/09/2008 - 4:16am

The problem of MySQL Replication unable to catch up is quite common in MySQL world and in fact I already wrote about it. There are many aspects of managing mysql replication lag such as using proper hardware and configuring it properly. In this post I will just look at couple of query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag

Finding what Created_tmp_disk_tables with log_slow_filter

MySQL Performance Blog - Mon, 22/09/2008 - 3:16pm

Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

show global status like 'Created_tmp%'

| Created_tmp_disk_tables           | 91970        |
| Created_tmp_files                 | 19624        |
| Created_tmp_tables                | 1617031      |

Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

mysqladmin ext -ri60

Speaking on HighLoad++, Moscow, Russia

MySQL Performance Blog - Mon, 22/09/2008 - 5:26am

I’ll be speaking at HighLoad++ conference in Moscow,Russia taking place 6,7 Oct 2008. This conference was quite success with very interesting presentation last year and I’m hoping it would be even better this year.

I also will have a full dayMaster Class focused on Scaling MySQL w Sharding and Replication based on material in our book

A common problem when optimizing COUNT()

MySQL Performance Blog - Sat, 20/09/2008 - 4:38pm

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

When is it a time to upgrade memory ?

MySQL Performance Blog - Wed, 17/09/2008 - 4:29am

Quite commonly I get a question similar to this - “My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?”
This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
How to figure out if it is time for upgrade when ?

Unused indexes by single query

MySQL Performance Blog - Sat, 13/09/2008 - 1:31am

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

Google’s user_statistics V2 port and changes

MySQL Performance Blog - Fri, 12/09/2008 - 9:18pm

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

      New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)
      New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user
      FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

Pages

Subscribe to MySQL Performance Blog