How to optimise MySQL?
Posted: Wed Mar 13, 2013 10:21 pm
Recently my webhost was complaining that my database programming was placing a lot of strain on the server; I was told to add indexes, so I added a few that seemed relevant and it apparently helped some, but I'm still pretty new to MySQL programming (I taught myself the basics through online tutorials) and I'm not sure what else I might be doing wrong.
All the following were red-flagged, and I don't understand what each means:
Slow_queries: 1.5k
Innodb_buffer_pool_reads: 32.2M
Innodb_log_waits: 758
Innodb_row_lock_time_av: 16
Innodb_row_lock_time_max: 1.1k
Innodb_row_lock_waits: 225
Handler_read_rnd: 1.1G
Handler_read_rnd_next: 3.5G
Slow_launch_threads: 2
Created_tmp_disk_tables: 17.4M
Delayed_errors: 19.9k
Key_reads: 539.6M
Select_full_join: 729.7k
Select_range_check: 23.7k
Sort_merge_passes: 21.7k
Opened_tables: 75.1M
Table_locks_waited: 35.4k
What are the priority problems here, and what are some of the things I should look into in order to fix them?
For the Handler_read_rnd, it says: "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."
I do have a lot of queries that require sorting of results, mostly listing >2.7k articles in chronological order (split into 16 portions by year), or other lists in alphabetical order. Is there a way to fix the problem while still enabling the sorting?
and for Handler_read_rn_next it says: "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."
I'd really appreciate any help. Thanks!
All the following were red-flagged, and I don't understand what each means:
Slow_queries: 1.5k
Innodb_buffer_pool_reads: 32.2M
Innodb_log_waits: 758
Innodb_row_lock_time_av: 16
Innodb_row_lock_time_max: 1.1k
Innodb_row_lock_waits: 225
Handler_read_rnd: 1.1G
Handler_read_rnd_next: 3.5G
Slow_launch_threads: 2
Created_tmp_disk_tables: 17.4M
Delayed_errors: 19.9k
Key_reads: 539.6M
Select_full_join: 729.7k
Select_range_check: 23.7k
Sort_merge_passes: 21.7k
Opened_tables: 75.1M
Table_locks_waited: 35.4k
What are the priority problems here, and what are some of the things I should look into in order to fix them?
For the Handler_read_rnd, it says: "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."
I do have a lot of queries that require sorting of results, mostly listing >2.7k articles in chronological order (split into 16 portions by year), or other lists in alphabetical order. Is there a way to fix the problem while still enabling the sorting?
and for Handler_read_rn_next it says: "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."
I'd really appreciate any help. Thanks!