Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

mysql couple slow queries to TABLE_SCHEMA

Discussion in 'Database Discussion' started by hekri, Jul 14, 2010.

  1. hekri

    hekri Well-Known Member

    Joined:
    Oct 14, 2003
    Messages:
    147
    Likes Received:
    2
    Trophy Points:
    168
    Hello

    On 2 servers we have more mysql usage on other servers.

    In mysqld.log we have couple lines:
    HTML:
    # Time: 100712 12:31:12
# User@Host: root[root] @ localhost []
# Query_time: 70  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100712 16:31:11
# User@Host: root[root] @ localhost []
# Query_time: 69  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100712 20:31:08
# User@Host: root[root] @ localhost []
# Query_time: 66  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713  0:31:05
# User@Host: root[root] @ localhost []
# Query_time: 63  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713  4:31:10
# User@Host: root[root] @ localhost []
# Query_time: 69  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713  8:31:02
# User@Host: root[root] @ localhost []
# Query_time: 60  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713 12:31:24
# User@Host: root[root] @ localhost []
# Query_time: 82  Lock_time: 0  Rows_sent: 494  Rows_examined: 25867
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713 16:31:16
# User@Host: root[root] @ localhost []
# Query_time: 74  Lock_time: 0  Rows_sent: 494  Rows_examined: 25880
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100713 20:31:05
# User@Host: root[root] @ localhost []
# Query_time: 63  Lock_time: 0  Rows_sent: 495  Rows_examined: 25901
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100714  0:31:03
# User@Host: root[root] @ localhost []
# Query_time: 62  Lock_time: 0  Rows_sent: 495  Rows_examined: 25901
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
# Time: 100714  4:31:13
# User@Host: root[root] @ localhost []
# Query_time: 71  Lock_time: 0  Rows_sent: 495  Rows_examined: 25901
select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;

    What is thise? why fro user root? Please help.
     
    #1 hekri, Jul 14, 2010
  2. jave

    jave Member

    Joined:
    Oct 19, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    51
    The couple of lines that you see in the log is nothing but a mysql query for tallying the mysql database space into disk space. This is a new cpanel feature. This query runs every time when you load your cpanel. This query can take quite a little while to run, especially if you have quite a few large databases. Each time it runs, a new 'cache' file is created. This 'cache' file was setup to run every 4 hours.
     
    #2 jave, Jul 14, 2010
  3. hekri

    hekri Well-Known Member

    Joined:
    Oct 14, 2003
    Messages:
    147
    Likes Received:
    2
    Trophy Points:
    168
    ok thanx :)
     
    #3 hekri, Jul 14, 2010
  4. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,561
    Likes Received:
    42
    Trophy Points:
    308
    cPanel Access Level:
    Root Administrator
    This is a feature we are continuing to refine in 11.25.1. On some systems this query generates tremendous loads. The goal of the refinement is to lessen the load on such systems.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 cPanelKenneth, Jul 14, 2010
  5. hekri

    hekri Well-Known Member

    Joined:
    Oct 14, 2003
    Messages:
    147
    Likes Received:
    2
    Trophy Points:
    168
    my system is centos 5 - 64bit on raid 10
     
    #5 hekri, Jul 14, 2010
  6. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,171
    Likes Received:
    370
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Important cPanel/WHM Version Number Designation Change

    Please Note: Important cPanel/WHM Version Number Designation Change

    As of July 28, 2010 the cPanel/WHM version number designations have been officially changed.

    Version 11.25.1 is now designated 11.28 and version 11.25.2 is now designated 11.30.

    These new changes were explained in some detail recently at the July 2010 - Quarterly Road map - Webinar direct from cPanel's PodCast Studio in Houston, Texas with speakers David Grega and Mario Rodriguez.

    An official press release about these changes is forthcoming and can be accessed at this link as soon as it's made available to the Forum Team:
    Important cPanel/WHM Version Number Designation Change (To be updated)

    This post serves to update users who are subscribed to threads (where this message is posted) looking forward to upcoming enhancements in future versions of cPanel.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 Infopro, Jul 30, 2010
  7. rclemings

    rclemings Active Member

    Joined:
    Nov 5, 2007
    Messages:
    43
    Likes Received:
    4
    Trophy Points:
    58
    I'm on 11.26.20 and this thing is still killing me. Is there an easy way to disable it or at least set it to cache for a longer time? I don't really think I need it. If I could get it to run one per day in the middle of the night that would help. Or once per week even.

    Code:
    Count: 310  Time=56.02s (17367s)  Lock=0.00s (0s)  Rows=23.0 (7130), root[root]@localhost
  select TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA
     
    #7 rclemings, Dec 10, 2010
  8. VeZoZ

    VeZoZ Well-Known Member

    Joined:
    Dec 14, 2002
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    166
    cPanel Access Level:
    DataCenter Provider

    I believe in tweak settings you can turn it off "Include databases in disk usage calculations" turn that off then it should not calculate disk usage from the databases.
     
    #8 VeZoZ, Dec 10, 2010
  9. rclemings

    rclemings Active Member

    Joined:
    Nov 5, 2007
    Messages:
    43
    Likes Received:
    4
    Trophy Points:
    58
    That's in 11.28. The only thing I see similar to that in 11.26 is "Calculate the disk usage of account MySQL and PostgreSQL databases" and it's unchecked. Yet that query keeps running, every four hours.
     
    #9 rclemings, Dec 10, 2010
  10. rclemings

    rclemings Active Member

    Joined:
    Nov 5, 2007
    Messages:
    43
    Likes Received:
    4
    Trophy Points:
    58
    Aha ... it's in the root crontab:

    30 */4 * * * /usr/bin/test -x /scripts/update_db_cache && /scripts/update_db_cache
     
    #10 rclemings, Dec 10, 2010
  11. rclemings

    rclemings Active Member

    Joined:
    Nov 5, 2007
    Messages:
    43
    Likes Received:
    4
    Trophy Points:
    58
    Well, great. If you remove it from the crontab, upcp puts it right back in again. Grrrr.
     
    #11 rclemings, Dec 13, 2010
  12. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    3
    Trophy Points:
    68
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    In 11.26 /scripts/update_db_cache utilizes MySQL information schema when calculating disk usage for databases (if you're MySQL version is 5.x)

    You can place the following in your cpanel conifg (/var/cpanel/cpanel.config) to force /scripts/update_db_cache to utilize a `du` style disk calculation. This should help you.

    Code:
    use_information_schema=0
    Also, you shouldn't need to alter the cron task, or anything of the like. The code in /scripts/update_db_cache should work in a more sane way (for your situation) once you place that value in your config. I don't think you'll need to restart cpanel, but might be a good measure (/etc/init.d/cpanel restart)

    CAVEAT EMPTOR: This setting is not exposed in the Tweak Settings in 11.26. While it should be pretty straight forward, I always advise caution for unofficial tweaks (like placing values directly in the config file). Testing for something like that cannot be guaranteed: it wasn't an official, fully developed feature at the time 11.26 code base was released.

    In 11.28, there is a tweak setting; the code is feature complete and all that code base has be well tested with/without that setting enabled.

    Regards,
    -DavidN
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #12 cPanelDavidN, Dec 14, 2010
  13. rclemings

    rclemings Active Member

    Joined:
    Nov 5, 2007
    Messages:
    43
    Likes Received:
    4
    Trophy Points:
    58
    That seems to help. At least it's not showing up in the slow-query log now. thx

     
    #13 rclemings, Dec 14, 2010
  14. HostingH

    HostingH Well-Known Member

    Joined:
    Jan 13, 2008
    Messages:
    123
    Likes Received:
    17
    Trophy Points:
    68
    cPanel Access Level:
    Root Administrator
    Hi cPanelDavidN,

    Can we run this cron on daily basis?

    30 */4 * * * /usr/bin/test -x /scripts/update_db_cache && /scripts/update_db_cache
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #14 HostingH, Jan 22, 2011
    Last edited: Jan 22, 2011
  15. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,609
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello rajguru,

    Are you asking if you can run the cron once a day rather than every 4 hours at 30 minutes after the hour, which is the current setting?

    If you change the cron times, a /scripts/upcp --force may re-enter the cron entry back to the original format at some point or if you re-save Tweak Settings it might change back again at some point.

    To check if they do it by default, I tested changing the cron to the following to run at 4:30am server time every day in /var/spool/cron/root entry:

    30 4 * * * /usr/bin/test -x /scripts/update_db_cache && /scripts/update_db_cache

    I then ran /scripts/upcp --force and it didn't change the cron time. I then also tried re-saving Tweak Settings and it also didn't change the cron time, but since crons are periodically updated by cPanel for configurations, it's possible it would revert at some point to the prior time settings due to that.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #15 cPanelTristan, Jan 24, 2011
  16. HostingH

    HostingH Well-Known Member

    Joined:
    Jan 13, 2008
    Messages:
    123
    Likes Received:
    17
    Trophy Points:
    68
    cPanel Access Level:
    Root Administrator
    Hi Tristan,

    Yes..I want to run above cron on daily basis. I have also changed the cron time but as you said crons are periodically updated by cPanel for configurations which has set cron time as it is. Now, I would like to ask you that, is there any problem..If I run this cron on daily/weekly basis. Or is it necessary to run it after every 4.30 hrs?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #16 HostingH, Jan 26, 2011
    Last edited: Jan 26, 2011
  17. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,609
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hi rajguru,

    I don't see any issue with changing the cron times to daily if you don't want to have the database usage as frequently updated for users. I wouldn't suggest doing it weekly, because then you might not know if someone suddenly has a 50GB database added to their account when checking their account until a week later when you get the usage updating. There are a variety of reasons not knowing the database sizes might be problematic on a weekly basis for you as the system administrator as well as your account users.

    Thanks.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #17 cPanelTristan, Jan 26, 2011
  18. HostingH

    HostingH Well-Known Member

    Joined:
    Jan 13, 2008
    Messages:
    123
    Likes Received:
    17
    Trophy Points:
    68
    cPanel Access Level:
    Root Administrator
    Thanks Tristan,

    I will set it on daily basis for my servers.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #18 HostingH, Jan 28, 2011
(You must log in or sign up to post here.)
Loading...
Similar Threads - mysql couple slow
  1. RobNH

    MySQLi - Too Many Connections Error due to Amazon AWS

    RobNH, Jun 21, 2018 at 7:47 AM, in forum: Database Discussion
    Replies:
    5
    Views:
    41
    Infopro
    Jun 21, 2018 at 10:03 AM
  2. geekbaaz

    Multiple cPanel servers with a common MySQL server?

    geekbaaz, Jun 21, 2018 at 3:24 AM, in forum: Database Discussion
    Replies:
    2
    Views:
    57
    cPanelMichael
    Jun 21, 2018 at 11:32 AM
  3. cosmin

    MySQL InnoDB errors after upgrade to 5.7

    cosmin, Jun 20, 2018 at 11:46 PM, in forum: Database Discussion
    Replies:
    2
    Views:
    46
    cPanelMichael
    Jun 21, 2018 at 10:31 AM
  4. tyuuu

    mysql_pconnect can not work after change password/username

    tyuuu, Jun 19, 2018 at 9:14 AM, in forum: Database Discussion
    Replies:
    10
    Views:
    63
    cPanelLauren
    Jun 20, 2018 at 9:38 AM
  5. Skin

    Migration from VPS with MySQL 5.6 to a VPS with MySQL 5.7

    Skin, Jun 18, 2018, in forum: Database Discussion
    Replies:
    2
    Views:
    37
    Skin
    Jun 19, 2018 at 12:09 PM

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice