Welcome to Geeklog, Anonymous Monday, April 29 2024 @ 07:08 am EDT
Geeklog Forums
MySQL - MyISAM or INNODB for Geeklog
Status: offline
Laugh
Site Admin
Admin
Registered: 09/27/05
Posts: 1468
Location:Canada
Has anyone done any speed tests under Geeklog with the 2 storage engines?
On most of my installs I have a mix of storage engines thanks to plugin installs etc.. which is not ideal.
I am thinking of switching all of the tables over to INNODB just because eventually that is where I want to take Geeklog (data integrity with foreign keys, etc.., record locks). I thought MyISAM was supposed to be the faster of the two but doing a few quick searches on Google suggested that this is now changing.
BTW here is a quick and easy SQL statement to make a bunch of SQL statements to convert tables in your db from one storage engine to another:
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
AND engine = 'MyISAM'
AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');
One of the Geeklog Core Developers.
On most of my installs I have a mix of storage engines thanks to plugin installs etc.. which is not ideal.
I am thinking of switching all of the tables over to INNODB just because eventually that is where I want to take Geeklog (data integrity with foreign keys, etc.., record locks). I thought MyISAM was supposed to be the faster of the two but doing a few quick searches on Google suggested that this is now changing.
BTW here is a quick and easy SQL statement to make a bunch of SQL statements to convert tables in your db from one storage engine to another:
Text Formatted Code
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
AND engine = 'MyISAM'
AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');
One of the Geeklog Core Developers.
2
3
Quote
Status: offline
Dirk
Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
I haven't done any performance tests. Just wanted to point out that InnoDB is actually the default engine as of MySQL 5.5 if you don't specify anything. We do specify MyISAM, though, unless you pick the InnoDB option during install.
Also, there's an option to convert the database to InnoDB in admin/database.php. It goes through the $_TABLES array, so it would also convert plugin tables. There's no option to go back to MyISAM, though.
bye, Dirk
Also, there's an option to convert the database to InnoDB in admin/database.php. It goes through the $_TABLES array, so it would also convert plugin tables. There's no option to go back to MyISAM, though.
bye, Dirk
3
3
Quote
Status: offline
Laugh
Site Admin
Admin
Registered: 09/27/05
Posts: 1468
Location:Canada
Hmmm, I never knew about the convert option. I see the optimize tables on my installs but not Convert to InnoDB. Is this dependant on if you have the backup set up properly? (ie the path to mysqldump) I do not since it is not on the localhost.
One of the Geeklog Core Developers.
One of the Geeklog Core Developers.
2
3
Quote
Status: offline
Dirk
Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
Quote by: Laugh
Hmmm, I never knew about the convert option. I see the optimize tables on my installs but not Convert to InnoDB. Is this dependant on if you have the backup set up properly? (ie the path to mysqldump) I do not since it is not on the localhost.
It checks if your MySQL does actually support InnoDB (doing a SHOW STORAGE ENGINES). It works here on geeklog.net but, as I just noticed, not on my local test machine. Looking into it ...
bye, Dirk
2
6
Quote
All times are EDT. The time is now 07:08 am.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content