mysql eating up CPU

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

mysql eating up CPU

Post by GeXus »

I have an AMD opteron 2.6 dual processor dual core, with 4GB cache and 4GB memory... Mysql is eating up 37% of the CPU, I have a process ID, but all that is shown is
/usr/sbin/mysqld�--basedir=/�--datadir=/var/lib/mysql�--user=mysql�--pid-file=/var/lib/mysql/domain.domain.com.pid�--skip-external-locking�

There is no specific query associated... How do I drill this down? I'm sure it's probably due to just one or two poorly written queries...
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Post by Inkyskin »

What kind of traffic do you have online at one time?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Inkyskin wrote:What kind of traffic do you have online at one time?
At a single time I don't know, but according to analytics it's about 70,000 visits a day... It should be relatively spread out.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Post by Inkyskin »

I don't know what tools you have, but if you have MySQL Administrator, you'll be able to see what queries are being run, how long they have been running for, and how many sleeping threads you may have.

Lot's of sleeping threads can eat up alot of resources. As of course can lots of heavy queries.

You can also use the same tool to optimize the server settings - allow more connections at once etc These may help:
http://www.databasejournal.com/features ... hp/3367871
http://www.debianhelp.co.uk/mysqlperformance.htm

You have a decent server spec, so it just sounds like a bit of optimization is in order - be it status variables in mysql, or your actual queries (Or both!)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Thanks, I did find that query_cache_limit was set to 0, so I set that to 64000000 ...
Post Reply