MySQL Forums
Forum List  »  Newbie

Re: re writing database
Posted by: Rick James
Date: April 09, 2009 09:20AM

RAM is used as a "cache". The 'unit' of transfer to/from disk depends on a lot of things, usually 1KB-16KB.

MyISAM tables are cached in two ways:
* Index blocks are cached in the key buffer; suggest setting key_buffer size to 20% of available RAM.
* Data blocks are cached by the Operating System, as it does with any file I/O.

InnoDB tables are cached in its 'buffer pool'. Suggest setting innodb_buffer_pool_size to 70% of available RAM. All data and index blocks, as they are needed, are transferred to/from the buffer pool. The block size is 16KB.

If you use both engine types, lower the recommendations above.

MEMORY type tables are ONLY in memory. And you have to do your own loading. And if the system crashes, you lose the whole table.

It is usually not advised to pre-load stuff in the cache(s). But you could. There is a command to preload the key_buffer (MyISAM only). Or, doing something like this would load all of one index:
SELECT avg(indexed_col) FROM tbl;
This would preload all the data for one table:
SELECT avg(non_indexed_col) FROM tbl;
But, if the index or data is too big for the cache space, you are really wasting your time.

A better pre-load scheme is to apply some 'representative' queries taken directly from your application. This will hit both data and index blocks, a do it in a realistic way.

Options: ReplyQuote


Subject
Written By
Posted
April 08, 2009 07:35AM
Re: re writing database
April 09, 2009 09:20AM


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.