Hi all, Only to report on this ad avoid loosing it...
I was inspired by my brother, Jérôme, to optimize the SQL migration script of Chamilo 1.8.7.1 -> 1.8.8.2. Last time we had run a migration for the free campus (campus.chamilo.org), it had taken over 16h to actually get the databases upgraded... several alter table, create table, add index and insert/update repeated over 4000 databases (the number of courses we had back then). This time, I took about 3h of my time to review MySQL documentation on optimizing queries, on PHP parallel processing and a series of related contents, then optimized the upgrade script. You can find the original upgrade script here: http://code.google.com/p/chamilo/source/browse/main/install/migrate-db-1.8.7-1.8.8-pre.sql?repo=classic The different sections do not matter much, but the last section (marked by --xxCOURSExx) is repeated once for every course (in this case 7180 times). The optimizations only consisted in: - adding indexes *after* alter tables and inserts/updates (to avoid having to update the indexes during the operation) - moving the CREATE TABLE statements in a group before everything - joining all INSERT queries into one single INSERT with values (a1,a2),(b1,b2),(c1,c2) instead of separate complete INSERT queries - joining all ALTER TABLE on the same table into one single ALTER TABLE (pretty much the same way as inserts) - manually LOCKing all the tables before proceeding with ALTER, INSERT or UPDATEs, and UNLOCKING them afterwards I first tried on a small campus database (17 databases), and sadly the server was too quick to show any evidence of speed gain (1.5s average for the whole database update). Then I tried migrating the free campus (so 7180 databases). The previous migration (the one that took +16h) had about double the queries on the courses databases, so I expected this update to be twice as fast. Instead, the complete database update took place in a staggering 26 minutes (making me spend a great Sunday catching up delayed work instead of watching anxiously the load on my server go insanely up for 16h). I leave you with a very interesting reference about how MySQL spends its time when processing an INSERT query (there are links to other types of queries there): http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html Yannick _______________________________________________ Dev mailing list Dev@lists.chamilo.org http://lists.chamilo.org/listinfo/dev
