LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 07-16-2006, 10:38 PM   #1
DigiCrime
Member
 
Registered: Dec 2002
Location: St. Louis
Distribution: All Flavors
Posts: 195

Rep: Reputation: 30
MySQL Question


im wondering what keeps mysql stuck in the process list for so long

for example
21371 mysql 15 0 536M 403M 3216 S 21.9 19.8 688:05 3 mysqld

its been running a long time but if I check the processlist

+--------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------+-----------+-----------+---------+------+-------+------------------+
| 393996 | eximstats | localhost | eximstats | Sleep | 21 | | |
| 394060 | root | localhost | | Query | 0 | | show processlist |
+--------+-----------+-----------+-----------+---------+------+-------+------------------+


I watch it off and on and see mysql queries here and there but their quick, exim is sendmail clone so to speak it keeps stats of email on specific domains it doesnt run very often, anyway what keeps it stuck ??

copy of my.cnf

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
 
Old 07-16-2006, 11:18 PM   #2
MasterC
LQ Guru
 
Registered: Mar 2002
Location: Salt Lake City, UT - USA
Distribution: Gentoo ; LFS ; Kubuntu
Posts: 12,612

Rep: Reputation: 69
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
 
Old 07-17-2006, 12:08 AM   #3
DigiCrime
Member
 
Registered: Dec 2002
Location: St. Louis
Distribution: All Flavors
Posts: 195

Original Poster
Rep: Reputation: 30
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
 
Old 07-17-2006, 12:12 AM   #4
Matir
LQ Guru
 
Registered: Nov 2004
Location: San Jose, CA
Distribution: Ubuntu
Posts: 8,507

Rep: Reputation: 128Reputation: 128
Mysqld must be continuously running or no applications that need access to databases would be able to get at the data.
 
Old 07-25-2006, 09:36 AM   #5
DigiCrime
Member
 
Registered: Dec 2002
Location: St. Louis
Distribution: All Flavors
Posts: 195

Original Poster
Rep: Reputation: 30
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM
MySQL question rjcrews General 3 11-16-2005 05:27 PM
A question about MySQL mnguyen Programming 1 05-14-2004 10:32 AM
MySQL question TraCe66 Linux - Newbie 11 07-15-2003 02:35 PM
Another mysql question dkc_ace Linux - Software 0 03-23-2003 04:10 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 04:51 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration