AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Kill queries running longer than 60s: pt-kill --busy-time 60 --kill Print, do not kill, queries running longer than 60s: pt-kill --busy-time 60 --print Print all login processes: pt-kill --match-state login --print --victims all Find all tables created more than a day ago, which use the MyISAM engine, and print their names:  pt-find --ctime +1 --engine MyISAM Find InnoDB tables and convert them to MyISAM: pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM" Find empty tables in the test and junk databases, and delete them: pt-find --empty junk test --exec-plus "DROP TABLE %s" Find tables more than five gigabytes in total size: pt-find --tablesize +5G Summarize MySQL information nicely: pt-mysql-summary --user=root Diff MySQL configuration files and server variables: Diff host1 config from SHOW VARIABLES against host2: pt-config-diff h=host1 h=host2 Diff config from [mysqld] section in my.cnf against host1 config: pt-config-diff /etc/my.cn

Disables locking while copying the non-InnoDB files: [mysqlbackup] no-locking After issuing FLUSH TABLES WITH READ LOCK: We can see one of the following status: Flushing tables Waiting for table flush Waiting for global read lock Pending FLUSH TABLES WITH READ LOCK because connection has: executed LOCK TABLES ... WRITE; for one or more tables a long running query (including DDL, simple SELECT, etc.) Identify Queries Taking Longer Than 10 Seconds: SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10; SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10; Flush MySQL Host: flush hosts; Flush MySQL bin logs: flush bin logs; Select current date and time: s

Select Histograms for tables:- SELECT column_name, num_distinct, histogram FROM   user_tab_col_statistics WHERE  table_name = '<table_name>'; Gather tables stats using DBMS_STATS for schema exec dbms_stats.gather_schema_stats ( ownname => '<owner_name>', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt  => 'FOR ALL COLUMNS SIZE 1', cascade => true, degree => 10, granularity => 'ALL' ); exec dbms_stats.gather_schema_stats ( ownname=> 'SAMAPPL', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade=> true, granularity=>'ALL', degree => 10 ); Extended statistics R ef.: https://blogs.oracle.com/optimizer/extended-statistics http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm        method_opt options FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE