How MySQL performance metrics are calculated

--Generate report file to/tmp directory

Tee/tmp/mysql_performance_stat.txt

--Statistical performance indicators to open the following parameters, which uses the IS database to hold database information, due to the use of PS library storage there is still a bug, information statistics are not complete

Show variables like ' show_compatibility_56 ';

Set global show_compatibility_56=on;

Show variables like ' show_compatibility_56 ';

-QPS calculation (mainly for MyISAM engines)

Select Variable_value into @v_questions from information_schema.global_status t1 where t1.variable_name= ' questions ';

Select Variable_value into @v_uptime from information_schema.global_status t1 where t1.variable_name= ' uptime ';

Select round (@v_questions/@v_uptime, 3) as "MYISAM/QPS";

-QPS calculation (mainly for InnoDB engines)

Select Variable_value into @v_com_update from information_schema.global_status t1 where t1.variable_name= ' com_update ';

Select Variable_value into @v_com_select from information_schema.global_status t1 where t1.variable_name= ' com_select ';

Select Variable_value into @v_com_insert from information_schema.global_status t1 where t1.variable_name= ' Com_insert ';

Select Variable_value into @v_com_delete from information_schema.global_status t1 where t1.variable_name= ' com_delete ';

Select Variable_value into @v_uptime from information_schema.global_status t1 where t1.variable_name= ' uptime ';

Select Round ((@[email protected][email protected][email protected]_com_delete)/@v_uptime, 3) as "INNODB/QPS";

--TPS calculation (number of transactions per second)

Select Variable_value into @v_com_commit from information_schema.global_status t1 where t1.variable_name= ' com_commit ';

Select Variable_value into @v_com_rollback from information_schema.global_status t1 where T1.variable_name= ' com_ Rollback ';

Select Variable_value into @v_uptime from information_schema.global_status t1 where t1.variable_name= ' uptime ';

Select Round ((@[email protected]_com_rollback)/@v_uptime, 3) as "Innodb/tps (number of transactions per second)";

--InnoDB Cache Hit rate

Select Variable_value into @v_read_requests from information_schema.global_status t1 where T1.variable_name= ' innodb_ Buffer_pool_read_requests ';

Select Variable_value into @v_read_ahead from information_schema.global_status t1 where T1.variable_name= ' innodb_ Buffer_pool_read_ahead ';

Select Variable_value into @v_reads from information_schema.global_status t1 where T1.variable_name= ' Innodb_buffer_ Pool_reads ';

Select Concat (Round (@v_read_requests/(@[email protected][email protected]_reads) *100,3), "%") as "InnoDB cache hit ratio";

--Thread Cache Hit rate

Select Variable_value into @v_threads_created from information_schema.global_status t1 where t1.variable_name= ' threads _created ';

Select Variable_value into @v_connections from information_schema.global_status t1 where t1.variable_name= ' connections ‘;

Select Concat (Round ([email protected]_threads_created/@v_connections) *100,3), "%") as "thread cache hit ratio";

--Use of temporary tables

Select Variable_value into @v_Created_tmp_disk_tables from Information_schema.global_status T1 where t1.variable_name= ' Created_tmp_disk_tables ';

Select Variable_value into @v_Created_tmp_files from information_schema.global_status t1 where T1.variable_name= ' Created_tmp_files ';

Select Variable_value into @v_Created_tmp_tables from information_schema.global_status t1 where T1.variable_name= ' Created_tmp_tables ';

Select variable_value/1024/1024 to @v_tmp_table_size from INFORMATION_SCHEMA. Global_variables where variable_name= ' tmp_table_size ';

Select @v_tmp_table_size as "Tmp_table_size (M)", @v_Created_tmp_disk_tables as Created_tmp_disk_tables, @v_Created_ Tmp_tables as Created_tmp_tables, @v_Created_tmp_files as Created_tmp_files,concat (round (@v_Created_tmp_disk_tables /@v_Created_tmp_tables *100,3), "%") as "temporary table disk utilization";

--Connection ratio

Select Variable_value to @v_max_conn from INFORMATION_SCHEMA. Global_variables where variable_name= ' max_connections ';

Select Variable_value to @v_top_con from INFORMATION_SCHEMA. Global_status where variable_name= ' max_used_connections ';

Select COUNT (*) into @v_current_con from performance_schema.threads where type = ' FOREGROUND ';

Select @v_current_con as "Current number of connections", @v_max_conn as "Maximum Connections", @v_top_con as "maximum peak of connections";

--INNODB Log buffer size setting

Select variable_value/1024/1024 to @v_innodb_log_buffer_size from INFORMATION_SCHEMA. Global_variables where variable_name= ' innodb_log_buffer_size ';

Select Variable_value to @Innodb_log_waits from INFORMATION_SCHEMA. Global_status where variable_name= ' innodb_log_waits ';

Select @v_innodb_log_buffer_size as "Log buffer size (M)", @Innodb_log_waits as Innodb_log_waits;

--Statistics of storage engine distribution

SELECT COUNT (*), engine from INFORMATION_SCHEMA. TABLES WHERE table_schema (' information_schema ', ' performance_schema ', ' sys ', ' MySQL ') GROUP by engine;

SELECT COUNT (*), table_schema,engine from INFORMATION_SCHEMA. TABLES WHERE table_schema (' information_schema ', ' performance_schema ', ' sys ', ' MySQL ') GROUP by Table_schema,engin E

How MySQL performance metrics are calculated