Too many MySQL processes

Question

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!

Answer 1

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, which will analyze your performance counters, then produce a recommendation on what you should change in your server config.

Answer 2

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.

Answer 3

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.