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

[RobertMfromLI]

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.

ok... so the issue isn't there - it's doing the same thing as the REXX wrapper DLL.

[Something like that]

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.

It uses the MySQL C interface. All data is passed back and forth as plain ASCII strings, so it's practically impossible that any corruption would happen there. This is also why PHP returns numeric columns in MySQL as strings.

[RobertMfromLI]

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.

It uses the MySQL C interface. All data is passed back and forth as plain ASCII strings, so it's practically impossible that any corruption would happen there. This is also why PHP returns numeric columns in MySQL as strings.

So, it's virtually exactly the same as what the REXX DLL does. This still leaves me baffled. 229 inserts a second (on the older Netfinity) on one 550MHz CPU is far less than our forum's total 6-8 MySQL queries a second (with peaks that I presume aren't much more than 30/sec) on two 2.4GHz CPUs.


I think I'll start by installing and using APC, then take a look at whether I'm (technically) capable of porting Sphinx over (or if the OS/2 AMP maintainer will do so for me).


Question: If I can get Sphinx installed and running, can I correctly presume that I can turn smf_messages into an INNODB format?

[Something like that]

Question: If I can get Sphinx installed and running, can I correctly presume that I can turn smf_messages into an INNODB format?

Yes, definitely.

I run entirely InnoDB, using Sphinx for search.

[RobertMfromLI]

Here's an oddity I cannot explain (though I can speculate maybe). The only change I've done in a little while was doubling the thread_concurrency value (to twice the recommended - currently at 8 for a dual CPU machine). This seems to have alleviated table corruption issues.


I'm at a loss to explain why... though I do know Warp Server for eBusiness was designed for exceptional thread handling... but I am not sure how using more threads is actually alleviating the problem.

[Something like that]

Here's an oddity I cannot explain (though I can speculate maybe). The only change I've done in a little while was doubling the thread_concurrency value (to twice the recommended - currently at 8 for a dual CPU machine). This seems to have alleviated table corruption issues.


I'm at a loss to explain why... though I do know Warp Server for eBusiness was designed for exceptional thread handling... but I am not sure how using more threads is actually alleviating the problem.

I'd file a bug with MySQL then!

[RobertMfromLI]

Here's an oddity I cannot explain (though I can speculate maybe). The only change I've done in a little while was doubling the thread_concurrency value (to twice the recommended - currently at 8 for a dual CPU machine). This seems to have alleviated table corruption issues.


I'm at a loss to explain why... though I do know Warp Server for eBusiness was designed for exceptional thread handling... but I am not sure how using more threads is actually alleviating the problem.

I'd file a bug with MySQL then!

Will do... I'm going to give it another week first though, and see what happens. I'll also re-enable the old settings, debug mode and try to capture an error or crash log.


Thanks to everyone who's responded!

[Deaks]

RobertMfromLI, did you get this sorted? its been longer than a week

I have marked this as solved until you get back to us

[RobertMfromLI]

RobertMfromLI, did you get this sorted? its been longer than a week

I have marked this as solved until you get back to us

Hi Runic,


I guess the answer would be "worked-around", so, for now (since there isn't such an option), "Solved" should be fine. I'm guessing from my experience on both Linux and OS/2 boxes with SMF, that this is indeed a bug in MySQL, which I am currently working around by doubling the thread_concurrency settings. As of now (since I mentioned I'd done that), I havent had the type of problems I had been previously experiencing. Auto recovery from crash works now, and table crashes are a lot lot less frequent.


In the event we get somewhere with tracking down the bug and the MySQL team finds a solution, I'll post here and change the solution to that post. I think I may need to fire up a Linux box to do that though (even though the OS/2 sources are nearly a direct compile of the Linux version) just to make things easier and bypass that hurdle (ensuring they understand that I've managed to replicate this on Linux as well, so it's not OS/2 specific).


Best,
Robert