6

I have trying to fix this issue for two days with no success. The server is a MySQL database server.

Hardware: DELL Poweredge 1950, 2x Intel Xeon Quad Core E5345 @ 2.33GHz, 16 Gb mem, 2x 146Gb SAS (software RAID1)

Software: Ubuntu 10.04.3 LTS, MySQL 5.1.41

Issue: while MySQL is not used and runs with no database, everything seems alright. As soon as I install a database, it has the reason to bring all 8 cores in 100% with low memory consumption. So, you can imagine the load average goes high (I saw 212 load average for the first time). The server doesn't become unresponsive, but you can see it's slow while browsing the project installed.

Additional info:

  • The database used is not more than 24MB and it was moved from a server with less resources and a lot more larger databases. So it's not the database/project.
  • my.cnf is not a reason also, as I used both default one and the one I use on the same distribution on another server.What is interesting is that mysql doesn't close any process and runs to the limit of the max_connections.
  • Logs are quiet. Nothing there.
  • I switched to this Ubuntu version after I suspected some problems in the newly Ubuntu 11.10 server. This one worked alright for an hour after I made a kernel upgrade to 3.0.1 (it was using the memory also)

I tested disk speed and seems alright.

Some more output on the running server:

dstat -cndymlp -N total -D total 3:

dstat

htop command:

htop

Did anyone meet the same problem? Any fix you can think of?

Bill the Lizard
  • 352
  • 1
  • 7
  • 15
deadtired
  • 141
  • 1
  • 7
  • 4
    You need to figure out what MySQL is doing. Run a query; SHOW FULL PROCESSLIST; and figure out what it's spending its time on. There is usually a source for these things. – Kvisle Nov 02 '11 at 12:39
  • 4
    Theres been a recent kernel bug that causes the system clock to be off-skew which results in futex_wait calls returning too quickly. Try running `date --set "$(date)"` to see if that helps. – Matthew Ife Jul 25 '12 at 20:23
  • 1
    service ntp stop && date -s "@`date +%s`" && service ntp start –  Jul 29 '12 at 17:52
  • Could it be a corrupt copy of the database? – hookenz Dec 04 '12 at 00:04

2 Answers2

1

The interesting part (in addition to MySQL being so busy...) is that majority of the CPU time seems to go to system, not user time.

Can you trigger this same kind of behaviour with some other program than MySQL? Try, for example, stress and make it consume the CPU with stress -c 128. For me that spawned 128 threads consuming 93% user, 7% system CPU (according to top). This in Fedora 15, anyway. If even stress makes your server consume mostly system time, something is very wrong.

Sometimes unexpected things like outdated server firmware can cause all kind of funky side-effects. Have you upgraded your server firmware? Also newish kernel features, like dynamic ticks can make things laggy under certain conditions. You may try disabling those by passing nohz=off as boot parameter to GRUB.

Janne Pikkarainen
  • 31,454
  • 4
  • 56
  • 78
  • The same command on this server changed the usr/sys report from 33/63 to 96/4 (dstat). I am thinking about firmware now and I believe I saw in a boot sequence something about firmware updated. Is it possible to make it automatically, without me being asked? – deadtired Nov 02 '11 at 14:20
  • It seems that the us cpu usage is actually the exact sum between mysqld and __ticket_spin_lock. Even if server didn't reached 100% in the last 2h, i discovered that __ticket_spin_lock took more than 40% of the cpu (while mysqld process was somewhere ~14%). uname says Linux 2.6.32-34-server #77-Ubuntu SMP Tue Sep 13 20:54:38 UTC 2011 x86_64 GNU/Linux Any thoughts? – deadtired Nov 02 '11 at 16:04
0

Try killing mysql to see what happens. You will need to identify what took so much CPU usage.

If the CPU utilization drops, then please provide more information on your MySQL configuration and the nature of the business.

Sorry didn't read carefully, I was paying too much attention on the picture.

What about trying to use a new version or re-install MySQL? Ten years of sysadmin experience tells me this just seems impossible.

Bill the Lizard
  • 352
  • 1
  • 7
  • 15
c2h2
  • 759
  • 2
  • 8
  • 20