I'm trying to help a friend with his server after he told me his MySQL was using too much memory... It turns out it's not only using a lot of memory, but there are way too many mysql process running!

Here's the result of ps auxww|grep mysql: http://pastebin.com/kYrHLXVW

So basically there are 13 MySQL processes, and there is only 1 client connected according to mysqladmin...

After a while, each of these process eat up to 50mb of memory so it ends up consuming a LOT of memory...

I'm using the my-medium.cnf config template straight from /usr/share/mysql... I restarted the mysql server but, as soon as it starts, the 13 processes are back... I have no idea what the problem could be... any ideas/suggestions would be really appreciated!

mysql     9804  0.0  0.6 58556 22960 pts/0   S    12:43   0:00  \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myhostname.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
mysql     9807  0.0  0.6 58556 22960 pts/0   S    12:43   0:00      \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myhostname.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
mysql     9808  0.0  0.6 58556 22960 pts/0   S    12:43   0:00          \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myhostname.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
mysql     9809  0.0  0.6 58556 22960 pts/0   S    12:43   0:00          \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myhostname.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
mysql     9810  0.0  0.6 58556 22960 pts/0   S    12:43   0:00          \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myhostname.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
[ ... repeated output truncated ... ]

They're not using 13 * 50mb of ram -- They're probably using something like 70mb total. Remember that that linux will share unmodified memory pages between processes, so if your server is freshly started most of that memory would all be shared. In fact, since mysql is threaded there will only probably be even less memory allocated per thread. If you're concerned about the memory usage of each mysql process, look at /etc/mysql/my.cnf, and look at the variables in the mysqld section:

  • key_buffers
  • thread_stack
  • thread_cache_size
  • max_connections
  • query_cache_limit
  • query_cache_size

Be warned though, that those are very powerful variables to tune, and you can easily kill your mysql performance setting them too low, or waste memory that could be used elsewhere by setting them too high.

One easy starting point to figure out the best way to tune your mysql instance is to run your app for a little bit, then run the mysqltune script from here, which will analyze your performance counters, then produce a recommendation on what you should change in your server config.

  • 1
    I didn't know mysqltune, very useful thanks. Also, one can use htop to color our threads and see the actual memory used per process.
    – mveroone
    Sep 9 '13 at 9:13
  • the link above for mysqltune is no longer available
    – Todor
    Jun 10 at 10:22

Linux shows threads as separate processes. MySQL starts one thread per connection. Running ps with f. For example, ps auxfw will display how the threads are related.

How many connections are there to MySQL? Run show full processlist; in the MySQL client. From what you're showing, I'm not even confident that there's anything wrong.

  • Here's the result: pastebin.com/raw.php?i=XNCsG6KS The server was just started Not sure if it helps... By init scripts do you mean the content of /etc/init.d/mysql ?
    – user24994
    Aug 14 '10 at 16:55
  • That looks perfectly normal to me.
    – Warner
    Aug 14 '10 at 16:57
  • According to show full processlist there are 2 connections to the database. One of them being localhost executing show full processlist, the other one is also me, via phpmyadmin. There shouldn't be any more connection as the site is not live yet...
    – user24994
    Aug 14 '10 at 17:01
  • The amount of connections is going to be dependent upon how your application performs and not necessarily directly related to the quantity of users. MySQL utilizes memory based on the cnf setting. What makes you think it's doing anything wrong?
    – Warner
    Aug 14 '10 at 17:11
  • 1
    Sorry, I guess it wasn't really clear in my question haha, it's totally possible that there's nothing wrong going on! I just noticed that mysql was using a lot of memory (approx. 13 * 50mb, so 650mb of ram) and there's no traffic to the site yet...Just me messing around in phpmyadmin. So I guess my question was more "Is there something wrong?" instead of "What's wrong" :)
    – user24994
    Aug 14 '10 at 17:19

You might want to try MySQLTuner which I found very helpful, it will suggest to you which of the config variables you should try adjusting.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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