MySQL Question

DigiCrime · 07-16-2006, 10:38 PM

Code:

[mysqld]
max_connections = 500
key_buffer = 200M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 6M
table_cache = 2000
thread_cache_size = 150
wait_timeout = 60
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1500
query_cache_limit = 4M
query_cache_size = 256M
query_cache_type = 1
thread_concurrency=2
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysql]
no-auto-rehash
# safe-updates

[isamchk]
key_buffer=128M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=128M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

top

21:36:48 up 4 days, 6:41, 1 user, load average: 2.02, 1.81, 1.64
147 processes: 143 sleeping, 3 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 25.1% 0.0% 2.6% 0.0% 0.2% 1.1% 70.6%
cpu00 24.6% 0.0% 4.3% 0.1% 1.1% 0.1% 69.4%
cpu01 24.4% 0.0% 2.7% 0.0% 0.0% 3.3% 69.4%
cpu02 11.6% 0.0% 1.5% 0.0% 0.0% 0.3% 86.3%
cpu03 39.6% 0.0% 2.1% 0.0% 0.0% 0.7% 57.3%
Mem: 2074352k av, 1967624k used, 106728k free, 0k shrd, 155400k buff
1398828k active, 439384k inactive
Swap: 4096532k av, 0k used, 4096532k free 1053600k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
15171 nobody 15 0 27012 12M 2708 S 3.0 0.6 0:01 0 httpd
15167 nobody 15 0 25888 11M 2660 S 2.6 0.5 0:00 0 httpd
15157 nobody 16 0 27032 12M 2740 S 2.1 0.6 0:00 3 httpd
15151 nobody 15 0 25292 11M 2648 S 1.9 0.5 0:00 1 httpd
15156 nobody 16 0 27600 13M 2276 R 1.6 0.6 0:00 2 httpd
15210 mailnull 15 0 7468 2964 1920 S 0.4 0.1 0:00 0 exim
21371 mysql 15 0 532M 398M 3216 S 0.3 19.6 689:39 1 mysqld

This system is a 2.2ghz dual xeon with 2 gigs of ram mysql 4.1.19

MasterC · 07-16-2006, 11:18 PM

MySQL is running in daemon mode (mysqld). This means that the mysql service is always running, waiting for a connection to a database from some client with proper permissions...

HTH

Cool

DigiCrime · 07-17-2006, 12:08 AM

Hi

So its normal for it to stay running for a long time? In top its showing 20% memory usage on average so i figured its whats keeping the load up a bit

Heres another dual xeon i have with mysql usage

23:04:29 up 25 days, 8:12, 1 user, load average: 0.61, 0.92, 1.12
213 processes: 211 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 18.0% 0.0% 5.2% 0.0% 15.2% 0.0% 360.4%
cpu00 10.4% 0.0% 2.3% 0.0% 0.1% 0.0% 86.9%
cpu01 2.3% 0.0% 0.9% 0.0% 0.0% 0.3% 96.2%
cpu02 1.1% 0.0% 0.9% 0.0% 0.0% 0.0% 97.8%
cpu03 4.3% 0.0% 1.1% 0.0% 15.0% 0.0% 79.3%
Mem: 1553336k av, 1512796k used, 40540k free, 0k shrd, 136712k buff
1041960k active, 351560k inactive
Swap: 1534168k av, 569028k used, 965140k free 671892k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
16376 mysql 16 0 484M 236M 2344 S 4.1 9.5 34:28 1 mysqld
16535 mailnull 15 0 6672 1892 1552 S 2.7 0.1 4:51 0 exim
25894 root 18 0 7728 4048 2784 S 2.7 0.2 0:00 2 exim
16261 root 16 0 11188 8208 1948 S 2.5 0.5 3:30 0 httpd

Hasnt been running long and has a lower mem usage

Matir · 07-17-2006, 12:12 AM

Mysqld must be continuously running or no applications that need access to databases would be able to get at the data.

DigiCrime · 07-25-2006, 09:36 AM

Quote:

Originally Posted by Matir

Mysqld must be continuously running or no applications that need access to databases would be able to get at the data.

I knew that much I meant more or less is there a way to tune mysql so sleep mode doesn't keep the load up. For example this was yesterday

Code:

top
 13:37:32  up 7 days,  2:22,  1 user,  load average: 5.74, 5.36, 5.93
215 processes: 205 sleeping, 5 running, 3 zombie, 2 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total   79.8%    0.1%   19.0%   0.0%     0.0%    0.0%    0.9%
           cpu00   69.9%    0.1%   29.2%   0.0%     0.0%    0.0%    0.6%
           cpu01   83.4%    0.0%   14.6%   0.0%     0.0%    0.0%    1.8%
           cpu02   84.5%    0.1%   14.0%   0.0%     0.0%    0.0%    1.2%
           cpu03   81.5%    0.1%   18.1%   0.0%     0.0%    0.0%    0.1%
Mem:  2067468k av, 1996888k used,   70580k free,       0k shrd,  163488k buff
       515384k active,             698940k inactive
Swap: 4096532k av,  149424k used, 3947108k free                  998388k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
18368 mysql      9   0  454M 423M 11996 S     0.0 20.9  57:02   2 mysqld
10803 mysql      9   0  454M 423M 11996 S     1.4 20.9  54:36   2 mysqld
22817 mysql      9   0  454M 423M 11996 S     0.0 20.9  53:20   0 mysqld
18259 mysql      9   0  454M 423M 11996 S     0.0 20.9  52:39   0 mysqld
 9705 mysql      9   0  454M 423M 11996 S     0.0 20.9  51:46   0 mysqld
 9817 mysql      9   0  454M 423M 11996 S     0.0 20.9  50:36   3 mysqld
18009 mysql      9   0  454M 423M 11996 S     0.0 20.9  50:12   3 mysqld
9814 mysql      9   0  454M 423M 11996 S     0.0 20.9  49:27   3 mysqld
22813 mysql      9   0  454M 423M 11996 S     0.0 20.9  49:22   1 mysqld
12853 mysql      9   0  454M 423M 11996 S     0.0 20.9  49:01   1 mysqld
12801 mysql      9   0  454M 423M 11996 S     0.0 20.9  49:00   0 mysqld
22818 mysql      9   0  454M 423M 11996 S     0.0 20.9  48:56   1 mysqld
 9874 mysql      9   0  454M 423M 11996 S     0.0 20.9  48:35   0 mysqld
 1733 root      19  19 63200  27M 27232 S N   0.0  1.3  48:31   2 cpanellogd
19038 mysql      9   0  454M 423M 11996 S     0.0 20.9  48:15   1 mysqld
 9704 mysql      9   0  454M 423M 11996 S     0.0 20.9  47:41   0 mysqld
 9660 mysql      9   0  454M 423M 11996 S     0.0 20.9  47:12   3 mysqld
 9858 mysql      9   0  454M 423M 11996 S     0.0 20.9  46:43   2 mysqld
12852 mysql      9   0  454M 423M 11996 S     0.0 20.9  46:31   2 mysqld
25808 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:55   2 mysqld
19039 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:50   0 mysqld
20092 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:37   1 mysqld
25797 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:37   2 mysqld
25817 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:12   2 mysqld
 9659 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:09   3 mysqld
22822 mysql      9   0  454M 423M 11996 S     0.0 20.9  45:06   0 mysqld
31526 mysql      9   0  454M 423M 11996 S     0.0 20.9  44:31   0 mysqld
25809 mysql      9   0  454M 423M 11996 S     0.0 20.9  43:55   3 mysqld
19168 mysql      9   0  454M 423M 11996 S     0.0 20.9  43:46   2 mysqld
19169 mysql      9   0  454M 423M 11996 S     0.0 20.9  43:17   1 mysqld
25717 mysql      9   0  454M 423M 11996 S     0.0 20.9  43:10   3 mysqld
25812 mysql      9   0  454M 423M 11996 S     0.0 20.9  42:53   1 mysqld
25711 mysql      9   0  454M 423M 11996 S     0.0 20.9  42:41   2 mysqld
25713 mysql      9   0  454M 423M 11996 S     0.2 20.9  42:16   2 mysqld
 9734 mysql      9   0  454M 423M 11996 S     0.0 20.9  42:10   1 mysqld
19171 mysql      9   0  454M 423M 11996 S     0.0 20.9  42:00   1 mysqld
25810 mysql      9   0  454M 423M 11996 S     0.0 20.9  41:48   2 mysqld

mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1140778 to server version: 4.1.19-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW FULL PROCESSLIST;
+---------+------------------+-----------+------------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------------------+-----------+------------------+---------+------+-------+-----------------------+
| 1140225 | eximstats | localhost | eximstats | Sleep | 2 | | NULL |
| 1140778 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 1140792 | salmoncr_vforums | localhost | salmoncr_vforums | Sleep | 0 | | NULL |
+---------+------------------+-----------+------------------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

STATUS;
--------------
mysql Ver 14.7 Distrib 4.1.19, for pc-linux-gnu (i686) using readline 4.3

Connection id: 1140778
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.19-standard
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 7 days 2 hours 3 min 57 sec

Threads: 4 Questions: 44725202 Slow queries: 1611 Opens: 80139 Flush tables: 1 Open tables: 2000 Queries per second avg: 73.052

It was suggested that you need to flush the query cache out and restart mysql, however you should not have to do that on a regular basis. Today the load looks fine but it will return eventually thats what it does. Been going on for a few weeks now my question here is it normal for mysql to stay in sleep mode like this so much?

Some app is probably accessing mysql more then often so it doesn't have a chance to exit I guess but I watch the process list often enough that this kind of behavoir that exists on other systems, IE message boards that are active dont cause the system this much grief so I am led to believe that there is something more wrong then meets the eye I just can't place it.