optimization for mass sql insert

Question

I have to insert 100k+ record in the db and I have some memory issues. $_data is an array holding the arrays of data. I even increase the memory size but still got into problems

// VERSION 1
protected function save() {

    $memory_limit = ini_get('memory_limit');
    ini_set('memory_limit', '512M');

    $sql = "
        INSERT INTO table (
            c1,
            c2,
            c3,
            c4,
            c5,
            c6,
            c7,
            c7,
            c9,
            c10,
            c11             
        ) VALUES (?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE 
            c10 = VALUES(c10),
            c11 = VALUES(c10),
            c12 = VALUES(c12)
    ";
    $db = Zend_Registry::get('db');
    $stmt = new Zend_Db_Statement_Pdo($db, $sql);
    foreach($this->_data as $entry){
        $stmt->execute($entry);
    }
    unset($this->_data, $stmt, $sql);
    ini_set('memory_limit', $memory_limit);

The second tries to insert all entries in a multi-insert, but not better.

// VERSION 2
protected function save2(){
    $question_marks = str_repeat('?,', count($this->_data[0]));
    $question_marks = trim($question_marks, ',');
    $question_marks = str_repeat("($question_marks),", count($this->_data));
    $question_marks = trim($question_marks, ',');
    $sql = "
        INSERT INTO table (
            c1,
            c2,
            c3,
            c4,
            c5,
            c6,
            c7,
            c7,
            c9,
            c10,
            c11             
        ) VALUES $question_marks
        ON DUPLICATE KEY UPDATE 
            c10 = VALUES(c10),
            c11 = VALUES(c11),
            c12 = VALUES(c12)
        ;";
    $db = Zend_Registry::get('db');
    $stmt = new Zend_Db_Statement_Pdo($db, $sql);
    $insert_values = call_user_func_array('array_merge', $this->_data);
    $stmt->execute($insert_values);
    $affected_rows = $stmt->rowCount();
    if ($affected_rows){
        // @todo log    
    } 
    unset($this->_data);
    unset($stmt, $sql, $insert_values, $affected_rows, $question_marks);

Column names are not the original. Any suggestions?

---

I will try to split the data array to 5k entries and do the inserts in batches. Also trying to see how modifying max_allowed_packet in mysql cnf helps. Meanwhile I would appreciate any suggestions. Thanks

UPDATE

in my case modifying the max_allowed_packet from 16M to 1024M helped and I did make the insert without splitting the array.

Answer 1

In complement to @Tudor Constantin answer and as you ask for other suggestions:

One way to handle that is, indeed, to work with chunks of data and not with a big _data array of 100k+ rows (which, even without talking of the query is an array which consume a lot of your available memory).

You should check Zend_Memory for storing your data structure containing all the 100K rows data. This will allow you to manage a virtual objects containing all the rows without having all of them in the real PHP memory. And this will maybe allow you to avoid asynchronous batchs.

Then run your insert query with a limited number of rows each time (so that the query string doesn't get too big). Your 2 codes examples could be used and as said in other response direct access to mysql_query could prevent memleaks from Zend_Db or PDO..

If you do not use mysql_query, check that you do not have the Zend_Db_Profiler activated on the Db object. If you still see some memory leaks after each request you could try a Zend_Debug of the Db object and try to see if some historical data is not stored somewhere (list of past queries for example). Then check the statement object as well for the same things.memory_get_usage function calls can help you as well debug the leaks.

I would try to unset($entry); in the foreach loop end as well.

Answer 2

How do you know you have memory problems and not script execution timeouts?

Anyway, I would try with the simple mysql_query

Also, take a look and make sure to not have memory leaks on the part that reads the data and prepares it for insert into DB

Answer 3

I think your problem is the array size - not anything related to DB. You have to split the $this->_data array IMO.