Problem: CLI script barely running since server upgrade

[digitalcowboy]

I recently upgraded a dedicated server, moving from an Athlon dual core with 2GB RAM to a Dual Xeon dual core with 4GB of RAM.  Everything is great (main app is CakePHP based) except for one CLI PHP script that is running very, very slow on the new server.

 

The script runs nightly from Cron and is quite simple.  It selects new records for the last 24 hours from one table into a summary table.  (That part is selecting/inserting ~8-9000 records from ~1.2M and is fine; it completes in under 2 seconds.)  Then user records are pulled to create a personal email for each user based on category and location preferences.  It's a pretty simple foreach that selects the records from the summary table that match the cat and loc preferences by user and insert them into a second summary table from which the individual emails are later built.  (Approximately 22k users in the foreach.)

 

On the old, less powerful, server that script completed in ~30-40 minutes.  On the new server it's taking over 3 hours and using almost no CPU.  I ran the same script, against a mirror of the database, on my test server (a C2D Mac Mini) and it ran in 14 minutes using around 20% CPU while the mini was also recording and displaying HDTV via EyeTV.

 

On the Dual, dual core Xeon, top shows the script bouncing around between 0.3% and 0.7% of CPU while the server is almost completely idle, hovering at 74% or higher idle CPU.  Occasionally the script's CPU usage goes higher but very rarely over 1.0%.

 

I'm using PHP 5.2.9 with Zend Engine v2.2.0.  Memory is unlimited in the php.ini and top shows the script is using less than 1.0% memory anyway.  The server has ~2GB phys. memory free so I don't think it's a memory issue.

 

When I tail the script's output log, I can see it is essentially doing nothing for minutes at a time, then will process a couple hundred user records in a few seconds, then back to "stalled" for another few minutes.  As I type this, the script has been executing for right at 3 hours and top's TIME+ is showing it's actually run 1:09.52.

 

I apologize if this is addressed elsewhere in the forums.  I searched but didn't really know what terms to search on for this.

 

Any suggestions or help would be very much appreciated.  This is a production process for a client and this is a real problem for me.  I've been experimenting and searching for a couple weeks trying to figure out how to fix  this and have come up frustrated.

 

Thanks in advance.

[corbin]

Sounds to me like something is hanging in there.

 

 

Do you happen to use mail()?  mail() with a lot of calls can be terribly slow.

 

 

Can you trackdown some how where the script slows down?

 

[digitalcowboy]

No calls to mail().  This script is just summarizing the data.  Another process on a different server sends the mail later and is running fine.

 

My thinking was the same as yours but it seems to just be --not running-- a lot of the time.  The part of the script that is running slow is a foreach loop that runs through a little over 22k times.  It's a pretty simple SELECT and INSERT INTO... Like I said in the original post, it will process hundreds of them in a matter of seconds (while showing 0.7-1.0% CPU use) and then just sit there for minutes.  There's nothing unusual about the record(s) where it hangs, nor is it predictable where and for how long.  It acts like it's just sleeping.  It runs in less than 15 minutes on a Mac Mini with only a fraction of the available CPU, but using about 20 times more CPU (percentage-wise) than it does on this server.

 

All of my CakePHP stuff running with Apache 2 on the same server and against the same database is fine - serving over 80k hits a day on the Cake app.

 

Also, as I said before, the code hasn't changed; it ran fine on the old server with half the power and an almost identical software config.  The only difference is that this server is running 64 bit CentOS and 64 bit MySQL.  The old server was 32 bit CentOS and MySQL.  Could that be causing this?  Does CLI PHP require some special consideration for a 64 bit host or 64 bit DB?  The  Mac Mini is running the same version of 64 bit MySQL...

[corbin]

Hrmmm... It sounds to me like poorly designed SQL statements.

 

 

Would there be anyway to optimize the SQL stuff?

[yettie10ff]

Did you ever find a solution to your problem?  Sounds eerily similar to our current situation.  Thanks!