MySQL Forums
Forum List  »  Performance

Which memory settings to change?
Posted by: BJ Quinn
Date: January 26, 2009 06:22PM

Ok, I've read a bunch of stuff out there, and haven't come to much of a conclusion.

Most of the stuff out there sounds something like "setting innodb buffer pool size to 64MB may crash your system if you don't have enough available memory!!!". Ok, well I have 8GB of memory, and I'd pretty much like to allocate just about all of it.

I understand that both innodb_buffer_pool_size and key_buffer are important settings. I seem to only be able to find posts and articles about how to tune MySQL for a server circa 2000 or some beast with 128GB of memory or something.

So what I've done is start with the my-huge.cnf. Oh yeah, huge. innodb_buffer_pool is 384MB, key_buffer is 384MB. I guess that's Moore's law in action for you. Anyway, there seem to be dozens of settings (table_cache, sort_buffer_size, read_buffer_size, read_rnd_buffer_size, myisam_sort_buffer_size, etc., etc.), and I'm not sure if the order of magnitude increase in memory would increase what most of those values should be.

I have only a few dozen connections at a time, I'm just looking to optimize query performance since many of the queries are quite intensive.

So my questions are -

1. innodb_buffer_pool correlates to InnoDB. I've heard that key_buffer correlates with MyISAM. Is this correct?
2. Are there really any major improvements to be had by manipulating any memory or caching settings other than those two?
3. Since innodb_buffer_pool allocates memory specifically for InnoDB, would I get more efficient memory usage by just converting all my MyISAM tables to InnoDB and setting innodb_buffer_pool to ~80% of available memory? Would key_buffer or any other settings likely need to be increased from their default (my-huge.cnf) values?

Options: ReplyQuote

Written By
Which memory settings to change?
January 26, 2009 06:22PM
January 28, 2009 12:39AM
January 28, 2009 04:46PM
January 29, 2009 01:24AM
January 29, 2009 02:14PM
February 03, 2009 09:53AM

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.