1 # phpMyAdmin Advisory rules file
3 # Use only UNIX style newlines
5 # This file is being parsed by Advisor.php, which should handle syntax
6 # errors correctly. However, PHP Warnings and the like are being consumed by
7 # the phpMyAdmin error handler, so those won't show up E.g.: Justification line
8 # is empty because you used an unescape percent sign, sprintf() returns an
9 # empty string and no warning/error is shown
12 # 'rule' identifier[the name of the rule] eexpr [an optional precondition]
13 # expr [variable or value calculation used for the test]
14 # expr [test, if evaluted to 'true' it fires the rule. Use 'value' to insert the calculated value (without quotes)]
15 # string [the issue (what is the problem?)]
16 # string [the recommendation (how do i fix it?)]
17 # formatted-string '|' comma-seperated-expr [the justification (result of the calculated value / why did this rule fire?)]
19 # comma-seperated-expr: expr(,expr)*
20 # eexpr: [expr] - expr enclosed in []
21 # expr: a php code literal with extras:
22 # - variable names are replaced with their respective values
23 # - fired('name of rule') is replaced with true/false when given rule has
24 # been fired. Note however that this is a very simple rules engine.
25 # Rules are only checked in sequential order as they are written down
26 # here. If given rule has not been checked yet, fired() will always
28 # - 'value' is replaced with the calculated value. If it is a string, it
29 # will be put within single quotes
30 # - other than that you may use any php function, initialized variable or
33 # identifier: A string enclosed in single quotes
34 # string: A quoteless string, may contain HTML. Variable names enclosed in
35 # curly braces are replaced with links to directly edit this variable.
36 # e.g. {tmp_table_size}
37 # formatted-string: You may use classic php sprintf() string formatting here,
38 # the arguments must be appended after a trailing pipe (|) as
39 # mentioned in above syntax percent signs (%) are
40 # automatically escaped (%%) in the following cases: When
41 # followed by a space, dot or comma and at the end of the
44 # Comments start with #
49 rule 'Uptime below one day'
52 Uptime is less than 1 day, performance tuning may not be accurate.
53 To have more accurate averages it is recommended to let the server run for longer than a day before running this analyzer
54 The uptime is only %s | ADVISOR_timespanFormat(Uptime)
56 rule 'Questions below 1,000'
59 Fewer than 1,000 questions have been run against this server. The recommendations may not be accurate.
60 Let the server run for a longer time until it has executed a greater amount of queries.
61 Current amount of Questions: %s | Questions
63 rule 'Percentage of slow queries' [Questions > 0]
64 Slow_queries / Questions * 100
66 There is a lot of slow queries compared to the overall amount of Queries.
67 You might want to increase {long_query_time} or optimize the queries listed in the slow query log
68 The slow query rate should be below 5%, your value is %s%. | round(value,2)
70 rule 'Slow query rate' [Questions > 0]
71 (Slow_queries / Questions * 100) / Uptime
73 There is a high percentage of slow queries compared to the server uptime.
74 You might want to increase {long_query_time} or optimize the queries listed in the slow query log
75 You have a slow query rate of %s per hour, you should have less than 1% per hour. | ADVISOR_bytime(value,2)
77 rule 'Long query time'
80 {long_query_time} is set to 10 seconds or more, thus only slow queries that take above 10 seconds are logged.
81 It is suggested to set {long_query_time} to a lower value, depending on your environment. Usually a value of 1-5 seconds is suggested.
82 long_query_time is currently set to %ds. | value
84 rule 'Slow query logging' [PMA_MYSQL_INT_VERSION < 50600]
87 The slow query log is disabled.
88 Enable slow query logging by setting {log_slow_queries} to 'ON'. This will help troubleshooting badly performing queries.
89 log_slow_queries is set to 'OFF'
91 rule 'Slow query logging' [PMA_MYSQL_INT_VERSION >= 50600]
94 The slow query log is disabled.
95 Enable slow query logging by setting {slow_query_log} to 'ON'. This will help troubleshooting badly performing queries.
96 slow_query_log is set to 'OFF'
100 rule 'Release Series'
102 substr(value,0,2) <= '5.' && substr(value,2,1) < 1
103 The MySQL server version less than 5.1.
104 You should upgrade, as MySQL 5.1 has improved performance, and MySQL 5.5 even more so.
105 Current version: %s | value
107 rule 'Minor Version' [! fired('Release Series')]
109 substr(value,0,2) <= '5.' && substr(value,2,1) <= 1 && substr(value,4,2) < 30
110 Version less than 5.1.30 (the first GA release of 5.1).
111 You should upgrade, as recent versions of MySQL 5.1 have improved performance and MySQL 5.5 even more so.
112 Current version: %s | value
114 rule 'Minor Version' [! fired('Release Series')]
116 substr(value,0,1) == 5 && substr(value,2,1) == 5 && substr(value,4,2) < 8
117 Version less than 5.5.8 (the first GA release of 5.5).
118 You should upgrade, to a stable version of MySQL 5.5.
119 Current version: %s | value
123 preg_match('/source/i',value)
124 Version is compiled from source, not a MySQL official binary.
125 If you did not compile from source, you may be using a package modified by a distribution. The MySQL manual only is accurate for official MySQL binaries, not any package distributions (such as RedHat, Debian/Ubuntu etc).
126 'source' found in version_comment
130 preg_match('/percona/i',value)
131 The MySQL manual only is accurate for official MySQL binaries.
132 Percona documentation is at <a href="https://www.percona.com/software/documentation/">https://www.percona.com/software/documentation/</a>
133 'percona' found in version_comment
135 rule 'MySQL Architecture'
137 value > 3072*1024 && !preg_match('/64/',version_compile_machine) && !preg_match('/64/',version_compile_os)
138 MySQL is not compiled as a 64-bit package.
139 Your memory capacity is above 3 GiB (assuming the Server is on localhost), so MySQL might not be able to access all of your memory. You might want to consider installing the 64-bit version of MySQL.
140 Available memory on this host: %s | ADVISOR_formatByteDown(value*1024, 2, 2)
145 # Lame: 'ON' == 0 is true, so you need to compare 'ON' == '0'
146 rule 'Query cache disabled'
148 value == 0 || query_cache_type == 'OFF' || query_cache_type == '0'
149 The query cache is not enabled.
150 The query cache is known to greatly improve performance if configured correctly. Enable it by setting {query_cache_size} to a 2 digit MiB value and setting {query_cache_type} to 'ON'. <b>Note:</b> If you are using memcached, ignore this recommendation.
151 query_cache_size is set to 0 or query_cache_type is set to 'OFF'
153 rule 'Query caching method' [!fired('Query cache disabled')]
156 Suboptimal caching method.
157 You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use <a href="https://dev.mysql.com/doc/refman/5.5/en/ha-memcached.html">memcached</a> instead of the MySQL Query cache, especially if you have multiple slaves.
158 The query cache is enabled and the server receives %d queries per second. This rule fires if there is more than 100 queries per second. | round(value,1)
160 rule 'Query cache efficiency (%)' [Com_select + Qcache_hits > 0 && !fired('Query cache disabled')]
161 Qcache_hits / (Com_select + Qcache_hits) * 100
163 Query cache not running efficiently, it has a low hit rate.
164 Consider increasing {query_cache_limit}.
165 The current query cache hit rate of %s% is below 20% | round(value,1)
167 rule 'Query Cache usage' [!fired('Query cache disabled')]
168 100 - Qcache_free_memory / query_cache_size * 100
170 Less than 80% of the query cache is being utilized.
171 This might be caused by {query_cache_limit} being too low. Flushing the query cache might help as well.
172 The current ratio of free query cache memory to total query cache size is %s%. It should be above 80% | round(value,1)
174 rule 'Query cache fragmentation' [!fired('Query cache disabled')]
175 Qcache_free_blocks / (Qcache_total_blocks / 2) * 100
177 The query cache is considerably fragmented.
178 Severe fragmentation is likely to (further) increase Qcache_lowmem_prunes. This might be caused by many Query cache low memory prunes due to {query_cache_size} being too small. For a immediate but short lived fix you can flush the query cache (might lock the query cache for a long time). Carefully adjusting {query_cache_min_res_unit} to a lower value might help too, e.g. you can set it to the average size of your queries in the cache using this formula: (query_cache_size - qcache_free_memory) / qcache_queries_in_cache
179 The cache is currently fragmented by %s% , with 100% fragmentation meaning that the query cache is an alternating pattern of free and used blocks. This value should be below 20%. | round(value,1)
181 rule 'Query cache low memory prunes' [Qcache_inserts > 0 && !fired('Query cache disabled')]
182 Qcache_lowmem_prunes / Qcache_inserts * 100
184 Cached queries are removed due to low query cache memory from the query cache.
185 You might want to increase {query_cache_size}, however keep in mind that the overhead of maintaining the cache is likely to increase with its size, so do this in small increments and monitor the results.
186 The ratio of removed queries to inserted queries is %s%. The lower this value is, the better (This rules firing limit: 0.1%) | round(value,1)
188 rule 'Query cache max size' [!fired('Query cache disabled')]
190 value > 1024 * 1024 * 128
191 The query cache size is above 128 MiB. Big query caches may cause significant overhead that is required to maintain the cache.
192 Depending on your environment, it might be performance increasing to reduce this value.
193 Current query cache size: %s | ADVISOR_formatByteDown(value, 2, 2)
195 rule 'Query cache min result size' [!fired('Query cache disabled')]
198 The max size of the result set in the query cache is the default of 1 MiB.
199 Changing {query_cache_limit} (usually by increasing) may increase efficiency. This variable determines the maximum size a query result may have to be inserted into the query cache. If there are many query results above 1 MiB that are well cacheable (many reads, little writes) then increasing {query_cache_limit} will increase efficiency. Whereas in the case of many query results being above 1 MiB that are not very well cacheable (often invalidated due to table updates) increasing {query_cache_limit} might reduce efficiency.
200 query_cache_limit is set to 1 MiB
204 rule 'Percentage of sorts that cause temporary tables' [Sort_scan + Sort_range > 0]
205 Sort_merge_passes / (Sort_scan + Sort_range) * 100
207 Too many sorts are causing temporary tables.
208 Consider increasing {sort_buffer_size} and/or {read_rnd_buffer_size}, depending on your system memory limits.
209 %s% of all sorts cause temporary tables, this value should be lower than 10%. | round(value,1)
211 rule 'Rate of sorts that cause temporary tables'
212 Sort_merge_passes / Uptime
214 Too many sorts are causing temporary tables.
215 Consider increasing {sort_buffer_size} and/or {read_rnd_buffer_size}, depending on your system memory limits.
216 Temporary tables average: %s, this value should be less than 1 per hour. | ADVISOR_bytime(value,2)
221 There are lots of rows being sorted.
222 While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting.
223 Sorted rows average: %s | ADVISOR_bytime(value,2)
226 rule 'Rate of joins without indexes'
227 (Select_range_check + Select_scan + Select_full_join) / Uptime
229 There are too many joins without indexes.
230 This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.
231 Table joins average: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
233 rule 'Rate of reading first index entry'
234 Handler_read_first / Uptime
236 The rate of reading the first index entry is high.
237 This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
238 Index scans average: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
240 rule 'Rate of reading fixed position'
241 Handler_read_rnd / Uptime
243 The rate of reading data from a fixed position is high.
244 This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
245 Rate of reading fixed position average: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
247 rule 'Rate of reading next table row'
248 Handler_read_rnd_next / Uptime
250 The rate of reading the next table row is high.
251 This indicates that many queries are doing full table scans. Add indexes where applicable.
252 Rate of reading next table row: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
255 rule 'Different tmp_table_size and max_heap_table_size'
256 tmp_table_size - max_heap_table_size
258 {tmp_table_size} and {max_heap_table_size} are not the same.
259 If you have deliberately changed one of either: The server uses the lower value of either to determine the maximum size of in-memory tables. So if you wish to increase the in-memory table limit you will have to increase the other value as well.
260 Current values are tmp_table_size: %s, max_heap_table_size: %s | ADVISOR_formatByteDown(tmp_table_size, 2, 2), ADVISOR_formatByteDown(max_heap_table_size, 2, 2)
262 rule 'Percentage of temp tables on disk' [Created_tmp_tables + Created_tmp_disk_tables > 0]
263 Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100
265 Many temporary tables are being written to disk instead of being kept in memory.
266 Increasing {max_heap_table_size} and {tmp_table_size} might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an <a href="https://www.facebook.com/note.php?note_id=10150111255065841&comments">Article by the Pythian Group</a>
267 %s% of all temporary tables are being written to disk, this value should be below 25% | round(value,1)
269 rule 'Temp disk rate' [!fired('Percentage of temp tables on disk')]
270 Created_tmp_disk_tables / Uptime
272 Many temporary tables are being written to disk instead of being kept in memory.
273 Increasing {max_heap_table_size} and {tmp_table_size} might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the <a href="https://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html">MySQL Documentation</a>
274 Rate of temporary tables being written to disk: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
278 rule 'MyISAM key buffer size'
281 Key buffer is not initialized. No MyISAM indexes will be cached.
282 Set {key_buffer_size} depending on the size of your MyISAM indexes. 64M is a good start.
285 rule 'Max % MyISAM key buffer ever used' [key_buffer_size > 0]
286 Key_blocks_used * key_cache_block_size / key_buffer_size * 100
288 MyISAM key buffer (index cache) % used is low.
289 You may need to decrease the size of {key_buffer_size}, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
290 max % MyISAM key buffer ever used: %s%, this value should be above 95% | round(value,1)
292 # Don't fire if above rule fired - we don't need the same advice twice
293 rule 'Percentage of MyISAM key buffer used' [key_buffer_size > 0 && !fired('Max % MyISAM key buffer ever used')]
294 ( 1 - Key_blocks_unused * key_cache_block_size / key_buffer_size) * 100
296 MyISAM key buffer (index cache) % used is low.
297 You may need to decrease the size of {key_buffer_size}, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
298 % MyISAM key buffer used: %s%, this value should be above 95% | round(value,1)
300 rule 'Percentage of index reads from memory' [Key_read_requests > 0]
301 100 - (Key_reads / Key_read_requests * 100)
303 The % of indexes that use the MyISAM key buffer is low.
304 You may need to increase {key_buffer_size}.
305 Index reads from memory: %s%, this value should be above 95% | round(value,1)
309 rule 'Rate of table open'
310 Opened_tables / Uptime
312 The rate of opening tables is high.
313 Opening tables requires disk I/O which is costly. Increasing {table_open_cache} might avoid this.
314 Opened table rate: %s, this value should be less than 10 per hour | ADVISOR_bytime(value,2)
316 rule 'Percentage of used open files limit'
317 Open_files / open_files_limit * 100
319 The number of open files is approaching the max number of open files. You may get a "Too many open files" error.
320 Consider increasing {open_files_limit}, and check the error log when restarting after changing {open_files_limit}.
321 The number of opened files is at %s% of the limit. It should be below 85% | round(value,1)
323 rule 'Rate of open files'
326 The rate of opening files is high.
327 Consider increasing {open_files_limit}, and check the error log when restarting after changing {open_files_limit}.
328 Opened files rate: %s, this value should be less than 5 per hour | ADVISOR_bytime(value,2)
330 rule 'Immediate table locks %' [Table_locks_waited + Table_locks_immediate > 0]
331 Table_locks_immediate / (Table_locks_waited + Table_locks_immediate) * 100
333 Too many table locks were not granted immediately.
334 Optimize queries and/or use InnoDB to reduce lock wait.
335 Immediate table locks: %s%, this value should be above 95% | round(value,1)
337 rule 'Table lock wait rate'
338 Table_locks_waited / Uptime
340 Too many table locks were not granted immediately.
341 Optimize queries and/or use InnoDB to reduce lock wait.
342 Table lock wait rate: %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
347 Thread cache is disabled, resulting in more overhead from new connections to MySQL.
348 Enable the thread cache by setting {thread_cache_size} > 0.
349 The thread cache is set to 0
351 rule 'Thread cache hit rate %' [thread_cache_size > 0]
352 100 - Threads_created / Connections
354 Thread cache is not efficient.
355 Increase {thread_cache_size}.
356 Thread cache hitrate: %s%, this value should be above 80% | round(value,1)
358 rule 'Threads that are slow to launch' [slow_launch_time > 0]
361 There are too many threads that are slow to launch.
362 This generally happens in case of general system overload as it is pretty simple operations. You might want to monitor your system load carefully.
363 %s thread(s) took longer than %s seconds to start, it should be 0 | value, slow_launch_time
365 rule 'Slow launch time'
368 Slow_launch_time is above 2s.
369 Set {slow_launch_time} to 1s or 2s to correctly count threads that are slow to launch.
370 slow_launch_time is set to %s | value
374 rule 'Percentage of used connections'
375 Max_used_connections / max_connections * 100
377 The maximum amount of used connections is getting close to the value of {max_connections}.
378 Increase {max_connections}, or decrease {wait_timeout} so that connections that do not close database handlers properly get killed sooner. Make sure the code closes database handlers properly.
379 Max_used_connections is at %s% of max_connections, it should be below 80% | round(value,1)
381 rule 'Percentage of aborted connections'
382 Aborted_connects / Connections * 100
384 Too many connections are aborted.
385 Connections are usually aborted when they cannot be authorized. <a href="https://www.percona.com/blog/2008/08/23/how-to-track-down-the-source-of-aborted_connects/">This article</a> might help you track down the source.
386 %s% of all connections are aborted. This value should be below 1% | round(value,1)
388 rule 'Rate of aborted connections'
389 Aborted_connects / Uptime
391 Too many connections are aborted.
392 Connections are usually aborted when they cannot be authorized. <a href="https://www.percona.com/blog/2008/08/23/how-to-track-down-the-source-of-aborted_connects/">This article</a> might help you track down the source.
393 Aborted connections rate is at %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
395 rule 'Percentage of aborted clients'
396 Aborted_clients / Connections * 100
398 Too many clients are aborted.
399 Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.
400 %s% of all clients are aborted. This value should be below 2% | round(value,1)
402 rule 'Rate of aborted clients'
403 Aborted_clients / Uptime
405 Too many clients are aborted.
406 Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.
407 Aborted client rate is at %s, this value should be less than 1 per hour | ADVISOR_bytime(value,2)
411 rule 'Is InnoDB disabled?' [PMA_MYSQL_INT_VERSION < 50600]
414 You do not have InnoDB enabled.
415 InnoDB is usually the better choice for table engines.
416 have_innodb is set to 'value'
418 rule 'InnoDB log size' [innodb_buffer_pool_size > 0]
419 innodb_log_file_size / innodb_buffer_pool_size * 100
420 value < 20 && innodb_log_file_size / (1024 * 1024) < 256
421 The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.
422 Especially on a system with a lot of writes to InnoDB tables you should set {innodb_log_file_size} to 25% of {innodb_buffer_pool_size}. However the bigger this value, the longer the recovery time will be when database crashes, so this value should not be set much higher than 256 MiB. Please note however that you cannot simply change the value of this variable. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also <a href="https://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html">this blog entry</a>
423 Your InnoDB log size is at %s% in relation to the InnoDB buffer pool size, it should not be below 20% | round(value,1)
425 rule 'Max InnoDB log size' [innodb_buffer_pool_size > 0 && innodb_log_file_size / innodb_buffer_pool_size * 100 < 30]
426 innodb_log_file_size / (1024 * 1024)
428 The InnoDB log file size is inadequately large.
429 It is usually sufficient to set {innodb_log_file_size} to 25% of the size of {innodb_buffer_pool_size}. A very big {innodb_log_file_size} slows down the recovery time after a database crash considerably. See also <a href="https://www.percona.com/blog/2006/07/03/choosing-proper-innodb_log_file_size/">this Article</a>. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also <a href="https://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html">this blog entry</a>
430 Your absolute InnoDB log size is %s MiB | round(value,1)
432 rule 'InnoDB buffer pool size' [system_memory > 0]
433 innodb_buffer_pool_size / system_memory * 100
435 Your InnoDB buffer pool is fairly small.
436 The InnoDB buffer pool has a profound impact on performance for InnoDB tables. Assign all your remaining memory to this buffer. For database servers that use solely InnoDB as storage engine and have no other services (e.g. a web server) running, you may set this as high as 80% of your available memory. If that is not the case, you need to carefully assess the memory consumption of your other services and non-InnoDB-Tables and set this variable accordingly. If it is set too high, your system will start swapping, which decreases performance significantly. See also <a href="https://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/">this article</a>
437 You are currently using %s% of your memory for the InnoDB buffer pool. This rule fires if you are assigning less than 60%, however this might be perfectly adequate for your system if you don't have much InnoDB tables or other services running on the same machine. | value
441 rule 'MyISAM concurrent inserts'
443 value === 0 || value === 'NEVER'
444 Enable {concurrent_insert} by setting it to 1
445 Setting {concurrent_insert} to 1 reduces contention between readers and writers for a given table. See also <a href="https://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html">MySQL Documentation</a>
446 concurrent_insert is set to 0
448 # INSERT DELAYED USAGE
450 #Delayed_insert_threads 0
452 #Not_flushed_delayed_rows