libraries/advisory_rules_mysql_before80003.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 return [
   6     // Query cache
   7     [
   8         'id' => 'Query cache disabled',
   9         'name' => __('Query cache disabled'),
  10         'formula' => 'query_cache_size',
  11         'test' => 'value == 0 || query_cache_type == \'OFF\' || query_cache_type == \'0\'',
  12         'issue' => __('The query cache is not enabled.'),
  13         'recommendation' => __(
  14             'The query cache is known to greatly improve performance if configured correctly. Enable it by'
  15             . ' setting {query_cache_size} to a 2 digit MiB value and setting {query_cache_type} to \'ON\'.'
  16             . ' <b>Note:</b> If you are using memcached, ignore this recommendation.'
  17         ),
  18         'justification' => __('query_cache_size is set to 0 or query_cache_type is set to \'OFF\''),
  19     ],
  20     [
  21         'id' => 'Query cache efficiency (%)',
  22         /* xgettext:no-php-format */
  23         'name' => __('Query cache efficiency (%)'),
  24         'precondition' => 'Com_select + Qcache_hits > 0 && !fired(\'Query cache disabled\')',
  25         'formula' => 'Qcache_hits / (Com_select + Qcache_hits) * 100',
  26         'test' => 'value  < 20',
  27         'issue' => __('Query cache not running efficiently, it has a low hit rate.'),
  28         'recommendation' => __('Consider increasing {query_cache_limit}.'),
  29         'justification' => __('The current query cache hit rate of %s%% is below 20%%'),
  30         'justification_formula' => 'round(value,1)',
  31     ],
  32     [
  33         'id' => 'Query Cache usage',
  34         'name' => __('Query Cache usage'),
  35         'precondition' => '!fired(\'Query cache disabled\')',
  36         'formula' => '100 - Qcache_free_memory / query_cache_size * 100',
  37         'test' => 'value < 80',
  38         /* xgettext:no-php-format */
  39         'issue' => __('Less than 80% of the query cache is being utilized.'),
  40         'recommendation' => __(
  41             'This might be caused by {query_cache_limit} being too low.'
  42             . ' Flushing the query cache might help as well.'
  43         ),
  44         'justification' => __(
  45             'The current ratio of free query cache memory to total query'
  46             . ' cache size is %s%%. It should be above 80%%'
  47         ),
  48         'justification_formula' => 'round(value,1)',
  49     ],
  50     [
  51         'id' => 'Query cache fragmentation',
  52         'name' => __('Query cache fragmentation'),
  53         'precondition' => '!fired(\'Query cache disabled\')',
  54         'formula' => 'Qcache_free_blocks / (Qcache_total_blocks / 2) * 100',
  55         'test' => 'value > 20',
  56         'issue' => __('The query cache is considerably fragmented.'),
  57         'recommendation' => __(
  58             'Severe fragmentation is likely to (further) increase Qcache_lowmem_prunes. This might be'
  59             . ' caused by many Query cache low memory prunes due to {query_cache_size} being too small. For a'
  60             . ' immediate but short lived fix you can flush the query cache (might lock the query cache for a'
  61             . ' long time). Carefully adjusting {query_cache_min_res_unit} to a lower value might help too,'
  62             . ' e.g. you can set it to the average size of your queries in the cache using this formula:'
  63             . ' (query_cache_size - qcache_free_memory) / qcache_queries_in_cache'
  64         ),
  65         'justification' => __(
  66             'The cache is currently fragmented by %s%% , with 100%% fragmentation meaning that the query'
  67             . ' cache is an alternating pattern of free and used blocks. This value should be below 20%%.'
  68         ),
  69         'justification_formula' => 'round(value,1)',
  70     ],
  71     [
  72         'id' => 'Query cache low memory prunes',
  73         'name' => __('Query cache low memory prunes'),
  74         'precondition' => 'Qcache_inserts > 0 && !fired(\'Query cache disabled\')',
  75         'formula' => 'Qcache_lowmem_prunes / Qcache_inserts * 100',
  76         'test' => 'value > 0.1',
  77         'issue' => __('Cached queries are removed due to low query cache memory from the query cache.'),
  78         'recommendation' => __(
  79             'You might want to increase {query_cache_size}, however keep in mind that the overhead of'
  80             . ' maintaining the cache is likely to increase with its size, so do this in small increments'
  81             . ' and monitor the results.'
  82         ),
  83         'justification' => __(
  84             'The ratio of removed queries to inserted queries is %s%%. The lower this value is,'
  85             . ' the better (This rules firing limit: 0.1%%)'
  86         ),
  87         'justification_formula' => 'round(value,1)',
  88     ],
  89     [
  90         'id' => 'Query cache max size',
  91         'name' => __('Query cache max size'),
  92         'precondition' => '!fired(\'Query cache disabled\')',
  93         'formula' => 'query_cache_size',
  94         'test' => 'value > 1024 * 1024 * 128',
  95         'issue' => __(
  96             'The query cache size is above 128 MiB. Big query caches may cause significant'
  97             . ' overhead that is required to maintain the cache.'
  98         ),
  99         'recommendation' => __(
 100             'Depending on your environment, it might be performance increasing to reduce this value.'
 101         ),
 102         'justification' => __('Current query cache size: %s'),
 103         'justification_formula' => 'ADVISOR_formatByteDown(value, 2, 2)',
 104     ],
 105     [
 106         'id' => 'Query cache min result size',
 107         'name' => __('Query cache min result size'),
 108         'precondition' => '!fired(\'Query cache disabled\')',
 109         'formula' => 'query_cache_limit',
 110         'test' => 'value == 1024*1024',
 111         'issue' => __('The max size of the result set in the query cache is the default of 1 MiB.'),
 112         'recommendation' => __(
 113             'Changing {query_cache_limit} (usually by increasing) may increase efficiency. This variable'
 114             . ' determines the maximum size a query result may have to be inserted into the query cache.'
 115             . ' If there are many query results above 1 MiB that are well cacheable (many reads, little writes)'
 116             . ' then increasing {query_cache_limit} will increase efficiency. Whereas in the case of many query'
 117             . ' results being above 1 MiB that are not very well cacheable (often invalidated due to table'
 118             . ' updates) increasing {query_cache_limit} might reduce efficiency.'
 119         ),
 120         'justification' => __('query_cache_limit is set to 1 MiB'),
 121     ],
 122 ];