Personal blog of Yzmir Ramirez

Working with customer, I faced pretty nasty bug, which is actually not rare situation , but in this particular there are some lessons I would like to share.

The case is pretty much described in bug 55981, or
in pastebin.

Everything below is related to InnoDB-plugin/XtraDB, but not to regular InnoDB ( i.e in MySQL 5.0)

In short, if you use big BLOBS ( TEXT, MEDIUMBLOB, etc) (that allocated in external segment in InnoDB), you can get your database in trash state just executing update on row with blob and rolling back transaction twice ( on the same row)

The keywords that diagnose you hit this bug is

InnoDB: Serious error! InnoDB is trying to free page N
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.
InnoDB: You may need to dump your InnoDB tables and recreate the whole
InnoDB: database!


Trash state means that InnoDB won’t start, and you need to use innodb_force_recovery=3 and mysqldump your data. What makes problem even worse is that InnoDB does not report tablename, so you are pretty much blind and need to dump whole dataset, which can be long process.

The moment where DRBD come in play, is if you use DRBD for HA purposes ( as is in the case I worked with), you screwed,
as DRBD mirroring physical data, and MySQL keeping crashing on both instances – active and passive.

So DRBD can’t be considered fully reliable HA solution if there is risk that application corrupts data by itself

Now to bug 55981. It has MySQL version 5.6, but the problem exists in MySQL 5.1.50 or below and in MySQL 5.5, and
corresponding bug is 55543, which you actually can’t see, as
“You do not have access to bug #55543.”, because it is marked as “Security problem”.

And I actually tend to agree that bug can be considered as “security”.
If you running public hosting or your public users can execute direct SQL statements, I strongly recommend to upgrade to
MySQL version 5.1.51+ .

Now another interesting point – how can you be sure that 5.1.51 works.

The bug 55543 is not mentioned in ChangeLog for 5.1.51 nor 5.1.52. However if you look into source code and revision history, you can see that bug 55543 is fixed in MySQL 5.1.51. I assume it is technical problem with ChangeLog process and it will be fixed soon, but I reported it so it is not lost

At the end let me reiterate my points:
– if you have BLOB/TEXT fields in your InnoDB-plugin schema, it is recommended to upgrade to 5.1.51+
– if you provide public access to MySQL instance ( hosting provider, etc) with InnoDB-plugin installed – it is STRONGLY recommended to upgrade to 5.1.51+
– Review your HA schema. DRBD by itself (without additional solutions) can’t guaranty decent level of High Availability. And just to be clear, it is not DRBD problem, DRBD basically can’t help if there is possibility that application corrupts data by itself. For this case regular Master-Slave setup (in addition to DRBD) would protect from such problem.

Entry posted by Vadim |
6 comments

Add to: | | | |

Erin and Mike, organizers of SF Meetup generously invited me to talk on coming SF Meetup on Dec-14 about new features in InnoDB in MySQL 5.1 and 5.5 and, what is pay attention to, when you upgrade from MySQL 5.0.
Although I personally mostly in 5.1->5.5 upgrade area, Erin insured me that upgrade from MySQL 5.0 is still actually question for many users, so I accepted invitation. Event details are on Meetup.com page. So if you are up for free pizza, soda and do not mind to listen to me, welcome to join!

Entry posted by Vadim |
No comment

Add to: | | | |

We’ve had such a large number of signups to our Migrating MyISAM to InnoDB webinar, that we decided to hold one more before the year is out:

December, 8th 9AM PST: Introduction to Percona Server, XtraDB and Xtrabackup.

As the title suggests: this presentation is an introduction.  We will be running through the main aspects that change, and where you can expect to get the most benefit.

This will be a technical talk, but perhaps less technical than some of our other posts. I recommend inviting a few friends & colleagues that are already familiar with MySQL, but perhaps not regular readers of our blog.

(Time for Q&A will follow the main presentation).

Entry posted by Morgan Tocker |
2 comments

Add to: | | | |

• Karaoke'd for the first time in a long time tonight.. ahh the soft afterglow of my 4 minutes of fame.. #
• Twitter just allowed me to not freak out because I heard what sounded like Shock and Awe in #LosAngeles .. #fireworks at The Grove #
• We're not going to use the release cycle anymore? http://is.gd/hIo2s To extract a point though, this does make sense for apps.. #

Powered by Twitter Tools

Percona Server version 5.1.52-rel11.6 is now available for download.

The main purpose of this release is to update the current Percona stable release to the latest version of MySQL 5.1.

Functionality Added or Changed

•  Percona Server 5.1.52-rel11.6 is now based on MySQL 5.1.52.
•  New Features Added: None
•  Other Changes: None

Bugs Fixed

• Bug #671764 – innochecksum wasn’t distributed with RPM and .DEB packages. (Aleksandr Kuzminsky)
• Bug #673426 – Use of some system variables as command-line options caused a crash or undefined behavior. (Oleg Tsarev)
• Bug #673929 – Query cache misses were being reported for some queries when hits were actually occurring. (Oleg Tsarev)
• Bug #676146 – The development environment test of log_slow_verbosity=innodb on a slave for row-based replication was not working correctly. (Oleg Tsarev)
• Bug #676147 – The development environment test of option log_slow_slave_statements for row-based replication was not working correctly. (Oleg Tsarev)
• Bug #676148 – Similar to Bug #676147. A check is now made for the replication type to test. (Oleg Tsarev)
• Bug #676158 – Setting the query cache size to 512M caused test failure on low memory systems. (Aleksandr Kuzminsky)

Release Notes for this and previous releases can be found in our Wiki.

The latest downloads are available on our website. The latest source code for Percona Server, including the development branch, can be found on Launchpad.

Please report any bugs found at Bugs in Percona Server.

For general questions, use our Percona Discussions Group, and for development questions our Percona Development Group.

For support, commercial, and sponsorship inquiries, contact Percona.

Entry posted by Fred Linhoss |
2 comments

Add to: | | | |

I do not know if it is because we’re hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb.

Webinar will cover the process in a lot more details though I would like to go over basics in advance. You can also check my old post on this topic about Moving MyISAM to Innodb as well as searching the blog – We’ve blogged so much on this topic.

So what are the basics ?

Regression Benchmarks – Make sure to run regression benchmarks in particular in terms of concurrent behavior. You may have hidden dependencies of MyISAM table lock behavior in your applications, also check if your application handled deadlocks well. MyISAM will not produce deadlocks for Innodb you should always see deadlocks as a probability as long as you write to the database. They may be rate but it is rather hard to guaranty you will never run into them.

Performance Benchmarks – Innodb and MyISAM have different performance properties and you can’t really say one is faster than other it is very much workload dependent, and again concurrent tests should be important here. Innodb also may result in different plans for some queries which is rather easy to check with mk-upgrade

Feature Differences – There are some feature differences between MyISAM and Innodb though well it is typically easily spotted by converting tables to Innodb on restored backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. There are also different limits for MyISAM and Innodb – it is possible to have some rows which can be stored in MyISAM but would not fit to Innodb, though this is an exception.

Space Innodb Tables tend to be larger. Again converting schema will show you some of this. Though the best is to take a look at the size after stressing system with load for a while as depending on the schema and usage Innodb tables may increase in size significantly due to fragmentation.

Usage Differences What works well for MyISAM may not work for Innodb and vice versa. You may benefit from different index structure for Innodb, including different primary key setup, as well as you may want to structure workload differently. With MyISAM it is often for people to do updates in small chunks, almost row by row to avoid holding table lock for long time in Innodb you want larger updates to reduce cost of transaction commit. You also may want to avoid excessive SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but does table/index scan for Innodb.

Defaults You need to know two things about defaults for Innodb. First Depending on MySQL version they may be somewhere from suboptimal to absolutely disastrous, you do not want to try to run Innodb or XtraDB with them. Second Innodb is tuned to be ACID by default – if you’re moving from MyISAM often you do not need such strong guarantees and can at least change innodb_flush_log_at_trx_commit=2. It still will be much more secure than storing data in MyISAM. The 3 most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are a lot more options for fine tuning but make sure at least these are right.

Entry posted by peter |
One comment

Add to: | | | |

Percona XtraBackup 1.4 is now available for download.

Version 1.4 fixes problems related to incremental backups. If you do incremental backups, it’s strongly recommended that you upgrade to this release.

Functionality Added or Changed

• Incremental backups have changed and now allow the restoration of full backups containing certain rollback transactions that previously caused problems. Please see Preparing the Backups and the --apply-log-only option. (From innobackupex, the --redo-only option should be used). (Yasufumi Kinoshita)
• The XtraBackup Test Suite was implemented and is now a standard part of each distribution. (Aleksandr Kuzminsky)
• Other New Features:
  • The --prepare option now reports xtrabackup_binlog_pos_innodb if the information exists. (Yasufumi Kinoshita)
  • When --prepare is used to restore a partical backup, the data dictionary is now cleaned and contains only tables that exist in the backup. (Yasufumi Kinoshita))
  • The --table option was extended to accept several regular expression arguments, separated by commas. (Yasufumi Kinoshita)
• Other Changes:
  • Ported to the Percona Server 5.1.47-11 code base. (Yasufumi Kinoshita)
  • XtraBackup now uses the memory allocators of the host operating system, rather than the built-in InnoDB allocators (see Using Operating System Memory Allocators). (Yasufumi Kinoshita)

Bugs Fixed

• Bug #595770 – Binaries are stripped by rpmbuild, so __os_install_post is redefined to change the default behaviour. (Aleksandr Kuzminsky)
• Bug #589639 – Fixed a problem of hanging when tablespaces were deleted during the recovery process. (Yasufumi Kinoshita)
• Bug #611960 – Fixed a segmentation fault in “xtrabackup”. (Yasufumi Kinoshita)
• Miscellaneous important fixes related to incremental backups.

Release Notes for this and previous releases of Percona Xtrabackup can be found in our Wiki.

The latest downloads are available on our website. The latest source code can be found on Launchpad.

Please report any bugs found at Bugs in Percona XtraBackup.

For general questions, use our Percona Discussions Group, and for development questions our Percona Development Group.

For support, commercial, and sponsorship inquiries, contact Percona.

Entry posted by Fred Linhoss |
5 comments

Add to: | | | |

This past weekend, I attended the 2010 Los Angeles Auto Show. I’m not a huge car buff. I do think that BMW’s are the bomb, and I like Honda’s common sense vehicles, but really, I am NOT a car guy. However, I thought this was an interesting chance to take a look at an industry that, in my opinion, isn’t all that different than the one I’m in.

Now, that may surprise some. Its pretty easy to think that I work for a super advanced company that has started a revolution and sits on the bleeding edge of innovation. I mean, at Canonical, we’re doing all kinds of amazing stuff with “the cloud” and building software that makes peoples’ jaw drop when they see it in action sometimes.

But really, I think we’re more like CODA. CODA has built what looks to be a sustainable, practical electric car. The car itself is not visually stunning, but the idea behind it is. Make an electric car that anyone can buy *and* use. Make it fun, and make sure the business is sustainable. But, in no way is CODA challenging the ideas and revisions that have worked for the 100+ years that the car industry has existed.

CODA is still putting a steering wheel, gas pedals, and gear shift in the cockpit for the driver. There are doors, wipers, lights, and probably floor mats. In much the same way, in Ubuntu, we’re still putting our software out there with the intention that, while its created differently, and affords the user more capabilities, it is basically driven in much the same way as Windows 7 or OS X, mostly as a web, errrr, cloud terminal.

The exciting part is that for $3 of possibly more efficiently produced electricity, you can drive 100 miles. Even more exciting is that the CODA might actually compete with sensibly priced  (but larger) Honda and Toyota sedans, rather than like the Tesla cars that compete with Lexus and BMW’s.

Given this way of thinking, the auto show was extremely interesting. The electric car (open source?) has “arrived”, and the established players are buying the interesting enabling technology like batteries (android’s linux kernel, darwin for mac, etc) from companies like Tesla, and putting them in their established products.

Whether consumers care about either open source or electric cars is another story.. maybe the 2011 LA Auto Show will have an answer for me on at least one of them.

My post back in April, http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/, caused quite interest, especially on topic SSD vs Memory.

That time I used fairy small dataset, so it caused more questions, like, should we have more then 128GB of memory?
If we use fast solid state drive, should we still be looking to increase memory, or that configuration provides best possible performance.

To address this, I took Cisco UCS C250 server in our lab, with 384GB of memory and FusionIO 320GB MLC. I generated 230GB data for sysbench benchmark
and run read-only and read-write OLTP workload with varying buffer pool size from 50 to 300GB (with O_DIRECT setting, so
os cache is not used)

This allows as to see effect of having more memory available.

The graph result is:

and raw numbers are on Wiki bencmarks

So let’s take detailed look on numbers with 120GB ( as if you have system with 128GB of RAM) and 250GB

So you see doubling memory gives 3x ! performance improvement. And it is despite we store data on one of fastest available storage.

So to get best possible performance our advise is still the same – you should try to fit your active dataset into memory, and it is possible as nowadays systems with 300GB+ RAM already available.

Entry posted by Vadim |
6 comments

Add to: | | | |

Ewen and I have just published Percona’s latest white paper, Forecasting MySQL Scalability with the Universal Scalability Law. This is essentially a streamlined walk-through of Dr. Neil J. Gunther’s book Guerrilla Capacity Planning, with examples to show how you can apply it to MySQL servers.

One thing alluded to in the paper is extracting the necessary metrics from network traffic. I had this idea after studying the data in Linux’s /proc/diskstats file. It turns out that two simple metrics can provide amazingly rich insight into system performance and scalability, in combination with Little’s Law and queueing theory. These are the busy time and the total time that requests were resident in the system. There are different terms for the latter, but in MySQL we’d call it query response time. After studying these for a few months, I’m so awed by how useful they are that I am going to make a blanket recommendation: if you create server software, you must expose these simple metrics. (I have filed a feature request for Percona Server to add these metrics for MySQL users.)

I’ll probably follow this up with another blog post or white paper at some point in the future, to show how to use the busy time and query response time to predict a system’s scalability.

Entry posted by Baron Schwartz |
3 comments

Add to: | | | |