MySQLd InnoDB tuning

Go To


In trying to configure my mysqld appropriately for my all-InnoDB application environment (mysql DB is still MyISAM, of course) I keep seeing varients of this formula to determine total memory usage:

key_buffer_size + query_cache_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size
+ max_connections * (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack)

This sum is greater than my available physical memory, and may result in paging. I certainly am seeing a performance hit compared to my alternate server (which has more ram).

I am trying, and failing, to find which of these variables are not useful to InnoDB (are only useful to MyISAM) and can be greatly reduced to shrink my total memory allocation.

Question, which config variables can I tune down to reduce MySQL's total memory allocation?

2012-04-04 00:50
by Umbrella


After much checking, and much more fuss, I discovered that it doesn't matter. Apparently MySQLd sparsely allocates buffers, meaning that if I am not using MyISAM, the buffers that only benefit MyISAM won't grow to take significant portions of physical memory and the calculated "total memory allocation" will not actually be allocated.

2012-05-01 00:54
by Umbrella


You might try Percona's configuration wizard. It should be a good start. The Percona folks know their MySQL.

Warning: It'll ask you to sign up to get the final results.

You'll probably need to do some benchmarking to tune for your specific load pattern beyond that.

2012-04-04 01:51
by Joshua Martell
I went through the wizard. Not only did it not provide insight to my question, it let me go all the way through and THEN ask me to login/signup before showing results - Umbrella 2012-04-05 01:17
@Umbrella Same thing happened to me, really annoyin - robd 2012-04-30 23:07