MySQL Forums
Forum List  »  Performance

Re: slow queries and high % of wait I/O
Posted by: Rick James
Date: January 21, 2009 01:22AM

Increasing RAM -- that would essentially eliminate the disk I/O. Rule of thumb: 10x speedup. If your data grows beyond what you can cache (about 14GB), you will gradually slow down. But don't get more RAM -- read on...

* What is the last_visited_at? Maybe that needs to be outside the blob?
* Toss the id -- device_id can be the PRIMARY (and only) KEY.
* INSERT becomes BEGIN, SELECT FOR UPDATE, if missing then INSERT else { append, UPDATE }, COMMIT
* SELECT from history is still that, but you get only one row, then unserialize it.
* Consider compressing the blob -- cpu time is is pretty cheap (compared to disk). Hmmmm... This might even let you fit in 8GB of RAM.

Suggest you experiment with BLOBizing (with compression), say, on 1% of the rows. Do the math to see how big the table will become. Even if it comes out, say, 9GB, it will still be quite viable in 8GB of RAM (6GB cache). Why? Because your 25 fetches are down to 1. That is, even if you always have to hit disk, it would perform very well.

Options: ReplyQuote

Written By
January 19, 2009 05:36PM
January 19, 2009 11:37PM
Re: slow queries and high % of wait I/O
January 21, 2009 01:22AM
January 20, 2009 01:28AM
January 20, 2009 08:41PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.