Limit MySQL threads/processes

[navjeet]

When I run command 'top' on my VPS, I see 11 mysqld processes/threads running, each using 5% memory. One process is running as root and rest as mysql user. How can limit these threads/processes to an arbitrary number. I did not see anything obvious in /etc/my.cnf that could limit these.

--Navjeet

[RedOut]

There should be only one process, and several threads... MySQL creates a few threads to begin with, and each connection must have its own thread: http://www.mysql.com/doc/en/MySQL_threads.html

I don't believe each of these threads is actually taking up 5% memory. I believe that stat is for the process.

If you want to limit the number of threads, you'll want to limit the number of connections. You can use the max_user_connections variable to do this or with recent versions you can limit them per user.
http://dev.mysql.com/doc/mysql/en/user-resources.html

Doing this could cause apps that access your database to fail though if you reach your connection limit.

[SpaceAdmiral]

Although this doesn't answer your question, I can at least explain the one process running as root and the others running as mysql.

I read somewhere that mysql_safe starts as root so that it has the permissions to secure ports, et cetera, but then it spawns a process(s) as user mysql to handle the actual connections/queries, since it's safer that way. (If someone maliciously gains control of the process handling their connection, they only have the limited permissions of user mysql, not root permissions).

[rashgash]

Hi,

I'm seeing the same thing -- about 10 mysqld processes (with different pid's) right after i start up /etc/init.d/mysql start on debian.

/etc/init.d/mysql status gives me:

/usr/bin/mysqladmin Ver 8.41 Distrib 4.1.11, for pc-linux-gnu on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.11-Debian_4sarge2-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 sec

Threads: 1 Questions: 27 Slow queries: 0 Opens: 26 Flush tables: 1 Open tables: 15 Queries per second avg: 5.400

ps aux gives me a bunch of these:

mysql 17062 0.0 11.6 61244 14724 pts/0 S 21:19 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
mysql 17063 0.0 11.6 61244 14724 pts/0 S 21:19 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
mysql 17064 0.0 11.6 61244 14724 pts/0 S 21:19 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
m

This doesn't to be killing my system as a cursory reading would suggest, but it would be nice to find out what all this means. I've been searching google but haven't come up with anything yet.

Best,
Josh

[RedOut]

Try using ps -auxf It should give you a better idea of what you're looking at...
For instance...
tomcat 563 0.0 10.0 259252 12680 ? S Mar01 0:04 /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.endorsed.d
tomcat 572 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.endors
tomcat 573 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 574 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 575 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 576 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 577 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 578 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 579 0.0 10.0 259252 12680 ? S Mar01 0:01 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 634 0.0 10.0 259252 12680 ? S Mar01 0:01 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 635 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 636 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 637 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 638 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 639 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 640 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 641 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 642 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en
tomcat 643 0.0 10.0 259252 12680 ? S Mar01 0:00 \_ /usr/java/jdk/bin/java -Xms16m -Xmx96m -Djava.awt.headless=true -Djava.en


Obviously I don't have 19 threads taking up 10% of system memory each. So what you're seeing is the memory usage for the parent process carried down to the threads, since all the threads are sharing the same memory space. Some other programs display differently but the vast majority share memory with other processes, so the amount of memory reported by ps isn't the accurate amount of memory being used by an individual process.