Mysql – the impact of SHOW STATUS, and how often is acceptable to call it

MySQLperformancestatistics

I am setting up a monitoring thread in my application that should use the SHOW STATUS call of mysql to periodically calculate system / db runtime statistics.

Being relatively unfamiliar with datbase backends, my question is

What is the impact of running SHOW STATUS against the database? And how often is acceptable to call it? Would this cause any significant db slow downs?

The rest of my application collects statistics once per minute.

Best Answer

SHOW STATUS has minor impact on the system.

For monitoring, you really need SHOW GLOBAL STATUS so you can get the system-wide counters, not the SESSION counters for the monitor's connection.

It is probably unreasonable to do SHOW GLOBAL STATUS more than once a second. And, at that frequency, it has negligible overhead. Keep in mind that you then have to do something with the hundreds of values. If you turn around and INSERT it into the same server, that action may be more invasive.

Or is this what you are doing to see what is happening with a query?

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

Again, do it as needed; don't worry.

Related Solutions

Mysql – Why do MySQL threads often show “freeing items” status when the query cache is disabled

Check the value of innodb_thread_concurrency.

For my system increasing the value from 8 to 32, per the guidelines in the MySql documentation, caused a discernible decrease in the number of threads concurrently reporting the "freeing items" state. Also, the obesrved average query time dropped by an order of magnitude.

While this made a large difference in overall server performance, it was not the "freeing items" silver bullet. My hardware ecosystem leads me to hypothesize that this state is mostly seen on systems with "slow" disks (2x10k disks raid 1), and is less prevalent on systems with faster storage (12x15k disks raid 10). So, a check of disk performance may also be warranted.

Good Luck!

Also:

It is worth noting that the default value of innodb_thread_concurrency is radically different depending on what 5.0 point release is being used.

The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on. -- source

This means that a seemingly innocuous upgrade from 5.0.20 to 5.0.21 changed the default from infinite to 8, and brought along with it the performance ramifications.

Mysql – How to SELECT from SHOW TABLE STATUS results

This has more columns than SHOW TABLE STATUS; but does the trick:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

UPDATE 2011-06-07 19:02

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();

These queries work if you set the current database.

You can also hard code the specific database:

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';

Best Answer

Related Solutions

Mysql – Why do MySQL threads often show “freeing items” status when the query cache is disabled

Mysql – How to SELECT from SHOW TABLE STATUS results

Related Question