Results 1 to 16 of 16
  1. #1
    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>>

  2. #2
    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!!!

  3. #3
    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>>

  4. #4
    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

  5. #5
    Join Date
    Nov 2011
    Location
    EU
    Posts
    729
    Quote Originally Posted by zippyhosts View Post
    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
    no... that's not the full output... also, only one command, not 2 as requested
    SpiderVPS.com
    It's official: WE'RE CRAZY!!!

  6. #6
    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>>

  7. #7
    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!!!

  8. #8
    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>>

  9. #9
    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!!!

  10. #10
    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. #11
    Join Date
    Nov 2011
    Location
    EU
    Posts
    729
    but again, it's not the full query...
    SpiderVPS.com
    It's official: WE'RE CRAZY!!!

  12. #12
    Join Date
    Jan 2010
    Posts
    84
    how can i get the full query
    <<Please see rules for signature setup>>

  13. #13
    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!!!

  14. #14
    Join Date
    Mar 2005
    Location
    Ten1/0/2
    Posts
    2,529
    Quote Originally Posted by zippyhosts View Post
    how can i get the full query
    look within your application for the query, assuming that the code is not encrypted. If the code is encrypted, I would assume that you have purchased a script. In that case, ask for support from whomever wrote the script.
    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

  15. #15
    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>>

  16. #16
    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

  1. MYSQL is taking too much of CPU load
    By itajooba in forum Hosting Security and Technology
    Replies: 8
    Last Post: 04-17-2010, 10:17 AM
  2. Trouble with MySQL taking up CPU
    By Sykoi in forum Hosting Security and Technology
    Replies: 18
    Last Post: 07-05-2006, 01:18 AM
  3. mysql process taking up all my CPU
    By BootsSiR in forum Hosting Security and Technology
    Replies: 1
    Last Post: 06-16-2006, 08:57 PM
  4. MySQL Taking up 25% CPU Usage Since Upgrading PHP
    By JTM in forum Hosting Security and Technology
    Replies: 3
    Last Post: 08-19-2005, 07:19 PM
  5. XFS taking 100% CPU!!
    By ezclassified in forum Hosting Security and Technology
    Replies: 5
    Last Post: 01-20-2005, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •