MySQL Performance Blog

Over last couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or critical enough issue so I could spend time creating repeatable test case and search of the bug in the MySQL database did not find anything. Recently we had this problem again and now discussed it with Monty’s team – this time we found the bug for this issue.

It is no surprise why I could not find the bug easily – the bug is not really related to multiple key caches but to online key cache resize. It is just this code most actively used in case you’re using multiple key caches. It is very rare one would resize single key cache in production and it only triggers crash sometimes, while if you’re using multiple key caches there are often some scripts in place which adjust their size or change mappings of the tables.
[read more...]

It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it turns out, it can sometimes have a much more severe impact on the MySQL performance that it would seem. When or why?
[read more...]

Recently my attention was brought to this bug which is a nightmare bug for any consultant.

Working with production systems we assume reads are reads and if we’re just reading we can’t break anything. OK may be we can crash the server with some select query which runs into some bug but not cause the data loss.

This case teaches us things can be different – reads can in fact cause certain writes (updates) inside which add risk, such as exposed by this bug.

This is why transparency is important – to understand how safe something is it is not enough to know what is this logically but also what really happens inside and so what can go wrong.

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse – critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like “sometimes my slave clone script does not work” may hang on for years.

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:
[read more...]

Over a year ago I wrote about pretty nasty Innodb Recovery Bug. I ran in the same situation again (different system, different customer) and went to see the status of the bug… and it is still open.

You may thing it is minor issue but in fact with large buffer pool this bug makes database virtually unrecoverable (if 10% of progress in 2hours qualifies as that). It is especially nasty as it is quite hard to predict. Both customers had MySQL crash recovery happening in reasonable time… most of the times until they run into this problem.
[read more...]

As you might know ANALYZE TABLE just quickly updates table statistics using index dives, unlike with MyISAM when it scans indexes holding table lock for long period of time.

So ANALYZE TABLE should be very fast and non intrusive operation doing just little update on the data. Right ?
[read more...]

JOINs are expensive and it most typical the fewer tables (for the same database) you join the better performance you will get. As for any rules there are however exceptions

The one I’m speaking about comes from the issue with MySQL optimizer stopping using further index key parts as soon as there is a range clause on the previous key part. So if you have INDEX(A,B) and have a where clause A BETWEEN 5 and 10 AND B=6 only the first part (A) of the index will be used which can be seriously affect performance. Of course in this example you can use index (B,A) but there are many similar cases when it is not possible.
[read more...]

MySQL 5.1 was in RC stage for around 10 months now finally planned to be released as GA soon. As Monty Says MySQL 5.1.26 will be renamed as GA if no serious bugs are discovered.

Lets help MySQL to ensure GA release will be indeed GA quality and support Monty in his call to ensure this RC candidate works well for you.
[read more...]

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with “Syntax error”.
So RTMF and documentation says
“This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.”

For me term ‘dangerous’ is interesting there , as I’d expect really dangerous is DROP DATABASE (which I hope will not be removed in next release) , and RENAME DATABASE is supposed to be kind of safe – just replaces old name to new one.

I guess there are some related bugs – and there are:
Bug#28360: RENAME DATABASE destroys routines
Bug#17565: RENAME DATABASE destroys events
so basically RENAME DATABASE destroyed all events and routines related to database.

And solution of bug is:

Removed the
RENAME DATABASE db1 TO db2
statement.

So it seems MySQL took an interesting practice to get GA released as soon as possible – just remove features that does not work instead of fix it.
The similar was with FEDERATED storage engine, which was disabled by default in 5.1.23 binaries, in contrast to previous version (but should be enabled again in 5.1.24).

That probably helps to get ‘Zero P1 bugs reported’