On Aug 19, Davide Alberani <alberanid-VGgt2q2+T+FeoWH0uzbU5w@xxxxxxxxxxxxxxxx>
wrote:
> It's time to do some benchmarks, to see if it's true for InnoDB,
> for MyISAM and for other database servers as well.
I have tested the creation of a database with only three
information: movie titles, actors and actresses.
1.
MySQL, MyISAM, indexes created _after_ the data was inserted.
# TIME readMovieList() : 3 min, 32 sec.
# TIME castLists(actor) : 13 min, 26 sec.
# TIME castLists(actress) : 8 min, 26 sec.
# TIME createIndexes() : 12 min, 24 sec.
TOTAL: 0h 37m 48s (this is the actual default).
2.
MySQL, MyISAM, indexes created _before_ the data was inserted.
# TIME readMovieList() : 6 min, 8 sec.
# TIME castLists(actor) : 27 min, 8 sec.
# TIME castLists(actress) : 51 min, 42 sec.
TOTAL: 1h 24m 58s (and things are only getting worse as tables grow).
3.
MySQL, InnoDB, indexes created _after_ the data was inserted.
# TIME readMovieList() : 4 min, 49 sec.
# TIME castLists(actor) : 14 min, 19 sec.
# TIME castLists(actress) : 8 min, 34 sec.
# TIME createIndexes() : 120 min, 26 sec.
TOTAL: 2h 28m 08s (woah! and about a third more of harddisk space used
than MyISAM)
4.
MySQL, InnoDB, indexes created _before_ the data was inserted.
(InnoDB seems to be more CPU and memory-intensive than MyISAM)
# TIME readMovieList() : 31 min, 17 sec.
# TIME castLists(actor) : 65 min, 52 sec.
# TIME castLists(actress) : 65 min, 59 sec.
TOTAL: 2h 43m 08s (I'm definitively not in love with InnoDB ;-) - and this
run used even 10% more HD space than the previous one)
5.
Time to alter the table (with indexes), converting from MyISAM
to InnoDB: 77 min, 29 sec.
Total time to create a database using MyISAM and then converting it
to InnoDB: 1h 55m 17s.
Looks like this one is the best option.
Now... does anyone know if there's a single SQL statement to
convert every table of a database from MyISAM to InnoDB?
--
Davide Alberani <alberanid-VGgt2q2+T+FeoWH0uzbU5w@xxxxxxxxxxxxxxxx> [PGP KeyID:
0x465BFD47]
http://erlug.linux.it/~da/
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
|
