2.6.0-test2-mm3 and mysql
Andrew,
I do not know about the specific corruption Shane is talking about, but I could summarize what we have found out in the past 2 years. I have written the InnoDB backend to MySQL, and have been hunting Linux corruption bugs for 2 years now.
- Corruption seems to happen on Red Hat kernels 2.4.18 under heavy file i/o load on some computers.
- A user ran a very simple stress test of type SELECT 'abbaguu' with many clients. On a 2-way Dell server he was able to get mysqld to crash predictably in < 24 hours. Sometimes he also got corruption. But another, cheaper computer worked ok. Both were running a Red Hat kernel 2.4.18. When the user upgraded to a 'stock' kernel 2.4.20, the crashes and corruption disappeared.
- Our 4-way Xeon SuSE-2.4.18 computer never corrupts databases, though I run very heavy stress tests on it.
- Kernels 2.4.20 seem to be more reliable than 2.4.18. I have only one corruption case from such a kernel.
- We know with certainty that corruption is sometimes caused by OS/drivers/hardware and not by mysqld, because in some cases rebooting the computer has magically fixed the corruption. Looks like Linux had corrupted its own file cache, but the data on disk was ok. I reported this on the Linux kernel mailing list 2 years ago, but got no definite feedback.
- In some cases InnoDB reports checksum errors in pages. In those cases it is also very probable that the corruption was caused by OS/drivers/hardware, and not by mysqld.
- I have not noticed any clear connection between corruption reports and the used file system.
- I have personally tested on 4 Linux computers. On an old 2.2 kernel computer I was able to get read errors in 30 seconds. The three 2.4 kernel computers have worked ok.
My hypothesis is that there are bugs in drivers of Linux. That would explain why some computers work ok. Or there are Linux kernel bugs which only manifest on certain hardware under certain file i/o workload.
What to do? People who write drivers should run heavy, multithreaded file i/o tests on their computer using some SQL database which calls fsync(). For example, run the Perl '/sql-bench/innotest's all concurrently on MySQL. If the problems are in drivers, that could help.
Best regards,
Heikki Tuuri
Innobase Oy
.................
List: linux-kernel Subject: Re: 2.6.0-test2-mm3 and mysql From: Andrew Morton <akpm () osdl ! org> Date: 2003-08-03 2:08:59
[Download message RAW]
Quoted 2 lines · Shane Shrybman
The db corruption hit again on test2-mm2.
How do you know it is "db corruption"?
Quoted 2 lines
I am still backing out the 64 bit devt bit
why?
Quoted 5 lines · Heikki Tuuri
What to do? People who write drivers should run heavy, multithreaded file
i/o tests on their computer using some SQL database which callsfsync(). For example, run the Perl '/sql-bench/innotest's all concurrently on MySQL. If the problems are in drivers, that could help.
Well there's a problem. We're kernel people, not database people. I, for one, would not have a clue how to set such a thing up.
If someone could prepare a simple-enough-for-kernel-people description of how to get such a test up and running, then we might make some progress.
Andrew,
----- Original Message -----
From: "Andrew Morton" <[email protected]>
To: "Heikki Tuuri" <[email protected]>
Cc: <[email protected]>
Sent: Sunday, August 03, 2003 12:27 PM
Subject: Re: 2.6.0-test2-mm3 and mysql
Quoted 3 lines
Quoted 2 lines · Heikki Tuuri
What to do? People who write drivers should run heavy, multithreaded
file
Quoted 1 line
Quoted 1 line
i/o tests on their computer using some SQL database which calls
fsync(). For
Quoted 1 line
Quoted 1 line
example, run the Perl '/sql-bench/innotest's all concurrently on MySQL.
If
Quoted 7 lines
Quoted 1 line
the problems are in drivers, that could help.
Well there's a problem. We're kernel people, not database people. I, for one, would not have a clue how to set such a thing up.
If someone could prepare a simple-enough-for-kernel-people description of how to get such a test up and running, then we might make some progress.
ok :).
1. Download
from http://www.mysql.com/downloads/mysql-4.0.html:
MySQL-server-VERSION.i386.rpm The MySQL server. You will need this unless you only want to connect to a MySQL server running on another machine. Please note that this package was called MySQL-VERSION.i386.rpm before MySQL 4.0.10.
MySQL-client-VERSION.i386.rpm The standard MySQL client programs. You probably always want to install this package.
MySQL-bench-VERSION.i386.rpm Tests and benchmarks. Requires Perl and the DBD-mysql module.
MySQL-shared-compat-VERSION.i386.rpm This package includes the shared libraries for both MySQL 3.23 and MySQL 4.0. Install this package instead of MySQL-shared, if you have applications installed that are dynamically linked against MySQL 3.23 but you want to upgrade to MySQL 4.0 without breaking the library dependencies. This package is available since MySQL 4.0.13. (these are named 'Dynamic client libraries (including 3.23.x libraries)' on the download page).
Do NOT use the MySQL distro which comes with Red Hat distros. It is old and may not be properly built. Only use binaries downloaded from www.mysql.com.
2. Install with
shell> rpm -i MySQL-server-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm
etc.
3. I am assuming that you have Perl which comes in most Linux distros. You probably also have the MySQL DBI/DBD module in your Linux distro. It will use those MySQL-shared-compat client libraries.
http://search.cpan.org/author/JWIED/DBD-mysql-2.1026/lib/DBD/mysql/INSTALL.pod:
"
Red Hat Linux
As of version 7.1, Red Hat Linux comes with MySQL and DBD::mysql. You need to ensure that the following RPM's are installed:
mysql
perl-DBI
perl-DBD-MySQL
"
If you do not have DBI/DBD, you have to resort to
http://www.mysql.com/downloads/api-dbi.html.
4. The rpm installation should now have the mysqld daemon running and mysqld etc. placed in a bin dir (probably /usr/bin). You can shut it down with
mysqladmin shutdown
You can start it again with
mysqld
it should print something like:
"
030803 13:13:48 InnoDB: Started
mysqld: ready for connections.
Version: '4.0.14-debug-log' socket: '/home/heikki/MySQLheikki' port: 3307 "
(If something went wrong in the grants table creation, it will complain it cannot find the 'host.frm' file. In that case refer to
http://www.mysql.com/doc/en/Post-installation.html about the script mysql_install_db.)
The 'datadir' of MySQL is typically /var/lib/mysql. Under it is the actual database data.
To connect to the database from a console, type
mysql test
mysql> show databases;
...
mysql> exit
5. To run Perl tests, go to the sql-bench directory (typically under /usr/local/mysql) and:
perl innotest1
perl innotest1a
perl run-all-tests --create-options=type=innodb
etc.
You should run all innotests concurrently.
mysqld should not crash or print anything. The Perl tests themselves print quite a lot as they test deadlocks etc.
We are testing a Pogo Linux server with a Red Hat 2.4.20 kernel,
http://www.mysql.com/press/release_2003_20.html, and so far that combination seems to work ok.
Greetings to Linus! I hope you are having a good time at OSDL!
Heikki
Quoted 9 lines · Heikki Tuuri
Quoted 5 lines
Well there's a problem. We're kernel people, not database people. I, for one, would not have a clue how to set such a thing up.
If someone could prepare a simple-enough-for-kernel-people description of how to get such a test up and running, then we might make some progress.
ok :).
1. Download
[4 screenfuls snipped]
That's a hell of a setup work, and kernel folks will always get slightly different setups. Can database folks make a fully configured chrootable tarball for mysql stress testing?
--
vda
Denis,
----- Original Message -----
From: "Denis Vlasenko" <[email protected]>
To: "Heikki Tuuri" <[email protected]>; <[email protected]>
Sent: Monday, August 04, 2003 3:24 PM
Subject: Re: 2.6.0-test2-mm3 and mysql
Quoted 2 lines · Heikki Tuuri
Quoted 1 line
Quoted 1 line
Well there's a problem. We're kernel people, not database people. I,
for
Quoted 3 lines
Quoted 3 lines
Quoted 3 lines
one, would not have a clue how to set such a thing up.
If someone could prepare a simple-enough-for-kernel-people description
of
Quoted 1 line
Quoted 1 line
Quoted 1 line
how to get such a test up and running, then we might make some
progress.
Quoted 10 lines
Quoted 4 lines
ok :).
1. Download
[4 screenfuls snipped]
That's a hell of a setup work, and kernel folks will always get slightly different setups. Can database folks make a fully configured chrootable tarball for mysql stress testing?
I think an even better idea is to use some multithreaded file i/o stress test program. There probably are such programs already. If not, write a simple C program which calls pread(), pwrite(), and fsync() on pages of size 2 - 16 kB. Vary the data you write, and check that the data you read is what you wrote to the file the last time. Run the test for several days or even weeks. Vary the size of the files so that you get real disk reads.
Is there such a stress test in the standard test suite for Linux kernel/driver developers?
Running an actual SQL database on top of that file i/o workload may also have some effect, because it is possible some bugs are really corruption of the process memory space. Maybe we could simulate the database CPU load by simple memcpy's etc.
Some additional info I forgot to mention about corruption:
1. In some cases corruption happens very frequently, even several times per hour. In those cases I suspect a hardware fault. In addition to mysqld, also other programs may suffer and crash.
2. Most cases of corruption only happen once in several weeks. They require heavy database load to manifest.
3. A typical case of corruption is that an area of size varying from 4 bytes to 4 kB is reset to zero in a 16 kB database page. Often is has been the end of the page. In Sergey's case the whole 16 kB page was reset to zero.
Quoted 2 lines
--
vda
Regards,
Heikki
http://www.innodb.com
Quoted 5 lines · Heikki Tuuri
What to do? People who write drivers should run heavy, multithreaded file i/o tests on their computer using some SQL database which calls
fsync(). For example, run the Perl '/sql-bench/innotest's all concurrently on MySQL. If the problems are in drivers, that could help.
Did you know that until test2-mm3, nothing would report errors that occurred on non-synchronous writes? There was no infrastructure to propagate the error back to userspace. If you wrote a page, the write failed on an intermittent I/O error, and then read again, you'd silently get back the old page.
Signature · 1 line
Matt Mackall : http://www.selenic.com : of or relating to the moon
Matt,
----- Original Message -----
From: "Matt Mackall" <[email protected]>
To: "Heikki Tuuri" <[email protected]>
Cc: <[email protected]>
Sent: Sunday, August 03, 2003 7:55 PM
Subject: Re: 2.6.0-test2-mm3 and mysql
Quoted 3 lines · Heikki Tuuri
Quoted 2 lines
What to do? People who write drivers should run heavy, multithreaded
file
Quoted 1 line
Quoted 1 line
i/o tests on their computer using some SQL database which calls
fsync().
For
Quoted 1 line
Quoted 1 line
example, run the Perl '/sql-bench/innotest's all concurrently on MySQL.
If
Quoted 7 lines
Quoted 1 line
the problems are in drivers, that could help.
Did you know that until test2-mm3, nothing would report errors that occurred on non-synchronous writes? There was no infrastructure to propagate the error back to userspace. If you wrote a page, the write failed on an intermittent I/O error, and then read again, you'd silently get back the old page.
we are not using the Linux async i/o. Do you mean that? Or the flush which the Linux kernel does from the file cache to the disk time to time on its own? I assume it will write to the system log an error message if a disk write fails?
The error 5 Shane reported came from a call of fsync(), and apparently he also got that same 5 from a simple file read which CHECK TABLE in MyISAM does.
Why would a write in the Linux async i/o fail? I am using aio on Windows, and if the disk space can be allocated, it seems to fail only in the case of a hardware failure.
Quoted 2 lines
Signature · 1 line
Matt Mackall : http://www.selenic.com : of or relating to the moon
Regards,
Heikki
Quoted 18 lines · Heikki Tuuri
Matt,
Quoted 3 lines · Heikki Tuuri
Quoted 2 lines
What to do? People who write drivers should run heavy, multithreaded
file
Quoted 1 line
Quoted 1 line
i/o tests on their computer using some SQL database which calls
fsync().For
Quoted 1 line
Quoted 1 line
example, run the Perl '/sql-bench/innotest's all concurrently on MySQL.
If
Quoted 7 lines
Quoted 1 line
the problems are in drivers, that could help.
Did you know that until test2-mm3, nothing would report errors that occurred on non-synchronous writes? There was no infrastructure to propagate the error back to userspace. If you wrote a page, the write failed on an intermittent I/O error, and then read again, you'd silently get back the old page.
Quoted 4 lines
we are not using the Linux async i/o. Do you mean that? Or the flush which the Linux kernel does from the file cache to the disk time to time on its own? I assume it will write to the system log an error message if a disk write fails?
This has nothing to do with the AIO interface.
Any write where the write returns immediately without syncing the file is asynchronous. This includes most normal write()s where you're not using O_DIRECT, O_SYNC or somesuch, and writes to memory-mapped files.
And no, prior to -mm3, there was absolutely no indication that these failed writes occurred. It was simply dropped. Now it should be reported in the logs and at the next sync point.
Quoted 3 lines
The error 5 Shane reported came from a call of
fsync(), and apparently he also got that same 5 from a simple file read which CHECK TABLE in MyISAM does.
Not sure what you're referring to here.
Quoted 3 lines
Why would a write in the Linux async i/o fail? I am using aio on Windows, and if the disk space can be allocated, it seems to fail only in the case of a hardware failure.
For various reasons, there was previously no infrastructure for transmitting write failure back to the writer after the page cache had taken ownership of the write.
Signature · 1 line
Matt Mackall : http://www.selenic.com : of or relating to the moon