How do I optimize MySQL's memory usage?

Solution 1:

This is a hard topic. Can't be answered here, entire books have been wrote about this. I recommend to make your innodb_buffer_pool_size big enough. If you're using myisam tables, then check key_buffer_size. Also table_cache and max_connections should be consider.

Here's something that can help you:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

EDIT:

As @drew010 said is important to consider the kind of use the DB has. The tunning for a heavy read DB is very different than a heavy write. Also, you may consider other strategies. Like Memcached, nosql databases, etc.

Solution 2:

If you need to be sure that MySQL uses all the memory it needs, you got to switch to InnoDB. Unless you use MySQL FULLTEXT search abilities, there won't be any serious problems.

In case of MyISAM a number of caches are put in use, including internal MySQL key buffers and system cache. If you want best performance you need to leave room for either of caches, but still I don't recommend MyISAM for that purpose especially if your databases are write-heavy: MyISAM locks whole table on write, whereas InnoDB locks only affected rows.

Even if you do put all you databases into memory you can still have slow-running queries because of insufficient indexes or other reasons.