PHP Script runs slow, but server CPU is idle

Question

I have a PHP script to import various data from text files.

The import is very complex and my test file has 32.000 entrys. These entrys have to be parsed and inserted into a mysql database.

If i will run my script it needs 30 minutes to finish... And at this time my server cpu is 99% idle.

Is there a chance to optimize php and mysql that they are using more power from the machine?

code:

if ($handle = @fopen($filename, "r")) {

    $canceled = false;
    $item = null;
    $result = null;

    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        $line = $buffer;

        if (substr($line, 0, 2) == '00') {

            continue;
        }
        else if (substr($line, 0, 2) == '99') {
            continue;
        }
        else if (strlen($line) < 75) {
            continue;
        }

        Reread:
        if ($canceled) {
            break;
        }

        if ($item == null) {
            $item = new DA11_Item();
            $item->boq_id = $boq_id;
        }
        $result = $this->add_line_into_item($item, $line);

        if ($result == self::RESULT_CLOSED) {
            $this->add_item($item);
            $item = null;
        }
        else if ($result == self::RESULT_REREAD) {
            $this->add_item($item);
            $item = null;
            goto Reread;
        }
        else if ($result == self::RESULT_IGNORD) {
            if (count($item->details()) > 0) {
                    $this->add_item($item);
            }
            $item = null;
        }
    }

    if ($item !== NULL) {
        $this->add_item($item);
    }

    fclose($handle);
}

add_item will perform a $item->save() and saves it to the database.

thx and kind regards, viperneo

are you looping through and doing single inserts? some code would be helpful. — Ian Wood
– Ian Wood, Commented Aug 7, 2012 at 11:45
I am using phpActiveRecord from (www.phpactiverecord.org). I am reading the file, line by line, and do single inserts, yes. I think i could do this via a transaction, should be perform a little bit faster. — swalter88
– swalter88, Commented Aug 7, 2012 at 11:47

KingCrunch · Accepted Answer · 2012-08-07 11:48:49Z

1

One problem you have is that every single insert is a separate request to your db-server including it's response. With 32.000 records you maybe get an idea, that this is a quite huge overhead. Use bulk inserts for (lets say) 1000 records at once

INSERT INTO foo (col1, col2) VALUES
  (1,'2'),
  (3,'4')
  -- 997 additional rows
  (1999, '2000');

Additional Transactions may help

Update, because you mentioned active-record: I recommend to avoid any additional abstraction layer for such mass import tasks.

answered Aug 7, 2012 at 11:48

KingCrunch

133k22 gold badges158 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

swalter88 Over a year ago

i have switched to transactions and now the execution time is only 2 minutes. it was right, that mysql was the bottleneck... thx!

Paul · Accepted Answer · 2012-08-07 11:47:54Z

0

As Florent says in the comment, the MySQL engine is too slow to handle the requests. Moving the MySQL database to a SSD (instead of a HDD) will improve speed significantly.

answered Aug 7, 2012 at 11:47

Paul

9,0523 gold badges30 silver badges48 bronze badges

Comments

René Höhle · Accepted Answer · 2012-08-07 11:48:23Z

0

There are some things that you can optimize.

But i think the most performance take your MySQL Database. Sometimes its possible that some indexes on your joined tables bring a lot of speed. You should check that at first.

When you have a linux system you could use mysqltuner to optimize your database.

The second way is to optimize your code and cache some results from your database when you've done the same before.

answered Aug 7, 2012 at 11:48

René Höhle

27.4k22 gold badges78 silver badges91 bronze badges

Collectives™ on Stack Overflow

PHP Script runs slow, but server CPU is idle

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related