src/Advisory/Rules.php

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