Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

http://tny.cz/b41a0b12

share|improve this question

migrated from serverfault.com Oct 1 '12 at 20:46

This question came from our site for professional system and network administrators.

    
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 '12 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 '12 at 11:19
    
Can you output the result of a SHOW STATUS GLOBAL query and add it to the question? –  EightBitTony Sep 28 '12 at 11:24
    
tny.cz/b41a0b12 –  Alex Sep 28 '12 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 '12 at 13:31
show 3 more comments

5 Answers 5

up vote 3 down vote accepted

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

share|improve this answer
    
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 '12 at 12:34
add comment

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"
share|improve this answer
    
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 '12 at 12:38
    
there's nothing special, just check pointers, i'm busy, sorry :D –  GioMac Sep 28 '12 at 16:20
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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:

read_buffer_size
join_buffer_size
sort_buffer_size
read_rnd_buffer_size

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.