Table of contents:' ; select '
|
|
Statistics generated on: ', now();
select ' by: ', user(), 'as: ',current_user();
select 'using: my2html.103.sql v.1.0.17 (2020-01-14)';
select '
Software by ';
select 'Meo
| Summary | |
| Item', ' | Value'; select ' |
| Version :', ' | ', version() union select ' |
| Created :', ' | ', min(create_time) from tables union select ' |
| Started :', ' | ', date_format(date_sub(now(), INTERVAL variable_value second),'%Y-%m-%d %T') from global_status where variable_name='UPTIME' union select ' |
| DB Size (MB):', ' | ', format(sum(data_length+index_length)/(1024*1024),0) from tables union select ' |
| Buffer Size (MB):', ' | ', format(sum(variable_value+0)/(1024*1024),0) from global_variables where lower(variable_name) like '%buffer_size' or lower(variable_name) like '%buffer_pool_size' union select ' |
| Logging Bin. :', ' | ', variable_value from global_status where variable_name='LOG_BIN' union select ' |
| Defined Users :', ' | ', format(count(*),0) from mysql.user union select ' |
| Defined Schemata :', ' | ', count(*) from schemata where schema_name not in ('information_schema') union select ' |
| Defined Tables :', ' | ', format(count(*),0) from tables union select ' |
| Sessions :', ' | ', format(count(*),0) from processlist union select ' |
| Sessions (active) :', ' | ', format(count(*),0) from processlist where command <> 'Sleep' union select ' |
| Questions (#/sec.) :', ' | ', format(g1.variable_value/g2.variable_value,5) from global_status g1,global_status g2 where g1.variable_name='QUESTIONS' and g2.variable_name='UPTIME' union select ' |
| BinLog Writes Day (MB) :', ' | ', format((g1.variable_value*60*60*24)/(g2.variable_value*1024*1024),0) from global_status g1,global_status g2 where g1.variable_name='INNODB_OS_LOG_WRITTEN' and g2.variable_name='UPTIME' union select ' |
| Hostname :', ' | ', variable_value from information_schema.global_variables where variable_name ='hostname' union select ' |
| Port :', ' | ', variable_value from information_schema.global_variables where variable_name ='port'; select ' |
| Version check | |||||
| Version', ' | Supported', ' | Last releases (N or N-1)', ' | Last updates (N or N-1)', ' | Last update (N) ', ' | Notes'; select ' |
| ', version(); select ' | ', if(SUBSTRING_INDEX(version(),'.',2) in ('8.0','5.7','5.6', '10.4','10.3','10.2','10.1','5.5'), 'YES', 'NO') ; -- supported version BOTH MySQL MariaDB select ' | ', if(SUBSTRING_INDEX(version(),'.',2) in ('8.0','5.7', '10.4','10.3'), 'YES', 'NO') ; -- last2 releases select ' | ', if(SUBSTRING_INDEX(version(),'-',1) in ('10.4.11','10.3.21','10.2.30','10.1.43','5.5.66', '10.4.12','10.3.22','10.2.31','10.1.44','5.5.67', '10.4.13','10.3.23','10.2.32','10.1.45','5.5.68'), 'YES', 'NO') ; -- last2 MariaDB updates (and the next) select ' | ', if(SUBSTRING_INDEX(version(),'-',1)
in ('10.4.12','10.3.22','10.2.31','10.1.44','5.5.67',
'10.4.13','10.3.23','10.2.32','10.1.45','5.5.68'), 'YES', 'NO') ; -- last updates (and the next)
select ' Last Releases (MariaDB): 10.4.12, 10.3.22, 10.2.31, 10.1.44, 5.5.67' from dual; select ' | Last Releases (MySQL): 8.0.19, 5.7.29, 5.6.47' from dual; -- notes select ' |
' ; select '
| Versions |
| ','MySQL:', variable_value from information_schema.global_variables where variable_name ='version' union select ' |
| plugin:',plugin_name, plugin_version from plugins union select ' |
| ',concat(variable_name, ': '), variable_value from information_schema.global_variables where variable_name like 'version%'; select ' |
| Engines | ||
| Engine', ' | Support', ' | Comment'; select ' |
| ', engine, ' | ', support, ' | ', comment from engines order by support; select ' |
| Schema/Object Matrix | ||||||||
| Database', ' | Tables', ' | Indexes', ' | Routines', ' | Triggers', ' | Views', ' | Primary Keys', ' | Foreign Keys', ' | All' ; select ' |
| ', sk, ' | ', sum(if(otype='T',1,0)), ' | ', sum(if(otype='I',1,0)), ' | ', sum(if(otype='R',1,0)), ' | ', sum(if(otype='E',1,0)), ' | ', sum(if(otype='V',1,0)), ' | ', sum(if(otype='P',1,0)), ' | ', sum(if(otype='F',1,0)), ' | ', count(*) from ( select 'T' otype, table_schema sk, table_name name from tables union select 'I' otype, constraint_schema sk, concat(table_name,'.',constraint_name) name from key_column_usage where ordinal_position=1 union select 'R' otype, routine_schema sk, routine_name name from routines union select 'E' otype, trigger_schema sk, trigger_name name from triggers union select 'V' otype, table_schema sk, table_name name from views union select distinct 'P' otype, CONSTRAINT_SCHEMA sk, TABLE_NAME name from KEY_COLUMN_USAGE where CONSTRAINT_NAME='PRIMARY' union select distinct 'F' otype, CONSTRAINT_SCHEMA sk, concat(TABLE_NAME,'-',CONSTRAINT_NAME) name from KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME is not null ) a group by sk with rollup; select ' |
' ; select '
| Index Types | Type', ' | Uniqueness', ' | Avg keys', ' | Max Keys', ' | Count', ' | Columns'; select ' |
| ', index_type, ' | ', if(non_unique, 'Not Unique', 'UNIQUE'), ' | ', avg(seq_in_index), ' | ', max(seq_in_index), ' | ', count(distinct table_schema,table_name, index_name), ' | ', count(*) from statistics group by index_type, non_unique; select ' |
' ; select '
| Orphaned Tables | Table ID', ' | Name', ' | Flags', ' | Row Format'; select ' |
| ', TABLE_ID, ' | ', NAME, ' | ', FLAG, ' | ', ROW_FORMAT from INNODB_SYS_TABLES where name like "%/#%" limit 100; select ' |
| Space Usage | ||||||||
| Database', ' | Row#', ' | Data size', ' | Index size', ' | Total size', ' | ', ' | MyISAM', ' | InnoDB', ' | Created'; select ' |
| ', table_schema, ' | ', format(sum(table_rows),0), ' | ', format(sum(data_length),0), ' | ', format(sum(index_length),0), ' | ', format(sum(data_length+index_length),0), ' | ', ' | ', format(sum((data_length+index_length)* if(engine='MyISAM',1,0)),0), ' | ', format(sum((data_length+index_length)* if(engine='InnoDB',1,0)),0), ' | ', date_format(min(create_time),'%Y-%m-%d') from tables group by table_schema with rollup; select ' |
' ; select '
| Free Space | ||
| Engine', ' | Table#', ' | Free (MB)'; select ' |
| ', engine, ' | ', count(*), ' | ', format(data_free/(1024*1024),0) from tables where engine='InnoDB' group by engine, data_free order by engine, data_free desc limit 8; select ' |
| ', engine, ' | TOTAL' ' | ', format(sum(data_free)/(1024*1024),0) from tables where data_free>0 group by engine order by engine limit 8; select ' |
| MySQL Memory Usage'; select ' | |
| Type', ' | Value (MB)' ; select ' |
| Global Caches | ', format(sum(variable_value)/(1024*1024),0) from global_variables where lower(variable_name) in ( 'innodb_buffer_pool_size', 'query_cache_size', 'innodb_additional_mem_pool_size', 'innodb_log_file_size', 'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache', 'tmp_table_size'); select ' |
| Estimated Client Alloc. (max conn:', g2.variable_value,') | ', format(sum(g1.variable_value*g2.variable_value)/(1024*1024),0) from global_variables g1, global_status g2 where lower(g1.variable_name) in ( 'binlog_cache_size', 'binlog_stmt_cache_size', 'read_buffer_size', 'read_rnd_buffer_size', 'sort_buffer_size', 'join_buffer_size', 'thread_stack') and lower(g2.variable_name)='max_used_connections'; select ' |
' ; select '
| Tuning Parameters (most used ones) | ||
| Parameter', ' | Value | Type' ; select ' |
| ', variable_name, ' | ', variable_value, ' | Flag' from global_variables where lower(variable_name) in ( 'log_bin', 'query_cache_type', 'slow_query_log', 'foo') union select ' |
| ', variable_name, ' | ', format(variable_value,0), ' | Cache' from global_variables where lower(variable_name) in ( 'innodb_buffer_pool_size', 'query_cache_size', 'innodb_additional_mem_pool_size', 'innodb_log_file_size', 'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache', 'tmp_table_size', 'max_heap_table_size', 'foo') union select ' |
| ', variable_name, ' | ', format(variable_value,0), ' | Tuning and timeout' from global_variables where lower(variable_name) in ( 'innodb_flush_log_at_trx_commit', 'innodb_flush_log_at_timeout', 'innodb_log_files_in_group', 'innodb_lock_wait_timeout', 'innodb_thread_concurrency', 'skip-external-locking', 'wait_timeout', 'long_query_time', 'sync_binlog', 'foo') union select ' |
| ', variable_name, ' | ', format(variable_value,0), ' | Client Cache' from global_variables where lower(variable_name) in ( 'binlog_cache_size', 'binlog_stmt_cache_size', 'max_connections', 'read_buffer_size', 'read_rnd_buffer_size', 'sort_buffer_size', 'join_buffer_size', 'thread_stack', 'foo') order by variable_name; select ' |
| Partitioning | ||
| Schema', ' | Partitioned Tables', ' | Partitions' ; select ' |
| ', table_schema, ' | ', count(distinct table_name), ' | ', count(*) from information_schema.partitions where partition_name is not null group by table_schema; select ' |
' ; select '
| Partition details | ||||
| Schema', ' | Table', ' | Method', ' | Partition Count', ' | SubPartition Count' ; select ' |
| ', table_schema, ' | ', table_name, ' | ', partition_method, ifnull(subpartition_method,''), ' | ', count(distinct partition_name), ' | ', count(distinct subpartition_name) from partitions where partition_name is not null group by table_schema, table_name, subpartition_name order by table_schema, table_name, subpartition_name; select ' |
| Users | |||||||
| Host', ' | DB', ' | User', ' | Password', ' | Select', ' | Execute', ' | Grant', ' | Expired'; SELECT ' |
| ',host, ' | ', ' | ', user, ' | ', if(password<>'','','NO PWD'), ' | ', select_priv, ' | ', execute_priv, ' | ', grant_priv, ' | ', password_expired FROM mysql.user d order by user,host; select ' |
| DB Access | |||||||
| ',host, ' | ', db, ' | ', user, ' | ', ' | ', select_priv, ' | ', execute_priv, ' | ', grant_priv FROM mysql.db d order by user,host; select ' |
' ; select '
| Virtual Roles | |
| Access Level | Users' ; select ' |
| Admin | ' ; select concat(user,'@',host) User from mysql.user where insert_priv='Y' or delete_priv='Y' order by 1; select ' |
| Oper | ' ; select concat(user,'@',host) from mysql.user where select_priv='Y' and concat(user,'@',host) not in ( select concat(user,'@',host) from mysql.user where insert_priv='Y' or delete_priv='Y') order by 1; select ' |
| Schema Owner | ' ; select concat(user,'@',host) from mysql.db where create_priv='Y'; select ' |
| CRUD | ' ; select concat(user,'@',host) from mysql.db where insert_priv='Y' and concat(user,'@',host) not in ( select concat(user,'@',host) from mysql.db where create_priv='Y') order by 1; select ' |
| Read Only | ' ; select concat(user,'@',host) from mysql.db where select_priv='Y' and concat(user,'@',host) not in ( select concat(user,'@',host) from mysql.db where insert_priv='Y') order by 1; select ' |
| Other | ' ; select concat(user,'@',host) from mysql.user where concat(user,'@',host) not in ( select concat(user,'@',host) from mysql.db where select_priv='Y') and concat(user,'@',host) not in ( select concat(user,'@',host) from mysql.user where select_priv='Y') order by 1; select ' |
' ; select '
| Users with poor passwords | |||
| Host', ' | User', ' | Password', ' | Note'; SELECT ' |
| ',host, ' | ', user, ' | ', ' | Empty password' FROM mysql.user WHERE password=''; SELECT ' |
| ',host, ' | ', user, ' | ', password, ' | Same as username' FROM mysql.user WHERE password =UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1(user))) AS CHAR))); SELECT ' |
| ',host, ' | ', user, ' | ', password, ' | Same as username' FROM mysql.user WHERE password = BINARY old_password(user); -- Known hash: root, secret, password, mypass, public, private, 1234, admin, secure, pass, mysql, my123, ... SELECT ' |
| ',host, ' | ', user, ' | ', password, ' | Weak password' FROM mysql.user WHERE password in ('*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B', '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7', '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19', '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4', '*A80082C9E4BB16D9C8E41B0D7EED46126DF4A46E', '*85BB02300F877EB061967510E83F68B1A7325252', '*A4B6157319038724E3560894F7F932C8886EBFCF', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', '*A36BA850A6E748679226B01E159EF1A7BF946195', '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7', '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA', '*AF35041D44DF3E88C9F97CC8D3ACAF4695E65B69', password('prova'), password('test'), password('demo'), password('qwerty'), password('manager'), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('moodle'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('drupal'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('admin01'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('joomla'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('wp'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('ilikerandompasswords'))) AS CHAR))), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('changeme'))) AS CHAR))) ); SELECT ' |
| ',host, ' | ', user, ' | ', password, ' | Weak password' FROM mysql.user WHERE password in (old_password('root'), old_password('secret'), old_password('password'), old_password('mypass'), old_password('public'), old_password('private'), old_password('1234'), old_password('admin'), old_password('secure'), old_password('pass'), old_password('mysql'), old_password('my123'), old_password('prova'), old_password('test'), old_password('demo'), old_password('qwerty'), old_password('manager'), old_password('moodle'), old_password('drupal'), old_password('admin01'), old_password('joomla'), old_password('wp'), old_password('ilikerandompasswords'), old_password('changeme') ); SELECT ' |
| ',host, ' | ', user, ' | ', password, ' | Old [pre 4.1] password format' FROM mysql.user WHERE password not like '*%' and password<>'' and password<>'invalid'; SELECT ' |
| ',host, ' | ', user, ' | ', authentication_string, ' | Suspect backdoor user' FROM mysql.user WHERE user in ('hanako', 'kisadminnew1', '401hk$', 'guest', 'Huazhongdiguo110'); select ' |
' ; select '
| Suspect SQL Statements | ||
| Schema', ' | Statement', ' | Count'; select ' |
| ',SCHEMA_NAME,' | ', DIGEST_TEXT, ' | ', COUNT_STAR -- FIRST_SEEN, LAST_SEEN from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '% OR %? = ?%' limit 20; select ' |
| ',SCHEMA_NAME,' | ', DIGEST_TEXT, ' | ', COUNT_STAR -- FIRST_SEEN, LAST_SEEN from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '%mysql.user% limit 20'; select ' |
' ; select '
| Spammable tables | ||||
| Database', ' | Object', ' | Type', ' | Rows', ' | MBytes' ; select ' |
| ', table_schema, ' | ', table_name, ' | T', ' | ', format(table_rows,0), ' | ', format((data_length+index_length)/(1024*1024),0) from tables where (table_name like '%comments' or table_name like '%redirection') and table_rows > 1000 order by table_rows desc; select ' |
|
|
|
| Processes | ||||||
| Id | User | Host'; select ' | DB | Command | Time | State'; select ' |
| ',id, ' | ', user, ' | ', host, ' | ', db, ' | ', command, ' | ', time, ' | ', state from processlist order by id; select ' |
| Running SQL | ||||
| Id | User | Time'; select ' | State | Info'; select ' |
| ',id, ' | ', user, ' | ', time, ' | ', state, ' | ', substr(replace(replace(info,'<','<'),'>','>'),0,512) from processlist where command <> 'Sleep' and command <> 'Daemon' order by id; select ' |
Table Locks' ; show open tables WHERE In_use > 0; select ' |
| InnoDB Statistics |
| Transactions | ||||||||
| Id | TRX Id | State | Started'; select ' | Weight | Req. lock | Query | Operation | Isolation'; select ' |
| ',trx_mysql_thread_id, ' | ',trx_id, ' | ',trx_state, ' | ',trx_started, ' | ',trx_weight, ' | ',trx_requested_lock_id, ' | ',trx_query, ' | ',trx_operation_state, ' | ',trx_isolation_level from INFORMATION_SCHEMA.innodb_trx; select ' |
| Waiting Locks | |||
| TRX Id | Lock Id | Blocking TRX | Blocking Lock'; select ' |
| ',requesting_trx_id, ' | ',requested_lock_id, ' | ',blocking_trx_id, ' | ',blocking_lock_id from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; select ' |
| Locks | |||||
| TRX Id | Lock Id | Mode | Type'; select ' | Table | Index'; select ' |
| ',lock_trx_id, ' | ',lock_id, ' | ',lock_mode, ' | ',lock_type, ' | ',lock_table, ' | ',lock_index from INFORMATION_SCHEMA.INNODB_LOCKS; select ' |
| Pool Statistics | |||||||||
| Pool Id | Size | Free buffers | Database pages'; select ' | Old pages | Modified pages | Pages read | Pages created'; select ' | Pages written | Hit rate'; select ' |
| ',POOL_ID, ' | ',POOL_SIZE, ' | ',FREE_BUFFERS, ' | ',DATABASE_PAGES, ' | ',OLD_DATABASE_PAGES, ' | ',MODIFIED_DATABASE_PAGES, ' | ',NUMBER_PAGES_READ, ' | ',NUMBER_PAGES_CREATED, ' | ',NUMBER_PAGES_WRITTEN, ' | ',HIT_RATE from INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS; select ' |
' ; select '
| Tablespaces | |||
| Tablespace Type | Row Format | Tables', ' | Columns'; SELECT ' |
| ',if(SPACE=0,'System','FilePerTable') TBS,' | ', ROW_FORMAT,' | ', count(*) TABS,' | ', sum(N_COLS-3) COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES group by ROW_FORMAT, if(SPACE=0,'System','FilePerTable'); select ' |
| Performance Statistics Summary | |||
| Statistic | Value | Suggested value | Action to correct'; select ' |
| ', variable_name, ' (days) | ', round(variable_value/(3600*24),1), '', '' from global_status where variable_name='UPTIME' union select ' | ||
| ', 'Buffer Cache: MyISAM Read Hit Ratio', ' | ', format(100-t1.variable_value*100/t2.variable_value,2), ' | >95', ' | Increase KEY_BUFFER_SIZE' from global_status t1, global_status t2 where t1.variable_name='KEY_READS' and t2.variable_name='KEY_READ_REQUESTS' union select ' |
| ', 'Buffer Cache: InnoDB Read Hit Ratio', ' | ', format(100-t1.variable_value*100/t2.variable_value,2), ' | >95', ' | Increase INNODB_BUFFER_SIZE' from global_status t1, global_status t2 where t1.variable_name='INNODB_BUFFER_POOL_READS' and t2.variable_name='INNODB_BUFFER_POOL_READ_REQUESTS' union select ' |
| ', 'Buffer Cache: MyISAM Write Hit Ratio', ' | ', format(100-t1.variable_value*100/t2.variable_value,2), ' | >95', ' | Increase KEY_BUFFER_SIZE' from global_status t1, global_status t2 where t1.variable_name='KEY_WRITES' and t2.variable_name='KEY_WRITE_REQUESTS' union select ' |
| ', 'Log Cache: InnoDB Log Write Ratio', ' | ', format(100-t1.variable_value*100/t2.variable_value,2), ' | >95', ' | Increase INNODB_LOG_BUFFER_SIZE' from global_status t1, global_status t2 where t1.variable_name='INNODB_LOG_WRITES' and t2.variable_name='INNODB_LOG_WRITE_REQUESTS' union select ' |
| ', 'Query Cache: Efficiency', ' | ', format(t1.variable_value*100/(t1.variable_value+t2.variable_value),2), ' | >30', ' | ' from global_status t1, global_status t2 where t1.variable_name='QCACHE_HITS' and t2.variable_name='COM_SELECT' union select ' |
| ', 'Query Cache: Hit Ratio', ' | ', format(t1.variable_value*100/(t1.variable_value+t2.variable_value),2), ' | >80', ' | ' from global_status t1, global_status t2 where t1.variable_name='QCACHE_HITS' and t2.variable_name='QCACHE_INSERTS' union select ' |
| ', s.variable_name, ' | ', concat(s.variable_value, ' /', v.variable_value), ' | Far from maximum', ' | Increase MAX_CONNECTIONS' from global_status s, global_variables v where s.variable_name='THREADS_CONNECTED' and v.variable_name='MAX_CONNECTIONS' union select ' |
| ', variable_name, ' | ', variable_value, ' | LOW', ' | Check user load' from global_status where variable_name='THREADS_RUNNING' union select ' |
| ', variable_name, ' | ', format(variable_value,0), ' | LOW', ' | Check application' from global_status where variable_name='SLOW_QUERIES' union select ' |
| ', g1.variable_name, ' #/sec. | ', format(g1.variable_value/g2.variable_value,5), '', '' from global_status g1,global_status g2 where g1.variable_name='QUESTIONS' and g2.variable_name='UPTIME' union select ' | ||
| ', g1.variable_name, ' #/sec. | ', format(g1.variable_value/g2.variable_value,5), '', '' from global_status g1,global_status g2 where g1.variable_name='COM_SELECT' and g2.variable_name='UPTIME' union select ' | ||
| ', g1.variable_name, ' #/sec. (TPS) | ', format(g1.variable_value/g2.variable_value,5), '', '' from global_status g1,global_status g2 where g1.variable_name='COM_COMMIT' and g2.variable_name='UPTIME' union select ' | ||
| ', g1.variable_name, ' #/sec. | ', format(g1.variable_value/g2.variable_value,5), '', '' from global_status g1,global_status g2 where g1.variable_name='CONNECTIONS' and g2.variable_name='UPTIME' union select ' | ||
| ','COM DML #/sec.',' | ', format((g1.variable_value+g3.variable_value+g4.variable_value+g5.variable_value+g6.variable_value +g7.variable_value+g8.variable_value+g9.variable_value)/g2.variable_value,5), '', '' from global_status g1,global_status g2,global_status g3,global_status g4,global_status g5, global_status g6,global_status g7,global_status g8,global_status g9 where g1.variable_name='COM_INSERT' and g2.variable_name='UPTIME' and g3.variable_name='COM_UPDATE' and g4.variable_name='COM_DELETE' and g5.variable_name='COM_INSERT_SELECT' and g6.variable_name='COM_UPDATE_MULTI' and g7.variable_name='COM_DELETE_MULTI' and g8.variable_name='COM_REPLACE' and g9.variable_name='COM_REPLACE_SELECT' union select ' | ||
| ', g1.variable_name, ' Mb/sec. | ', format(g1.variable_value*8/(g2.variable_value*1024*1024),5), '', '' from global_status g1,global_status g2 where g1.variable_name='BYTES_SENT' and g2.variable_name='UPTIME' union select ' | ||
| ', g1.variable_name, ' Mb/sec. | ', format(g1.variable_value*8/(g2.variable_value*1024*1024),5), '', '' from global_status g1,global_status g2 where g1.variable_name='BYTES_RECEIVED' and g2.variable_name='UPTIME' union select ' | ||
| ', 'DBcpu (SUM_TIMER_WAIT)', ' | ', format((sum(SUM_TIMER_WAIT)/1000000000000)/variable_value, 5), '', '' from information_schema.global_status, performance_schema.events_statements_summary_global_by_event_name where variable_name='UPTIME' order by 1; select ' | ||
| Expert suggestions on | Value | Action to correct'; select ' | |
| ', g1.variable_name, ' #/hour | ', format((g1.variable_value*60*60)/g2.variable_value,5), ' | ', ' | Increase TABLE_OPEN_CACHE' from global_status g1,global_status g2 where g1.variable_name='OPENED_TABLES' and g2.variable_name='UPTIME' and g1.variable_value*60*60/g2.variable_value>12 union select ' |
| ', g1.variable_name, ' #/hour | ', format((g1.variable_value*60*60)/g2.variable_value,5), ' | ', ' | Increase SORT_BUFFER_SIZE' from global_status g1,global_status g2 where g1.variable_name='SORT_MERGE_PASSES' and g2.variable_name='UPTIME' and g1.variable_value*60*60/g2.variable_value>12 union select ' |
| ', g1.variable_name, ' % | ', format(g1.variable_value*100/(g1.variable_value+g2.variable_value),5), ' | ', ' | Increase MAX_HEAP_TABLE_SIZE and TMP_TABLE_SIZE' from global_status g1,global_status g2 where g1.variable_name='CREATED_TMP_DISK_TABLES' and g2.variable_name='CREATED_TMP_TABLES' and g1.variable_value/g2.variable_value>0.2 union select ' |
| ', g1.variable_name, ' % | ', format(g1.variable_value*100/(g2.variable_value),5), ' | ', ' | Increase BINLOG_CACHE_SIZE' from global_status g1,global_status g2 where g1.variable_name='BINLOG_CACHE_DISK_USE' and g2.variable_name='BINLOG_CACHE_USE' and g1.variable_value/g2.variable_value>0.2 union select ' |
| ', g1.variable_name, ' #/hour | ', format((g1.variable_value*60*60)/g2.variable_value,5), ' | ', ' | Increase INNODB_LOG_BUFFER_SIZE' from global_status g1,global_status g2 where g1.variable_name='INNODB_LOG_WAITS' and g2.variable_name='UPTIME' and g1.variable_value*60*60/g2.variable_value>1 union select ' |
| ', g1.variable_name, ' MB/hour | ', format((g1.variable_value*60*60)/(g2.variable_value*1024*1024),5), ' | ', ' | Tune INNODB_LOG_FILE_SIZE' from global_status g1,global_status g2 where g1.variable_name='INNODB_OS_LOG_WRITTEN' and g2.variable_name='UPTIME' and (g1.variable_value*60*60)/(g2.variable_value*1024*1024)>5 order by 1; select ' |
' ; select '
| Performance Schema Statistics |
'; select '
| Consumers | |
| Name', ' | Enabled'; select ' |
| ', NAME, ' | ',ENABLED from performance_schema.setup_consumers order by enabled, name; select ' |
'; select '
| Uptime | ', truncate(variable_value/(3600*24),0), 'g ', SEC_TO_TIME(mod(variable_value, 3600*24)), ' |
| Statement Events | |||
| Event', ' | Count',' | Sum Timer'; select ' | |
| ',EVENT_NAME, ' | ',COUNT_STAR, ' | ',SUM_TIMER_WAIT, ' | ', SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) from performance_schema.events_statements_summary_global_by_event_name where count_star > 0 order by SUM_TIMER_WAIT desc limit 10; select ' |
'; select '
| Wait Events | |||
| Event', ' | Count',' | Sum Timer'; select ' | |
| ',EVENT_NAME, ' | ',COUNT_STAR, ' | ',SUM_TIMER_WAIT, ' | ', SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) from performance_schema.events_waits_summary_global_by_event_name where count_star > 0 and event_name != 'idle' order by SUM_TIMER_WAIT desc limit 10; select ' |
'; select '
| Lock Wait | |||||
| Type',' | Schema',' | Name', ' | Count',' | Sum Timer'; select ' | |
| ',OBJECT_TYPE, ' | ', OBJECT_SCHEMA, ' | ', OBJECT_NAME, ' | ', COUNT_STAR, ' | ', SUM_TIMER_WAIT, ' | ', SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) from performance_schema.table_lock_waits_summary_by_table where count_star > 0 order by SUM_TIMER_WAIT desc limit 10; select ' |
'; select '
| File events | |||
| Event', ' | Count',' | Sum Timer'; select ' | |
| ',EVENT_NAME,' | ',COUNT_STAR,' | ',SUM_TIMER_WAIT, ' | ', SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) from performance_schema.file_summary_by_event_name order by SUM_TIMER_WAIT desc limit 10; select ' |
'; select '
| File access | ||||||||||
| File Name',' | Event Name', ' | Count',' | Sum Timer',' | Human Timer', ' | #Read',' | Timer Read',' | Byte Read', ' | #Write',' | Timer Write',' | Byte Write'; select ' |
| ',FILE_NAME,' | ',EVENT_NAME,' | ',COUNT_STAR,' | ',SUM_TIMER_WAIT,' | ', SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000),' | ', COUNT_READ,' | ',SUM_TIMER_READ,' | ',SUM_NUMBER_OF_BYTES_READ,' | ', COUNT_WRITE,' | ',SUM_TIMER_WRITE,' | ',SUM_NUMBER_OF_BYTES_WRITE from performance_schema.file_summary_by_instance order by SUM_TIMER_WAIT desc limit 10; select ' |
| SQL Statements | ' ; select 'Representativeness:', round((1-sum(if(digest is null, count_star,0))/sum(count_star))*100,2), '%' from performance_schema.events_statements_summary_by_digest; select ' | ||||||||||||
| Schema',' | Text', ' | Count',' | Sum Timer',' | Human Timer',' | Average (sec.)', ' | Rows affected',' | Rows Sent',' | Rows Examined', ' | TMP Disk Create',' | TMP Create', ' | Sort Merge#',' | No Index',' | No Good Index'; select ' |
| ',SCHEMA_NAME,' | ',DIGEST_TEXT,' | ',COUNT_STAR,' | ', SUM_TIMER_WAIT,' | ',SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000),' | ', round(AVG_TIMER_WAIT/1000000000000,3) AVG_TIMER_WAIT,' | ', SUM_ROWS_AFFECTED,' | ',SUM_ROWS_SENT,' | ',SUM_ROWS_EXAMINED,' | ', SUM_CREATED_TMP_DISK_TABLES,' | ',SUM_CREATED_TMP_TABLES,' | ',SUM_SORT_MERGE_PASSES,' | ', SUM_NO_INDEX_USED,' | ',SUM_NO_GOOD_INDEX_USED from performance_schema.events_statements_summary_by_digest order by SUM_TIMER_WAIT desc limit 20; select ' |
| Slowest Statements' ; select ' | |||||||||||||
| Schema',' | Text', ' | Count',' | Sum Timer',' | Human Timer',' | Average (sec.)', ' | Rows affected',' | Rows Sent',' | Rows Examined', ' | TMP Disk Create',' | TMP Create', ' | Sort Merge#',' | No Index',' | No Good Index'; select ' |
| ',SCHEMA_NAME,' | ',DIGEST_TEXT,' | ',COUNT_STAR,' | ', SUM_TIMER_WAIT,' | ',SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000),' | ', round(AVG_TIMER_WAIT/1000000000000,3) AVG_TIMER_WAIT,' | ', SUM_ROWS_AFFECTED,' | ',SUM_ROWS_SENT,' | ',SUM_ROWS_EXAMINED,' | ', SUM_CREATED_TMP_DISK_TABLES,' | ',SUM_CREATED_TMP_TABLES,' | ',SUM_SORT_MERGE_PASSES,' | ', SUM_NO_INDEX_USED,' | ',SUM_NO_GOOD_INDEX_USED from performance_schema.events_statements_summary_by_digest order by AVG_TIMER_WAIT desc limit 5; select ' |
| Biggest Objects | |||||
| Database', ' | Object', ' | Type', ' | Engine', ' | Bytes', ' | Est. rows'; select ' |
| ', table_schema, ' | ', table_name, ' | T',' | ',engine, ' | ', format(data_length+index_length,0), ' | ', format(table_rows,0) from tables order by data_length+index_length desc limit 20; select ' |
| Host Connections | ||
| Host', ' | Current Connections', ' | Total Connections'; select ' |
| ',HOST, ' | ', CURRENT_CONNECTIONS, ' | ', TOTAL_CONNECTIONS from performance_schema.hosts order by CURRENT_CONNECTIONS desc, TOTAL_CONNECTIONS desc; select ' |
| TOTAL HOSTS:',count(distinct HOST), ' | ', sum(CURRENT_CONNECTIONS), ' | ', sum(TOTAL_CONNECTIONS) from performance_schema.hosts; select ' |
' ; select '
| Host Cache | |||||||||
| Host', ' | IP', ' | Validated', ' | SUM Errors', ' | First Seen', ' | Last Seen', ' | Last Error Seen', ' | # Handshake Err.', ' | # Authentication Err.', ' | # ACL Err.' ; select ' |
| ', host, ' | ', ip, ' | ', host_validated, ' | ', SUM_CONNECT_ERRORS ERR, ' | ', FIRST_SEEN, ' | ', LAST_SEEN, ' | ', LAST_ERROR_SEEN, ' | ', COUNT_HANDSHAKE_ERRORS, ' | ', COUNT_AUTHENTICATION_ERRORS, ' | ', COUNT_HOST_ACL_ERRORS from performance_schema.host_cache; select ' |
' ; select '
| Max Connect Errors | ', @@global.max_connect_errors; select ' |
| Replication |
Master' ; show master status; SHOW VARIABLES LIKE 'rpl_semi_sync_master_%'; SHOW STATUS LIKE 'rpl_semi_sync_master_status'; select ' |
Slave' ; show slave status\G SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; SHOW STATUS LIKE 'rpl_semi_sync_slave_status'; select ' |
| Stored Routines | ||
| Schema', ' | Type', ' | Objects' ; select ' |
| ',routine_schema, ' | ', routine_type, ' | ', count(*) from routines group by routine_schema, routine_type; select ' |
| Scheduler |
| ', variable_value from global_variables where variable_name='EVENT_SCHEDULER'; select ' |
' ; select '
| Scheduled Jobs | ||||
| Event', ' | Status', ' | Type', ' | Schedule', ' | Command'; select ' |
| ',concat(event_schema,'.',event_name), ' | ', status, ' | ', event_type, ' | ', ifnull(execute_at,''), ifnull(interval_value,''),ifnull(interval_field,''), ' | ', event_definition from events; select ' |
| NLS | ||
| Schema',' | DEFAULT CHARACTER_SET_NAME',' | DEFAULT COLLATION_NAME'; SELECT ' |
| ',schema_name, ' | ', DEFAULT_CHARACTER_SET_NAME, ' | ', DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA where schema_name not in ('mysql', 'information_schema', 'sys', 'performance_schema','test') order by schema_name; select ' |
' ; select '
| NLS: Columns | |||
| Schema',' | CHARACTER_SET_NAME',' | COLLATION_NAME',' | Count'; SELECT ' |
| ',table_schema, ' | ', CHARACTER_SET_NAME, ' | ', COLLATION_NAME, ' | ', count(*) FROM information_schema.COLUMNS where table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema', 'test') and CHARACTER_SET_NAME is not null group by table_schema, CHARACTER_SET_NAME, COLLATION_NAME union SELECT ' |
| ', 'TOTAL', ' | ', CHARACTER_SET_NAME, ' | ', COLLATION_NAME, ' | ', count(*) FROM information_schema.COLUMNS where table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema', 'test') and CHARACTER_SET_NAME is not null group by CHARACTER_SET_NAME , COLLATION_NAME; select ' |
' ; select '
| NLS: Parameters | |
| Parameter', ' | Value' ; select ' |
| ', variable_name, ' | ', replace(variable_value,',',', ') from global_variables where variable_name like 'character_set_%' or variable_name like 'collation_%' order by variable_name; select ' |
| MySQL Parameters | |
| Parameter', ' | Value' ; select ' |
| ', variable_name, ' | ', replace(variable_value,',',', ') from global_variables order by variable_name; select ' |
| MySQL Global Status | |
| Statistic', ' | Value' ; select ' |
| ', variable_name, ' | ', variable_value from global_status order by variable_name; select ' |
Statistics generated on: ', now(); select '
For more info on my2html contact' ; select 'Meo Bogliolo.
' ;