Performance Drops after PHP & MySQL Upgrade

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Performance Drops after PHP & MySQL Upgrade

Post by pawprint_net »

I've been battling with this for the last week and I'm hoping someone may have an idea. I have a longstanding set of scripts using PHP and mySQL tried and tested under mySQL 3.xxx and PHP4.xxx They are a standard CMS package in use by about 200-300 customers on about 20 different servers.

Recently one of the hosts upgraded to PHP 5.1.4 and MySQL 4.1.19 and ever since then that server (which was one of my fastest) has ground to a creeping hault. EG: a script which takes 10 seconds to process a lot of data on my dev server (old 1GHz system) under php 4 takes over 7 minutes on the PHP 5 server - same exact code. It generates no errors or warnings on either. I have testing using the ZDE debugging and code analyze functions - and nothing seems amiss there.

The code is not extensively OOP - mainly just standalong functions.

Can anyone think of anything in going from PHP 4.4 to 5.1 and.or MySQL 3.23 to 4.1 that could cause something like this?

As you may guess - the host is intent on blaiming my code -- sorry for no code sample - it;s about 500,000 lines of code and I doubt you'd want that

:wink:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

using pconnect?
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

No, although I haven't tried that as an option... could that make that big a difference beteren these versions?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There are a great many of settings that affect performance. These are mostly in the server configuration file. Pconnect is often slower in later versions of MySQL because the standard connection was sped up, that I remember.
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

Well, I'm not using pconnect - I did try - just for kicks - and yes it did slow it down more.

Any other ideas?
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

I'd recommend doing some mircotimes in there to narrow down where in the code the hold up is.

I can't imagine a script running so slow, what's the CPU / Memory utilization?

Sounds more like you might be opening files back through the web server or something weird like that.
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

Actually I just built a timer class the let's me set lap times and then gives me a little report at the end last night - and I have been able to narrow it down a bit on a per-report bases. Although the current function that seems to be the source for one of the reports is called from other functions where there is no problem. I do believe it may be linked to server load.

Unfortunately I don't have much access to the server so I can't view load myself or even see the log files.

There is no file access in these cases - it's just a script that grabs a bunch of information from various tables in a single database (no joins) and then computes a bunch of information and sends the output to the client broswer. As I mentioned it runs really well on my dev server and did run well on that server before they upgraded. I have implemented a bunch of caching, report splitting, and paired down versions of some of my utility functions that is speeding it all up - but I still want to know the root cause.

DB access seems to be the key - I can make PHP work like a horse and it's not an issue - but each database access seems to cost em dearly.

PS: I did rebuilt all the indexes etc after the upgrade too - no dice.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

Are you using any Names and not IPs for your DBase server? Just ruling out like a DNS issue or something else silly like that. You running the same versions of MySQL and PHP in Dev with the same INI settings?
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

Using a name for the mysql server in production - dev is localhost. The production server didn't change IP as a result of the upgrade - the IPs are allo the same and they didn't change DNS/BIND versions as far as I know - but I'll ask them.

The whole problem surrounds dev being PHP 4 and Production being PHP 5 - also Dev is MySQL 3 and Production is MySQL 4 - so no the two versions are most certainly not the same.

I have about 200 customers for this system (I'm a web designer/developer) all but one use hosts on PHP 4/MySQL 3 - this is the first PHP 5/MySQL 4 system I have experienced in production. All the systems work great except this one.

I don't have the right os on my current dev server to run PHP 5 (did try to do that) but at this point I don't have time to re-work my entire dev platform for just the one host (granted it is a rather big project - Canadian Coast Guard Auxiliary tracking all SAR Cases in the Pacific) - especially since it's a voulenteer based project.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

What happens if you use the IP in production?

I'd try to replicate the problem in test. I've been a fan of the free VMWare server for duplicating mulitple environments in dev/test and being able to go back to a snapshot if I hose something down.
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

Well, I just re-wrote some of the code that I found to be the source using the timer and it seems to be faster then it was now...

Production is still slower then my dev - and that's odd considering the the HW for production is 10 times my dev system.

I'll try the IP and let you know - but now I'm not sure I'll be able to tell.

I guess it may be resolved at this stage - but I still wish I knew what it was. The good news is that the re-write is speeding up all other 199 customers :)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Is your dev system processing the same amount of data as the production system?
User avatar
pawprint_net
Forum Newbie
Posts: 9
Joined: Sat Jun 10, 2006 11:35 pm
Location: Sechelt BC Canada
Contact:

Post by pawprint_net »

It wasn't at first - but I took a database extract from the production and now they are exactly in sync. It didn't make any difference.

Here is an update... as I have got it back to normal speed.

Changing to IP address didn't make any difference.

I had one recursive function in the "mix" I have re-written it to use loops instead, not as clean but it sped things up a lot. Again it worked fine under PHP 4 - but 5 seems to take issue with recursion - go figure.

I'm not sure exactly if this is the problem at a root level - but it has solved my immediate issue.

Thanks to everyone offering ideas - it is much appreciated. If you want to see the site in question (for curiosity) visit http://www.ccga-pacific.org/ the SAR stats on the home page come from this system - but the main portion is restricted to members so I can't really show that part.
Post Reply