1 # phpMyAdmin Advisory rules file
2 # Use only UNIX style newlines
3 # This file is being parsed by advisor.lib.php, which should handle syntax errors correctly.
4 # However, PHP Warnings and the like are being consumed by the phpMyAdmin error handler, so those won't show up
5 # E.g.: Justification line is empty because you used an unescape percent sign, sprintf() returns an empty string and no warning/error is shown
8 # 'rule' identifier[the name of the rule] eexpr [an optional precondition]
9 # expr [variable or value calculation used for the test]
10 # expr [test, if evaluted to 'true' it fires the rule. Use 'value' to insert the calculated value (without quotes)]
11 # string [the issue (what is the problem?)]
12 # string [the recommendation (how do i fix it?)]
13 # formatted-string '|' comma-seperated-expr [the justification (result of the calculated value / why did this rule fire?)]
15 # comma-seperated-expr: expr(,expr)*
16 # eexpr: [expr] - expr enclosed in []
17 # expr: a php code literal with extras:
18 # - variable names are replaced with their respective values
19 # - fired('name of rule') is replaced with true/false when given rule has been fired. Note however that this is a very simple rules engine. Rules are only checked in sequential order as they are written down here. If given rule has not been checked yet, fired() will always evaluate to false
20 # - 'value' is replaced with the calculated value. If it is a string, it will be put within single quotes
21 # - other than that you may use any php function, initialized variable or constant
23 # identifier: A string enclosed in single quotes
24 # string: A quoteless string, may contain HTML. Variable names enclosed in curly braces are replaced with links to directly edit this variable. e.g. {tmp_table_size}
25 # formatted-string: You may use classic php sprintf() string formatting here, the arguments must be appended after a trailing pipe (|) as mentioned in above syntax
26 # percent signs (%) are automatically escaped (%%) in the following cases: When followed by a space, dot or comma and at the end of the line)
28 # Comments start with #
34 rule 'Uptime below one day'
37 Uptime is less than 1 day, performance tuning may not be accurate.
38 To have more accurate averages it is recommended to let the server run for longer than a day before running this analyzer
39 The uptime is only %s | PMA_timespanFormat(Uptime)
41 rule 'Questions below 1,000'
44 Fewer than 1,000 questions have been run against this server. The recommendations may not be accurate.
45 Let the server run for a longer time until it has executed a greater amount of queries.
46 Current amount of Questions: %s | Questions
48 rule '% slow queries' [Questions > 0]
49 Slow_queries / Questions * 100
51 There is a lot of slow queries compared to the overall amount of Queries.
52 You might want to increase {long_query_time} or optimize the queries listed in the slow query log
53 The slow query rate should be below 5%, your value is %s%. | round(value,2)
55 rule 'slow query rate' [Questions > 0]
56 (Slow_queries / Questions * 100) / Uptime
58 There is a high percentage of slow queries compared to the server uptime.
59 You might want to increase {long_query_time} or optimize the queries listed in the slow query log
60 You have a slow query rate of %s per hour, you should have less than 1% per hour. | PMA_bytime(value,2)
62 rule 'Long query time'
65 long_query_time is set to 10 seconds or more, thus only slow queries that take above 10 seconds are logged.
66 It is suggested to set {long_query_time} to a lower value, depending on your enviroment. Usually a value of 1-5 seconds is suggested.
67 long_query_time is currently set to %ss. | value
69 rule 'Slow query logging'
72 The slow query log is disabled.
73 Enable slow query logging by setting {log_slow_queries} to 'ON'. This will help troubleshooting badly performing queries.
74 log_slow_queries is set to 'OFF'
80 !PMA_DRIZZLE && substr(value,0,3) != "5.1"
81 The MySQL server version less than 5.1.
82 You should upgrade, as MySQL 5.1 has improved performance, and MySQL 5.5 even more so.
83 Current version: %s | value
87 !PMA_DRIZZLE && substr(value,4,2) < 30
88 Version less than 5.1.30 (the first GA release of 5.1).
89 You should upgrade, as recent versions of MySQL 5.1 have improved performance and MySQL 5.5 even more so.
90 Current version: %s | value
94 preg_match('/source/i',value)
95 Version is compiled from source, not a MySQL official binary. If you did not compile from source, you may be using a package modified by a distribution.
96 The MySQL manual only is accurate for official MySQL binaries, not any package distributions (such as RedHat, Debian/Ubuntu etc).
97 'source' found in version_comment
101 preg_match('/percona/i',value)
102 The MySQL manual only is accurate for official MySQL binaries.
103 Percona documentation is at http://www.percona.com/docs/wiki/
104 'percona' found in version_comment
106 rule 'MySQL Architecture'
108 value > 3072 && !preg_match('/64/',version_compile_machine)
109 MySQL is not compiled as a 64-bit package, though your memory capacity is above 3 GiB.
110 MySQL might not be able to access all of your memory. You might want to consider installing the 64-bit version of MySQL.
111 Available memory on this host: %s | implode(' ',PMA_formatByteDown(value*1024*1024, 2, 2))
116 # Lame: 'ON' == 0 is true, so you need to compare 'ON' == '0'
117 rule 'Query cache disabled'
119 value == 0 || query_cache_type == 'OFF' || query_cache_type == '0'
120 The query cache is not enabled.
121 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'
122 query_cache_size is set to 0 or query_cache_type is set to 'OFF'
124 rule 'Query cache efficiency (%)' [Com_select + Qcache_hits > 0 && !fired('Query cache disabled')]
125 Qcache_hits / (Com_select + Qcache_hits) * 100
127 Query cache not running efficiently, it has a low hit rate.
128 Consider increasing {query_cache_limit}.
129 The current query cache hit rate of %s% is below 20% | round(value,1)
131 rule 'Query Cache usage' [!fired('Query cache disabled')]
132 100 - Qcache_free_memory / query_cache_size * 100
134 Less than 80% of the query cache is being utilized.
135 This might be caused by {query_cache_limit} being too low. Flushing the query cache might help as well.
136 The current ratio of free query cache memory to total query cache size is %s%. It should be above 80% | round(value,1)
138 rule 'Query cache fragmentation' [!fired('Query cache disabled')]
139 Qcache_free_blocks / (Qcache_total_blocks / 2) * 100
141 The query cache is considerably fragmented.
142 Severe fragementation 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
143 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)
145 rule 'Query cache low memory prunes' [Qcache_inserts > 0 && !fired('Query cache disabled')]
146 Qcache_lowmem_prunes / Qcache_inserts * 100
148 Cached queries are removed due to low query cache memory from the query cache.
149 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.
150 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)
152 rule 'Query cache max size' [!fired('Query cache disabled')]
155 The query cache size is above 128 MiB. Big query caches may cause significant overhead that is required to maintain the cache.
156 Depending on your enviroment, it might be performance increasing to reduce this value.
157 Current query cache size: %s | implode(' ',PMA_formatByteDown(value, 2, 2))
159 rule 'Query cache min result size' [!fired('Query cache disabled')]
162 The max size of the result set in the query cache is the default of 1 MiB.
163 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.
164 query_cache_limit is set to 1 MiB
168 rule '% sorts that cause temporary tales' [Sort_scan + Sort_range > 0]
169 Sort_merge_passes / (Sort_scan + Sort_range) * 100
171 Too many sorts are causing temporary tables.
172 Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits
173 %s% of all sorts cause temporary tables, this value should be lower than 10%. | round(value,1)
175 rule 'rate of sorts that cause temporary tables'
176 Sort_merge_passes / Uptime
178 Too many sorts are causing temporary tables.
179 Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits
180 Temporary tables average: %s, this value should be less than 1 per hour. | PMA_bytime(value,2)
185 There are lots of rows being sorted.
186 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 fields in the ORDER BY clause, as this will result in much faster sorting
187 Sorted rows average: %s | PMA_bytime(value,2)
190 rule 'rate of joins without indexes'
191 (Select_range_check + Select_scan + Select_full_join) / Uptime
193 There are too many joins without indexes.
194 This means that joins are doing full table scans. Adding indexes for the fields being used in the join conditions will greatly speed up table joins
195 Table joins average: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
197 rule 'rate of reading first index entry'
198 Handler_read_first / Uptime
200 The rate of reading the first index entry is high.
201 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.
202 Index scans average: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
204 rule 'rate of reading fixed position'
205 Handler_read_rnd / Uptime
207 The rate of reading data from a fixed position is high.
208 This indicates 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.
209 Rate of reading fixed position average: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
211 rule 'rate of reading next table row'
212 Handler_read_rnd_next / Uptime
214 The rate of reading the next table row is high.
215 This indicates many queries are doing full table scans. Add indexes where applicable.
216 Rate of reading next table row: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
219 rule 'tmp_table_size vs. max_heap_table_size'
220 tmp_table_size - max_heap_table_size
222 tmp_table_size and max_heap_table_size are not the same.
223 If you have deliberatly 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 increse the in-memory table limit you will have to increase the other value as well.
224 Current values are tmp_table_size: %s, max_heap_table_size: %s | implode(' ',PMA_formatByteDown(tmp_table_size, 2, 2)), implode(' ',PMA_formatByteDown(max_heap_table_size, 2, 2))
226 rule '% temp disk tables' [Created_tmp_tables + Created_tmp_disk_tables > 0]
227 Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100
229 Many temporary tables are being written to disk instead of being kept in memory.
230 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 elminiate these you will have to rewrite your queries to avoid those conditions (Within a temprorary 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="http://www.facebook.com/note.php?note_id=10150111255065841&comments">Article by the Pythian Group</a>
231 %s% of all temporary tables are being written to disk, this value should be below 25% | round(value,1)
233 rule 'temp disk rate'
234 Created_tmp_disk_tables / Uptime
236 Many temporary tables are being written to disk instead of being kept in memory.
237 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 elminiate these you will have to rewrite your queries to avoid those conditions (Within a temprorary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in in the <a href="http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html">MySQL Documentation</a>
238 Rate of temporay tables being written to disk: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
240 # I couldn't find any source on the internet that suggests a direct relation between high counts of temporary tables and any of these variables.
241 # Several independent Blog entries suggest (http://ronaldbradford.com/blog/more-on-understanding-sort_buffer_size-2010-05-10/ and http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/)
242 # that sort_buffer_size should be left as it is. And increasing read_buffer_size is only suggested when there are a lot of
243 # table scans (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_read_buffer_size and other sources) though
244 # setting it too high is bad too (http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/).
245 #rule 'temp table rate'
246 # Created_tmp_tables / Uptime
247 # value * 60 * 60 > 1
248 # Many intermediate temporary tables are being created.
249 # This may be caused by queries under certain conditions as mentioned in the <a href="http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html">MySQL Documentation</a>. Consider increasing {sort_buffer_size} (sorting), {read_rnd_buffer_size} (random read buffer, ie, post-sort), {read_buffer_size} (sequential scan).
253 rule 'MyISAM key buffer size'
256 Key buffer is not initialized. No MyISAM indexes will be cached.
257 Set {key_buffer_size} depending on the size of your MyISAM indexes. 64M is a good start.
260 rule 'max % MyISAM key buffer ever used' [key_buffer_size > 0]
261 Key_blocks_used * key_cache_block_size / key_buffer_size * 100
263 MyISAM key buffer (index cache) % used is low.
264 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.
265 max % MyISAM key buffer ever used: %s, this value should be above 95% | round(value,1)
267 # Don't fire if above rule fired - we don't need the same advice twice
268 rule '% MyISAM key buffer used' [key_buffer_size > 0 && !fired('max % MyISAM key buffer ever used')]
269 ( 1 - Key_blocks_unused * key_cache_block_size / key_buffer_size) * 100
271 MyISAM key buffer (index cache) % used is low.
272 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.
273 % MyISAM key buffer used: %s, this value should be above 95% | round(value,1)
275 rule '% index reads from memory' [Key_read_requests > 0]
276 100 - (Key_reads / Key_read_requests * 100)
278 The % of indexes that use the MyISAM key buffer is low.
279 You may need to increase {key_buffer_size}.
280 Index reads from memory: %s%, this value should be above 95% | round(value,1)
284 rule 'rate of table open'
285 Opened_tables / Uptime
287 The rate of opening tables is high.
288 Opening tables requires disk I/O which is costly. Increasing {table_open_cache} might avoid this.
289 Opened table rate: %s, this value should be less than 10 per hour | PMA_bytime(value,2)
292 Open_files / open_files_limit * 100
294 The number of open files is approaching the max number of open files. You may get a "Too many open files" error.
295 Consider increasing {open_files_limit}, and check the error log when restarting after changing open_files_limit.
296 The number of opened files is at %s% of the limit. It should be below 85% | round(value,1)
298 rule 'rate of open files'
301 The rate of opening files is high.
302 Consider increasing {open_files_limit}, and check the error log when restarting after changing open_files_limit.
303 Opened files rate: %s, this value should be less than 5 per hour | PMA_bytime(value,2)
305 rule 'Immediate table locks %' [Table_locks_waited + Table_locks_immediate > 0]
306 Table_locks_immediate / (Table_locks_waited + Table_locks_immediate) * 100
308 Too many table locks were not granted immediately.
309 Optimize queries and/or use InnoDB to reduce lock wait.
310 Immediate table locks: %s%, this value should be above 95% | round(value,1)
312 rule 'Table lock wait rate'
313 Table_locks_waited / Uptime
315 Too many table locks were not granted immediately.
316 Optimize queries and/or use InnoDB to reduce lock wait.
317 Table lock wait rate: %s, this value should be less than 1 per hour | PMA_bytime(value,2)
322 Thread cache is disabled, resulting in more overhead from new connections to MySQL.
323 Enable the thread cache by setting {thread_cache_size} > 0.
324 The thread cache is set to 0
326 rule 'thread cache hit rate %' [thread_cache_size > 0]
327 100 - Threads_created / Connections
329 Thread cache is not efficient.
330 Increase {thread_cache_size}.
331 Thread cache hitrate: %s%, this value should be above 80% | round(value,1)
333 rule 'Threads that are slow to launch' [slow_launch_time > 0]
336 There are too many threads that are slow to launch.
337 This generally happens in case of general system overload as it is pretty simple operations. You might want to monitor your system load carefully.
338 %s thread(s) took longer than %s seconds to start, it should be 0 | value, slow_launch_time
340 rule 'Slow launch time'
343 Slow_launch_threads is above 2s
344 Set slow_launch_time to 1s or 2s to correctly count threads that are slow to launch
345 slow_launch_time is set to %s | value
349 rule '% connections used'
350 Max_used_connections / max_connections * 100
352 The maximum amount of used connnections is getting close to the value of max_connections.
353 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.
354 Max_used_connections is at %s% of max_connections, it should be below 80% | round(value,1)
356 rule '% aborted connections'
357 Aborted_connects / Connections * 100
359 Too many connections are aborted.
360 Connections are usually aborted when they cannot be authorized. <a href="http://www.mysqlperformanceblog.com/2008/08/23/how-to-track-down-the-source-of-aborted_connects/">This article</a> might help you track down the source.
361 %s% of all connections are aborted. This value should be below 1% | round(value,1)
363 rule 'rate of aborted connections'
364 Aborted_connects / Uptime
366 Too many connections are aborted
367 Connections are usually aborted when they cannot be authorized. <a href="http://www.mysqlperformanceblog.com/2008/08/23/how-to-track-down-the-source-of-aborted_connects/">This article</a> might help you track down the source.
368 Aborted connections rate is at %s, this value should be less than 1 per hour | PMA_bytime(value,2)
370 rule '% aborted clients'
371 Aborted_clients / Connections * 100
373 Too many clients are aborted.
374 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.
375 %s% of all clients are aborted. This value should be below 2% | round(value,1)
377 rule 'rate of aborted clients'
378 Aborted_clients / Uptime
380 Too many clients are aborted.
381 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.
382 Aborted client rate is at %s, this value should be less than 1 per hour | PMA_bytime(value,2)
386 rule 'Is InnoDB disabled?'
389 You do not have InnoDB enabled.
390 InnoDB is usually the better choice for table engines.
391 have_innodb is set to 'value'
393 rule '% InnoDB log size' [innodb_buffer_pool_size > 0]
394 innodb_log_file_size / innodb_buffer_pool_size * 100
396 The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.
397 Especiallay one a system with a lot of writes to InnoDB tables you shoud 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="http://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html">this blog entry</a>
398 Your InnoDB log size is at %s% in relation to the InnoDB buffer pool size, it should not be below 20% | round(value,1)
400 rule 'Max InnoDB log size' [innodb_buffer_pool_size > 0 && innodb_log_file_size / innodb_buffer_pool_size * 100 < 30]
401 innodb_log_file_size / (1024 * 1024)
403 The InnoDB log file size is inadequately large.
404 It is usually sufficient to set innodb_log_file_size to 25% of the size of {innodb_buffer_pool_size}. A very innodb_log_file_size slows down the recovery time after a database crash considerably. See also <a href="http://www.mysqlperformanceblog.com/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="http://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html">this blog entry</a>
405 Your absolute InnoD log size is %s MiB | round(value,1)
407 rule 'InnoDB buffer pool size' [system_memory > 0]
408 innodb_buffer_pool_size / system_memory * 100
410 Your InnoDB buffer pool is fairly small.
411 The InnoDB buffer pool has a profound impact on perfomance 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="http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/">this article</a>
412 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.
416 rule 'MyISAM concurrent inserts'
419 Enable concurrent_insert by setting it to 1
420 Setting {concurrent_insert} to 1 reduces contention between readers and writers for a given table. See also <a href="http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html">MySQL Documentation</a>
421 concurrent_insert is set to 0
423 # INSERT DELAYED USAGE
425 #Delayed_insert_threads 0
427 #Not_flushed_delayed_rows