MySQL Performance Blog

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:

The result set can be retrieved from query cache (for statements both inside and outside of transactions) until there is a statement inside transactions which modifies the table. As soon as table is modified in transaction it becomes uncachable by query cache until that transaction is committed. Not only query cache can’t be used inside the same transaction which modified data but also in other concurrent transactions which do not even see the changes done yet
[read more...]

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.
[read more...]

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:
[read more...]

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.

In practice you can also often use 4th variant which is not comparable directly, which is using integer value and having value mapping done on application level.
[read more...]

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.

First it is very interesting for me MySQL choose to be bought out by Sun rather than going IPO even though as I understand majority of the steps required for IPO already were done. This could be related to current market conditions or may be 1B price tag was at higher end what was expected from IPO, It also could be getting mostly cash payment now was attractive.
[read more...]

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.
[read more...]

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.

I had already written about the mess that 64-bit integers are in PHP. But if the numbers you use do not cover 64-bit range fully, floats might save the day. The trick is that PHP floats are in fact doubles, i.e. double-precision 64-bit numbers. They have 52 bits for mantissa, and integer values up to 2^53-1 can be stored exactly. So if you’re using up to 53 bits, you’re OK with floats.

However, there’s a conversion caveat you should be aware of.
[read more...]

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.

If you prefer to run binaries based on Enterprise codebase – the most up to date version 5.0.54 is now available for CentOS starting with CentOS4:

mysql.x86_64 5.0.54-1.el4.centos centosplus
mysql-bench.x86_64 5.0.54-1.el4.centos centosplus
mysql-devel.x86_64 5.0.54-1.el4.centos centosplus
mysql-libs.x86_64 5.0.54-1.el4.centos centosplus
mysql-server.x86_64 5.0.54-1.el4.centos centosplus

These are not built exactly same as ones you would download on MySQL Web site, ie BDB may be enabled by default but in general I have not seen major issues with RedHat builds for quite a while now.