Re: Performance improvement with my.cnf configs

[Rick James]

Provide these, and I will give a thorough critique:
SHOW VARIABLES;
SHOW GLOBAL VARIABLES;

Pruning the Query cache is a linear task; by making the QC 10x larger, you have increased the pruning task by about 10x.

> don't allocate more memory (allocate maximum 2MB) per connection variables(sort_buffer_size etc..) whereas we are thinking allocate more memory (32MB) to connection will be the good option, could you please suggest on this.

Since you have 189GB of RAM, 32MB/connection is possibly ok. The 2MB recommendation dates back to days of perhaps 4GB RAM and perhaps 100 connections. Those blogs probably fail to point out that some settings are not just per-connection, but per-temp table -- such as in a complex SELECT with UNIONs or subqueries, etc. Still, 500*32M is only 9% of RAM.

> binlog_cache_size - 0.25

Is that a typo?
(My critique will see the real value and make a judgement call on whether it is ok.)

> Regarding slow queries...

I'd be happy to provide another pair of eyes.