List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 11 2003 7:05pm
Subject:Re: Ram Usage, and processes
View as plain text  
At 8:01 -0800 1/11/03, James M. Luedke wrote:
>Hello:
>     I have a few questions about how MySQL uses Ram. I have a machine
>that I am using as a database server. The machine has close to 1G
>of ram. It is running Slackware Linux 8.1 With a 2.4.18 kernel.
>The main use of this server is a backend for Radius Authentication.
>
>     I have read all of the documentation I have been able to get my
>hands on, and believe I have a very good understanding of how MySQL
>works in general. However, this has me a little stumped.
>
>     Please take a look at the output from top, my.cnf, and the processlist
>below.
>
>-------begin top output-----------------
>top - 15:21:46 up 56 days, 10:57, 11 users,  load average: 0.03, 0.16, 0.15
>Tasks:  61 total,   1 running,  58 sleeping,   2 stopped,   0 zombie
>Cpu(s):   5.6% user,   2.0% system,   0.0% nice,  92.5% idle
>Mem:    900472k total,   896048k used,     4424k free,    22956k buffers
>Swap:   907664k total,     3676k used,   903988k free,   744248k cached
>
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  Command          
>   2361 mysql      9   0 53416  52m 2068 S  0.3  5.9   2:48.61 
>mysqld           
>   2357 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:00.18 
>mysqld           
>   2359 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:00.28 
>mysqld           
>   2360 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:11.29 
>mysqld           
>   2362 mysql      9   0 53416  52m 2068 S  0.0  5.9  17:28.20 
>mysqld           
>   2371 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:02.82 
>mysqld           
>   2386 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:00.12 
>mysqld           
>   2395 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:01.01 
>mysqld           
>   2419 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:00.21 
>mysqld           
>   2427 mysql      9   0 53416  52m 2068 S  0.0  5.9  17:00.51 
>mysqld           
>   2428 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:03.32 
>mysqld           
>   2458 mysql      9   0 53416  52m 2068 S  0.0  5.9   0:01.15 
>mysqld           
>-------end top output-----------------
>
>Notice that when viewing top output for user mysql that there are
>12 seprate processes? I am assuming that this is done for connections

No.  Linux displays threads as processes.  They're all the same process.

>that the MySQL server may need to field. Does MySQL spawn a new
>process for every connection that comes in? And if so why do I have

Spawns a new thread.

>12 processes when the output from show processlist returns 7 rows.

Administrative threads.

>Is this how you would expect the MySQL server to behave? It appears
>that MySQL is sucking up a large amount of system recourses even
>when no-one is connected... There is little else running on this
>system besides MySQL. I have read similar messages to this email
>in the mysql list archive, and am familiar with the fact that MySQL
>will use key_buffer amount of ram for the mysqld process. According
>to the top output It would appear to me that MySQL is using
>52M*12 of ram which is 624M. Am I correct in my understanding?

There's only one key_buffer (now called key_buffer_size), shared by
all threads.

Now, read_buffer_size and sort_buffer_size, that's a different
matter. :-)

>I would assume that MySQL would share the cache between processes.
>Is there a reason why each process needs key_buffer ram?
>Am I doing something horribly wrong?

Nope.  You're simply thinking that there are more processes than
there really are.

>
>
>---------show processlist output -----
>+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
>| Id  | User        | Host           | db         | Command     | 
>Time  | State                                       | Info 
>|
>+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
>|   1 | radiusd     | localhost      | radiusd_db | Sleep       | 0 
>|                                             | NULL             |
>|  20 | root        | localhost      | customers  | Sleep       | 
>23750 |                                             | NULL 
>|
>|  49 | radiusd     | 216.250.251.22 | radiusd_db | Sleep       | 
>23076 |                                             | NULL 
>|
>|  80 | root        | localhost      | customers  | Sleep       | 
>22168 |                                             | NULL 
>|
>| 403 | radiusd     | 64.139.37.2    | radiusd_db | Sleep       | 2 
>|                                             | NULL             |
>| 524 | replication | 64.139.37.2    | NULL       | Binlog Dump | 
>9218  | Slave connection: waiting for binlog update | NULL 
>|
>| 769 | root        | localhost      | radiusd_db | Query       | 0 
>| NULL                                        | show processlist |
>+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
>-----end show processlist output -----

[clipped]

>
>Just a side question. I noticed in the show variables output the variable
>have_raid? I do have the data directory stored on an external raid device,
>however the variable is set to NO. Will this cause me any problems...

have_raid relates to the RAID_XXX table creation options.

Thread
Ram Usage, and processesJames M. Luedke11 Jan
  • Re: Ram Usage, and processesJoseph Bueno11 Jan
  • Re: Ram Usage, and processesPaul DuBois11 Jan