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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysql couple slow queries to TABLE_SCHEMA

Discussion in 'Database Discussions' 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,553
    Likes Received:
    40
    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.
     
    #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,064
    Likes Received:
    347
    Trophy Points:
    433
    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.
     
    #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:
    1
    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
     
    #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:
    122
    Likes Received:
    15
    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
     
    #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:
    31
    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!
     
    #15 cPanelTristan, Jan 24, 2011
  16. HostingH

    HostingH Well-Known Member

    Joined:
    Jan 13, 2008
    Messages:
    122
    Likes Received:
    15
    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?
     
    #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:
    31
    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.
     
    #17 cPanelTristan, Jan 26, 2011
  18. HostingH

    HostingH Well-Known Member

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

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

    InnoDB crashed, no MySQL service available

    Peter Horvath, Apr 17, 2018 at 6:03 PM, in forum: Database Discussions
    Replies:
    2
    Views:
    40
    Infopro
    Apr 17, 2018 at 8:28 PM
  2. Spuddyboy

    SOLVED Setup Remote MySQL - WHM 68

    Spuddyboy, Apr 16, 2018 at 9:00 AM, in forum: Database Discussions
    Replies:
    3
    Views:
    43
    cPanelMichael
    Apr 17, 2018 at 8:37 AM
  3. sergios

    MySQL Remoto em Produção

    sergios, Apr 11, 2018, in forum: Discussões em Português
    Replies:
    3
    Views:
    68
    cPanelLauren
    Apr 18, 2018 at 7:52 AM
  4. gersonfs

    Permanently modify /etc/systemd/system/mysql.service

    gersonfs, Apr 3, 2018, in forum: Database Discussions
    Replies:
    6
    Views:
    76
    cPanelMichael
    Apr 3, 2018
  5. baowesome

    MySQL Very High CPU Usage issue

    baowesome, Apr 2, 2018, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    112
    cPanelMichael
    Apr 2, 2018

Share This Page