Performance change with my.cnf configuration

[manohar.kompelly]

We are using MySql 5.6.10 on Cent OS 6.2 with 32 Cores CPU. Recently we have upgraded RAM from 64G to 189G and updated my.cnf according to the available RAM.
After upgrading the MySql configurations we have observed that Innodb Cache hit ratio is 99% (earlier it is around 60%), Query cache hit ratio is 70% (earlier it is around 60%) and prune ratio is 10% (earlier it is around 50%) but we didn’t observe any significant performance improvement instead we are getting insert/update queries in the slow log. DB size is 180G and rate of size increase 10-15% for every month, most of the tables of type InnoDB engine. Below are configuration we have now
key_buffer_size - 2G
query_cache_size - 4G
tmp_table_size - 3G
innodb_buffer_pool_size - 133G
innodb_additional_mem_pool_size - 2G
innodb_log_buffer_size - 1G
max_connections - 500
sort_buffer_size - 8M
read_buffer_size - 32M
read_rnd_buffer_size - 8M
join_buffer_size - 1M
thread_stack - 1M
binlog_cache_size - 0.25
table_open_cache - 1024

Could you please suggest the optimal configurations

[scott.nemes]

What did you actually change in your my.cnf after adding more memory?

If your insert performance decreased after adding the memory, and all you changed is the innodb_buffer_pool_size, then you might be swapping now if the new size is a larger percentage of total memory than it was before (is about 70% now).

[manohar.kompelly]

We have increased above variables by three times. Earlier innodb_buffer_pool_size is 32G and now it is 133G, buffer pool in 70% of memory.
Could you please elaborate more on swapping.

[scott.nemes]

If you are running some form of Linux, you can check swap with the “free -g” command. You’ll see the line at the bottom for swap, with a value for “total” and a value for “used”.

What I would do is use your original my.cnf, and only change the innodb_buffer_pool_size value and nothing else, and see how it performs. Changing a bunch of config options at once makes it very difficult to see what the problem is, as you do not know if it’s a config item, or hardware, or what. So start with a simple change of the buffer pool and go from there so you can narrow down the problem.

[manohar.kompelly]

below is the swap space details.
free -g
total used free shared buffers cached
Mem: 189 182 6 0 0 51
-/+ buffers/cache: 130 58
Swap: 15 3 12

There is no much swap space being used.

I agree with you to change one config at a time to see the difference. As we are doing the change in production directly we can’t wait for one week to see the config change results. Please suggest global variables vs per connection variables values.

[scott.nemes]

You should get most of your benefit from having the larger innodb_buffer_pool_size, so simply increasing that alone to test it is the way I would go. It is simply too difficult to predict the results when changing that many settings at once, especially when I’m guessing you do not actually need to change a lot of them. Often performance ends up being worse when you start changing some of the standard config items that are often best at their defaults for many workloads.

If you want a standard config to start with, I would use the Percona Configuration Wizard (https://tools.percona.com/) to generate a sample config using your specific specs and go from there. Use that to compare with what you currently have, and then it is up to you as to what you change at once, but I would still stick with just the innodb_buffer_pool_size to start with.