Posted on: 12/08/12 10:04am
By: Laugh
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:
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');
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/08/12 02:15pm
By: ::Ben
Tell us if INNODB become the standard for Geeklog I will update my plugins.
Thanks,
Ben
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/08/12 08:00pm
By: Laugh
Of course. We would discuss it first before any decision is made.
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/09/12 09:29am
By: Dirk
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
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/09/12 09:42am
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.
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/09/12 10:06am
By: Dirk
Quote by: LaughHmmm, 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
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/09/12 10:19am
By: Dirk
Found it - and it has to do with what I mentioned above: It's the default on MySQL 5.5 now, so the Support column reads "default" instead of "yes".
Fix here.[*1]
bye, Dirk
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/10/12 11:16am
By: Laugh
Thanks Dirk. I added the fix to my 1.8.1 install and it works fine.
Tom
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/11/12 06:25pm
By: LWC
Where is the GUI option for converting?
Re: MySQL - MyISAM or INNODB for Geeklog
Posted on: 12/11/12 08:01pm
By: Laugh
It should appear in the DB Backups menu.