1

We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter numeric data. The database was setup to used InnoDB tables. Before the big day, we made a test when a great portion of the expected users entered data in order to see the cpu utilization of the master server and its performance in general.

The big day came and when the data entry began we experienced an issue of losing data from one table. The user data entry form included data that were saved in two different tables. We discovered an application bug that the data for the first table was outside the transaction for the second. This would explain the behavior of not saving data for the sub-form (the second table). However, after fixing the bug and rolling out the newer version, after ~ 2 hours, the issue happened again. The were several records that had the data for the main form saved (the first table) and the data for the sub-form lost (the second table). The users entered again the missing records. Querying the database returned 0 records of lost rows. And then, the issue stroke again. Without explanation, we had somewhat ~170 lost records from the sub-form (the second table).

Could a possible corrupted ibd file lead to such a situation? After doing some basic research in Google, it came down to a post saying that when using InnoDB plugin (as in our case) and you upgrade to a newer version, inconsistencies could arise and you have to delete your database and re-create it again to be sure that all is ok. When we started developing the application, the MySQL version used was 5.1.56 (Debian 6 stable) and at some point, 5.1.61 was pushed as an update. Could this upgrade lead to a situation when some relatively medium-weight contention cause lost records?

Improve this question
1

My guess would be that there are three(3) aspects to look over:

ASPECT #1

I would be concerned with InnoDB settings because there are new features in InnoDB (starting with the plugin as of MySQL 5.1.38) that allow for accessing multiple CPUs. If you do not set the parameters, InnoDB will behave like it did before the enhancements. In fact, there are cases where InnoDB in older versions of MySQL will operate faster than newer version:

ASPECT #2

I would be very carful when upgrading MySQL 5.1 to another version of MySQL 5.1 because MySQL 5.1 introduced the concept of installing plugins because you now have the table mysql.plugin. If you install a newer version of MySQL, make sure you do not hose mysql.plugin. You have to just reinstall the plugin. This, in itself, is not the cause of your problem. Nevertheless, you need to be aware of this in the event that this is a cause.

ASPECT #3

If you have not already done this, I would raise innodb_log_buffer_size to 32M. The default is 8M. Increasing it will reduce disk I/O. Please do not go beyond this, since large COMMITs can and will form I/O-based bottlenecks.

RECOMMENDATION

I would upgrade completely to MySQL 5.5 because all of the InnoDB Plugin is natively installed in MySQL 5.5. As highlighted in my previous links, you must configure and tune the settings needed for InnoDB to use multiple CPUs. Increase your log buffer

UPDATE 2012-06-03 18:30 EDT

Since you cannot install MySQL 5.5, you still have aspects 2 and 3 you could implement.

Improve this answer
4
  • Hi, thanks for your detailed answer and sorry for the delay to reply. At the moment, I can't upgrade to MySQL 5.5, because I'm using the stable Debian Squeeze branch. Maybe when Debian 7.0 is released, the upgrade will probably be possible. What I did is to reinstall Debian in a new machine and delete all files from /var/lib/mysql and re-create from scratch (using MySQL 5.1.61). We created a batch test script to simulate the insertion procedure and this time, no records were lost. It seems that the issue went away. Thanks. – Vasilis Lourdas Jun 3 '12 at 22:10
  • We are already using the InnoDB plugin (I said it in my initial question) and as I recall, the InnoDB log buffer size is set to 48M, so both your suggestions were already set from the beginning. Thanks. – Vasilis Lourdas Jun 3 '12 at 22:26
  • OK sorry for being redundant :( – RolandoMySQLDBA Jun 3 '12 at 22:31
  • No problem, don't worry about it. :-) – Vasilis Lourdas Jun 3 '12 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.