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