Portal Home > Knowledgebase > Articles Database > Tuning MySQL for really slow writes
Tuning MySQL for really slow writes
| Posted by Amarsir, 12-19-2010, 03:26 PM |
| I've had this problem for far too long and it baffles me. I have a LAMP VPS that hosts about a thousand regular visitors on custom pages with a lot of DB reads and a fair number of writes too.
What I've found is that SELECTs, even complex sorts of unindexed fields in long tables, take milliseconds as expected. But writes, even a simple update of a small table off the primary key (and sole index) can take up to 2 minutes. And not consistently: the same INSERT can be instant for a while then an hour later it's a 40-second query. Slow queries occur basically in the same ratio of all writes, so it's not about any certain query or table. And it's not even a lot of slow queries really. It's just the ones that are long are really really long.
The tables in question are all InnoDB, so there shouldn't be any table-locking involved, right? (I have some MyISAM around the DB but almost never write to them.) Below is a copy of mysqlreport and to the extent that I understand it (or tuning-primer.sh, or innotop, etc) nothing jumps out as a problem. The VPS has 1 GB of memory. The entire DB is only 100mb large, and I allocated 500mb to the buffer pool so there shouldn't be any disk waits involved.
I'm out of ideas and don't know where to look. If it's something from the VPS's shared service I wouldn't know what to look for, though I could believe it. And if it's hardware limited I wouldn't mind upgrading, but I just don't see where it is.
Many thanks for any help or ideas.
|
| Posted by IDediServer Kevin, 12-19-2010, 06:23 PM |
| During these writes use iotop or top, do you have I/O wait% ? (asking due to this being a VPS) iSCSI disk ?
Also this looks like mysqlsla output so I assume you have 0 query_cache ?
That appears to be the case, otherwise a large query cache can cause this.
|
| Posted by Amarsir, 12-20-2010, 05:11 AM |
| Now that you mention it, the I/O wait% is usually 0 but I have seen it bounce around 8-14% during these troublesome periods. I haven't watched that enough to know if it's always correlated or causal, but that's a good question, it might be related.
My query cache size is zero, but here are the related attributes:
|
| Posted by Amarsir, 12-20-2010, 06:14 AM |
| Here's a screenshot during one of the awkward waits. The top connection is running innotop and the bottom is running top:
http://forums.simunomics.com/images/Waiting.JPG
Although wait% is 9.3% there, it doesn't necessarily correlate. As I'm typing this I'm looking at 0% wa, 99.9% idle, and still 40 second updates.
|
| Posted by x86brandon, 12-20-2010, 05:58 PM |
| I don't suppose you have replication running? Delays on inserts and updates can often be attributed to binary logging
Also, are you sure the INSERT is taking long and not one of those UPDATES? If you don't mind, feel free to share query and schema info with me in private and I would be happy to take a look.
|
| Posted by Amarsir, 12-20-2010, 08:40 PM |
| I'm pretty sure there's no replication. Not only do I have no idea where it would be replicating to, but that would show up on the processlist would it not? I don't see any connections there that shouldn't be.
I think inserts are just as bad as updates, it's just that updates happen more frequently. But I see what you're getting at. Here's an example of the most common query from the most frequently modified table, which not coincidentally Jet Profiler tells me is the one that has occupied the most time. This certain example took 113 seconds:
And here's the table. It does have foreign keys (and thus indexes) but those values almost never change once created. (Rows get deleted rather frequently too: the index is at 1.1 million but there's only around 115k rows.)
|
| Posted by x86brandon, 12-20-2010, 09:03 PM |
| do
show index from stored_list;
Updates essentially do a select, delete and then insert.. more I/O operations than just an insert and if you are doing an update against something missing an index, it will force a full table scan.
|
| Posted by Amarsir, 12-20-2010, 10:12 PM |
| show index from stored_list;
Does that tell us anything?
|
| Posted by x86brandon, 12-20-2010, 10:33 PM |
| Well, I don't think bigint is needed for that kind of row count, so cutting that back to int and re-creating the index will help. My guess is none of those need to be anything but int.
|
| Posted by Amarsir, 12-20-2010, 10:44 PM |
| OK I admit the extra 4 bytes are probably overkill. But you must agree that wouldn't cause 2-minute updates. Especially given that there's more than enough memory allocated for both the index and the table as a whole.
I appreciate the thought. Any other ideas?
|
| Posted by x86brandon, 12-20-2010, 11:10 PM |
| My guess is that perhaps a process upstream is causing the delay and causing deadlocks. Have you captured a full processlist when this is happening? If you haven't already, turn on the slow query log as well as log queries not using indexes.
log-slow-queries
log-queries-not-using-indexes
|
Add to Favourites 
Print this Article
Also Read
