- The top 5 proactive measures to minimize MySQL downtime
- The small improvements of MySQL 5.6: Duplicate Index Detection
- Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo
- Analyzing Slow Query Table in MySQL 5.6
- Percona Toolkit by example – pt-stalk
- innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
- Webinar: SQL Query Patterns, Optimized
- Sphinx search performance optimization: attribute-based filters
- Sphinx search performance optimization: multi-threaded search
- Performance problem with [innodb_file_per_table=1] and DROP TABLE
- ALTER TABLE to ENGINE=InnoDB
pt-online-schema-change --alter "ENGINE=InnoDB" D=<database>,t=<table> --execute
- pt-archiver For archive records into normal file, you can run something like
pt-archiver --source h=localhost,D=nil,t=test --file '/home/nilnandan/%Y-%m-%d-tabname' --where "name='nil'" --limit-1000
From archive records from one table to another table on same server or different, you can run something like
pt-archiver --source h=localhost,D=nil,t=test --dest h=fedora.vm --where "name='nil'" --limit-1000
Archiving in a replication environment:
In the replication environment it’s really important that the slave should not lag for a long time. So for that, there are two options which we can use while archiving to control the slave lag on slave server.
–check-slave-lag : Pause archiving until the specified DSN’s slave lag is less than –max-lag. In this option, you can give slave details to connect slave lag. (i.e –check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock)
–max-lag : Pause archiving if the slave given by –check-slave-lag lags. This options causes pt-archiver to look at the slave every time when it’s about to fetch another row. If the slave’s lag is greater than the option’s value, or if the slave isn’t running (so its lag is NULL), pt-archiver sleeps for –check-interval seconds and then looks at the lag again. It repeats until the slave is caught up, then proceeds to fetch and archive the row. Some useful options for pt-archiver:
–for-update/-share-lock : Adds the FOR UPDATE/LOCK IN SHARE MODE modifier to SELECT statements.
–no-delete : Do not delete archived rows.
–plugin : Perl module name to use as a generic plugin.
–progress : Print progress information every X rows.
–statistics : Collect and print timing statistics.
–where : WHERE clause to limit which rows to archive (required).
nilnandan@nil:~$ pt-archiver --source h=localhost,D=nil,t=test,S=/tmp/mysql_sandbox29783.sock --file '/home/nilnandan/%Y-%m-%d-tabname' --where "name='nilnandan'" --limit=50000 --progress=50000 --txn-size=50000 --statistics --bulk-delete --max-lag=1 --check-interval=15 --check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock
TIME ELAPSED COUNT
2013-08-08T10:08:39 0 0
2013-08-08T10:09:25 46 50000
2013-08-08T10:10:32 113 100000
2013-08-08T10:11:41 182 148576
Started at 2013-08-08T10:08:39, ended at 2013-08-08T10:11:59
Source: D=nil,S=/tmp/mysql_sandbox29783.sock,h=localhost,t=test
SELECT 148576
INSERT 0
DELETE 148576
Action Count Time Pct
print_file 148576 18.2674 9.12
bulk_deleting 3 8.9535 4.47
select 4 2.9204 1.46
commit 3 0.0005 0.00
other 0 170.0719 84.95
nilnandan@nil:~$