You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 1 hour 19 min ago
Updated: 1 hour 19 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog

How MySQL Query Cache works with Transactions

MySQL Performance Blog - Tue, 29/01/2008 - 9:30pm

As MySQL Manual Says Query Cache works with transactions with Innodb tables but it does not tell you how and with which restrictions.

According to my tests it works but it is very restricted and one could expect it to work much better:

Monty unviels Maria and starts Blogging

MySQL Performance Blog - Tue, 29/01/2008 - 9:44am

This weekend we’re hearing great news from Michael “Monty” Widenius - one of the Fathers of MySQL. Monty finally found a time to create his own blog with very descriptive name Monty Says. At the same time Monty finally announces Maria - the MyISAM successor storage engine he has been working for last few years. You can now get Maria from MySQL BitKeeper Server.

No more MySQL Crash Safe Replication in 5.0 ?

MySQL Performance Blog - Tue, 29/01/2008 - 8:26am

As you might know even if you're only using Innodb tables your replication is not completely crash safe - if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale.

During MySQL 4.0 and 4.1 series there was a great workaround if you're using only Innodb tables - Innodb when Innodb does crash recovery it would print position in master log files up to which replication was done:

Enum Fields VS Varchar VS Int + Joined table: What is Faster?

MySQL Performance Blog - Thu, 24/01/2008 - 6:29pm

Really often in customers' application we can see a huge tables with varchar/char fields, with small sets of possible values. These are "state", "gender", "status", "weapon_type", etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I'd like to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns.

What should we say about Sun buying MySQL ?

MySQL Performance Blog - Mon, 21/01/2008 - 1:26pm

Kaj wrote me email yesterday asking if I was sleeping for a whole week or may be dead because I’m probably the only one of people blogging about MySQL who has not commented about announced Sun - MySQL Deal.

In fact I was just on extremely busy travel schedule last week, so I’m just finding a bit of time now to comment on it.

MySQL Blob Compression performance benefits

MySQL Performance Blog - Fri, 11/01/2008 - 5:43pm

When you’re storing text of significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL Does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make them transparently compressed) but you well can do it yourself by using COMPRESS/UNCOMPRESS functions or compressing/decompressing things on the client.

PHP vs. BIGINT vs. float conversion caveat

MySQL Performance Blog - Thu, 10/01/2008 - 11:59pm

Sometimes you need to work with big numbers in PHP (gulp). For example, sometimes 32-bit identifiers are not enough and you have to use BIGINT 64-bit ids; e.g. if you are encoding additional information like the server ID into high bits of the ID.

CentOS Comes with free build of MySQL Enterprise ?

MySQL Performance Blog - Fri, 04/01/2008 - 9:19am

As I already wrote you can well go to Linux distribution vendors if you’re looking for recent MySQL version in a hassle free binary form. (On the time of this writing you could only get binaries for MySQL Community 5.0.45 from MySQL Download Pages which was released in July and so about half a year old.

What is the longest part of Innodb Recovery Process ?

MySQL Performance Blog - Thu, 20/12/2007 - 10:25am

In MySQL 4.1 and above the longest part of recovery after crash for Innodb tables could be UNDO stage - it was happening in foreground and was basically unbound - if you have large enough transaction which needed to be undone this could take long hours.
REDO stage on other hand always could be regulated by size of your Innodb log files so you could have it as large as you like. Read more about it here.

Large result sets vs. compression protocol

MySQL Performance Blog - Thu, 20/12/2007 - 9:08am

mysql_connect() function in PHP’s MySQL interface (which for reference maps to mysql_real_connect() function in MySQL C API) has a $client_flags parameter since PHP 4.3.0. This parameter is barely known and almost always overlooked but in some cases it could provide a nice boost to your application.

MVCC: Transaction IDs, Log Sequence numbers and Snapshots

MySQL Performance Blog - Wed, 19/12/2007 - 10:35am

MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular and some implementation can be different but I hope this will let you to understand MVCC a bit better.

Fixing column encoding mess in MySQL

MySQL Performance Blog - Tue, 18/12/2007 - 2:34pm

Just had an interesting issue with an encoding mess on a column containing non-ASCII (Russian) text. The solution was not immediately obvious so I decided it's worth sharing.

The column (actually the whole table) was created with DEFAULT CHARSET cp1251. Most of the data was in proper cp1251 national encoding indeed. However, because of web application failure to properly set the encoding, some of the rows were actually in UTF-8. That needed to be fixed.

Slides from Egypt talks

MySQL Performance Blog - Sun, 16/12/2007 - 1:16pm

I published slides from my two talks from our Egypt company meeting.
First one is about Sphinx search engine. The originally presentation was prepared by Andrew Aksenoff, author of Sphinx for conference, I translated it on English for our meeting.

Back from company meeting in Hurghada, Egypt

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

Percona team and families
So we’re back from the company meeting which took place last week in Hurghada, Egypt. It was a great opportunity to meet a lot of people for the first time in person to work together and have some fun.

Be careful rotating MySQL logs

MySQL Performance Blog - Sun, 09/12/2007 - 6:40pm

If you enable logging of all queries as “slow queries” using the patch or MySQL 5.1 you can get log file to grow huge. Same may happen with general log file. In some cases we’ve got log file sizes of 100G or more which may need to be cleaned up.

Packing for company meeting in Egypt

MySQL Performance Blog - Thu, 29/11/2007 - 3:44pm

Our company, Percona is even more Virtual than MySQL AB - we have no offices and 100% of staff members work out of their homes (or traveling, onsite with customers etc). We also probably have just couple of cities where more than one Employee lives.

Data Recovery Toolkit for InnoDB Version 0.1 Released

MySQL Performance Blog - Mon, 26/11/2007 - 8:29pm

As Peter mentioned in one of previous posts, we’ve done huge work developing robust strategies of InnoDB data recovery to provide our customers effective data recovery services and one of major parts of these strategies is our toolkit for InnoDB data recovery.

Slides from Zurich talk are now posted

MySQL Performance Blog - Sun, 25/11/2007 - 6:56pm

I have now posted slides for the talk I gave in Zurich last week in MySQL Performance Presentations section.

That was fun talk and fun trip in general. Thank you for inviting.

Pitfall of proxying HTTP requests through Lighttpd

MySQL Performance Blog - Fri, 23/11/2007 - 12:04am

Recently I had a case with a web server farm where a random node went down every few minutes. I don't mean any of them rebooted except once or twice, but rather they were slowing down so much that practically stopped serving any requests and were being pulled out from the LVS cluster. The traffic was not any different than usual, all other elements of the system worked perfectly fine (e.g. databases, storage), no one started any backup in the middle of the day as it happens sometimes... so what was happening?

Giving a talk in Zurich

MySQL Performance Blog - Sun, 18/11/2007 - 10:25pm

I’m visiting customer in Zurich next week and also was invited to give a talk on MySQL Performance Wednesday November 21.
If you’re in the area please feel free to drop by I’ll start by talking about Query Optimization and we’ll keep it interactive to see what else audience would like to know.

Entry posted by peter | No comment

MySQL: Data Storage or Data Processing

MySQL Performance Blog - Sun, 18/11/2007 - 5:02pm

I was thinking today of how people tend to use MySQL in modern applications and it stroke me in many cases MySQL is not used to process the data, at least not on the large scale - instead it is used for data storage and light duty data retrieval. Even in this case however the cache (ie memcache) is often implemented to offload MySQL.

10+ Ways to Crash or Overload MySQL

MySQL Performance Blog - Tue, 13/11/2007 - 3:04pm

People are sometimes contacting me and asking about bugs like this which provide a trivial way to crash MySQL to the user with basic privileges and asking me what to do.

My answer to them is - there is nothing new to it and they just sit should back and relax :)

Innodb Undelete and Sphinx Support

MySQL Performance Blog - Mon, 12/11/2007 - 9:54pm

At Percona we are pleased to announce couple of services which should be helpful to MySQL Community and which are not offered by MySQL, Oracle and other companies I know about.

How we work on High Performance MySQL Second Edition

MySQL Performance Blog - Mon, 12/11/2007 - 1:18pm

Baron have been updating you a lot about chapter by chapter progress so I should not repeat it, instead I thought it would be a good idea to write a bit about how we work on the book.

Generally, quite similar to the previous book this is long process, which is probably what happens when busy people attempt to get time to write a book. I started working on book Outline with Andy Oram (our great editor) about a year ago.

Filtered MySQL Replication

MySQL Performance Blog - Wed, 07/11/2007 - 12:40pm

To get this straight - I’m not a big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) - there is enough gotchas with replication itself and getting things right with filtering can get quite bumpy road. In some applications however it is very helpful so lets see what one should do or should not do to make it work.

Experience shared here mainly applies to logical level replication. MySQL 5.1 can use row level replication which elevates some of these issues but I guess it will still take some time before it becomes massively used.


Subscribe to MySQL Performance Blog