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

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?
share|improve this question

migrated from stackoverflow.com Nov 13 '11 at 12:14

This question came from our site for professional and enthusiast programmers.

    
you can check with htop program. –  neworld Sep 27 '11 at 22:08
    
Maximizing memory usage won't the whole database into memory. You have to have RAM-based filesystem... –  user57260 Sep 27 '11 at 22:10
4  
RAM-based filesystem is unsafe. If server crash all data will be lost –  neworld Sep 27 '11 at 22:32

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
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 '11 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 '11 at 23:44

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.

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.