Yii Framework - InnoDB vs MyISAM

Question

i have a question: i have built a big application with Yii and InnoDB and came to the problem, that the insert/update durate really really long time, here is my php report:

INNODB: admin User update 55.247464895248 seconds ekuskov User update 13.282548904419 seconds doriwall User update 0.002094030380249 seconds

MYISAM: admin User update 7.8317859172821 seconds ekuskov User update 1.6304929256439 seconds doriwall User update 0.0020859241485596 seconds

Can anyone suggest some solution to speed up the insert/update?

EDIT ----------------------------------------------

Now i used some very simple insert loop:

public function run($args) {
    $time = -microtime(true);

    $begin    = DateTime::createFromFormat('Y-m-d H:i:s', '2010-01-01 00:00:00');
    $end    = DateTime::createFromFormat('Y-m-d H:i:s', '2013-01-01 00:00:00');
    $end->add(new DateInterval('P1D'));
    $interval = DateInterval::createFromDateString('1 day');
    $days     = new DatePeriod($begin, $interval, $end);


    foreach ( $days as $day ) {
        echo "i";
        $track = new TimeTracking();
        $track->user_id = 25;
        $track->date = $day->format('Y-m-d H:i:s');
        $track->active = 4;
        $track->save(false);
    }

    $time += microtime(true);
    echo count($days)." items insert - $time seconds\n";
}

and now the INSERT times are following:

InnoDB: items insert - 72.269570827484 seconds

MyISAM: items insert - 0.87537479400635 seconds

[EDIT] And now i was counting time for whole SAVE method and Yii Models "save()" function:

UPDATE: model->save(false) - 0.1096498966217 seconds

UPDATE: controller save function () - 0.1302649974823 seconds

CREATE: model->save(false) - 0.052282094955444 seconds

CREATE: controller save function () - 0.057214975357056 seconds

Why just save() method takes so long?

[EDIT] I have tested save() vs command() and they durate same:

$track->save(false);

or

$command = Yii::app()->db->createCommand();
            $command->insert('timeTracking', array(
                    'id'=>NULL,
                    'date'=>$track->date,
                    'active'=>$track->active,
                    'user_id'=>$track->user_id,
            ));

EDIT -----------------------------

And here is a statistic for inserting 1,097 Objects:

save(): 0.86-0.94, 
$command->insert(): 0.67-0.72,
$command->execute(): 0.46-0.48,
mysql_query(): 0.33-0.36

FINALLY ANSWER: If you want to use some massive INSERT or UPDATE methods you should consider to create the functions with direct MYSQL Calls, there you will save almost 70% of execution time.

Regards,

Edgar

Georgetown

This doesnt sound right. Maybe you should check your queries. What is difference between admin, ekuskov and doriwall users? – Eeg

can you post your table stuctures ? – Factual

d.pr/i/P6zp The function just updates the balance-times for each user. – Georgetown

it doesnt exactly show all the keys,indexes etc, can you just show us the create statement for both tables ? (the myisam one and the innodb one) – Factual

Bottom i wrote about the command, there is the exact command. Query: INSERT INTO timeTracking (id, date, active, user_id) VALUES (NULL, $track->date, $track->active, $track->user_id); – Georgetown

Remember that the InnoDB engine is ACID-compliant. If you are throwing a big bunch of inserts/updates at an InnoDB database, then it must write each and every modification to the disk to guarantee data safety. Try wrapping those many saves to one transaction to allow InnoDB to group those disk writes into one modification at the end of the transaction. – Norma

Nice investingation and solving by your own. I added mysql tag, with that you probably would get far more audience. – Goutweed

possible duplicate of Why is MySQL InnoDB insert so slow? – Lacombe

In addition: If you are going for raw speed then you don't wanna use ActiveRecord but DAO/PDO => less memory usage and a lot faster as you get rid of all the unnecessary validation stuff etc. – Dose

try to wrap a bunch of insert statements into a transaction for the innodb table. – Jard

I think you should put your answer, even if you found it yourself in an answer. Easier to see at first glance that the question was already answered. – Cathepsin

Answer 1

A table crawling on insert and update may indicate that you've got a bit carried away with your indexes. Remember that the DB has to stop and recompile indexes after every commit.

Costplus

Answer 2

With Yii and InnoDB You should wrap your commands in a transaction like so:

$transaction = Yii::app()->db->beginTransaction();

try {
    // TODO Loop through all of your inserts

    $transaction->commit();
} catch (Exception $ex) {
    // There was some type of error. Rollback the last transaction
    $transaction->rollback();
}

Comedietta

Answer 3

The solution was: for those tables, where you need big inserts and quick response, convert them to MyISAM. Otherwise the user has to wait a long time and there is a threat, that the PHP Max Script Execution Time will stop your script.

Georgetown

Answer 4

InnoDB is a much more complex, feature-rich engine than MyISAM in many respects. That makes it slower and there is not much you can do in your queries, configuration, or otherwise to fix that. However, MySQL is trying to close the gap with recent updates.

Look at version 5.6:

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html

Your best bet may be to upgrade your version of MySQL if you're behind.

Santiagosantillan

Answer 5

InnoDB gives the option to make relations and constraints which makes the database faster and you will be able to generate models & crud with those relations.

You could also consider to break the queries into smaller ones and execute them one by one.

Oscitancy

Answer 6

As MySQL specialists say, use InnoDB until you can explicitly prove that you need MyISAM. Performance issues is not good argument.

If you have big application, you probably will face problems with table-level locks and data inconsistency. So use InnoDB.

Your performance issue may be connected to the lack of indexes or hard disk and its file system issues.

I worked with tables having hundreds of millions rows which were updated and inserted constantly from several connections. Those tables had InnoDB engine.

Of course, it you have data that should be added in a bunch, add them using one insert statement.

Swahili