I have a VM which has 2GB Ram, (full specs) And I am setting up a site which has one table in particular with over a million records. There's little or no usage of this particular database (perhaps once or twice a day) but simply running mysql grinds the whole server to a halt. I've looked through the top results but nothing is really denting the CPU however the memory seems to be the issue. The site isnt even live of taking requests yet. the memory situation looks like this:

# free -m
             total       used       free     shared    buffers     cached
Mem:          2006       1880        126          0          3         53
-/+ buffers/cache:       1823        183
Swap:         2047        345       1702

Are there any good pointers to tune mysql to stop hogging the system memory?

Thanks very much

EDIT: (requested by 8bit):


  • My first question would be - how do you know it's MySQL which is hogging the memory? Also 'grinds the server to a halt' in what sense?
    – EightBitTony
    Sep 28, 2012 at 10:35
  • Q1: top command shows mysql using 1.5 GB memory. Q2: Apache becomes very unresponsive and takes about 1 min to load a basic page (the is only me using the server/ viewing pages)
    – Alex
    Sep 28, 2012 at 11:19
  • Can you output the result of a SHOW STATUS GLOBAL query and add it to the question?
    – EightBitTony
    Sep 28, 2012 at 11:24
  • tny.cz/b41a0b12
    – Alex
    Sep 28, 2012 at 12:32
  • Sounds to me like you need to do some work on your database design, starting with ensuring you have appropriate indexes.
    – John Gardeniers
    Sep 28, 2012 at 13:31

5 Answers 5


MySQL should include a number of starting point configuration files for my.cnf that can adjust the memory usage. The files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf.

There is an online MySQL memory usage calculator at http://www.mysqlcalculator.com/. By comparing the starting point files and using the memory usage calculator you should be able to adjust your my.cnf configuration file to accomodate your server's memory constraints.

For more information about what each parameter does, you can reference the MySQL documentation available online. Here is a link to the MySQL documentation for MySQL server 5.1: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

  • I'll have a good look at these. In theory the server has enough resources to handle the load easily. And on my local system it's really smooth. Thnks for these
    – Alex
    Sep 28, 2012 at 12:34

Check http://mysqltuner.pl/mysqltuner.pl script and parameters it's checking for memory use:

badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n"
  • rrr, perls not my forte, it was the 90s last time I wrote any :-) I'll have a good look through though. Thanks a lot for the ref
    – Alex
    Sep 28, 2012 at 12:38
  • there's nothing special, just check pointers, i'm busy, sorry :D
    – GioMac
    Sep 28, 2012 at 16:20

I doubt it is the MySQL server itself but the queries that it is processing that is your culprit.

Have you enabled any form of query logging (e.g. the slow query log in MySQL as well as capturing any queries from within your web app)? Perhaps they could be optimized to improve performance.

Running the mysqltuner.pl script mentioned by GloMac as well as the tuning-primer.sh script will also help point out configuration issues.


My first thought was: do you do a full table scan? and if yes, is this necessary? In most cases an index would help to at first improve performance of the database and at second the mysql database wouldn't need to load the whole database into memory, only the needed rows.


Your server is probably grinding to a halt because it is at near full memory consumption before MySQL is running and starting MySQL pushes you into utilizing swap space. The SHOW STATUS output doesn't help much (use SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; next time), but it at least shows that your innodb_buffer_pool_size isn't large and you've left most values at default. Therefore, I doubt you have MySQL configured in a way that will exhaust most of your memory.

The following tunable parameters define the global memory buffers that MySQL will use:

MyISAM: key_buffer_size
InnoDB: innodb_buffer_pool_size

Your worst case memory usage is largely contingent on the sum of these two values; per-session buffers typically don't have as much memory overhead and cause scripts like mysqltuner to give extremely unreasonable maximum memory usage estimates to the point that they are largely worthless to look at.

You should run the following command to see how much of your total memory is attributed to MySQL:

ps -orss -U mysql

If MySQL is utilizing most of your memory, from here you should consider tuning the variables listed above as well as resetting the following to their default values if you made the mistake of changing them:


Keep in mind that if you lower the allowed memory usage of MySQL you will be increasing I/O utilization.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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