Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just upgraded my RAM on my server from 4 GB to 16 GB and I'm told that I can utilize this memory to a huge advantage with certain MySQL settings if I get then just right. Do you know of any guides that could walk me through some steps I need to look into? What are the biggest settings I should research?

update

Where can I find a simple explination of MySQL's cache optimization features....

My current configuration:

query_cache_limit = 1M
query_cache_size = 16M
share|improve this question

2 Answers 2

up vote 4 down vote accepted

General (and likely overly broad) recommendations:

  • Try and fit your whole dataset in memory if possible.
  • Optimize your queries and make the cache as efficient as possible.
  • Log slow queries.

Resources:

  • mysqltuner.pl is a script in fairly widespread use written by a former colleague of mine. It should give you some basic performance improvements and sane settings given your hardware setup, but this is only a starting point.

  • The MySQL Performance Blog is a great resource. Be sure to check out their book, High Performance MySQL. Here is an excellent post to get you started: http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/

  • MySQLForge has some tips that should generally be followed as solid advice, if sometimes a little outdated. The part where it mentions "hire a DBA" may be extremely relevant as your needs grow.

  • MySQL by Paul DuBois is generally regarded as the MySQL Bible. One of the better technical books I have ever read, actually.

share|improve this answer
    
I'm hoping for a simpler source that the one you mentioned... I don't have perl on my server (at least I don't think) –  Webnet Nov 7 '10 at 4:21
    
I would imagine you do. I can't recall a Linux distro that doesn't include Perl as part of at least a core install. If not, you can certainly install it. –  Sam Halicke Nov 7 '10 at 4:25
    
Try and fit your whole dataset in memory if possible. - What do you mean by that? –  Webnet Nov 7 '10 at 23:14
    
@Webnet for example with InnoDB, a good starting point is to make the innodb_buffer_pool_size larger than the size of your database + 10%. –  Sam Halicke Nov 8 '10 at 1:49

High Performance MySQL, Second Edition -- I've only read the first edition but it taught me tons. The second edition is almost twice as many pages when I glanced at it.

MySQL query cache simple explanation -- It's only a page and the first paragraph tells you what it does.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.