Home / server / Questions / 330522
Accepted
T. Brian Jones
Asked: 2011-09-28 14:06:21 +0800 CST

How do I optimize MySQL's memory usage?

  • 772

MY SETUP ( example )

I am running Linux on an Amazon High CPU Extra Large EC2 instance with the following specs:

  • 7 GB of memory
  • 20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units each)
  • 1690 GB of local instance storage
  • 64-bit platform

I have two large MySQL databases running on the MyISAM storage engine. One is 2GB and the other is 500MB. I'd like to make sure MySQL is using as much RAM as it can / needs to maximize query speeds. I know there are a bunch of MySQL memory config options like key_buffer_size and MyISAM_sort_buffer_size, but I'm unfamiliar with optimizing these.

QUESTIONS

  1. How does one check what memory MySQL is currently using on a Linux system?
  2. How does one maximize / optimize MySQL memory usage?
  3. Assuming my queries and schema are optimized, what other changes should I consider?
linux mysql memory

2 Answers

  1. Best Answer

    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.

    • 7

  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.

    • 1