Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted
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 your concerned about the memory useage of each mysql process, look @ /etc/mysql/my.cnf, and look @ 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.

share|improve this answer
    
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. –  Kwaio Sep 9 '13 at 9:13
add comment

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.

share|improve this answer
    
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
    
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
add comment

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.

share|improve this answer
    
For new readers, the MySQLTuner script is placed here: mysqltuner.pl –  Lauer Sep 6 '13 at 9:24
    
@Lauer thanks for the prompt - link updated –  icc97 Sep 9 '13 at 9:03
add comment

Your Answer

 
discard

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

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