mysql couple slow queries to TABLE_SCHEMA

[hekri]

Hello

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

In mysqld.log we have couple lines:

# Time: 100712 12:31:12
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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.

 

[jave]

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.

 

[hekri]

ok thanx

 

[cPanelKenneth]

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.

 

[hekri]

my system is centos 5 - 64bit on raid 10

 

[Infopro]

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.

 

[rclemings]

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.

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.

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

 

[VeZoZ]

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.

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

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.

 

[rclemings]

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.

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.

 

[rclemings]

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.

Aha ... it's in the root crontab:

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

 

[rclemings]

Aha ... it's in the root crontab:

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

Well, great. If you remove it from the crontab, upcp puts it right back in again. Grrrr.

 

[cPanelDavidN]

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.

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

 

[rclemings]

That seems to help. At least it's not showing up in the slow-query log now. thx

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.

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

 

[HostingH]

Hi cPanelDavidN,

Can we run this cron on daily basis?

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

 

[cPanelTristan]

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!

 

[HostingH]

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?

 

[cPanelTristan]

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.

 

[HostingH]

Thanks Tristan,

I will set it on daily basis for my servers.