XF 1.2 UGH! Converted Forum - Now I find out about INNODB

[ProCom]

Well, isn't this just peachy!

I spend months evaluating xF, testing my conversion, and today I finally pull the trigger on my biggest site conversion. I'm 6 hours into the conversion (to give you an idea it took 2 hours to import posts / topics) and then while my search cache rebuild is underway I decide to browse the forum here.

Well, I stumble on this MySQL: MyISAM or INNODB thread and check my tables...

EVERY SINGLE ONE OF THEM IS MyISAM!!!

So, what do I do? I mean, my forum seems to be running fine, but it also hasn't been hammered by a bunch of concurrent users yet.

The last time I tried to convert a platform from MyISAM to INNODB (another project different platform) it went terribly terribly wrong and I had to do a full server restore and roll back to MyISAM. The thought of going through that again is terrifying at best.

So my questions for the masters:

1. Why the heck on a brand new install weren't the correct table structures setup?
   
2. Do I "have to" convert the tables to INNODB?
3. What happens if I don't convert to INNODB?

I vaguely remember that some of the problems I ran into before was related to the fact that I'm still on a 32 bit CentOS and innodb didn't play well with my settings, memory, or whatever

Some of my tables aren't super huge, but also not tiny:


I'm kinda freaking out about this, so thanks in advance for your help and suggestions!

 

[MattW]

To answer 1, I suspect the default engine set in your MySQL install is MyISAM, which is why on a fresh install, the tables are MyISAM.

You can also change the tables to InnoDB with phpmyadmin, you just have a to do a table at a time. I've changed a phpbb3 forum from MyISAM to InnoDB, and back again with no ill effects.

 

[Chris D]

It's worth noting that if you do decide to convert the tables there's a couple which are deliberately MyIsam I think.

 

[ProCom]

Thanks guys!

Yup, I've seen the masters, like Brogan post about the tables that should remain myISAM, but I'm just freaking out about the process of converting them and if it will make things better.

As it is right now, my loads on my relatively beefy VPS are going up and there are barely any peeps on my forum. I'm guessing it might have to do with the table types, but at this point that's totally just a guess.

 

[MattW]

What do you have in your my.cnf file? A few people report that load increases after conversions when MySQL isn't tuned for the new XF setup

 

[ProCom]

Here's my.cnf:

[mysqld]
#skip-bdb
skip-networking

long_query_time=5 # in seconds
log_slow_queries = /var/lib/mysql/slow_query.log
#log_queries_not_using_indexes
#safe-show-database
table_cache=512
#open_files_limit=12288
join_buffer_size=64M
key_buffer_size=64M
read_buffer_size=32M
read_rnd_buffer_size=32M
sort_buffer_size=32M
thread_cache_size=32
tmp_table_size=512M
max_heap_table_size=512M
query_cache_limit=2M
query_cache_size=32M
query_cache_type=1
low_priority_updates=1
max_allowed_packet=64M
max_connections=1000

wait_timeout=35
interactive_timeout=100

skip-innodb

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_log_arch_dir = /var/lib/mysql
#innodb_additional_mem_pool_size = 128M
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 4M
#innodb_flush_log_at_trx_commit = 0
#innodb_lock_wait_timeout = 50
#innodb_file_per_table = 1
# innodb_buffer_pool_size = 2G

[safe_mysqld]
log-error = /var/log/mysqld.log

So, it looks like innodb is turned off? This is starting to get over my payscale / skill level for sure.

 

[MattW]

Yes, InnoDB is disabled, so as it stands, you'd not be able to convert the tables.

You need to remove the skip-InnoDB value from the config file, and remove the hash before the other InnoDB settings, as they are currently commented out.

Then restart MySQL.

You will then be able to use InnoDB.

 

[MattW]

Also, if you want any help doing the above, drop me a PC, and I'll be happy to help out.

 

[CTXMedia]

Firstly, relax - it's something that can be sorted out with a little time and patience.

Secondly I would expect the server load to be a little higher at the start because visitors are downloading everything afresh (caching should help to lower the load as more people login and things move forward), usergroup promotions are checked at first login (and lots of people will rush in at the start), people will "play" with the new software (and you'll be doing lots of admin stuff as things crop up), and you're unlikely to be optimised for XF on your server at the start - which, if you ask here, will be something you can get help with once you're settled in.

Cheers,
Shaun

 

[CTXMedia]

Oh, and good luck.

 

[ProCom]

Thanks for all the help Matt!

Hopefully this discussion will help some other poor saps er... newbie users like me that got into the same bind.

Is there any way to quantify how much better INNODB is for xF than myisam? I mean, is it like I put diesel gasoline in my unleaded car and now it's all gummed up, or are we talking the difference between 87 and 88 octane gas (i.e., very slight difference)?

Also, are the sizes of those tables such that I would expect to run into problems converting them to Innodb in phpmyadmin?

 

[MattW]

No, should be fine converting those to InnoDB via phpmyadmin once you have enabled it in my.cnf

Probably best to check the max execution time allowed for PHP in your PHP.ini file to make sure the script can run for as long as needed.

 

[ProCom]

Thanks Shaun! <breathing heavily into paper lunch bag>

 

[ProCom]

My friend that runs a bunch of stuff on xF just shot over the following... how's this look?

<?php
$db = mysql_connect('localhost','username','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('database_name');
$result=mysql_query('show tables');
$tables_to_ignore = array('xf_import_log','xf_search_index','xf_session','xf_session_admin','xf_attachment_view','xf_session_activity','xf_thread_view');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
if(!in_array($value, $tables_to_ignore)){
mysql_query("ALTER TABLE $value ENGINE=innodb");
}
}
}
echo "Your database tables have been changed to innodb!";
?>

It made me wonder if all the tables from various addons should all also be innodb too? I'm assuming they should be.

 

[MattW]

Looks good!

I'd dump a copy of the DB before running it just incase.

All the add ons I've got installed have InnoDB tables as they pick up the default table type when they are installed.

 

[HWS]

Before you convert to InnoDB it is worth to get familiar with the very different administration tasks of that table engine. If you are used to MyISAM you have to change all infrastructure. InnoDB tables cannot be handled as easily as MyISAM tables.

Maybe you have to change your backup solution also.

And last but not least check if there is a reason why InnoDB is disabled at your machine.

 

[ProCom]

Ugh, I have MORE complexity coming my way?

Also, I finally got my backup processes / scripts all configured and now I may have to change those too?!?! <sad face>

I believe InnoDB was disabled since we just never used it on our VPS and never expected we would.

On a side note, I had a bit of a freak-out! I removed the skip-innodb line and removed the commented out "#" and restarted mysql and got this nasty error:

root@server [/var/lib/mysql]# /etc/init.d/mysql restart
ERROR! MySQL manager or server PID file could not be found!
Starting MySQL...... ERROR! Manager of pid-file quit without updating file.

... and then all my sites were dead.

After some testing I discovered it was one of the following (which I recommented and restarted):
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_log_arch_dir = /var/lib/mysql

So, one of those was the thing that gave me that error and didn't let me restart mysql

 

[MattW]

You should have an error log in your /var/lib/mysql folder that should give an indication why it's not starting. Looking at those, I suspect it could be either or both of these

#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M

 

[HWS]

You disabled very important lines in your config.

I highly recommend that you get familiar with InnoDB first (with reading the MySQL documentation) and stay with MyISAM until you are comfortable with it.

 

[ProCom]

Trial and error... it was this one:
#innodb_log_arch_dir = /var/lib/mysql

How much do I have to worry about fine tuning all these settings before converting over to innodB? I'm on VPS, 32 bit CentOS version 5.10 with 4 GB RAM.