Questions[innodb](server)

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it:

╰$ sudo service mysql restart
stop: Unknown instance:
start: Job failed to start

And this shows in "/var/log/mysql/error.log":

120415 23:01:09 [Note] Plugin 'InnoDB' is disabled.
120415 23:01:09 [Note] Plugin 'FEDERATED' is disabled.
120415 23:01:09 [ERROR] Unknown/unsupported storage engine: InnoDB
120415 23:01:09 [ERROR] Aborting

120415 23:01:09 [Note] /usr/sbin/mysqld: Shutdown complete

I've checked permissions on all the mysql directories to make sure it had ownership and I also renamed the previou ib_logs so that it could remake them. I'm just getting no where with this issue right now, after looking at google results for 2 hours.

I'm a pretty big noob when it comes to setting up MySQL for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of MySQL, but I do know that the most important thing to do that provides some of the best results is setting up caches/buffers correctly.

I've tried to keep things simple by using only InnoDB as a storage engine. And I do have a dedicated server for MySQL. It has 8gb of RAM, how should I be allocating that to maximize performance? I'd like to be able to fit my entire database into memory for the best performance. The database is about 5gb. Is this possible?

How much memory should I allocate to the query cache? How much to the InnoDB buffer pool? How much for the rest of the computer (i.e. non MySQL related processes)? Etc.

Since I'm not using MyISAM I don't really need to put a lot of memory in the key cache correct?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table.

I am wondering why innodb_file_per_table is not enabled by default. Are there downsides to using it?

I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... So those alter table commands take several hours. In a couple months they'll take days I'm guessing.

Since I'm using amazon RDS, I can't have slave servers to play with and then promote to master. So my question is if there's a way to do this with minimal downtime? I don't mind an operation taking hours or even days if users can still use the db of course... Can they at least read while columns are being added? What happens if my app tries to write? Insert or update? If it fails immediately that's actually not so bad, if it just hangs and causes problems for the db server that's a big problem..

This must be a fairly common scaling issue, everyone needs to add columns.. What's typically done to a production db? Slave -> master migration?

Update - I forgot to mention I'm using the innodb storage engine

I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine.

innodb_buffer_pool_size = 2560M
innodb_log_file_size    = 256M
innodb_log_buffer_size  = 8M
innodb_flush_log_at_trx_commit  = 2
innodb_thread_concurrency   = 16
innodb_flush_method = O_DIRECT

But it raise "ERROR 2013 (HY000) at line 2: Lost connection to MySQL server during query" error restarting mysqld. And mysql error log shows the following

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
100118 20:52:52 [ERROR] Plugin 'InnoDB' init function returned error.
100118 20:52:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100118 20:52:52 [ERROR] Unknown/unsupported table type: InnoDB
100118 20:52:52 [ERROR] Aborting

So I commented out this line

# innodb_log_file_size  = 256M

And it restarted mysql successfully.

I wonder what's the "5242880 bytes of log file" showed in mysql error? It's the first database on InnoDB engine on this server so when and where is that log file created? In this case, how can I enable innodb_log_file_size directive in my.cnf?

EDIT

I tried to delete /var/lib/mysql/ib_logfile0 and restart mysqld but it still failed. It now shows the following in error log.

100118 21:27:11  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Error: log file ./ib_logfile1 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!

Resolution

It works now after deleted both ib_logfile0 and ib_logfile1 in /var/lib/mysql