mysql using a bit too much ressources

[Ovidiu]

I realized my mysql server is slowing things down for me, here are some stats, does anything strike you as unusual or what can I check to see whats going on here?

mytop output, mostly accesses to dbispconfig to be seen, why so many???

MySQL on localhost (5.0.51a-24+lenny4-log)                                                                                                                                             up 0+00:16:05 [17:26:34] Queries: 103.9k  qps:  110 Slow:     0.0         Se/In/Up/De(%):    75/00/00/00              qps now:  139 Slow qps: 0.0  Threads:   23 (   4/  16) 91/00/00/00  Key Efficiency: 97.2%  Bps in/out:   0.4/ 67.2   Now in/out:   8.2/ 1.5k

      Id      User         Host/IP         DB      Time    Cmd Query or State                                                                                                                                        --      ----         -------         --      ----    --- ----------                                                                                                                                          3099 c1diekell       localhost c1diekelle         0  Query SELECT id FROM wpwp_montycomment_messages WHERE `sig_id` = 495                                                                                      3121 c1diekell       localhost c1diekelle         0  Query SELECT id FROM wpwp_montycomment_messages WHERE `sig_id` = 495                                                                                      3649 ispconfig       localhost dbispconfi         0  Sleep                                                                                                                                                     3651 ispconfig localhost:56016 dbispconfi         0  Sleep                                                                                                                                                     3654 ispconfig localhost:56019 dbispconfi         0  Sleep                                                                                                                                                     3655 ispconfig       localhost dbispconfi         0  Sleep                                                                                                                                                     3668      root       localhost                    0  Query show full processlist                                                                                                                               3685 c1pacurar       localhost c1pacuraru         0  Query INSERT INTO `wp_pac_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:5802:{s:42:\"categories/(.+)/search_type/(.    3198 ispconfig       localhost dbispconfi         1  Sleep                                                                                                                                                     3652 ispconfig       localhost dbispconfi         1  Sleep                                                                                                                                                       12 ispconfig       localhost dbispconfi         2  Sleep                                                                                                                                                     3687 c1pacurar       localhost c1pacuraru         2  Sleep                                                                                                                                                     3197 ispconfig       localhost dbispconfi         3  Sleep                                                                                                                                                     3679 ispconfig localhost:56047 dbispconfi        21  Sleep                                                                                                                                                     3661 ispconfig localhost:56020 dbispconfi        46  Sleep                                                                                                                                                     3653 ispconfig localhost:56018 dbispconfi        50  Sleep                                                                                                                                                     3539 ispconfig localhost:49082 dbispconfi        51  Sleep                                                                                                                                                     3540 ispconfig localhost:49083 dbispconfi        51  Sleep                                                                                                                                                     3541 ispconfig localhost:49084 dbispconfi        51  Sleep                                                                                                                                                     3225 ispconfig localhost:49440 dbispconfi        71  Sleep                                                                                                                                                     3292 ispconfig localhost:49507 dbispconfi        71  Sleep                                                                                                                                                     3293 ispconfig localhost:49508 dbispconfi        71  Sleep                                                                                                                                                     3295 ispconfig localhost:49510 dbispconfi        71  Sleep

sorry I simply can't get this code correctly formatted. the one below looks great but this one sucks :-(

a sample of top, always mysql on top, rarely any apache processes to be seen around here:

top - 17:27:20 up 17 min,  1 user,  load average: 6.83, 5.89, 3.82
Tasks: 152 total,   5 running, 146 sleeping,   0 stopped,   1 zombie
Cpu(s): 64.0%us, 35.7%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   4047116k total,  3007920k used,  1039196k free,    57564k buffers
Swap:  2104496k total,        0k used,  2104496k free,  1437592k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2828 mysql     20   0  371m 162m 6268 S   92  4.1  14:54.19 mysqld
13399 web7      20   0  244m  70m 9068 R   31  1.8   0:00.92 php-cgi
13430 web7      20   0  215m  40m 8808 R   10  1.0   0:00.30 php-cgi
 3215 web9      20   0  284m 109m 9556 S    3  2.8   0:20.16 php-cgi
 2985 nobody    20   0 54024  33m  740 S    1  0.8   0:03.36 memcached
  976 root      15  -5     0    0    0 S    1  0.0   0:00.92 kjournald
12837 root      20   0 18960 1296  940 R    1  0.0   0:00.08 top
 2088 root      20   0  117m 1796  912 S    0  0.0   0:00.39 rsyslogd
 2116 root      20   0 95176 5780 1284 S    0  0.1   1:13.66 mailgraph
    1 root      20   0 10316  744  616 S    0  0.0   0:01.32 init
    2 root      15  -5     0    0    0 S    0  0.0   0:00.00 kthreadd
    3 root      RT  -5     0    0    0 S    0  0.0   0:00.00 migration/0
    4 root      15  -5     0    0    0 S    0  0.0   0:00.00 ksoftirqd/0
    5 root      RT  -5     0    0    0 S    0  0.0   0:00.00 watchdog/0
    6 root      RT  -5     0    0    0 S    0  0.0   0:00.00 migration/1
    7 root      15  -5     0    0    0 S    0  0.0   0:00.00 ksoftirqd/1
    8 root      RT  -5     0    0    0 S    0  0.0   0:00.00 watchdog/1

[Ovidiu]

sorry for double posting, was jsut trying to edit my post, don't know how it got posted twice, please delete the other thread with the same name.

[till]

You should try to optimize the mysql settings with mysqltuner:

http://www.faqforge.com/linux/optimi...th-mysqltuner/

[Ovidiu]

will try that but I was under the impression mysql was pretty well optimized till now. Just wondering if so many dbispcfg connections are usual? other than that will continue fiddling with mysql...

[Ovidiu]

Just realized the high load only started after the last debian updates a few days ago, will go look for info what exactly was updated and google maybe someone else is experiencing similar probs.

[till]

Just wondering if so many dbispcfg connections are usual?

On a normal used server you will have up to a few hundred as all services like postfix, courier or dovecot and amavisd connect to the ispconfig database.

[Ovidiu]

thx, that is solved then.