Results 1 to 16 of 16
Thread: Mysql Taking 100% CPU Load
-
11-21-2013, 04:39 PM #1Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Mysql Taking 100% CPU Load
Hello,
one of my client logs into his Image hosting admin panel, during that time, I see 100% CPU utilization for Mysql
It takes at least 5 mins to login to the admin panel and during that time entire website will be down.
when i opened ticket with my hosting company they informed that we have to add indexes to Mysql DB. I am not sure how to do that.
Can someone help me
Below is the SQL generated when we login to admin panel
root@dns [~]# mysqladmin processlist
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 4595 | leechprotect | localhost | leechprotect | Sleep | 2037 | | |
| 4762 | db_image | localhost | db_image | Query | 2 | Sending data | SELECT DISTINCT a_id, DATE(ph_upload_dt), u_login, a_name FROM albums_527, photos_639, users WHERE a |
| 4763 | root | localhost | | Query | 0 | | show processlist |
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
Top Command
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
20850 mysql 20 0 1037m 110m 5396 S 94.9 1.6 3:18.01 mysqld<<Please see rules for signature setup>>
-
11-21-2013, 04:46 PM #2Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
that query is longer than what you have posted above... and it won't help because we have no idea what table is albums_527, what hardware you're using, how is the IO, if you're using innodb or myisam and a lot of other things...
try to get the full query and execute it manually... it's a simple select as i can see, so it won't harm...
if it's still getting 100%, try to do a
# mysql> show create table albums_527;
# mysql> explain select.....;
and post the result here. Most probably you have no indexes or if you have, you're using them wrong...
Good luck!SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 06:17 PM #3Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Hi,
thanks for replying back.
below is the output
Table Create Table
albums_527 CREATE TABLE `albums_527` (
`a_id` int(11) NOT N...
i am not getting 100% query. can you help me here<<Please see rules for signature setup>>
-
11-21-2013, 06:17 PM #4The Linux Specialist
- Join Date
- Mar 2003
- Location
- /root
- Posts
- 23,990
Moved > Hosting Security and Technology.
Specially 4 U
Reseller Hosting: Boost Your Websites | Fully Managed KVM VPS: 3.20 - 5.00 Ghz, Pure Dedicated Power
JoneSolutions.Com is on the net 24/7 providing stable and reliable web hosting solutions, server management and services since 2001
Debian|Ubuntu|cPanel|DirectAdmin|Enhance|Webuzo|Acronis|Estela|BitNinja|Nginx
-
11-21-2013, 06:19 PM #5Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 06:23 PM #6Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Hello,
what is the command should i enter. when i enter show create table albums_527; in phpmyadmin SQL tab, i got the output which i pasted in the above reply<<Please see rules for signature setup>>
-
11-21-2013, 06:25 PM #7Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
as root on your machine do:
# mysql
mysql> show create table albums_527;
mysql> explain select <put here the full select>;SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 06:28 PM #8Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Hello,
below is the output
mysql> show create table albums_527;
+------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------+
| albums_527 | CREATE TABLE `albums_527` (
`a_id` int(11) NOT NULL AUTO_INCREMENT,
`a_u_id` int(11) NOT NULL,
`a_name` varchar(50) NOT NULL,
`a_views` int(11) NOT NULL DEFAULT '0',
`a_private` tinyint(1) DEFAULT '0',
`a_removed` tinyint(1) DEFAULT '0',
`a_update_dt` datetime DEFAULT NULL,
PRIMARY KEY (`a_id`)
) ENGINE=InnoDB AUTO_INCREMENT=369 DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------+
1 row in set (0.00 sec)
how can i enter the second command. what should i place as Put here the full select<<Please see rules for signature setup>>
-
11-21-2013, 06:31 PM #9Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
lol
should be the full query... as you described in the first post:
"SELECT DISTINCT a_id, DATE(ph_upload_dt), u_login, a_name FROM albums_527, photos_639, users WHERE a..."SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 06:38 PM #10Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Below is the output
mysql> explain SELECT DISTINCT a_id, DATE(ph_upload_dt), u_login, a_name FROM albums_527, photos_639, users WHERE a
-> ;
ERROR 1054 (42S22): Unknown column 'a' in 'where clause'<<Please see rules for signature setup>>
-
11-21-2013, 06:44 PM #11Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
but again, it's not the full query...
SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 06:57 PM #12Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
how can i get the full query
<<Please see rules for signature setup>>
-
11-21-2013, 06:59 PM #13Web Hosting Master
- Join Date
- Nov 2011
- Location
- EU
- Posts
- 729
when it's happening again:
# mysql
mysql> show full processlist;SpiderVPS.com
It's official: WE'RE CRAZY!!!
-
11-21-2013, 07:22 PM #14Web Hosting Master
- Join Date
- Mar 2005
- Location
- Ten1/0/2
- Posts
- 2,529
CPanel Shared and Reseller Hosting, OpenVZ VPS Hosting. West Coast (LA) Servers and Nodes
Running Linux since 1.0.8 Kernel!
Providing Internet Services since 1995 and Hosting Since 2004
-
11-21-2013, 08:07 PM #15Junior Guru Wannabe
- Join Date
- Jan 2010
- Posts
- 84
Below is the query
mysql> show full processlist;
+-------+--------------+-----------+--------------+---------+------+------------ --+----------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+--------------+-----------+--------------+---------+------+------------ --+----------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------+
| 9232 | eximstats | localhost | eximstats | Sleep | 2756 | | NULL |
| 13254 | root | localhost | db_image | Query | 0 | NULL | show full processlist |
| 13459 | leechprotect | localhost | leechprotect | Sleep | 3192 | | NULL |
| 13877 | db_image | localhost | db_image | Query | 3 | Sending dat a | SELECT DISTINCT a_id, DATE(ph_upload_dt), u_login, a_name FROM albums_527, p hotos_639, users WHERE a_id > 0 AND a_id = ph_a_id AND a_u_id = u_id AND DATE(ph _upload_dt) = (SELECT MAX(DATE(ph_upload_dt)) FROM photos_639 WHERE ph_a_id = a_ id) ORDER BY ph_upload_dt DESC |
+-------+--------------+-----------+--------------+---------+------+------------ --+----------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------+
4 rows in set (0.00 sec)<<Please see rules for signature setup>>
-
11-22-2013, 12:35 AM #16Newbie
- Join Date
- Sep 2013
- Posts
- 10
I've been working with MySQL in BigData processing for a long time, and the trouble often comes from:
- SELECT DISTINCT => DISTINCT is harmful if your table is large and no index on the DISTINCT field.
- Disk I/O => try to test your disk I/O.
Hope this help.
Similar Threads
-
MYSQL is taking too much of CPU load
By itajooba in forum Hosting Security and TechnologyReplies: 8Last Post: 04-17-2010, 10:17 AM -
Trouble with MySQL taking up CPU
By Sykoi in forum Hosting Security and TechnologyReplies: 18Last Post: 07-05-2006, 01:18 AM -
mysql process taking up all my CPU
By BootsSiR in forum Hosting Security and TechnologyReplies: 1Last Post: 06-16-2006, 08:57 PM -
MySQL Taking up 25% CPU Usage Since Upgrading PHP
By JTM in forum Hosting Security and TechnologyReplies: 3Last Post: 08-19-2005, 07:19 PM -
XFS taking 100% CPU!!
By ezclassified in forum Hosting Security and TechnologyReplies: 5Last Post: 01-20-2005, 07:14 PM