high mysql usage after 8.2 update

[atomic]

After upgrading from 7.5.4 -> 8.2 our dedicated email server suddenly has spikes of high cpu usage. Previously during peak times we would average 50-60% cpu usage and fall off to sub 25% at off times.

Now, at consistent half-hour increments our cpu usage shoots up with mysql claiming most of the responsibility. Here's our cpu usage load during a spike (our uptime has been about 6 days):

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3550 mysql     16   0  139m  42m 4924 S 73.2  1.2 994:27.59 mysqld
  333 popuser   17   0 47028  40m 2324 R 42.5  1.1   1:46.03 spamd
  454 popuser   16   0 43936  37m 2296 R 24.2  1.1   0:51.37 spamd
22280 psaadm    16   0 42568  21m  14m S 10.5  0.6   0:35.07 httpsd
 3397 named     16   0 58472  22m 1928 S  2.6  0.6 130:39.81 named
 7694 qmaild    15   0  3992  832  692 S  2.6  0.0   0:04.89 qmail-smtpd
 2662 drweb     17   0 20932  14m  860 S  2.3  0.4   0:03.26 drwebd
30807 root      16   0  2352 1000  748 R  0.7  0.0   0:07.55 top
 2398 root      15   0     0    0    0 S  0.3  0.0   8:59.69 kjournald
 3150 root      16   0  2520  556  464 S  0.3  0.0   6:19.26 syslogd
 3708 root      15   0 36972  31m 2380 S  0.3  0.9   6:01.30 spamd
 8201 psaadm    15   0 43288  24m  17m S  0.3  0.7   6:03.31 httpsd
 8615 drweb     15   0  5024 4168  552 S  0.3  0.1   0:00.46 qmail-queue
15818 qmails    16   0  1996  472  372 S  0.3  0.0   0:07.16 qmail-send

Here is a link to our MRTG graph showing the consistent half-hour spikes.

There are no slow queries being logged and I've even enabled the query cache and everything seems normal (I can post those stats as well, if it will help). I've looked through the cron jobs and don't see anything that would be running every half-hour.

So, is there anything in Plesk that has to run so consistently and chew up mysql? Is there something in Expand that would cause it to poll the email server? I'm just trying to go through every possibility to figure out why it only started after the upgrade and if there's anything we can do to ease the pressure.

Thanks,
Atomic

 

[yurtesen]

When there is high load you can check the output of
show processlist;
or
show full processlist;
mysql commands to see what is causing the load. As well you can use mytop
http://www.freebsd.org/cgi/url.cgi?ports/databases/mytop/pkg-descr

Thanks,
Evren

 

[atomic]

These are the 2 commands I saw most often.

Sorting result | SELECT UNIX_TIMESTAMP(event_time) FROM exp_event WHERE event_type = 'flushed'   AND obj_class = 'client' AND source = 'pa' ORDER BY event_time DESC LIMIT 1

Sending data | SELECT COUNT(*) FROM exp_event WHERE event_type = 'terminated'   AND obj_class = 'client_perms' AND source = 'pa'

Thank you for any insight.

 

[yurtesen]

Your customer's database might be large thus causing high cpu usage during sort operation. I doubt this has anything to do with the upgrade. Did your mysql version change?

About the half an hour intervals, your customer could have setup a cron job to run certain tasks which access to database every half hour. See, it might be a coincedence that your customer did this after you upgraded to 8.2

Please check the cron tasks setup by your customer who the owner of the query which pops up every half an hour

 

[atomic]

We do have a good amount of customers and clients ( approx 150 domains ), but I don't believe our mysql version changed, we are still on 4.1.20.

And this server that is having the problem is our dedicated email server, so no one shoud have access to run custom cron jobs.

Also, after looking again at the user and db associated with those queries it looks like it's coming from the 'admin' user querying the 'psa' database.

So, it's looking like it has something to do with Expand.

We'll try shutting down our Expand server tonight for an hour or so and see if the spikes go away.

Even if it is Expand, I don't know how we can stop it, or ease the pressure off the email server, but we'll still have to figure out something from there.

Thanks again for all of your help.
Atomic

 

[atomic]

It was definitely something with Expand. And definitely not something we saw with the old version of Expand, ver 2.0.1.

Here is our graph post Expand shutdown:

I also noticed it has the same effect on our dedicated web server, just not as pronounced:

(note the drop once Expand had been shutdown)

After poking around on the Expand server I think I found the issue.

Under Server -> System Help -> Operations, it shows an error on 'Sync Domain(s) usage statistics' Failed (148 subsequent failures since Sept 23, 2007 04:44 PM)

I also found another thread that references the error messages I saw:
http://forum.swsoft.com/showthread.php?s=&threadid=46631&highlight=Domain+has+no+address

I didn't use his workaround, but I can confirm that this has been fixed in the new Expand patch 2.2.1. Now things have definitely calmed down.

Thanks for your help yurtesen.

Atomic