MySQL Forums
Forum List  »  Performance

Re: Sudden drop in read performance
Posted by: Rick James
Date: June 12, 2012 11:03PM

> Do the indexes need to be rebuilt occasionally on MyISAM tables?
In general, no. Some possible explanations:

* There was a lot of churn, leading to index blocks being not very full. Rebuilding the indexes (OPTIMIZE TABLE) would shrink the index, thereby making it more cacheable. While, in theory, this can speed up things, it rarely makes any difference.

* Running the same query twice -- usually it runs 10x faster the second time because of caching. This fools people into thinking that something significant changed.

* Changing key_buffer_size -- The key_buffer is where index blocks (for MyISAM) are kept in RAM. Changing the size _may_ change from a SELECT being well cached versus being I/O bound.

* Switching to InnoDB leads to potentially significant improvements (or degradations) in index performance. I list the differences here:
http://mysql.rjweb.org/doc.php/myisam2innodb
In particular, fetching via PK is more efficient in InnoDB.
> The queries only use the primary key...

Note that you are near the boundary...
> INDEX_LENGTH - 1.05 GB
> key_buffer_size = 256M
That is, an "index scan" would effectively be I/O bound. Since you have 8GB of RAM (and assuming everything is 64-bit), I recommend 1200M for key_buffer_size. For further discussion (and notes on InnoDB), see
http://mysql.rjweb.org/doc.php/memory

If `SOURCE` does not need to contain anything other than English characters (or latin1), the I suggest you change it away from utf8. This will shrink certain operations by 3x.

If you don't need a max value of 9999999999999.99, I suggest you shrink the 15 in
`M_VALUE` decimal(15,2)
M_VALUE is occupying a constant 8 bytes (I think).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Sudden drop in read performance
1234
June 12, 2012 11:03PM


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.