Database Optimization
Posted: Thu May 07, 2009 10:39 pm
I notice a few things in my mysql database and think querys really need to be better.
Questions:
Why are there 0 cached querys?
Why is zoneminder scanning the entire table so often ?
I am not trying to offend anyone just want to know why no one has had a look at zonemindere's database usage. Its not a issue for me but on a large machine this could be very bad.
Examples from a dedicated zm box.
All below are high according to php myadmin.
Slow_queries 752 The number of queries that have taken more than long_query_time seconds.Documentation
Handler_read_rnd 587 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd_next 98 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have
Table_locks_waited 8,156 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
Opened_tables 100 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Questions:
Why are there 0 cached querys?
Why is zoneminder scanning the entire table so often ?
I am not trying to offend anyone just want to know why no one has had a look at zonemindere's database usage. Its not a issue for me but on a large machine this could be very bad.
Examples from a dedicated zm box.
All below are high according to php myadmin.
Slow_queries 752 The number of queries that have taken more than long_query_time seconds.Documentation
Handler_read_rnd 587 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd_next 98 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have
Table_locks_waited 8,156 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
Opened_tables 100 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.