Looking for any pointers for optimizing the my.cnf for InnoDB tables on a VPS. What I have is a Knownhost plan, their VPS Triple Xtx with another 256 MB of RAM added to it (704 MB total - burst to 2048 M). The site serves between 600K and 800K pages a month at present. Every page uses PHP, though nothing too taxing on the server (just formatting for display per a template). CPU load is usually low .10 and memory about 65%. The system is running eAccelerator and I just upgraded it to PHP 5.2 and MySQL 5.0.
The VPS runs phpAdsNew to serve the ads. At present 1 per page, soon to be 2. That means 1.2 M to 1.6 M ads per month.
The site has an SMF message board, 100K + posts, and usually about 10 or 15 people using it at the same time.
Now, to improve performance I changed the phpAdsNew tables and some of the SMF tables to InnoDB. I am not seeing problems, but would like to know a decent place to start with settings in my.cnf to tune the InnoDB tables. I am not familiar with tuning for InnoDB and am having a hard time finding suggestions (along with the why) online.
I see the defaults that can be uncommented and used are the following:
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
Champion of Cinematic Disasters
The Bad Movie Website www.badmovies.org