2014-10-17, 07:11 AM
Hi,
I an getting the following issues when I check phpMyAdmin/Status/Adviser, and I could use some guidance fixing it or at least what to change to fix the bigger issues. Using MyBB 1.6.15.
Thanks.
I an getting the following issues when I check phpMyAdmin/Status/Adviser, and I could use some guidance fixing it or at least what to change to fix the bigger issues. Using MyBB 1.6.15.
Thanks.
Quote:{long_query_time} is set to 10 seconds or more, thus only slow queries that take above 10 seconds are logged. 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.
The slow query log is disabled. Enable slow query logging by setting log_slow_queries to 'ON'. This will help troubleshooting badly performing queries.
The query cache is not enabled. 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'. Note: If you are using memcached, ignore this recommendation.
Too many sorts are causing temporary tables. Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits
There are lots of rows being sorted. 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
There are too many joins without indexes. 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
The rate of reading the first index entry is high. 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.
The rate of reading data from a fixed position is high. 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.
The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.
Many temporary tables are being written to disk instead of being kept in memory. 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 Article by the Pythian Group
MyISAM key buffer (index cache) % used is low. 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.
The rate of opening tables is high. Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.
Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
Thread cache is disabled, resulting in more overhead from new connections to MySQL. Enable the thread cache by setting thread_cache_size > 0.
There are too many threads that are slow to launch. This generally happens in case of general system overload as it is pretty simple operations. You might want to monitor your system load carefully.
The maximum amount of used connections is getting close to the value of {max_connections}. 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.
Too many connections are aborted. Connections are usually aborted when they cannot be authorized. This article might help you track down the source.
Too many clients are aborted. 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.
The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. 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 this blog entry
