Question :
I would like to know if the increasing the following parameters will improve the mysql performance.
| query_cache_size | 16777216 |
| query_cache_limit | 1048576 |
| table_open_cache | 2000 |
| table_open_cache_instances | 16 |
| thread_cache_size | 8 |
| host_cache_size | 279 |
Right now, the innodb_buffer_pool_size
is 2G.
UPDATE:
The output of all variables is available at pastebin. I would like to decrease IO tasks as I have 32GB of memory and it is find to set 16 GB to mysql.
Answer :
Disk activity is either lots of stuff being read in or lots of stuff being written out because there isn’t enough room in memory to hold it.
Increasing memory might reduce your disk activity, but it might not improve your overall performance.
Left to its own devices, your DBMS will not do lots of disk I/O for itself. It only does so in response to queries that are sent to it.
You need to look at the workload (queries) that the server is running. The chances are there are some “chunky” ones in there that are trawling through a lot of data. Find them, tune them, get rid of them. That will improve your server’s overall performance.