Skip to main content

MySQL Percona Tool Kit

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.cnf h=host1
Diff the [mysqld] section of two option files:
pt-config-diff /etc/my-small.cnf /etc/my-large.cnf
Print deadlocks on host1:
pt-deadlock-logger h=host1
Print deadlocks on host1 once then exit:
pt-deadlock-logger h=host1 --iterations 1
Save deadlocks on host1 to percona_schema.deadlocks on host2:
pt-deadlock-logger h=host1 --dest h=host2,D=percona_schema,t=deadlocks
Analyze MySQL queries from logs, processlist, and tcpdump
Report the slowest queries from slow.log:
pt-query-digest slow.log

Report the slowest queries from the processlist on host1:
pt-query-digest --processlist h=host1
Capture MySQL protocol data with tcppdump, then report the slowest queries:
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

Archive rows from a MySQL table into another table or a file.
Archive all rows from oltp_server to olap_server and to a file:
pt-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server \
--file '/var/log/archive/%Y-%m-%d-%D.%t'                           \
--where "1=1" --limit 1000 --commit-each

Purge (delete) orphan rows from child table:
pt-archiver --source h=host,D=db,t=child --purge \
--where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'

Verify MySQL replication integrity:
checksum every table, and report the results on every detected replica:
pt-table-checksum

Collect forensic data about MySQL when problems occur:
pt-stalk [OPTIONS]
pt-stalk waits for a trigger condition to occur, then collects data to help diagnose problems.

The tool is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to collect data on demand without waiting for the trigger to occur.

Ref.:
https://www.percona.com/doc/percona-toolkit/LATEST/pt-stalk.html
https://www.percona.com/sites/default/files/presentations/5_Percona_Toolkit_Tools_Save_Day_4.3.13.pdf
https://www.percona.com/doc/percona-toolkit/LATEST/pt-kill.htmlhttps://www.percona.com/doc/percona-toolkit/3.0/index.html






Comments

Popular posts from this blog

Oralce Pluggable Database

Pluggable database administration:-

Open all pluggable databases:
Connect to container database using oraenv
$. oraenv=DB_Name
$sqlplus "/as sysdba"
SQL> alter pluggable database all open;
Get pluggable database status:
SQL> select con_id, dbid, name, open_mode from v$pdbs;
Switch to Puggable database from container :
SQL> alter session set container = <Puggable_DB>;
Get status of Pluggable database
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
Get file location for pluggable database:
SQL>select file_name from dba_data_files;
Create table space for pluggable datatbase:
SQL> alter session set container=<Pluggable_DB>;
Session altered.
SQL> CREATE TABLESPACE TB_Name LOGGING DATAFILE '/<Path>/<File_Name>.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Set new t…

Oracle Golden Gate Basic Classic

Create environment
01. Source & target - Create GG tablespace.
02. Source & target - Create the GoldenGate Schema Owner
03. Grant privilege including DBA to GG schema owner
04. Add schema owner to global parameter file ./GLOBALS
05. Execute role set up script to create role GGS_GGSUSER_ROLE
06. Grant role GGS_GGSUSER_ROLE to GG user at both Source and target
Configure GG  Extract
01. Source and Target - Configure Manager Parameters
$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
02. Source, create parameter file for Extract - ex1,
EXTRACT ex1
USERID <id>, PASSWORD <pwd>
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE <Schema>.*;
03. Source, configure Data Pump Parameters
EXTRACT dp1
USERID <id>, PASSWORD <pwd>
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE <schema>.*;
Target - Create check point table
$ ggsci
GGSCI 1> DBLOGIN USERID <id>, PASSWORD <pwd>
GGSCI 2>…

Oracle Daily SQL

Enable System-wide tracing: alter system set events '10046 trace name context forever,level 12'; Disabled in all sessions alter system set events '10046 trace name context off'; Session Tracing alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever,level 12'; crsctl for Oracle RAC ./crsctl start cluster ./crsctl check crs Make column to CLOB ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME LONG); ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME CLOB); RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>; Rename column, change data type, drop column ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO COLUMN_NAME1; ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME VARCHAR2(10)); ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; Create Primary Key ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COL…