I used your my.cnf wizard to optimize mysql on a domU xen server. The server is running centos 6.5. After copying and pasting the result into my.cnf, I couldn’t restart mysql and was obliged to return to the default my.cnf.
cat /var/lib/mysql/mysql-error.log
140810 21:21:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/
140810 21:21:00 InnoDB: Initializing buffer pool, size = 128.0M
140810 21:21:00 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
140810 21:21:00 [ERROR] Plugin ‘InnoDB’ init function returned error.
140810 21:21:00 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
140810 21:21:00 [ERROR] Unknown/unsupported table type: InnoDB
140810 21:21:00 [ERROR] Aborting
The issue is that the new config has a larger value for your ib_logfile sizes (innodb_log_file_size). Move your current ib_logfiles to another location for safe keeping (you can delete them, but if you want to be extra careful you can just move them instead). MySQL will then recreate new ib_logfiles of the new size when you start up MySQL again.
Thanks. On a separate note, I have several databases running on the server. Some are using innodb and one is using myisam. As the wizard’s config optimizes innodb, I’m wondering if it would have a negative effect on the performance of the myisam database.
It’s definitely possible that it could cause performance to degrade for the MyISAM portion. The tool gives you a generic “best practice” my.cnf to start with that may work for most people, but what you actually need could be very different depending on your setup (especially if you heavily use both InnoDB and MyISAM).
The most critical setting for MyISAM is generally key_buffer_size, so if you do notice an issue you could try upping that setting and lowering the innodb_buffer_pool_size to account for it (otherwise you’ll end up with memory problems).
This is why benchmarking is important, so you know exactly what the performance is before and after, and can make adjustments as needed. =)