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

Making bugs public - good job MySQL

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

If you have been MySQL User for many years you might remember the times when MySQL had “zero bugs policy”, this is when all known bugs really were fixed before release was made. To be honest at that time bugs were reported via bugs mailing list not via bugs database as they are now so they were not tracked so accurately but still there was intention and all known serious bugs were fixed before release was made.

What would you like to hear about on MySQL Users Conference 2008 ?

MySQL Performance Blog - Mon, 22/10/2007 - 4:25pm

It is submission time now, with about one week left till proposal submission deadline. Both me and Vadim had submitted few talks to the Grand Jury which will make elite selection of sessions for MySQL Users Conference and we plan to submit few more.

Traffic Tricks by Hosting Providers.

MySQL Performance Blog - Wed, 17/10/2007 - 9:20pm

I already wrote once about hosting troubles which we had with this site a while back. Today we had another trouble to one of the European hit servers for ClickAider project.

MySQL Performance - eliminating ORDER BY function

MySQL Performance Blog - Wed, 17/10/2007 - 12:24pm

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some interesting exception.

MySQL Northern European Customer Conference

MySQL Performance Blog - Wed, 17/10/2007 - 11:18am

Yesterday I’ve attended MySQL Customers Conference in London. This event is much smaller size than Users Conference (one day and about 170 people attending) and surely less geeky - there were no one from MySQL Development Support or Consulting teams and Sales Engineers were as close as you could get. Though Anders Karlsson and Ivan Zoratti are one of the best of the kind being more technical than quite a few of the teams mentioned.

Be careful when joining on CONCAT

MySQL Performance Blog - Tue, 16/10/2007 - 9:32pm

The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT('prefix-', tb2.id) with tb1.vid - indexed varchar(100) and tb2.id - int(11) column. No matter what I did - forced it to use key, forced a different join order, it did not want to use tb1.vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge:

Managing Slave Lag with MySQL Replication

MySQL Performance Blog - Fri, 12/10/2007 - 7:18pm

The question I often get is how far MySQL may fall behind and how to keep replication from lagging.

The lag you will see will vary a lot from application to the application and from load to load. Plus what is the most important within same application the lag will likely have spikes - most of applications would have typical lag within few milliseconds while there will be rare cases when replication lags behind several seconds or even longer.

There are multiple reasons why application falls behind and why we see those lag spikes:

MyISAM Scalability and Innodb, Falcon Benchmarks

MySQL Performance Blog - Fri, 12/10/2007 - 12:01pm

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.

The main query which showed this problem was similar to this:

Add an option to Fail on Innodb Initialize failure, Please ?

MySQL Performance Blog - Thu, 11/10/2007 - 8:19pm

I already wrote about this issue but as I is third team I'm helping customers to resolve this "frm corruption" issue it is the time to return to it again.

HighLoad 2007 Review

MySQL Performance Blog - Sat, 06/10/2007 - 9:03pm

Almost two weeks have passed since HighLoad 2007 conference in Moscow, Russia so I’m pretty late with feedback and my only excuse I was pretty busy to spare some time for it.

UNION vs UNION ALL Performance

MySQL Performance Blog - Fri, 05/10/2007 - 9:01pm

When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being cool thing.

What did we want Interns to do

MySQL Performance Blog - Fri, 05/10/2007 - 5:35pm

About one month ago we posted a call for interns to work with us on various MySQL Performance related things.

We got good number of replies and a lot of people wondered what tasks exactly we could offer as surely this sort of involvement should be fun and should teach you something.

MySQL Quality of old and new features

MySQL Performance Blog - Thu, 04/10/2007 - 1:06pm

Recent couple of days our team was pointed to number of bugs in MySQL 5.0 which again seriously shakes the confidence in both MySQL Quality Control and bug fix promptness.

Let me just take couple of bugs as examples:

Triggers broken with auto-increment columns for Innodb tables (bug 26316). As you can see this bug is reported in February - over 6 months ago and it is still in verified state even though it has “serious” severity.

Heikki Tuuri to answer your in depth Innodb questions

MySQL Performance Blog - Fri, 28/09/2007 - 4:43pm

Have you ever had a question about Innodb internal design or behavior which is not well covered in MySQL manual ? I surely had.
Now you have a great chance to have them answered !

Heikki Tuuri, Creator of Innodb will answer your Questions about Innodb at MySQL Performance Blog.

Please leave your questions as comments to this post by 5th of October and I will pass them to Heikki to reply merging with questions I have myself.

Using VIEW to reduce number of tables used

MySQL Performance Blog - Fri, 28/09/2007 - 3:54pm

Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it is used on massive scale (blog hosting, forum hosting etc), resulting of hundreds of thousands if not millions of tables per server which can become really inefficient.

Innodb usability and ease of use.

MySQL Performance Blog - Fri, 28/09/2007 - 10:05am

It always surprised me how little Innodb team seems to think about product usability/ease of use, when it comes to settings, performance management etc.

InnoDB auto-inc scalability fixed

MySQL Performance Blog - Wed, 26/09/2007 - 7:40pm

There was long played scalability issue with InnoDB auto-increment field. For details check Bug 16979. In short words the problem is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual row-level locks. With many concurrent inserted threads this causes serious scalability problems, and in our consulting practice we had a lot of customers who was affected by InnoDB auto-inc.

Possible optimization for sort_merge and UNION ORDER BY LIMIT

MySQL Performance Blog - Tue, 18/09/2007 - 3:19pm

Every so often you need to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion of the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look at this example:

Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

MySQL Performance Blog - Mon, 17/09/2007 - 4:35pm

Quite typical query for reporting applications is to find top X values. If you analyze Web Site logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If you're looking at ecommerce reporting you may be interested in best selling product or top sales people. This information may often need simple select query, however what if you would like to show percents not just absolute value ?

For illustration purposes I've created a syntetic table filled with some 30mil rows evenly spread in 10.000 groups.

MySQL: what read_buffer_size value is optimal ?

MySQL Performance Blog - Mon, 17/09/2007 - 2:41pm

The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to less believe in common sense or common sense of documentation writers and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I've discovered even read_buffer_size selection may be less than obvious.

Going to MySQL Developers conference in Heidelberg

MySQL Performance Blog - Sat, 15/09/2007 - 4:47pm

I finally got my visa so I’m going to MySQL Developers meeting in Heidelberg, Germany next week.
Hopefully it will be great source of “technical insight” information, either from organized sessions or from developers themselves.

Plus I’m looking forward meeting a lot of friends our where both MySQL Developers and active community members.

Enabling/Disabling query log without server restart in MySQL 5.0

MySQL Performance Blog - Thu, 13/09/2007 - 10:19am

General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can’t switch it on and off without restarting server until MySQL 5.0.

What can you do in MySQL 5.0 and below ?

InnoDB in self-compiled MySQL 5.1

MySQL Performance Blog - Thu, 13/09/2007 - 7:47am

If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue.

What I usually do to fast compile and test is

PLAIN TEXT SQL:
  1. ./configure --prefix=/dir/to/mysql
  2. make
  3. make install

and then, for example, load the dump of InnoDB from previous version:

mysql testdatabase < dump.sql

I bet you will not notice all your tables now is MyISAM. Why?

Query Profiling with MySQL: Bypassing caches

MySQL Performance Blog - Wed, 12/09/2007 - 4:22pm

Quite frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ?

Pages

Subscribe to MySQL Performance Blog