I put a lot of work into optimizing our database queries over the years, but I always focus on our select queries. Since we are an ecommerce store, the majority of the queries we do are selects from the product catalog and even though the SQL in the selects can be rather large, they are very quick. Now that those have been cleaned up for awhile, I am noticing that the queries that seem to really take the longest are the ones that write data – inserts and updates. I went over the last 100 queries added to our mysqlslow.log file (queries that run 5 seconds or longer), and about 68 of them are updates and 19 are inserts. Now that I’ve conquered the select beast, what can I do to improve our other queries (or maybe the underlying performance issue?)?
And for the record, 90% of those queries are overly pure inserts or updates without any advanced select intos or joins. Also, we moved to a new, dedicated server just last year, so I am hoping hardware isn’t to blame here.
Snip of My.cnf file (mostly defaults I imagine):
set-variable = read_buffer_size=2M
set-variable = key_buffer=512M
set-variable = max_connections=200
set-variable = max_allowed_packet=12M
set-variable = table_cache=2048
set-variable = sort_buffer=5M
set-variable = record_buffer=2M
set-variable = net_buffer_length=16K
set-variable = myisam_sort_buffer_size=10M
set-variable = thread_concurrency=2
set-variable = tmp_table_size=512M
# mysql 4 stuff
set-variable = query_cache_limit=12M
set-variable = query_cache_size=512M
set-variable = query_cache_type=1
I’m guessing most of your query improvements have come about due to improved indexing on your tables. Unfortunately, there does come a point when you do have to pay a price for all those indexes, and that time is when you insert or update records. What’s happening is each index that is applied to that table will have to be checked to see whether they need to be updated. THEN, all of those indexes that need to be updated will all have to be re-indexed which does take time depending on how many records that can take. So there will always be some latency there.
You may have a problem with the db server OR you could have a latency issue between the db server and the web server. I ran into an issue where the pipe between the server was too slow - a query could run quickly on the db server, but when it called from the webserver, the latency was killing up until we corrected the latency issue between the two servers (it had to do with the security settings on the network and the packets getting scanned twice for each packet, one leaving the server and one on the receiving server, then rinse and repeat.
Thanks Dave. I expected indexes to slow down some queries, but I didn’t think I over indexed any tables either. I’m also seeing one query rather often, but the query shouldn’t be touching indexes. Here’s the query I’m looking at:
# Time: 120210 2:40:06
# User@Host: <snip>[<snip>] @ localhost []
# Query_time: 5.032839 Lock_time: 0.002408 Rows_sent: 0 Rows_examined: 1
SET timestamp=1328859606;
UPDATE Options
SET qty_on_hand = 0
WHERE id = 4060973;
Very simple query. id has a unique key on it and qty_on_hand is not indexed. There are two other indexes that each watch two columns, but I wouldn’t think they would come in to play on an update query.
Also for reference, the table is <10k rows.
No, that shouldn’t take more than 5 seconds to update that record, especially with < 10K records. The only thing I could see that might cause something like that (and it’s a stretch) would be if some of the indexes are looping on themselves (i.e. there’s a 1:many relationship from TableA to Table B and a 1:many relationship from Table B to Table A). Or triggers which might get fired off on insert or update? Maybe? Perhaps?
No indexes or relationships dealing with Options.qty_on_hand. We also don’t use triggers on any of our tables, so that’s not it either.
The other thing I should have added is that we process these updates in a batch, so when these show up in the log, there are a number of the same style updates happening around them (maybe 100 or so?). Only a few of the 100 are showing up in my slow log. Are they queuing because the hard drives can’t keep up or something? Is there a better way to update ~100 random rows with random values?
Sorry - didn’t get back in here over the weekend…
Batch updating could be part of the problem, and there might be a logjam in the buffer/cache, but I don’t know for sure. Are these updates running from the webserver, or the database server? Personally, I’m thinking there’s a configuration problem either in mySQL itself, or in your database instance. The only times I’ve seen problems like that (though they weren’t mySQL) were configuration problems. The problem I’ve resolved dealt with…
[LIST]
[]Overzealous virus scanning (this was a networking issue as scanning was occurring when something left one box and hit the next on the same network)
[]Permission problems on the filesystem
[*]indexes incorrectly built.
[/LIST]Sorry I can’t give you more guidance, but the problem doesn’t seem to be the query, but something else entirely…
Thanks for helping Dave – this is all over my head really. We are on a single server environment, so the web server is also the database. What can I do to start to narrow down the three possibilities you mentioned? I’m pretty sure 1 won’t be the issue since the database server is localhost. How do I double check the permissions and indexes are correct?
Hmm. These queries are not run as a cron, and the times that they appear in the slow log are kind of all over the place. Not sure how I would begin to look at other processes running at the same time. Looks like it may be time to get my host involved to see if they can offer any insight (sadly, they are not MySQL guru’s either).
Going to bump this just to see if anyone else has any thoughts on this. I looked through the slow log for this month so far and it is still being dominated by updates and inserts that should be very simple (in my mind anyways). 72% of the queries this month have been updates and 21% were inserts… Still scratching my head where to begin with this.