MySQL Forums
Forum List  »  Performance

Re: Very high disk utilization by mysql server during reads.
Posted by: Rick James
Date: July 07, 2012 12:34PM

> I have 32G of RAM. There are few other Innodb tables also in the database and this is the only MyIsam table. I have assigned 24G of RAM to innodb buffer pool. I am using mysql 5.5.25 version.

You have left virtually no room for caching MyISAM data blocks. Those are cached by the OS in whatever space is left over.

key_buffer_size = 3500M would hold that entire index, but (as Aftab suggests), this may be overkill.

Decrease the buffer_pool to 16G or 20G.

Changing this table to InnoDB would speed up _this_ query because it would take advantage of the "clustering" of the PRIMARY KEY.

VARCHAR(36) smells like a GUID. You are not in trouble -yet-, but there are issues with indexing such.

What is the average size of the BLOB? If it is most of the bulk of the rows, and if you don't always need the blob when you SELECT, then I recommend doing "vertical partitioning" to put the blob (plus the PK) in another table.

Based on the EXPLAIN, and assuming the rows were inserted somewhat randomly, then there might have been about 434 SSD hits to get the 434 rows. (Remember, you have very little room to cache _data_ blocks.) How long was such a SELECT taking? I would guess about a second.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Very high disk utilization by mysql server during reads.
1250
July 07, 2012 12:34PM


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.