MySQL advices to optimize zabbix queries
Through MySQL Enterprise Monitor applied to zabbix database I have found this issues an recomendations to improve zabbix performance.
1. Temporary Tables To Disk Ratio Excessive.
Problem Description
If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory.
Also, tables that have TEXT or BLOB columns are automatically placed on disk.
For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk.
Advice
If Query Analyzer is enabled, browse to the Query Analyzer tab and set the filters to look for queries that return lots of data issued shortly before the time of this alert (i.e. where bytes returned is greater than tmp_table_size, which is currently 512 MB, or greater than max_heap_table_size, which is currently 512 MB). Also look for statements with ORDER/GROUP BY and/or UNION/Subquery clauses, all of which can trigger temporary table creation.
In addition, review your application for any statements that cause temporary table creation that are against tables that contain BLOB or TEXT columns, to see if those columns can be changed to another type (such as TEXT to a large VARCHAR column in MySQL server version 5.x and later).
Lock Contention Excessive
Problem Description
Performance can be degraded if the percentage of table operations that have to wait for a lock is high compared to the overall number of locks. This can happen when using a table-level locking storage engine, such as MyISAM, instead of a row-level locking storage engine.
Advice
Check that no sessions are using the "LOCK TABLES" statement unnecessarily. If this is not the root of the problem, look for sessions with a "Locked" state within "SHOW FULL PROCESSLIST", and determine which tables those sessions are selecting from. If these tables use the MyISAM storage engine, they could be candidates for migration to a row-level locking storage engine.
Table Scans Excessive
Problem Description
The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together - which reflect the number of rows read via full table scans - are high compared to the sum of Handler variables which denote all row accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.
Advice
If Query Analyzer is enabled, browse to the Query Analyzer tab and set the filters to look for long-running queries issued shortly before the time of this alert, queries where avg or max execution time has increased a lot from one interval to another, or queries where max execution time is much greater than avg execution time.
Another option is to turn on the Slow Query Log and monitor what goes into it. Statements that are logged there are candidates for tuning. Once you have found tuning candidates, use the EXPLAIN statement on the queries to see which tables should have indexes added to them.
If you are using MySQL 4.1 or later you can use the --log-queries-not-using-indexes option to log all statements that do a full table scan, even if they would not otherwise qualify for the slow query log.
Note that full table scans are not necessarily bad, as long as they are confined to very small tables, so be sure to take table size into account as you review your queries and their EXPLAIN plans.
Just in case they could help you.
Best regards!
1. Temporary Tables To Disk Ratio Excessive.
Problem Description
If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory.
Also, tables that have TEXT or BLOB columns are automatically placed on disk.
For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk.
Advice
If Query Analyzer is enabled, browse to the Query Analyzer tab and set the filters to look for queries that return lots of data issued shortly before the time of this alert (i.e. where bytes returned is greater than tmp_table_size, which is currently 512 MB, or greater than max_heap_table_size, which is currently 512 MB). Also look for statements with ORDER/GROUP BY and/or UNION/Subquery clauses, all of which can trigger temporary table creation.
In addition, review your application for any statements that cause temporary table creation that are against tables that contain BLOB or TEXT columns, to see if those columns can be changed to another type (such as TEXT to a large VARCHAR column in MySQL server version 5.x and later).
Lock Contention Excessive
Problem Description
Performance can be degraded if the percentage of table operations that have to wait for a lock is high compared to the overall number of locks. This can happen when using a table-level locking storage engine, such as MyISAM, instead of a row-level locking storage engine.
Advice
Check that no sessions are using the "LOCK TABLES" statement unnecessarily. If this is not the root of the problem, look for sessions with a "Locked" state within "SHOW FULL PROCESSLIST", and determine which tables those sessions are selecting from. If these tables use the MyISAM storage engine, they could be candidates for migration to a row-level locking storage engine.
Table Scans Excessive
Problem Description
The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together - which reflect the number of rows read via full table scans - are high compared to the sum of Handler variables which denote all row accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.
Advice
If Query Analyzer is enabled, browse to the Query Analyzer tab and set the filters to look for long-running queries issued shortly before the time of this alert, queries where avg or max execution time has increased a lot from one interval to another, or queries where max execution time is much greater than avg execution time.
Another option is to turn on the Slow Query Log and monitor what goes into it. Statements that are logged there are candidates for tuning. Once you have found tuning candidates, use the EXPLAIN statement on the queries to see which tables should have indexes added to them.
If you are using MySQL 4.1 or later you can use the --log-queries-not-using-indexes option to log all statements that do a full table scan, even if they would not otherwise qualify for the slow query log.
Note that full table scans are not necessarily bad, as long as they are confined to very small tables, so be sure to take table size into account as you review your queries and their EXPLAIN plans.
Just in case they could help you.
Best regards!
Thanks for your effort anyway!
Comment