up vote 2 down vote favorite
1
share [g+] share [fb]

I have 2 days since I am trying to fix this issue, 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

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

link|improve this question
2  
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 2 '11 at 12:39
feedback

3 Answers

up vote 0 down vote

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.

link|improve this answer
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 2 '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 2 '11 at 16:04
feedback
up vote 0 down vote

System time is time spent executing kernel (ring 0) calls; as Janne already indicated, this should not happen on a mostly idle server.

What does the mysql error log say ?
Who is using these mysql connections ?

link|improve this answer
Logs are quiet. The connections are made by a website which is below average as traffic (also, is a ~24mb db). I don't focus on that even more as I moved it on this server for a initial testing (before adding more databases) from a server with less resources than this one. The strangest thing is that when I added a larger database and connected to live site, the system got stabilized a little bit (in 1h reached 21 load average 1 time and got back to 2-4). Still, it 's not looking reliable. – deadtired Nov 2 '11 at 14:00
feedback
up vote -1 down vote

kill mysql, see what happens. you will need to identify what took so much cpu usage.

If the cpu utilization drops, then plz provide more information on mysql conf and nature of the business.

EDIT: sorry didn't read carefully, paying too much attention on the picture.

What about try to use a new version or re-install mysql? ten years of sysadmin exp tell me just seems impossible.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.