1

I want to change my AWS RDS parameter query_cache_type value to 2 , I have done it in parameter group dashboard as in the given image. enter image description here

I have also restarted my mySQL server with the changed parameter, but when I am checking this by query I am getting a different response:-

enter image description here

How can I change this static parameter ?

  • please refer to this site if you did not create parameter group: survivalguides.wordpress.com/2012/07/11/… – Ahmad Abuhasna Feb 23 '16 at 12:30
  • @AhmadAbuhasna The parameter group has been created,i just want to change the query_cache_type value, can you suggest me how can this be done? – Jitin Maherchandani Feb 23 '16 at 12:38
  • Have you also configured the cache sizes? Can you post a screen grab showing all the cache control values - such as size, min_res, etc. – Dave Rix Feb 23 '16 at 14:04
1

Have you attached the parameter group to the RDS instance properly? I have just checked on my installation, and things are working as expected. (Note, I do have my cache size set to zero, as for our workload it is not beneficial to enable it)

Parameter Group Settings Query Cache Settings

MySQL Query Cache

mysql> show global variables like 'query_cache%';
+------------------------------+--------+
| Variable_name                | Value  |
+------------------------------+--------+
| query_cache_limit            | 0      |
| query_cache_min_res_unit     | 512    |
| query_cache_size             | 0      |
| query_cache_type             | DEMAND |
| query_cache_wlock_invalidate | OFF    |
+------------------------------+--------+
5 rows in set (0.01 sec)

Parameter Group Assigned to RDS Instance

enter image description here

Regards,
Dave

  • Don't set query_cache_size to more than 50M. – Rick James Feb 23 '16 at 17:59
  • The type should be OFF when the size is 0. With DEMAND the QC mutex is still used and slows you down - dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html : If query_cache_size is 0, you should also set query_cache_type variable to 0. In this case, the server does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution. – jkavalik Feb 23 '16 at 18:24
  • @Dave Rix I have successfully implemented DEMAND caching ,and also i am getting cache hits,how do i calculate hit percentage?dba.stackexchange.com/questions/130304/… – Jitin Maherchandani Feb 24 '16 at 12:32
  • @jkavalik - totally agree, and in our real workload we do have the query_cache_type set to zero, I just set it to 2 here as an example :) – Dave Rix Feb 24 '16 at 13:21
  • @JitinMaherchandani - I wouldn't worry too much about the cache hit ratio - see this blog post from Percona percona.com/blog/2010/02/28/… – Dave Rix Feb 24 '16 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.