-2

We have this mysql process running and kicking the cpu up to 200%. Any idea how this could be fixed?

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/xxxxxxxx.com.err --open-files-limit=48480 --pid-file=/var/lib/mysql/xxxxxxxx.com

user197653
  • 3
  • 1
  • 2
  • 1
    Well you could kill that process and that would solve the problem, but then you wouldn't have a db server running. You need to look into seeing how to observe processes *within* mysql, and see which queries are producing the load. – EEAA Nov 07 '13 at 23:11
  • I totally disagree - killing the MySQL process will have many negative knock-on effects, besides I presume this is a production server. For example any query cache, is totally lost when the new process is started-up (and needs to be re-generated). Any queries currently running especially insert, update, delete will not complete leaving broken constraints or missing data. – Ash Nov 07 '13 at 23:52
  • 1
    @Ash that woosh you heard was the sarcasm flying over your head :-) – voretaq7 Nov 07 '13 at 23:54
  • Well, excuse me for misunderstanding - today I had a running with a data centre whom did exactly the above causing me a ballache running cache queries. – Ash Nov 07 '13 at 23:59

3 Answers3

3

As EEAA commented:

You need to look into seeing how to observe processes within mysql, and see which queries are producing the load

A good start is to look at long running queries. You can enable the slow query log or do a:

mysqladmin -u root -p --verbose processlist

or:

SHOW FULL PROCESSLIST

from the mysql prompt.

Cheers

HTTP500
  • 4,833
  • 4
  • 23
  • 31
0

I entirely agree with @HTTP500's answer.

In addition running these commands on the command line you can press the up key and return to continuously run the same query.

I suggest you use the explain command before the statement. This will definitly diagnose your syntax which I would presume is the problem.

  • Check all tables have the correct indexes;
  • Check if a cron is hammering inserts, updates or deletes on an MyISAM table (which as we know has table-level locking);
Ash
  • 471
  • 1
  • 4
  • 14
0

When my MySQL server is getting overloaded what I run is mytop; which gives me a complete breakdown of all the queries executing on the server. This is especially useful because it makes it easy to sort by long running queries which are usually the culprit.

However, if you don't have mytop you can do the following:

  • SHOW FULL PROCESSLIST; will give you a dump of all running queries on the server.
  • Once you determine the query you can run EXPLAIN EXTENDED on that query.
  • If it's InnoDB you can run SHOW ENGINE INNODB STATUS to get a full report on the status of InnoDB as well as any errors.
  • If its a large table make sure the indexes are up to date.

Whatever you do, do not restart the server as it's just going to occur again in the future.