9

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?
Improve this question
3
  • you can check with htop program.
    – neworld
    Sep 27, 2011 at 22:08
  • Maximizing memory usage won't the whole database into memory. You have to have RAM-based filesystem...
    – user57260
    Sep 27, 2011 at 22:10
  • 4
    RAM-based filesystem is unsafe. If server crash all data will be lost
    – neworld
    Sep 27, 2011 at 22:32

2 Answers 2

Reset to default
7

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.

Improve this answer
2
  • 2
    Agreed, they are pretty small databases too relatively speaking. If you are expecting a ton of database read access, then you may consider some sort of caching system like memcached that your application can use to store values from the database to reduce hits to the database. If you are doing mostly inserts, then disk is going to be a very important factor. To some degree the question depends on what your use cases are. If the data isn't changing much, then the mysql query cache should help out a lot with faster queries too. Also make sure your indexes/primary keys are good.
    – drew010
    Sep 27, 2011 at 23:32
  • +1 you're right brother. I supposed that he was asking for basic tunning, but is important to consider the use of the DB and other things. Again, can't be discussed here, i think.
    – santiago.basulto
    Sep 27, 2011 at 23:44
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .