You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 40 min 26 sec ago
Updated: 40 min 26 sec ago
Update every: 2 hours


Subscribe to MySQL Performance Blog

Scaling to 256-way the Sun way

MySQL Performance Blog - Wed, 12/11/2008 - 4:55am

As you may have recently seen there are some articles about scaling MySQL one 256-way system.

I though wow did they really make it work, considering how many bottlenecks remain in MySQL.

What article really tells us ?

More patches

MySQL Performance Blog - Wed, 12/11/2008 - 4:07am

After some pause we are going to announce bunch of patches we made and ported for last period.

Ported patches (ported from Google V2 patch):

Thoughs on Innodb Incremental Backups

MySQL Performance Blog - Mon, 10/11/2008 - 7:22pm

For normal Innodb “hot” backups we use LVM or other snapshot based technologies with pretty good success. However having incremental backups remain the problem.

First why do you need incremental backups at all ? Why not just take the full backups daily. The answer is space - if you want to keep several generations to be able to restore to, having huge amount of full copies of large database is not efficient. Especially if it only changes couple of percents per day.

Drilling down to the source of the problem

MySQL Performance Blog - Sun, 09/11/2008 - 3:12am

I had an interesting tuning case few days ago. The system serving high traffic using Innodb tables would be stalling every so often causing even very simple queries both reads and writes taking long time to complete, with progress almost paused (dropping from thousands to tens of queries per second).

New Presentations published (Meetup and Highload)

MySQL Performance Blog - Fri, 07/11/2008 - 7:27pm

We have finally found the time to update out presentation pages - we’ve added the presentation on MySQL Scaling by Sharding and Replication as well as Russian Language presentations I’ve given at HighLoad++ conference back in October.

Poor man’s query logging

MySQL Performance Blog - Fri, 07/11/2008 - 2:20pm

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log (or --general-log in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by --log-slow-queries parameter (MySQL 5.1 requires also --slow-query-log), which was designed to store poorly performing queries that run at least 2 seconds.

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

Pages

Subscribe to MySQL Performance Blog