Linux – How to determine how much memory the MySQL server needs

linuxmemoryMySQLUbuntu

I have a virtual server just for MySQL for a handful of apps. It's a 256 MB server. There is currently barely any data in there, but free -m shows full memory usage:

             total       used       free     shared    buffers     cached
Mem:           245        197         47          0         23        120
-/+ buffers/cache:         53        192
Swap:          511          2        509

This is on Ubuntu 10.04. All the configuration settings are default IIRC — key buffer is 16, query cache is 16. (I might be missing some

So, it seems to me that with barely any data and no special settings for the buffers, I should be using well below 256. Is 256 just the lower table for what an OS + MySQL need these days?

Best Answer

As Jeff mentioned, the usage numbers on Linux can be confusing. Linux will automatically keep a copy of files you access in RAM. If you need them again, they're quicker to access than going back to disk. If more RAM is needed by running programs, these cached items can be tossed entirely. This makes sense. Unfortunately, the free command shows you numbers in a way that includes those cached files, and makes it appear you're almost out of RAM.

You really want to look more at the second line, than the first. That one is the memory usage without the buffers/cache data included. In your case, it shows that you are using 53MB, and have 192MB free.

--Christopher Karel

Related Solutions

Linux Bash – How to Sort du -h Output by Size

As of GNU coreutils 7.5 released in August 2009, sort allows a -h parameter, which allows numeric suffixes of the kind produced by du -h:

du -hs * | sort -h

If you are using a sort that does not support -h, you can install GNU Coreutils. E.g. on an older Mac OS X:

brew install coreutils
du -hs * | gsort -h

From sort manual:

-h, --human-numeric-sort compare human readable numbers (e.g., 2K 1G)

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

Best Answer

Related Solutions

Linux Bash – How to Sort du -h Output by Size

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

Related Topic