MyISAM to InnoDB

[Alien_Time]

Correct me if I am wrong. But from my memory, I think the older version of php (upto 3.0.12) is using MyISAM as the storage engine and the 3.1 uses InnoDB storage engine, is that right? (This is what I understood when I was looking at the sql tables). If this is the case, when I upgrade 3.0.12 board to 3.1, will the storage engine be changed from MyISAM to InnoDB for all existing tables as well?

[mrgtb]

News to me if 3.1 is using InnoDB, I'm running it and all tables are MyISAM. From what I can see 3.1 is still using MyISAM, not InnoDB as default for database storage engine.

[brunoais]

I'm trying to introduce InnoDB to phpBB but it won't happen for 3.1. There are other higher priority tasks that require being done.
Also,
DBAL for table creation still does not support external keys last time I checked. I don't know if I'll have time to work on that because I've been really busy

[Alien_Time]

News to me if 3.1 is using InnoDB, I'm running it and all tables are MyISAM. From what I can see 3.1 is still using MyISAM, not InnoDB as default for database storage engine.

hmm.... thats interesting. I had a test set-up for phpbb 3.1 RC3 in my WAMP server that was installed a few months ago and when I had a look at the phpbb sql tables it was listing Type as InnoDB. But the older version was showing MyISAM. That was weird..

I'm trying to introduce InnoDB to phpBB but it won't happen for 3.1. There are other higher priority tasks that require being done.
Also,
DBAL for table creation still does not support external keys last time I checked. I don't know if I'll have time to work on that because I've been really busy

@brunoais a lot has changed in InnoDB in the latest version of mysql ever since it because the default engine. Also Mariadb has XtraDB which is a performance enhanced improved version of Innodb. So yeah with a lot of changes and active development it might be possible now I would guess. I've seen some cms like Joomla already changing to Innodb. Dont get me wrong, MyIsam is also good in some cases but Innodb does have a few advantage than MyIsam. I am right now configuring my server and I might now need to give more key buffer size for MyIsam too. Damn... I was sure 3.1 had InnoDb. My bad..

[Alien_Time]

On the same note, can I ask what could be an ideal setting for MyIsam when I have a server with 8GB Ram? Look I understand that it is hard to give suggestion without testing out the sql demand, current usage statistics, etc.. but this is for a new server and I want to be ready with an ideal setting to begin with so it doesnt start by crashing when it goes live. I am thinking of maybe having something like this for MyIsam (for phpbb) and InnoDB (for Joomla). Do you think these MyIsam limits are good to start with for phpbb or would you distribute it differently?

Code: Select all

#
# * MyISAM
#

myisam_recover          = BACKUP
key_buffer_size         = 512M
open-files-limit        = 65535		
table_open_cache        = 400
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 6M		
read_rnd_buffer_size    = 3M		

#
# * InnoDB
#
innodb_log_file_size	= 1024M 	
innodb_buffer_pool_size = 5G		
innodb_log_buffer_size	= 30M		
innodb_file_per_table   = 1
innodb_open_files	= 1000		
innodb_io_capacity	= 1000		
innodb_write_io_threads = 8			
innodb_read_io_threads  = 8  		
innodb_flush_method     = O_DIRECT

PS: I understand this question is really server related and is maybe beyond the scope of the forum here. But if anyone who has poked around these sql settings can share their quick thought, I would be hugely appreciative!

[mrgtb]

News to me if 3.1 is using InnoDB, I'm running it and all tables are MyISAM. From what I can see 3.1 is still using MyISAM, not InnoDB as default for database storage engine.

hmm.... thats interesting. I had a test set-up for phpbb 3.1 RC3 in my WAMP server that was installed a few months ago and when I had a look at the phpbb sql tables it was listing Type as InnoDB. But the older version was showing MyISAM. That was weird.

If using latest version of WAMPSERVER, if I'm correct (have used it) think its configured to InnoDB as DB storage engine first. Unless phpBB 3.1 can use both types of storage engine and my online web server is setup to use MyISAM first. So could be that phpBB 3.1 does support both types then, but a dev would have to clear that one up for sure, as all my tables are MyISAM running 3.1?

Out of curiosity why do you want to use InnoDB, I know the difference about tables not locking up with InnoDB, but from what I've read in past its geared more towards benefiting very large active forums. Do you have a very large forum, because if not MyISAM will run faster of the two for your board. And shared hosting doesn't tend to support InnoDB very well, how its setup and configured. I had performance issues running XenForo on my shared host using InnoDB tables, which XenForo only uses InnoDB.

[brunoais]

As everything is now, InnoDB is the best option for the general case and MyISAM is now only useful if you require single table full table searches or a FullText index. Even then, it might be more useful to have that one as a separate table dedicated to the FullText search itself.

There are many advantages to the InnoDB, just check MySQL's website for that (too lazy to search). Both are mutually compatible for SQL queries except FULLTEXT queries. It's part of the objective to improve phpBB's performance by using InnoDB and to use external keys between tables that are usually joined. I think query performance will drastically increase in large forums, specially for executions that use such queries.

[Alien_Time]

Out of curiosity why do you want to use InnoDB, I know the difference about tables not locking up with InnoDB, but from what I've read in past its geared more towards benefiting very large active forums. Do you have a very large forum, because if not MyISAM will run faster of the two for your board. And shared hosting doesn't tend to support InnoDB very well, how its setup and configured. I had performance issues running XenForo on my shared host using InnoDB tables, which XenForo only uses InnoDB.

My preference for InnoDB is purely from the server point of view. If you had a look at my server config I had posted above, you will see that it is tuned mainly for Innodb engine so it performs best. It has a lot of features you can control.

What I like about InnoDB is the buffer pool feature it has. It can hold both data and indexes which is pretty cool. You can set like a large buffer pool setting that can hold both of these data and indexes and ofcourse reading from the ram will be faster than the disk. It puts like each table in a file and if there was any change it is written inside a log file that then writes to the disk during the next commit (or whichever way we want to set it). This log file is really useful too cuz lets say mysql crashes halfway during a transaction and there is a data to be changed but its not written on the disk yet, Luckily due to this "redo" log file, when it recovers it can complete writing to the disk. Once its written to the disk, the pages are flushed. So yeah InnoDB is really awesome cuz it has a nice working and you can nicely tune it for best performance from the server mysql conf.

I think maybe the reason Innodb is the shown as the default storage engine in my installation is cuz if you are having the latest MySql (since 5.5) or MariaDB, InnoDB is the default engine. If we do no explicitly specify the table DDL, it probably defaults to InnoDB. This is what I think...

[Alien_Time]

If using latest version of WAMPSERVER, if I'm correct (have used it) think its configured to InnoDB as DB storage engine first. Unless phpBB 3.1 can use both types of storage engine and my online web server is setup to use MyISAM first. So could be that phpBB 3.1 does support both types then, but a dev would have to clear that one up for sure, as all my tables are MyISAM running 3.1?

@mrgtb, If you ask about the programming side of differences this is what I think. MyIsam has full-text index and spatial indexes and Innodb doest. Due to this, we may need to refactor the application that only developers can tell. But I dont think that should be an issue cuz if we need serious full-text search functionality, then we should be using Spinx or something like that. Having said that, phpbb might still be able to use InnoDB as a drop-in replacement. In order to make the full use of InnoDb, phpbb can start taking advantage of the Transaction feature InnoDb has so it can complete or rollback and always stay in a consistent state. It also supports Foreign Key.

I wonder if there is a way to check / test if phpbb 3.1 is already ready to use Innodb as the storage engine...

[brunoais]

AFAIK, phpBB does not use FullText search nor spartial indexes so it is ready for InnoDB. It just does not use InnoDB to its fullest such as foreign keys which would be its greatest gain in performance.