?
Solved

MySQL high CPU usage

Posted on 2014-08-07
22
Medium Priority
?
2,640 Views
Last Modified: 2014-09-15
I'm running a windows server 2008/iis we server on which my primary website relies on php and MySQL. Mysqld.exe CPU usage periodically maxes out at 100% and he site becomes unresponsive. How can I find the root cause of this?
0
Comment
Question by:matthewi
22 Comments
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40246795
I would suggest looking at the processes when this happens. Stay connected to the MySQL database with some kind of querying program (the mysql.exe shell or a GUI program like HeidiSQL) and query the process list after a minute of consistently high usage and see if there are any queries that are taking a long time to run.

SHOW PROCESSLIST

is the query to see what's currently happening in MySQL.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40246845
Also check for slow queries in the relevant log.
You should consider checking the mysql server performance settings and/or to move the mysql off the iis server.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40247035
If you find a long-running query, EXPLAIN SELECT is your friend.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:matthewi
ID: 40247183
Thank you all for your help!  I have now moved the MySQL database off to another server and the mysqld process now utilizes nearly 100% cpu on that machine as well, but the spikes are shorter in duration.  During those spikes nothing sticks out in the process list.  Here's an example:

Full texts      Id      User      Host      db      Command      Time      State      Info
Kill Kill      56      root      129.47.32.7:56927      NULL      Sleep      116            NULL
Kill Kill      151      root      129.47.32.7:57024      pgs2      Sleep      794            NULL
Kill Kill      153      root      129.47.32.7:57027      pgs2      Sleep      294            NULL
Kill Kill      154      root      129.47.32.7:57028      pgs2      Sleep      2            NULL
Kill Kill      240      phpmyadmin      localhost      NULL      Sleep      0            NULL
Kill Kill      241      root      localhost      NULL      Query      0      NULL      show processlist


Also, even after setting the slow query long duration to 5, it fills EXTREMELY fast.  Here is a small excerpt:

`information_schema`.`COLUMNS`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000593  Lock_time: 0.000411 Rows_sent: 0  Rows_examined: 0
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`EVENTS`;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.000605  Lock_time: 0.000429 Rows_sent: 1  Rows_examined: 1
SET timestamp=1407439606;
select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.000705  Lock_time: 0.000532 Rows_sent: 1  Rows_examined: 2
SET timestamp=1407439606;
select languages_id, name, code, image, directory from languages order by sort_order;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000545  Lock_time: 0.000387 Rows_sent: 0  Rows_examined: 0
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`PARAMETERS`;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.012033  Lock_time: 0.000118 Rows_sent: 0  Rows_examined: 27
SET timestamp=1407439606;
delete from whos_online where time_last_click < '1407438704';
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.000277  Lock_time: 0.000061 Rows_sent: 0  Rows_examined: 25
SET timestamp=1407439606;
select session_id from whos_online where session_id = 'beq3c7rfhee684t9oida7idf82' limit 1;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.000641  Lock_time: 0.000477 Rows_sent: 0  Rows_examined: 2
SET timestamp=1407439606;
select banners_id, date_scheduled from banners where date_scheduled != '';
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.005267  Lock_time: 0.003966 Rows_sent: 2  Rows_examined: 91
SET timestamp=1407439606;
select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.000644  Lock_time: 0.000459 Rows_sent: 0  Rows_examined: 4
SET timestamp=1407439606;
select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.115918  Lock_time: 0.000421 Rows_sent: 0  Rows_examined: 148
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`PARTITIONS`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000825  Lock_time: 0.000295 Rows_sent: 0  Rows_examined: 23
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`PLUGINS`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000622  Lock_time: 0.000378 Rows_sent: 0  Rows_examined: 3
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`PROCESSLIST`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000567  Lock_time: 0.000442 Rows_sent: 0  Rows_examined: 0
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`ROUTINES`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.009725  Lock_time: 0.000315 Rows_sent: 0  Rows_examined: 0
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`TRIGGERS`;
# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.005045  Lock_time: 0.000249 Rows_sent: 0  Rows_examined: 0
SET timestamp=1407439606;
select count(*) into @discard from `information_schema`.`VIEWS`;
# User@Host: root[root] @  [129.47.32.7]
# Query_time: 0.229718  Lock_time: 0.000091 Rows_sent: 1  Rows_examined: 19412
SET timestamp=1407439606;
select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '2088907';
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40247267
Those queries shouldn't be in the log - they're far below the threshold. Can you post your MySQL ini file (and make sure you've restarted MySQL to ensure the settings have taken effect)?

Also, there are some additional tips in this article:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 40247997
I had similar performance issue while I was running with MySQL 5.5. Almost the entire CUP usage was taken by MySQL db and there was big performance issues on my CodeIgniter Application (it is student bidding software & concurrent access, row delete add was simultaneous process)

So, I used this script http://major.io/mysqltuner/ to check the live performance analysis   and found it was so useful to analyze the performance and speed.
0
 

Author Comment

by:matthewi
ID: 40248453
I have restarted MySQL after changing my.cnf  Here is the file:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
socket            = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket            = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file      = /var/run/mysqld/mysqld.pid
socket            = /var/run/mysqld/mysqld.sock
port            = 3306
basedir            = /usr
datadir            = /var/lib/mysql
tmpdir            = /tmp
lc-messages-dir      = /usr/share/mysql
bind-address      = 129.47.32.201
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer            = 2048M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit      = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries      = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id            = 1
#log_bin                  = /var/log/mysql/mysql-bin.log
expire_logs_days      = 10
max_binlog_size         = 100M
#binlog_do_db            = include_database_name
#binlog_ignore_db      = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash      # faster start of mysql but no tab completition

[isamchk]
key_buffer            = 2048M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
0
 

Author Comment

by:matthewi
ID: 40248464
Also, here is the output from mysqltuner.  When I try to optimize the tables, they cannot be optimized because they are innodb.

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 932B (Tables: 14)
[--] Data in InnoDB tables: 18M (Tables: 53)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 53

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 12s (108K q [221.392 qps], 103 conn, TX: 11M, RX: 13M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 2.2G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.6G (16% of installed RAM)
[!!] Slow queries: 6% (6K/108K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 2.0G/126.0K
[OK] Key buffer hit rate: 100.0% (98 cached / 0 reads)
[OK] Query cache efficiency: 91.8% (99K cached / 108K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 201 sorts)
[OK] Temporary tables created on disk: 13% (62 on disk / 456 total)
[OK] Thread cache hit rate: 87% (13 created / 103 connections)
[OK] Table cache hit rate: 32% (159 open / 490 opened)
[OK] Open file limit used: 7% (77/1K)
[OK] Table locks acquired immediately: 100% (16K immediate / 16K locks)
[OK] InnoDB buffer pool / data size: 128.0M/18.9M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40248765
For your situation, I would suggest setting your long_query_time to 2 and removing log-queries-not-using-indexes. Then restart MySQL and see what's in the logs.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40248772
Also, OPTIMIZE TABLE does work for InnoDB, but it technically just maps to a couple of other commands that essentially rebuild the table and indexes. It's not really an effective command on InnoDB like it is on MyISAM, but it can be run.

Run MySQLTuner after 2-3 days of uptime to get better statistics.
0
 

Author Comment

by:matthewi
ID: 40253177
I still have been unable to make any progress on this issue.  Does anyone have any further recommendations?
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40253225
What are the results of the slow query log after the suggest configuration changes (removing log-queries-not-using-indexes)?
0
 

Author Comment

by:matthewi
ID: 40253385
The slow query is completely empty other than the first line with information about the process starting
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40253720
Interesting. And you had lowered the slow query time to 2?

A few more suggestions. First, enable the general query log:
http://dev.mysql.com/doc/refman/5.1/en/query-log.html

After it's enabled and you've restarted MySQL, install and run Process Explorer from Microsoft:

Process Explorer:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

...and keep it running until you see the 100% spikes happen a few times.

You might also want to try installing and running Process Monitor and then filtering it down to only the mysqld.exe process, but if the spikes don't happen all the time, then this might log too much information to be useful:

Process Monitor:
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

In any event, once you see the CPU spikes happen, make copies of the query log each time they've finished. Between that practice and the process explorer (which will show you a short history of CPU activity, so you don't have to sit there and watch), you may be able to find some common patterns in the copied query log that points to particular queries causing problems.

Generally speaking, MySQL doesn't do much by itself - it needs queries like an employee needs a task, so the problem is likely STARTING with some query.

If it's any kind of I/O issue, then the Process Monitor might shed some light on what's happening at that level, but there's going to be a ton of information (even the simplest of any task of any application usually generates hundreds or thousands of events), so it's usually better to only run Process Monitor when you know the problem is going to happen.

Anyway, give those items a shot and see what kind of information shows up.

Oh, and also, have you tried installing another version of MySQL (maybe a more recent version) ? I don't know how you have it installed, but I've occasionally seen problems with the applications bundled in some of those all-in-one packages like WampServer or XAMPP. Sometimes it can help to download the latest version, ensuring you have the right platform build (e.g. 64-bit if you're on a 64-bit OS):

http://dev.mysql.com/downloads/mysql/

I probably wouldn't try this as a first step unless you're sure that an upgrade is acceptable, but it's probably safe if you're on a similar version (always make backup copies of the data, of course).
0
 

Author Comment

by:matthewi
ID: 40254242
I can't use process monitor because the MySQL is on Linux.  I upgrade the MySQL version when I moved my database from my webserver to the Linux server.  The CPU issues were on the mysqld.exe process on the windows machine and stayed on the Linux machine with a clean install of OS and MySQL after moving the DB.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40254463
I don't understand. mysqld.exe is a Windows process for the MySQL daemon. If you moved MySQL to Linux, then there's no reason for mysqld.exe to even be running on Windows.

You said this was a Windows 2008 / IIS web server, and mysqld.exe is the MySQL service process for Windows. Are you saying that you had CPU spikes on Linux? If so, they wouldn't be mysqld.exe, since the Linux binary file for MySQL doesn't use file extensions. So where is MySQL running today, and which machine is experiencing the CPU spikes?
0
 

Author Comment

by:matthewi
ID: 40255406
I need to clarify and fix some mistakes.  Originally, my web server and and MySQL server were one and the same.  They both ran on a Windows Server 2008 system.  One of the earlier comments suggested I offload the MySQL server to another machine completely.  I created a Linux VM and installed MySQL server and copied the database over to that and pointed my web server to the Linux DB server.  Even after moving the MySQL server to another machine, the MySQL process continues to spike.  I use "top" to show processor usage on the Linux machine.
0
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 2000 total points
ID: 40255776
Okay. While I usually don't like to disagree with the other experts, I disagree with the idea of moving the database to a different server - at least as a solution for this problem. It's one thing to move the database to a different server to improve overall performance, but not as a solution to a specific bug.

I don't know how you set up the Linux server in terms of network topology, but it also needs to be done in a way that the server is quickly accessible from the script. For example, if your IIS server is on your local network and you created a virtual Linux machine on a remote service like AWS, then you might actually see LOWER performance from the network overhead.

If your traffic volume is low, then it's usually a good idea to have the database server on the web server. If you're an exceptional network admin and you know what you're doing in terms of securing and hardening two separate servers properly, then go for it.

Anyways, back to the problem at hand. Is mysqld.exe still running on the IIS server today? If so, are you still seeing it hit 100% CPU, even with the Linux server handling the traffic? Or does it just sit there?

If it's not running, then I would suggest switching back to having the database service and web service on the same Windows machine again, so that you can run the process explorer and query log.
0
 

Author Comment

by:matthewi
ID: 40256712
The general query log fills up so quickly that there's no way to make heads or tails of it.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40256898
That brought my attention back to your statistics and I just realized you have an ENORMOUS amount of queries - roughly 220 queries per second, and 151 connections. How many unique visitors do you have per minute?

It's possible you might have a coding issue here. Code that is written improperly -could- hammer the database server with tons of queries until the database has to stop and catch its breath, so to speak.

I would definitely run some statistics on the general query log - see how many queries are unique / duplicate, see how many queries are run by each visitor / each page, etc...

A lot of the time I see these types of numbers on a non-enterprise site, it's usually because of code that is running queries in a loop.
0
 

Author Comment

by:matthewi
ID: 40263588
I don't have that many visitors...I'm having trouble finding the source of the huge volume of queries.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40263601
In the query log, do you see the same query over and over again with minimal changes (like a number changing, or even nothing changing at all)? Like:

SELECT * FROM somewhere WHERE ID=1
SELECT * FROM somewhere WHERE ID=2
SELECT * FROM somewhere WHERE ID=3
SELECT * FROM somewhere WHERE ID=4
...etc...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question