up vote 1 down vote favorite
share [g+] share [fb]

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
link|improve this question
90% accept rate
feedback

2 Answers

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.

link|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
feedback
up vote 1 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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