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!
How to optimise MySQL?
Moderator: General Moderators
Re: How to optimise MySQL?
Query optimization is a challenging area, and I hasten to state that I'm not an expert in this area. There are countless resources online, such as this .pdf: http://www.percona.com/files/presentati ... zation.pdf, but they are not always easy to understand. I think I would start by collecting every SQL query from all your scripts and printing them out, with space between them to add notes. Then determine what indexes you have created, and print a list of those. Then read a few of the online resources and when you understand a particular explanation for inefficient query syntax, check your queries to see if you have any like that. I don't know what more help we can give, short of you telling us all the details of your database: how many tables, what relationships exist, how many records per table, etc. and all your queries. That might be more than anyone here has time to wade through, but maybe you could tackle just a few queries at a time.
Re: How to optimise MySQL?
Thanks for the link! I'll have a look through it.
- mecha_godzilla
- Forum Contributor
- Posts: 375
- Joined: Wed Apr 14, 2010 4:45 pm
- Location: UK
Re: How to optimise MySQL?
Hi,
The flip-side of indexes is having too many of them, which can slow things down as well. It sounds like the sort operation is the main issue but you need to check what JOINs you're using (if any) because this might be causing issues as well - as a general rule, it's a good idea to index columns that you're using for joins, not just those that you're checking for matches (plus, using OFFSET can also slow things down as well I think). Remember to check the cardinality of your indexes as well. As califdon has said, the only way to work out how optimal everything is is by looking at your database schema (or using a 3rd party script to analyse it) and seeing if it's optimised for your queries or seeing if your queries could be rewritten to improve performance. The good news in this respect is that a simple query rewrite often solves serious problems, and it's rarely a lack of server overhead that's the root cause.
If the search terms used are always the same ones and the articles don't change that often then you could cache the results in some way (some 3rd party database abstraction layers will do this for you). The other option is to optimise the MySQL configuration settings for your database so that more overhead is available for these types of operations - I'm no expert on this but I found out a lot by just making the effort to read-up on all the various settings and then making changes based on my limited understanding of what was happening. The easy thing to do is run one of your searches and see how the MySQL statistics change before and after the queries have been completed, which should be helpful as a starting point at least. Echo()ing out the query execution time to the page somewhere (perhaps as a comment) might also give you an idea of where the problem lies.
HTH,
Mecha Godzilla
The flip-side of indexes is having too many of them, which can slow things down as well. It sounds like the sort operation is the main issue but you need to check what JOINs you're using (if any) because this might be causing issues as well - as a general rule, it's a good idea to index columns that you're using for joins, not just those that you're checking for matches (plus, using OFFSET can also slow things down as well I think). Remember to check the cardinality of your indexes as well. As califdon has said, the only way to work out how optimal everything is is by looking at your database schema (or using a 3rd party script to analyse it) and seeing if it's optimised for your queries or seeing if your queries could be rewritten to improve performance. The good news in this respect is that a simple query rewrite often solves serious problems, and it's rarely a lack of server overhead that's the root cause.
If the search terms used are always the same ones and the articles don't change that often then you could cache the results in some way (some 3rd party database abstraction layers will do this for you). The other option is to optimise the MySQL configuration settings for your database so that more overhead is available for these types of operations - I'm no expert on this but I found out a lot by just making the effort to read-up on all the various settings and then making changes based on my limited understanding of what was happening. The easy thing to do is run one of your searches and see how the MySQL statistics change before and after the queries have been completed, which should be helpful as a starting point at least. Echo()ing out the query execution time to the page somewhere (perhaps as a comment) might also give you an idea of where the problem lies.
HTH,
Mecha Godzilla