Performance issues on certain queries (and some semi-related questions)

[青山 素子]

Table corruption is almost always a database engine issue. Even on sites with very high load, tables shouldn't normally become corrupt. There is no "corrupt the table" SQL command.

Given you're running on an unusual OS, it's likely that you're hitting some odd bug in the OS or MySQL or an interaction between the two.

I'm likely to consider that such a thing is at least contributing to the search issues as well, but MySQL's full-text search can be slow at the best of times.

[RobertMfromLI]

Table corruption is almost always a database engine issue. Even on sites with very high load, tables shouldn't normally become corrupt. There is no "corrupt the table" SQL command.

Given you're running on an unusual OS, it's likely that you're hitting some odd bug in the OS or MySQL or an interaction between the two.

I'm likely to consider that such a thing is at least contributing to the search issues as well, but MySQL's full-text search can be slow at the best of times.

I considered as much, BUT:

   
• as there are only thousands of similar complaints out there from people running other OS's (Linux primarily)
• and DOZENS of bugs in earlier MySQL versions that caused this same behavior (maybe some left?)
• and this problem persisted when we were on BlueHost (20 times a month) which was an EIGHT core Linux machine. When msnbot was hammering the BlueHost Linux server (before we added the crawl delay), this would occur 5-10 times a DAY.

Somehow I do not think my choice of operating systems or the way it interacts with AMP is the problem. Would you come to the same conclusion? (I hope... because if not, this will be difficult for the OS/2 dev/porter to track down, as his site does not get the type of traffic we do).

On a possibly related, I'm not seeing these issues on the Wordpress sites on that server.

Best,
Robert


Addendum (11:17EST):
Perhaps there is an issue in certain branches of MySQL or the AMP package as a whole? I am not  sure what Linux source is being used in the OS/2 version, but if that Linux version is the cause of the issues, that would explain why numerous Linux users (and myself on OS/2) are experiencing this problem. The problem I have with this is though I can find out which Linux source/distro/whatever is being used in the OS/2 port, I'd then have to track down all the Linux related threads on this and ask which source tree their version(s) are being compiled from and compare the results.


Addendum #2 (11:47EST):
Is it possible it's a charset issue? There were earlier bugs in that (in MySQL) as well, and I am noting that for whatever reason, the build of MySQL I am using is defaulting to an odd charset (latin?) while the tables in question are UTF8.

character_set_client: latin1 character_set_connection: latin1 character_set_database: latin1 character_set_filesystem: binary character_set_results: latin1 character_set_server: latin1 character_set_system: utf8 collation_connection: latin1_swedish_ci collation_database: latin1_swedish_ci collation_server: latin1_swedish_ci

[RobertMfromLI]

I'm likely to consider that such a thing is at least contributing to the search issues as well, but MySQL's full-text search can be slow at the best of times.

MySQL's fulltext search is not a problem. That's reasonably fast (a few seconds). Switching to SMF's large text search has caused slowdowns in searching. I have increased (this morning) the relevant caches and will see how that goes.

[青山 素子]

   

• as there are only thousands of similar complaints out there from people running other OS's (Linux primarily)

How old are the complaints? I know when I was running early versions of 5.0 (or was it 5.1?) on transaction-heavy sites, I experienced crashed tables fairly often. It only occurred with MyISAM tables, which seems to indicate there was a bug specifically for that type. Upgrading to a newer version of MySQL fixed this issue.

   

• and DOZENS of bugs in earlier MySQL versions that caused this same behavior (maybe some left?)

I experienced it as well. Newer versions fixed those bugs. I haven't seen any recurrence yet, even on a site running at over 4mbit/sec in traffic. Of course, most of the transaction-heavy tables are InnoDB on that site because of prior issues with locking causing 20 minutes plus of backlog with queries.

The only time I've seen crashed tables since is when I've had either a power loss on my own personal servers (since fixed with a UPS unit and using NUT to do an orderly shutdown) or when I've had to kill -9 the daemon due to various issues (since resolved when the developers cleaned up the database schema).

   

• and this problem persisted when we were on BlueHost (20 times a month) which was an EIGHT core Linux machine. When msnbot was hammering the BlueHost Linux server (before we added the crawl delay), this would occur 5-10 times a DAY.

Interesting. What MySQL version were they running? Also, given how packed they run their servers, it might have been an issue with disk space filling up, leading to MySQL shutting down and then an unclean startup.

Somehow I do not think my choice of operating systems or the way it interacts with AMP is the problem. Would you come to the same conclusion? (I hope... because if not, this will be difficult for the OS/2 dev/porter to track down, as his site does not get the type of traffic we do).

Given the only table corruption I've ever seen for my hosting customers was on certain early versions of MySQL's 5-series, I'm inclined to believe it's a MyISAM issue causing the table crashes. Looking at the daemon log files might help disagnose things better.

On a possibly related, I'm not seeing these issues on the Wordpress sites on that server.

Wordpress is not very transactional. It's primarily SELECT-oriented, so you don't as much of a chance of corruption as the table files are only written when the table has changed. SMF has many tables that are changed almost constantly, which will have a greater chance of causing the problem.

Addendum (11:17EST):
Perhaps there is an issue in certain branches of MySQL or the AMP package as a whole? I am not  sure what Linux source is being used in the OS/2 version, but if that Linux version is the cause of the issues, that would explain why numerous Linux users (and myself on OS/2) are experiencing this problem. The problem I have with this is though I can find out which Linux source/distro/whatever is being used in the OS/2 port, I'd then have to track down all the Linux related threads on this and ask which source tree their version(s) are being compiled from and compare the results.

It is possible, depending on how far your OS/2 port has diverged from the upstream editions and how closely it follows upstream development. If it's based on an early, buggy, release then that would make sense.

Addendum #2 (11:47EST):
Is it possible it's a charset issue? There were earlier bugs in that (in MySQL) as well, and I am noting that for whatever reason, the build of MySQL I am using is defaulting to an odd charset (latin?) while the tables in question are UTF8.

Nah, that wouldn't cause the table files to corrupt themselves. Solar radiation is probably a more likely cause.

Latin-1 is a fine default character set and has been default for years. It covers nearly all languages based on the Roman Alphabet, which is why It's been the default. UTF-8 should also be stable if you're running one of the 5.x series of MySQL. I've not seen any reports of a character set causing corruption, anyway.

[RobertMfromLI]

   

   
• as there are only thousands of similar complaints out there from people running other OS's (Linux primarily)

How old are the complaints? I know when I was running early versions of 5.0 (or was it 5.1?) on transaction-heavy sites, I experienced crashed tables fairly often. It only occurred with MyISAM tables, which seems to indicate there was a bug specifically for that type. Upgrading to a newer version of MySQL fixed this issue.

Some very recent - but yes, as I cannot determine the MySQL version(s) running, it's possible the issues are fixed in the new version.

   

   
• and DOZENS of bugs in earlier MySQL versions that caused this same behavior (maybe some left?)

I experienced it as well. Newer versions fixed those bugs. I haven't seen any recurrence yet, even on a site running at over 4mbit/sec in traffic. Of course, most of the transaction-heavy tables are InnoDB on that site because of prior issues with locking causing 20 minutes plus of backlog with queries.

The only time I've seen crashed tables since is when I've had either a power loss on my own personal servers (since fixed with a UPS unit and using NUT to do an orderly shutdown) or when I've had to kill -9 the daemon due to various issues (since resolved when the developers cleaned up the database schema).

   

   
• and this problem persisted when we were on BlueHost (20 times a month) which was an EIGHT core Linux machine. When msnbot was hammering the BlueHost Linux server (before we added the crawl delay), this would occur 5-10 times a DAY.

Interesting. What MySQL version were they running? Also, given how packed they run their servers, it might have been an issue with disk space filling up, leading to MySQL shutting down and then an unclean startup.

We were the only ones on that box - or so we were told. We were switched to our own box after lots of high cpu usage and a few extra bucks for it. The msnbot issue I suspect was not restart issues... the server itself never went down, just MySQL table crashes. As for what version, that I sadly cannot recall. It was a v5 release, but as far as I can tell, quite a few of the earlier ones had the MyISAM storage engine/corruption bug(s).

Somehow I do not think my choice of operating systems or the way it interacts with AMP is the problem. Would you come to the same conclusion? (I hope... because if not, this will be difficult for the OS/2 dev/porter to track down, as his site does not get the type of traffic we do).

Given the only table corruption I've ever seen for my hosting customers was on certain early versions of MySQL's 5-series, I'm inclined to believe it's a MyISAM issue causing the table crashes. Looking at the daemon log files might help disagnose things better.

On a possibly related, I'm not seeing these issues on the Wordpress sites on that server.

Wordpress is not very transactional. It's primarily SELECT-oriented, so you don't as much of a chance of corruption as the table files are only written when the table has changed. SMF has many tables that are changed almost constantly, which will have a greater chance of causing the problem.

Addendum (11:17EST):
Perhaps there is an issue in certain branches of MySQL or the AMP package as a whole? I am not  sure what Linux source is being used in the OS/2 version, but if that Linux version is the cause of the issues, that would explain why numerous Linux users (and myself on OS/2) are experiencing this problem. The problem I have with this is though I can find out which Linux source/distro/whatever is being used in the OS/2 port, I'd then have to track down all the Linux related threads on this and ask which source tree their version(s) are being compiled from and compare the results.

It is possible, depending on how far your OS/2 port has diverged from the upstream editions and how closely it follows upstream development. If it's based on an early, buggy, release then that would make sense.

MySQL v5.1.52 - perhaps not recent enough to take advantage of the fixes of the MyISAM corruption issues? As far as I know, no code from earlier versions was cobbled into it.

Addendum #2 (11:47EST):
Is it possible it's a charset issue? There were earlier bugs in that (in MySQL) as well, and I am noting that for whatever reason, the build of MySQL I am using is defaulting to an odd charset (latin?) while the tables in question are UTF8.

Nah, that wouldn't cause the table files to corrupt themselves. Solar radiation is probably a more likely cause.

LoL, I'll look into that one. Thanks for the smile/laugh! 

Latin-1 is a fine default character set and has been default for years. It covers nearly all languages based on the Roman Alphabet, which is why It's been the default. UTF-8 should also be stable if you're running one of the 5.x series of MySQL. I've not seen any reports of a character set causing corruption, anyway.

Well, that's at least one more I can cross off my list.

[RobertMfromLI]

Is this (see link) a possibility?


http://www.hikaro.com/linux/mysql/mysql-table-corruption-post-migration-to-mysql5%C2%A0%C2%A0.html


Synopsis: upgrading versions of MySQL can cause such issues and require using a third party tool to properly correct the tables (specific mention of v4).


Note: I never ran v4.0, but I did run some v5.0 versions that upgrading required running the upgrade_table script.


Note 2: It is possible the table was originally on a MySQL v4 series release when it was on BlueHost, though I did not copy the database; I did a full export and then full import (into an early v5 version of MySQL).

[Joshua Dickerson]

Move your messages table to memcached? Your corruption probably relates to locking contentions due to MyISAM. Your searches are most likely slow due to locking. I agree with Motoko-Chan in that it is an odd combination.

[青山 素子]

We were the only ones on that box - or so we were told. We were switched to our own box after lots of high cpu usage and a few extra bucks for it. The msnbot issue I suspect was not restart issues... the server itself never went down, just MySQL table crashes. As for what version, that I sadly cannot recall. It was a v5 release, but as far as I can tell, quite a few of the earlier ones had the MyISAM storage engine/corruption bug(s).

You probably weren't the only one on the box, unless you were paying at least $200/mo for it alone, which would still cost them money (management of servers isn't cheap).

MySQL v5.1.52 - perhaps not recent enough to take advantage of the fixes of the MyISAM corruption issues? As far as I know, no code from earlier versions was cobbled into it.

Not too bad. That shouldn't be having issues on supported platforms at least. It is very possible that there is a bug between the interaction of the HPFS filesystem (I believe OS/2 still uses that) and MyISAM. As it's not a widely tested configuration, there are likely to be quite a few bugs.

Synopsis: upgrading versions of MySQL can cause such issues and require using a third party tool to properly correct the tables (specific mention of v4).

Note 2: It is possible the table was originally on a MySQL v4 series release when it was on BlueHost, though I did not copy the database; I did a full export and then full import (into an early v5 version of MySQL).

That issue is specific to when you copy the actual table files from one install to another. Importing a SQL script would re-create the table files and not be part of the issue.

If you have kept the same table files with upgrades to MySQL itself and have not run mysql_upgrade, that might be a potential cause. You should at least run mysql_upgrade on each update, even minor. I'd also advise running mysqldump and re-importing the data that way so you know that the backing data files are good.

Move your messages table to memcached? Your corruption probably relates to locking contentions due to MyISAM. Your searches are most likely slow due to locking. I agree with Motoko-Chan in that it is an odd combination.

Locking shouldn't cause table corruption under normal circumstances, but this is a MySQL port onto an OS and there is likely very little testing, especially with heavy loads and transactions. It wouldn't surprise me if there was a condition that caused table corruption under high loads on an unsupported platform.

[RobertMfromLI]

MySQL v5.1.52 - perhaps not recent enough to take advantage of the fixes of the MyISAM corruption issues? As far as I know, no code from earlier versions was cobbled into it.

Not too bad. That shouldn't be having issues on supported platforms at least. It is very possible that there is a bug between the interaction of the HPFS filesystem (I believe OS/2 still uses that) and MyISAM. As it's not a widely tested configuration, there are likely to be quite a few bugs.

I thought about that. It was actually originally running on JFS (which is what the Linux JFS port is based off of). I have since switched it to HPFS386 (a bit different and more thoroughly tested than HPFS) and noticed no change in behavior.

Synopsis: upgrading versions of MySQL can cause such issues and require using a third party tool to properly correct the tables (specific mention of v4).

Note 2: It is possible the table was originally on a MySQL v4 series release when it was on BlueHost, though I did not copy the database; I did a full export and then full import (into an early v5 version of MySQL).

That issue is specific to when you copy the actual table files from one install to another. Importing a SQL script would re-create the table files and not be part of the issue.

If you have kept the same table files with upgrades to MySQL itself and have not run mysql_upgrade, that might be a potential cause. You should at least run mysql_upgrade on each update, even minor. I'd also advise running mysqldump and re-importing the data that way so you know that the backing data files are good.

Excellent suggestion. Will do. I did not run mysqlupgrade on the minor version changes. Just on the first v5.0 to v5.1 change.

Move your messages table to memcached? Your corruption probably relates to locking contentions due to MyISAM. Your searches are most likely slow due to locking. I agree with Motoko-Chan in that it is an odd combination.

Locking shouldn't cause table corruption under normal circumstances, but this is a MySQL port onto an OS and there is likely very little testing, especially with heavy loads and transactions. It wouldn't surprise me if there was a condition that caused table corruption under high loads on an unsupported platform.

The interesting thing is, I run tables equally as large (on an older, far less powerful server no less (Quad 550MHz)) and never run into these issues. This page (http://store.aibpc.com/) is entirely dynamically generated (all daemons running on the older server), and is comprised of about 100,000 records in the main table. Each page load does all the "stats" in real time via SQL queries (ie, the item counts, vendor counts, category counts). That machine manages to handle 249 inserts a second (via using one of the CPUs for the table inserts and leaving the rest open for other stuff) with no table corruption. Same MySQL version. BUT, no Apache or PHP (Lotus DominoGo Webserver and REXX and the REXXSQL DLL to support MySQL inserts, queries, etc).


That'd point to it being... ? ? A PHP/MySQL interaction? Apache/MySQL interaction? I just can't figure out how that could create the problem.

[Something like that]

If you have the opportunity, stress test your hardware. Run something that maxes the CPU. Check for SMART errors. Etc.

I'd run memtest86, too.

[RobertMfromLI]

If you have the opportunity, stress test your hardware. Run something that maxes the CPU. Check for SMART errors. Etc.

I'd run memtest86, too.

I did all of that. The box is an IBM eServer xSeries 440, with the memory in RAIDed redundant mode and full ECC support. The drives have been thoroughly tested and scanned (and are in mirrored RAID configuration), as well full testing of the SCSI RAID adapter and it's battery & cache.


The server has a very extensive built in diagnostic suite (and all hardware passes), and I've additionally run various benchmarking and stress test tools against it with no issue.

[Something like that]

If you have the opportunity, stress test your hardware. Run something that maxes the CPU. Check for SMART errors. Etc.

I'd run memtest86, too.

I did all of that. The box is an IBM eServer xSeries 440, with the memory in RAIDed redundant mode and full ECC support. The drives have been thoroughly tested and scanned (and are in mirrored RAID configuration), as well full testing of the SCSI RAID adapter and it's battery & cache.


The server has a very extensive built in diagnostic suite (and all hardware passes), and I've additionally run various benchmarking and stress test tools against it with no issue.

Okay. Double checking, because I've had more than one flaky machine reveal itself that way.

That being done, I echo Motoko's suggestion of dumping and reloading.

[RobertMfromLI]

Much appreciated, as are any suggestions to anything I may have missed, forgotten, or overlooked.


-R

[Something like that]

Code Select Expand


max_seeks_for_key=100
key_cache_block_size=2048


Have you tested without these explicitly set?

I'm especially suspicious of key_cache_block_size. The default is 1024, and you may have stumbled upon a bug/assumption in the code. Google reveals there were corruption issues with setting it to 4096 in the past.

[RobertMfromLI]

Code Select Expand


max_seeks_for_key=100
key_cache_block_size=2048


Have you tested without these explicitly set?

I'm especially suspicious of key_cache_block_size. The default is 1024, and you may have stumbled upon a bug/assumption in the code. Google reveals there were corruption issues with setting it to 4096 in the past.

I've since set it back. It had originally been default (via no entry), forced 1024 (via the my.cnf entry) and then 2048 as noted above. :-(


And always a number that was a factor of 2 (which I  believe was the other earlier issue with that setting).

[青山 素子]

On that high-transaction code, what table type are you using?

[Joshua Dickerson]

http://blogs.oracle.com/mysql/2011/01/comparing_innodb_to_myisam_performance.html

[RobertMfromLI]

On that high-transaction code, what table type are you using?

All MyISAM. Only default caches enabled on that machine.


Correction: the Netfinity 7000 M10 runs at 229 inserts a second per 550MHz CPU, not 249/sec. Averaged over 10 runs, it's 229.1 (with them varying between 229.05 to 229.12).


In all fairness, I have very little idea how PHP deals with SQL. On the REXX end, the REXX DLL is simply a wrapper to the MySQL DLL, meaning the REXX code is virtually directly calling MySQL to do inserts. REXX DLLs are wonderful at wrapping and calling C code directly. Assuming the PHP integration is a little more complex, I'd expect performance degradation on the PHP side.


In this, I also expect higher performance then an AMP integrated solution, simply because I know that MySQL is better optimized to utilize OS/2's threading engine (and since it is the only component of AMP being used on that machine, this implementation won't suffer from the lack of optimization on the PHP and Apache end). And LDGW is entirely thread oriented, meaning the website side also benefits.


But that's getting a little off topic. To get back on topic, does anyone suspect that the PHP/MySQL integration could be the issue? As I said, I have no idea how PHP actually handles (back end/internally) MySQL requests; while the REXX DLL is only limited to the capabilities (or bugs) of MySQL itself.

[RobertMfromLI]

I'll be giving APC a try maybe tonight if I have the time. The OS/2 AMP maintainer just ported it for me yesterday... so, it'll be a test run.

[青山 素子]

To get back on topic, does anyone suspect that the PHP/MySQL integration could be the issue? As I said, I have no idea how PHP actually handles (back end/internally) MySQL requests; while the REXX DLL is only limited to the capabilities (or bugs) of MySQL itself.

I don't think so, but it's a possibility. PHP's MySQL module is just a simple wrapper around libmysql, the MySQL client library. It's a pretty stable thing, so any bugs would likely be in the MySQL side.