libraries/advisory_rules_generic.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 return [
   6     // Queries
   7     [
   8         'id' => 'Uptime below one day',
   9         'name' => __('Uptime below one day'),
  10         'formula' => 'Uptime',
  11         'test' => 'value < 86400',
  12         'issue' => __('Uptime is less than 1 day, performance tuning may not be accurate.'),
  13         'recommendation' => __(
  14             'To have more accurate averages it is recommended to let the server run for'
  15             . ' longer than a day before running this analyzer'
  16         ),
  17         'justification' => __('The uptime is only %s'),
  18         'justification_formula' => 'ADVISOR_timespanFormat(Uptime)',
  19     ],
  20     [
  21         'id' => 'Questions below 1,000',
  22         'name' => __('Questions below 1,000'),
  23         'formula' => 'Questions',
  24         'test' => 'value < 1000',
  25         'issue' => __(
  26             'Fewer than 1,000 questions have been run against this server.'
  27             . ' The recommendations may not be accurate.'
  28         ),
  29         'recommendation' => __(
  30             'Let the server run for a longer time until it has executed a greater amount of queries.'
  31         ),
  32         'justification' => __('Current amount of Questions: %s'),
  33         'justification_formula' => 'Questions',
  34     ],
  35     [
  36         'id' => 'Percentage of slow queries',
  37         'name' => __('Percentage of slow queries'),
  38         'precondition' => 'Questions > 0',
  39         'formula' => 'Slow_queries / Questions * 100',
  40         'test' => 'value >= 5',
  41         'issue' => __('There is a lot of slow queries compared to the overall amount of Queries.'),
  42         'recommendation' => __(
  43             'You might want to increase {long_query_time} or optimize the queries listed in the slow query log'
  44         ),
  45         'justification' => __('The slow query rate should be below 5%%, your value is %s%%.'),
  46         'justification_formula' => 'round(value,2)',
  47     ],
  48     [
  49         'id' => 'Slow query rate',
  50         'name' => __('Slow query rate'),
  51         'precondition' => 'Questions > 0',
  52         'formula' => '(Slow_queries / Questions * 100) / Uptime',
  53         'test' => 'value * 60 * 60 > 1',
  54         'issue' => __('There is a high percentage of slow queries compared to the server uptime.'),
  55         'recommendation' => __(
  56             'You might want to increase {long_query_time} or optimize the queries listed in the slow query log'
  57         ),
  58         'justification' => __('You have a slow query rate of %s per hour, you should have less than 1%% per hour.'),
  59         'justification_formula' => 'ADVISOR_bytime(value,2)',
  60     ],
  61     [
  62         'id' => 'Long query time',
  63         'name' => __('Long query time'),
  64         'formula' => 'long_query_time',
  65         'test' => 'value >= 10',
  66         'issue' => __(
  67             '{long_query_time} is set to 10 seconds or more,'
  68             . ' thus only slow queries that take above 10 seconds are logged.'
  69         ),
  70         'recommendation' => __(
  71             'It is suggested to set {long_query_time} to a lower value, depending on your environment.'
  72             . ' Usually a value of 1-5 seconds is suggested.'
  73         ),
  74         'justification' => __('long_query_time is currently set to %ds.'),
  75         'justification_formula' => 'value',
  76     ],
  77     [
  78         'id' => 'Slow query logging',
  79         'name' => __('Slow query logging'),
  80         'precondition' => 'PMA_MYSQL_INT_VERSION < 50600',
  81         'formula' => 'log_slow_queries',
  82         'test' => 'value == \'OFF\'',
  83         'issue' => __('The slow query log is disabled.'),
  84         'recommendation' => __(
  85             'Enable slow query logging by setting {log_slow_queries} to \'ON\'.'
  86             . ' This will help troubleshooting badly performing queries.'
  87         ),
  88         'justification' => __('log_slow_queries is set to \'OFF\''),
  89     ],
  90     [
  91         'id' => 'Slow query logging',
  92         'name' => __('Slow query logging'),
  93         'precondition' => 'PMA_MYSQL_INT_VERSION >= 50600',
  94         'formula' => 'slow_query_log',
  95         'test' => 'value == \'OFF\'',
  96         'issue' => __('The slow query log is disabled.'),
  97         'recommendation' => __(
  98             'Enable slow query logging by setting {slow_query_log} to \'ON\'.'
  99             . ' This will help troubleshooting badly performing queries.'
 100         ),
 101         'justification' => __('slow_query_log is set to \'OFF\''),
 102     ],
 103     // Versions
 104     [
 105         'id' => 'Release Series',
 106         'name' => __('Release Series'),
 107         'formula' => 'version',
 108         'test' => 'substr(value,0,2) <= \'5.\' && substr(value,2,1) < 1',
 109         'issue' => __('The MySQL server version less than 5.1.'),
 110         'recommendation' => __(
 111             'You should upgrade, as MySQL 5.1 has improved performance, and MySQL 5.5 even more so.'
 112         ),
 113         'justification' => __('Current version: %s'),
 114         'justification_formula' => 'value',
 115     ],
 116     [
 117         'id' => 'Minor Version',
 118         'name' => __('Minor Version'),
 119         'precondition' => '! fired(\'Release Series\')',
 120         'formula' => 'version',
 121         'test' => 'substr(value,0,2) <= \'5.\' && substr(value,2,1) <= 1 && substr(value,4,2) < 30',
 122         'issue' => __('Version less than 5.1.30 (the first GA release of 5.1).'),
 123         'recommendation' => __(
 124             'You should upgrade, as recent versions of MySQL 5.1 have improved performance'
 125             . ' and MySQL 5.5 even more so.'
 126         ),
 127         'justification' => __('Current version: %s'),
 128         'justification_formula' => 'value',
 129     ],
 130     [
 131         'id' => 'Minor Version',
 132         'name' => __('Minor Version'),
 133         'precondition' => '! fired(\'Release Series\')',
 134         'formula' => 'version',
 135         'test' => 'substr(value,0,1) == 5 && substr(value,2,1) == 5 && substr(value,4,2) < 8',
 136         'issue' => __('Version less than 5.5.8 (the first GA release of 5.5).'),
 137         'recommendation' => __('You should upgrade, to a stable version of MySQL 5.5.'),
 138         'justification' => __('Current version: %s'),
 139         'justification_formula' => 'value',
 140     ],
 141     [
 142         'id' => 'Distribution',
 143         'name' => __('Distribution'),
 144         'formula' => 'version_comment',
 145         'test' => 'preg_match(\'/source/i\',value)',
 146         'issue' => __('Version is compiled from source, not a MySQL official binary.'),
 147         'recommendation' => __(
 148             'If you did not compile from source, you may be using a package modified by a distribution.'
 149             . ' The MySQL manual only is accurate for official MySQL binaries,'
 150             . ' not any package distributions (such as RedHat, Debian/Ubuntu etc).'
 151         ),
 152         'justification' => __('\'source\' found in version_comment'),
 153     ],
 154     [
 155         'id' => 'Distribution',
 156         'name' => __('Distribution'),
 157         'formula' => 'version_comment',
 158         'test' => 'preg_match(\'/percona/i\',value)',
 159         'issue' => __('The MySQL manual only is accurate for official MySQL binaries.'),
 160         'recommendation' => __(
 161             'Percona documentation is at <a href="https://www.percona.com/software/documentation/">'
 162             . 'https://www.percona.com/software/documentation/</a>'
 163         ),
 164         'justification' => __('\'percona\' found in version_comment'),
 165     ],
 166     [
 167         'id' => 'MySQL Architecture',
 168         'name' => __('MySQL Architecture'),
 169         'formula' => 'system_memory',
 170         'test' => 'value > 3072*1024 && !preg_match(\'/64/\',version_compile_machine)'
 171             . ' && !preg_match(\'/64/\',version_compile_os)',
 172         'issue' => __('MySQL is not compiled as a 64-bit package.'),
 173         'recommendation' => __(
 174             'Your memory capacity is above 3 GiB (assuming the Server is on localhost),'
 175             . ' so MySQL might not be able to access all of your memory.'
 176             . ' You might want to consider installing the 64-bit version of MySQL.'
 177         ),
 178         'justification' => __('Available memory on this host: %s'),
 179         'justification_formula' => 'ADVISOR_formatByteDown(value*1024, 2, 2)',
 180     ],
 181     // Query cache
 182     [
 183         'id' => 'Query caching method',
 184         'name' => __('Query caching method'),
 185         'precondition' => '!fired(\'Query cache disabled\')',
 186         'formula' => 'Questions / Uptime',
 187         'test' => 'value > 100',
 188         'issue' => __('Suboptimal caching method.'),
 189         'recommendation' => __(
 190             'You are using the MySQL Query cache with a fairly high traffic database.'
 191             . ' It might be worth considering to use '
 192             . '<a href="https://dev.mysql.com/doc/refman/5.6/en/ha-memcached.html">memcached</a>'
 193             . ' instead of the MySQL Query cache, especially if you have multiple replicas.'
 194         ),
 195         'justification' => __(
 196             'The query cache is enabled and the server receives %d queries per second.'
 197             . ' This rule fires if there is more than 100 queries per second.'
 198         ),
 199         'justification_formula' => 'round(value,1)',
 200     ],
 201     // Sorts
 202     [
 203         'id' => 'Percentage of sorts that cause temporary tables',
 204         'name' => __('Percentage of sorts that cause temporary tables'),
 205         'precondition' => 'Sort_scan + Sort_range > 0',
 206         'formula' => 'Sort_merge_passes / (Sort_scan + Sort_range) * 100',
 207         'test' => 'value > 10',
 208         'issue' => __('Too many sorts are causing temporary tables.'),
 209         'recommendation' => __(
 210             'Consider increasing {sort_buffer_size} and/or {read_rnd_buffer_size},'
 211             . ' depending on your system memory limits.'
 212         ),
 213         'justification' => __('%s%% of all sorts cause temporary tables, this value should be lower than 10%%.'),
 214         'justification_formula' => 'round(value,1)',
 215     ],
 216     [
 217         'id' => 'Rate of sorts that cause temporary tables',
 218         'name' => __('Rate of sorts that cause temporary tables'),
 219         'formula' => 'Sort_merge_passes / Uptime',
 220         'test' => 'value * 60 * 60 > 1',
 221         'issue' => __('Too many sorts are causing temporary tables.'),
 222         'recommendation' => __(
 223             'Consider increasing {sort_buffer_size} and/or {read_rnd_buffer_size},'
 224             . ' depending on your system memory limits.'
 225         ),
 226         'justification' => __('Temporary tables average: %s, this value should be less than 1 per hour.'),
 227         'justification_formula' => 'ADVISOR_bytime(value,2)',
 228     ],
 229     [
 230         'id' => 'Sort rows',
 231         'name' => __('Sort rows'),
 232         'formula' => 'Sort_rows / Uptime',
 233         'test' => 'value * 60 >= 1',
 234         'issue' => __('There are lots of rows being sorted.'),
 235         'recommendation' => __(
 236             'While there is nothing wrong with a high amount of row sorting, you might want to'
 237             . ' make sure that the queries which require a lot of sorting use indexed columns in'
 238             . ' the ORDER BY clause, as this will result in much faster sorting.'
 239         ),
 240         'justification' => __('Sorted rows average: %s'),
 241         'justification_formula' => 'ADVISOR_bytime(value,2)',
 242     ],
 243     // Joins, scans
 244     [
 245         'id' => 'Rate of joins without indexes',
 246         'name' => __('Rate of joins without indexes'),
 247         'formula' => '(Select_range_check + Select_scan + Select_full_join) / Uptime',
 248         'test' => 'value * 60 * 60 > 1',
 249         'issue' => __('There are too many joins without indexes.'),
 250         'recommendation' => __(
 251             'This means that joins are doing full table scans. Adding indexes for the columns being'
 252             . ' used in the join conditions will greatly speed up table joins.'
 253         ),
 254         'justification' => __('Table joins average: %s, this value should be less than 1 per hour'),
 255         'justification_formula' => 'ADVISOR_bytime(value,2)',
 256     ],
 257     [
 258         'id' => 'Rate of reading first index entry',
 259         'name' => __('Rate of reading first index entry'),
 260         'formula' => 'Handler_read_first / Uptime',
 261         'test' => 'value * 60 * 60 > 1',
 262         'issue' => __('The rate of reading the first index entry is high.'),
 263         'recommendation' => __(
 264             'This usually indicates frequent full index scans. Full index scans are faster than'
 265             . ' table scans but require lots of CPU cycles in big tables, if those tables that have or'
 266             . ' had high volumes of UPDATEs and DELETEs, running \'OPTIMIZE TABLE\' might reduce the'
 267             . ' amount of and/or speed up full index scans. Other than that full index scans can'
 268             . ' only be reduced by rewriting queries.'
 269         ),
 270         'justification' => __('Index scans average: %s, this value should be less than 1 per hour'),
 271         'justification_formula' => 'ADVISOR_bytime(value,2)',
 272     ],
 273     [
 274         'id' => 'Rate of reading fixed position',
 275         'name' => __('Rate of reading fixed position'),
 276         'formula' => 'Handler_read_rnd / Uptime',
 277         'test' => 'value * 60 * 60 > 1',
 278         'issue' => __('The rate of reading data from a fixed position is high.'),
 279         'recommendation' => __(
 280             'This indicates that many queries need to sort results and/or do a full table scan,'
 281             . ' including join queries that do not use indexes. Add indexes where applicable.'
 282         ),
 283         'justification' => __('Rate of reading fixed position average: %s, this value should be less than 1 per hour'),
 284         'justification_formula' => 'ADVISOR_bytime(value,2)',
 285     ],
 286     [
 287         'id' => 'Rate of reading next table row',
 288         'name' => __('Rate of reading next table row'),
 289         'formula' => 'Handler_read_rnd_next / Uptime',
 290         'test' => 'value * 60 * 60 > 1',
 291         'issue' => __('The rate of reading the next table row is high.'),
 292         'recommendation' => __(
 293             'This indicates that many queries are doing full table scans. Add indexes where applicable.'
 294         ),
 295         'justification' => __('Rate of reading next table row: %s, this value should be less than 1 per hour'),
 296         'justification_formula' => 'ADVISOR_bytime(value,2)',
 297     ],
 298     // Temp tables
 299     [
 300         'id' => 'Different tmp_table_size and max_heap_table_size',
 301         'name' => __('Different tmp_table_size and max_heap_table_size'),
 302         'formula' => 'tmp_table_size - max_heap_table_size',
 303         'test' => 'value !=0',
 304         'issue' => __('{tmp_table_size} and {max_heap_table_size} are not the same.'),
 305         'recommendation' => __(
 306             'If you have deliberately changed one of either: The server uses the lower value of either'
 307             . ' to determine the maximum size of in-memory tables. So if you wish to increase the'
 308             . ' in-memory table limit you will have to increase the other value as well.'
 309         ),
 310         'justification' => __('Current values are tmp_table_size: %s, max_heap_table_size: %s'),
 311         'justification_formula' => 'ADVISOR_formatByteDown(tmp_table_size, 2, 2),'
 312             . ' ADVISOR_formatByteDown(max_heap_table_size, 2, 2)',
 313     ],
 314     [
 315         'id' => 'Percentage of temp tables on disk',
 316         'name' => __('Percentage of temp tables on disk'),
 317         'precondition' => 'Created_tmp_tables + Created_tmp_disk_tables > 0',
 318         'formula' => 'Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100',
 319         'test' => 'value > 25',
 320         'issue' => __('Many temporary tables are being written to disk instead of being kept in memory.'),
 321         'recommendation' => __(
 322             'Increasing {max_heap_table_size} and {tmp_table_size} might help. However some'
 323             . ' temporary tables are always being written to disk, independent of the value of these variables.'
 324             . ' To eliminate these you will have to rewrite your queries to avoid those conditions'
 325             . ' (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column'
 326             . ' bigger than 512 bytes) as mentioned in the beginning of an <a href="'
 327             . 'https://www.facebook.com/note.php?note_id=10150111255065841&comments'
 328             . '">Article by the Pythian Group</a>'
 329         ),
 330         'justification' => __(
 331             '%s%% of all temporary tables are being written to disk, this value should be below 25%%'
 332         ),
 333         'justification_formula' => 'round(value,1)',
 334     ],
 335     [
 336         'id' => 'Temp disk rate',
 337         'name' => __('Temp disk rate'),
 338         'precondition' => '!fired(\'Percentage of temp tables on disk\')',
 339         'formula' => 'Created_tmp_disk_tables / Uptime',
 340         'test' => 'value * 60 * 60 > 1',
 341         'issue' => __('Many temporary tables are being written to disk instead of being kept in memory.'),
 342         'recommendation' => __(
 343             'Increasing {max_heap_table_size} and {tmp_table_size} might help. However some'
 344             . ' temporary tables are always being written to disk, independent of the value of these variables.'
 345             . ' To eliminate these you will have to rewrite your queries to avoid those conditions'
 346             . ' (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column'
 347             . ' bigger than 512 bytes) as mentioned in the <a href="'
 348             . 'https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html'
 349             . '">MySQL Documentation</a>'
 350         ),
 351         'justification' => __(
 352             'Rate of temporary tables being written to disk: %s, this value should be less than 1 per hour'
 353         ),
 354         'justification_formula' => 'ADVISOR_bytime(value,2)',
 355     ],
 356     // MyISAM index cache
 357     [
 358         'id' => 'MyISAM key buffer size',
 359         'name' => __('MyISAM key buffer size'),
 360         'formula' => 'key_buffer_size',
 361         'test' => 'value == 0',
 362         'issue' => __('Key buffer is not initialized. No MyISAM indexes will be cached.'),
 363         'recommendation' => __(
 364             'Set {key_buffer_size} depending on the size of your MyISAM indexes. 64M is a good start.'
 365         ),
 366         'justification' => __('key_buffer_size is 0'),
 367     ],
 368     [
 369         'id' => 'Max % MyISAM key buffer ever used',
 370         /* xgettext:no-php-format */
 371         'name' => __('Max % MyISAM key buffer ever used'),
 372         'precondition' => 'key_buffer_size > 0',
 373         'formula' => 'Key_blocks_used * key_cache_block_size / key_buffer_size * 100',
 374         'test' => 'value < 95',
 375         /* xgettext:no-php-format */
 376         'issue' => __('MyISAM key buffer (index cache) % used is low.'),
 377         'recommendation' => __(
 378             'You may need to decrease the size of {key_buffer_size}, re-examine your tables to see'
 379             . ' if indexes have been removed, or examine queries and expectations'
 380             . ' about what indexes are being used.'
 381         ),
 382         'justification' => __('max %% MyISAM key buffer ever used: %s%%, this value should be above 95%%'),
 383         'justification_formula' => 'round(value,1)',
 384     ],
 385     [
 386         'id' => 'Percentage of MyISAM key buffer used',
 387         'name' => __('Percentage of MyISAM key buffer used'),
 388         // Don't fire if above rule fired - we don't need the same advice twice
 389         'precondition' => 'key_buffer_size > 0 && !fired(\'Max % MyISAM key buffer ever used\')',
 390         'formula' => '( 1 - Key_blocks_unused * key_cache_block_size / key_buffer_size) * 100',
 391         'test' => 'value < 95',
 392         /* xgettext:no-php-format */
 393         'issue' => __('MyISAM key buffer (index cache) % used is low.'),
 394         'recommendation' => __(
 395             'You may need to decrease the size of {key_buffer_size}, re-examine your tables to see'
 396             . ' if indexes have been removed, or examine queries and expectations'
 397             . ' about what indexes are being used.'
 398         ),
 399         'justification' => __('%% MyISAM key buffer used: %s%%, this value should be above 95%%'),
 400         'justification_formula' => 'round(value,1)',
 401     ],
 402     [
 403         'id' => 'Percentage of index reads from memory',
 404         'name' => __('Percentage of index reads from memory'),
 405         'precondition' => 'Key_read_requests > 0',
 406         'formula' => '100 - (Key_reads / Key_read_requests * 100)',
 407         'test' => 'value < 95',
 408         /* xgettext:no-php-format */
 409         'issue' => __('The % of indexes that use the MyISAM key buffer is low.'),
 410         'recommendation' => __('You may need to increase {key_buffer_size}.'),
 411         'justification' => __('Index reads from memory: %s%%, this value should be above 95%%'),
 412         'justification_formula' => 'round(value,1)',
 413     ],
 414     // Other caches
 415     [
 416         'id' => 'Rate of table open',
 417         'name' => __('Rate of table open'),
 418         'formula' => 'Opened_tables / Uptime',
 419         'test' => 'value*60*60 > 10',
 420         'issue' => __('The rate of opening tables is high.'),
 421         'recommendation' => __(
 422             'Opening tables requires disk I/O which is costly. Increasing {table_open_cache} might avoid this.'
 423         ),
 424         'justification' => __('Opened table rate: %s, this value should be less than 10 per hour'),
 425         'justification_formula' => 'ADVISOR_bytime(value,2)',
 426     ],
 427     [
 428         'id' => 'Percentage of used open files limit',
 429         'name' => __('Percentage of used open files limit'),
 430         'formula' => 'Open_files / open_files_limit * 100',
 431         'test' => 'value > 85',
 432         'issue' => __(
 433             'The number of open files is approaching the max number of open files.'
 434             . ' You may get a "Too many open files" error.'
 435         ),
 436         'recommendation' => __(
 437             'Consider increasing {open_files_limit}, and check the error log when'
 438             . ' restarting after changing {open_files_limit}.'
 439         ),
 440         'justification' => __('The number of opened files is at %s%% of the limit. It should be below 85%%'),
 441         'justification_formula' => 'round(value,1)',
 442     ],
 443     [
 444         'id' => 'Rate of open files',
 445         'name' => __('Rate of open files'),
 446         'formula' => 'Open_files / Uptime',
 447         'test' => 'value * 60 * 60 > 5',
 448         'issue' => __('The rate of opening files is high.'),
 449         'recommendation' => __(
 450             'Consider increasing {open_files_limit}, and check the error log when'
 451             . ' restarting after changing {open_files_limit}.'
 452         ),
 453         'justification' => __('Opened files rate: %s, this value should be less than 5 per hour'),
 454         'justification_formula' => 'ADVISOR_bytime(value,2)',
 455     ],
 456     [
 457         'id' => 'Immediate table locks %',
 458         /* xgettext:no-php-format */
 459         'name' => __('Immediate table locks %'),
 460         'precondition' => 'Table_locks_waited + Table_locks_immediate > 0',
 461         'formula' => 'Table_locks_immediate / (Table_locks_waited + Table_locks_immediate) * 100',
 462         'test' => 'value < 95',
 463         'issue' => __('Too many table locks were not granted immediately.'),
 464         'recommendation' => __('Optimize queries and/or use InnoDB to reduce lock wait.'),
 465         'justification' => __('Immediate table locks: %s%%, this value should be above 95%%'),
 466         'justification_formula' => 'round(value,1)',
 467     ],
 468     [
 469         'id' => 'Table lock wait rate',
 470         'name' => __('Table lock wait rate'),
 471         'formula' => 'Table_locks_waited / Uptime',
 472         'test' => 'value * 60 * 60 > 1',
 473         'issue' => __('Too many table locks were not granted immediately.'),
 474         'recommendation' => __('Optimize queries and/or use InnoDB to reduce lock wait.'),
 475         'justification' => __('Table lock wait rate: %s, this value should be less than 1 per hour'),
 476         'justification_formula' => 'ADVISOR_bytime(value,2)',
 477     ],
 478     [
 479         'id' => 'Thread cache',
 480         'name' => __('Thread cache'),
 481         'formula' => 'thread_cache_size',
 482         'test' => 'value < 1',
 483         'issue' => __('Thread cache is disabled, resulting in more overhead from new connections to MySQL.'),
 484         'recommendation' => __('Enable the thread cache by setting {thread_cache_size} > 0.'),
 485         'justification' => __('The thread cache is set to 0'),
 486     ],
 487     [
 488         'id' => 'Thread cache hit rate %',
 489         /* xgettext:no-php-format */
 490         'name' => __('Thread cache hit rate %'),
 491         'precondition' => 'thread_cache_size > 0',
 492         'formula' => '100 - Threads_created / Connections',
 493         'test' => 'value < 80',
 494         'issue' => __('Thread cache is not efficient.'),
 495         'recommendation' => __('Increase {thread_cache_size}.'),
 496         'justification' => __('Thread cache hitrate: %s%%, this value should be above 80%%'),
 497         'justification_formula' => 'round(value,1)',
 498     ],
 499     [
 500         'id' => 'Threads that are slow to launch',
 501         'name' => __('Threads that are slow to launch'),
 502         'precondition' => 'slow_launch_time > 0',
 503         'formula' => 'Slow_launch_threads',
 504         'test' => 'value > 0',
 505         'issue' => __('There are too many threads that are slow to launch.'),
 506         'recommendation' => __(
 507             'This generally happens in case of general system overload as it is pretty simple'
 508             . ' operations. You might want to monitor your system load carefully.'
 509         ),
 510         'justification' => __('%s thread(s) took longer than %s seconds to start, it should be 0'),
 511         'justification_formula' => 'value, slow_launch_time',
 512     ],
 513     [
 514         'id' => 'Slow launch time',
 515         'name' => __('Slow launch time'),
 516         'formula' => 'slow_launch_time',
 517         'test' => 'value > 2',
 518         'issue' => __('Slow_launch_time is above 2s.'),
 519         'recommendation' => __(
 520             'Set {slow_launch_time} to 1s or 2s to correctly count threads that are slow to launch.'
 521         ),
 522         'justification' => __('slow_launch_time is set to %s'),
 523         'justification_formula' => 'value',
 524     ],
 525     // Connections
 526     [
 527         'id' => 'Percentage of used connections',
 528         'name' => __('Percentage of used connections'),
 529         'formula' => 'Max_used_connections / max_connections * 100',
 530         'test' => 'value > 80',
 531         'issue' => __('The maximum amount of used connections is getting close to the value of {max_connections}.'),
 532         'recommendation' => __(
 533             'Increase {max_connections}, or decrease {wait_timeout} so that connections that do not'
 534             . ' close database handlers properly get killed sooner.'
 535             . ' Make sure the code closes database handlers properly.'
 536         ),
 537         'justification' => __('Max_used_connections is at %s%% of max_connections, it should be below 80%%'),
 538         'justification_formula' => 'round(value,1)',
 539     ],
 540     [
 541         'id' => 'Percentage of aborted connections',
 542         'name' => __('Percentage of aborted connections'),
 543         'formula' => 'Aborted_connects / Connections * 100',
 544         'test' => 'value > 1',
 545         'issue' => __('Too many connections are aborted.'),
 546         'recommendation' => __(
 547             'Connections are usually aborted when they cannot be authorized. <a href="'
 548             . 'https://www.percona.com/blog/2008/08/23/how-to-track-down-the-source-of-aborted_connects/'
 549             . '">This article</a> might help you track down the source.'
 550         ),
 551         'justification' => __('%s%% of all connections are aborted. This value should be below 1%%'),
 552         'justification_formula' => 'round(value,1)',
 553     ],
 554     [
 555         'id' => 'Rate of aborted connections',
 556         'name' => __('Rate of aborted connections'),
 557         'formula' => 'Aborted_connects / Uptime',
 558         'test' => 'value * 60 * 60 > 1',
 559         'issue' => __('Too many connections are aborted.'),
 560         'recommendation' => __(
 561             'Connections are usually aborted when they cannot be authorized. <a href="'
 562             . 'https://www.percona.com/blog/2008/08/23/how-to-track-down-the-source-of-aborted_connects/'
 563             . '">This article</a> might help you track down the source.'
 564         ),
 565         'justification' => __('Aborted connections rate is at %s, this value should be less than 1 per hour'),
 566         'justification_formula' => 'ADVISOR_bytime(value,2)',
 567     ],
 568     [
 569         'id' => 'Percentage of aborted clients',
 570         'name' => __('Percentage of aborted clients'),
 571         'formula' => 'Aborted_clients / Connections * 100',
 572         'test' => 'value > 2',
 573         'issue' => __('Too many clients are aborted.'),
 574         'recommendation' => __(
 575             'Clients are usually aborted when they did not close their connection to MySQL properly.'
 576             . ' This can be due to network issues or code not closing a database handler properly.'
 577             . ' Check your network and code.'
 578         ),
 579         'justification' => __('%s%% of all clients are aborted. This value should be below 2%%'),
 580         'justification_formula' => 'round(value,1)',
 581     ],
 582     [
 583         'id' => 'Rate of aborted clients',
 584         'name' => __('Rate of aborted clients'),
 585         'formula' => 'Aborted_clients / Uptime',
 586         'test' => 'value * 60 * 60 > 1',
 587         'issue' => __('Too many clients are aborted.'),
 588         'recommendation' => __(
 589             'Clients are usually aborted when they did not close their connection to MySQL properly.'
 590             . ' This can be due to network issues or code not closing a database handler properly.'
 591             . ' Check your network and code.'
 592         ),
 593         'justification' => __('Aborted client rate is at %s, this value should be less than 1 per hour'),
 594         'justification_formula' => 'ADVISOR_bytime(value,2)',
 595     ],
 596     // InnoDB
 597     [
 598         'id' => 'Is InnoDB disabled?',
 599         'name' => __('Is InnoDB disabled?'),
 600         'precondition' => 'PMA_MYSQL_INT_VERSION < 50600',
 601         'formula' => 'have_innodb',
 602         'test' => 'value != "YES"',
 603         'issue' => __('You do not have InnoDB enabled.'),
 604         'recommendation' => __('InnoDB is usually the better choice for table engines.'),
 605         'justification' => __('have_innodb is set to \'value\''),
 606     ],
 607     [
 608         'id' => 'InnoDB log size',
 609         'name' => __('InnoDB log size'),
 610         'precondition' => 'innodb_buffer_pool_size > 0 && ! (IS_MARIADB && PMA_MYSQL_INT_VERSION > 100500)',
 611         'formula' => '(innodb_log_file_size * innodb_log_files_in_group)/ innodb_buffer_pool_size * 100',
 612         'test' => 'value < 20 && innodb_log_file_size / (1024 * 1024) < 256',
 613         'issue' => __('The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.'),
 614         'recommendation' => __(/* xgettext:no-php-format */
 615             'Especially on a system with a lot of writes to InnoDB tables you should set'
 616             . ' {innodb_log_file_size} to 25% of {innodb_buffer_pool_size}. However the bigger this value,'
 617             . ' the longer the recovery time will be when database crashes, so this value should not be set'
 618             . ' much higher than 256 MiB. Please note however that you cannot simply change the value of'
 619             . ' this variable. You need to shutdown the server, remove the InnoDB log files, set the new'
 620             . ' value in my.cnf, start the server, then check the error logs if everything went fine.'
 621             . ' See also <a href="'
 622             . 'https://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html'
 623             . '">this blog entry</a>'
 624         ),
 625         'justification' => __(
 626             'Your InnoDB log size is at %s%% in relation to the InnoDB buffer pool size,'
 627             . ' it should not be below 20%%'
 628         ),
 629         'justification_formula' => 'round(value,1)',
 630     ],
 631     [
 632         'id' => 'InnoDB log size',
 633         'name' => __('InnoDB log size'),
 634         'precondition' => 'innodb_buffer_pool_size > 0 && IS_MARIADB && PMA_MYSQL_INT_VERSION > 100500',
 635         // From MariaDB 10.5, there is 1 redo log.
 636         // For MariaDB 10.4 and before, the number of redo log files is configured
 637         // by the innodb_log_files_in_group system variable.
 638         'formula' => 'innodb_log_file_size / innodb_buffer_pool_size * 100',
 639         'test' => 'value < 20 && innodb_log_file_size / (1024 * 1024) < 256',
 640         'issue' => __('The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.'),
 641         'recommendation' => __(/* xgettext:no-php-format */
 642             'Especially on a system with a lot of writes to InnoDB tables you should set'
 643             . ' {innodb_log_file_size} to 25% of {innodb_buffer_pool_size}. However the bigger this value,'
 644             . ' the longer the recovery time will be when database crashes, so this value should not be set'
 645             . ' much higher than 256 MiB. Please note however that you cannot simply change the value of'
 646             . ' this variable. You need to shutdown the server, remove the InnoDB log files, set the new'
 647             . ' value in my.cnf, start the server, then check the error logs if everything went fine.'
 648             . ' See also <a href="'
 649             . 'https://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html'
 650             . '">this blog entry</a>'
 651         ),
 652         'justification' => __(
 653             'Your InnoDB log size is at %s%% in relation to the InnoDB buffer pool size,'
 654             . ' it should not be below 20%%'
 655         ),
 656         'justification_formula' => 'round(value,1)',
 657     ],
 658     [
 659         'id' => 'Max InnoDB log size',
 660         'name' => __('Max InnoDB log size'),
 661         'precondition' => 'innodb_buffer_pool_size > 0 && innodb_log_file_size / innodb_buffer_pool_size * 100 < 30',
 662         'formula' => 'innodb_log_file_size / (1024 * 1024)',
 663         'test' => 'value > 256',
 664         'issue' => __('The InnoDB log file size is inadequately large.'),
 665         'recommendation' => __(/* xgettext:no-php-format */
 666             'It is usually sufficient to set {innodb_log_file_size} to 25% of the size of'
 667             . ' {innodb_buffer_pool_size}. A very big {innodb_log_file_size} slows down the recovery'
 668             . ' time after a database crash considerably. See also '
 669             . '<a href="https://www.percona.com/blog/2006/07/03/choosing-proper-innodb_log_file_size/">'
 670             . 'this Article</a>. You need to shutdown the server, remove the InnoDB log files, set the'
 671             . ' new value in my.cnf, start the server, then check the error logs'
 672             . ' if everything went fine. See also <a href="'
 673             . 'https://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html'
 674             . '">this blog entry</a>'
 675         ),
 676         'justification' => __('Your absolute InnoDB log size is %s MiB'),
 677         'justification_formula' => 'round(value,1)',
 678     ],
 679     [
 680         'id' => 'InnoDB buffer pool size',
 681         'name' => __('InnoDB buffer pool size'),
 682         'precondition' => 'system_memory > 0',
 683         'formula' => 'innodb_buffer_pool_size / system_memory * 100',
 684         'test' => 'value < 60',
 685         'issue' => __('Your InnoDB buffer pool is fairly small.'),
 686         'recommendation' => __(/* xgettext:no-php-format */
 687             'The InnoDB buffer pool has a profound impact on performance for InnoDB tables.'
 688             . ' Assign all your remaining memory to this buffer. For database servers that use solely InnoDB'
 689             . ' as storage engine and have no other services (e.g. a web server) running, you may set this'
 690             . ' as high as 80% of your available memory. If that is not the case, you need to carefully'
 691             . ' assess the memory consumption of your other services and non-InnoDB-Tables and set this'
 692             . ' variable accordingly. If it is set too high, your system will start swapping,'
 693             . ' which decreases performance significantly. See also '
 694             . '<a href="https://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/">this article</a>'
 695         ),
 696         'justification' => __(
 697             'You are currently using %s%% of your memory for the InnoDB buffer pool.'
 698             . ' This rule fires if you are assigning less than 60%%, however this might be perfectly'
 699             . ' adequate for your system if you don\'t have much InnoDB tables'
 700             . ' or other services running on the same machine.'
 701         ),
 702         'justification_formula' => 'value',
 703     ],
 704     // Other
 705     [
 706         'id' => 'MyISAM concurrent inserts',
 707         'name' => __('MyISAM concurrent inserts'),
 708         'formula' => 'concurrent_insert',
 709         'test' => 'value === 0 || value === \'NEVER\'',
 710         'issue' => __('Enable {concurrent_insert} by setting it to 1'),
 711         'recommendation' => __(
 712             'Setting {concurrent_insert} to 1 reduces contention between'
 713             . ' readers and writers for a given table. See also '
 714             . '<a href="https://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html">MySQL Documentation</a>'
 715         ),
 716         'justification' => __('concurrent_insert is set to 0'),
 717     ],
 718 ];