Percona provides repositories for yum (RPM packages for Red Hat, CentOS and Amazon Linux AMI) and apt (.debpackages for Ubuntu and Debian) for software such as Percona Server, Percona XtraBackup, and Percona Toolkit. This makes it easy to install and update your software and its dependencies through your operating system’s package manager. This is the recommend way of installing where possible.
Following guides describe the installation process for using the official Percona repositories for .deb and .rpm packages.
Installing Percona Server from Percona apt repository
- Fetch the repository packages from Percona web:
wget https://repo.percona.com/apt/percona-release_0.1-3.$(lsb_release -sc)_all.deb
- Install the downloaded package with dpkg. To do that, run the following commands as root or with sudo:
dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb
Once you install this package the Percona repositories should be added. You can check the repository setup in the/etc/apt/sources.list.d/percona-release.list file. - Remember to update the local cache:
apt-get update
- After that you can install the server package:
apt-get install percona-server-server-5.5
Percona apt Testing repository
Percona offers pre-release builds from the testing repository. To enable it add the just uncomment the testing repository lines in the Percona repository definition in your repository file (default /etc/apt/sources.list.d/percona-release.list). It should looks like this (in this example VERSION is the name of your distribution):
# Testing & pre-release packages
#
deb http://repo.percona.com/apt VERSION testing
deb-src http://repo.percona.com/apt VERSION testing
Apt-Pinning the packages
In some cases you might need to “pin” the selected packages to avoid the upgrades from the distribution repositories. You’ll need to make a new file /etc/apt/preferences.d/00percona.pref and add the following lines in it:
Package: *
Pin: release o=Percona Development Team
Pin-Priority: 1001
For more information about the pinning you can check the official debian wiki.
Installing Percona Server using downloaded deb packages
Download the packages of the desired series for your architecture from the download page. The easiest way is to download bundle which contains all the packages. Following example will download Percona Server 5.5.44-37.3 release packages for Debian 8.0:
$ wget https://www.percona.com/downloads/Percona-Server-5.5/Percona-Server-5.5.44-37.3/binary/debian/jessie/x86_64/Percona-Server-5.5.44-37.3-r729fbe2-jessie-x86_64-bundle.tar
You should then unpack the bundle to get the packages:
$ tar xvf Percona-Server-5.5.44-37.3-r729fbe2-jessie-x86_64-bundle.tar
After you unpack the bundle you should see the following packages:
$ ls *.deb libperconaserverclient18_5.5.44-rel37.3-1.jessie_amd64.deb libperconaserverclient18-dev_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-5.5-dbg_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-client_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-client-5.5_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-common-5.5_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-server_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-server-5.5_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-source-5.5_5.5.44-rel37.3-1.jessie_amd64.deb percona-server-test-5.5_5.5.44-rel37.3-1.jessie_amd64.deb
Now you can install Percona Server by running:
$ sudo dpkg -i *.deb
1) pt-query digest,
- Selecting which queries you should try to optimize to get better response times
; Analyze MySQL queries from logs, processlist, and tcpdump.
Usage
pt-query-digest [OPTIONS] [FILES] [DSN]
pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from SHOWPROCESSLIST and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no FILES are given, the tool reads STDIN. The optionalDSN is used for certain options like --since and --until.
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
Save query data from slow.log to host2 for later review and trend analysis:
pt-query-digest --review h=host2 --no-report slow.log
Column Meaning ============ ========================================================== Rank The query's rank within the entire set of queries analyzed Query ID The query's fingerprint Response time The total response time, and percentage of overall total Calls The number of times this query was executed R/Call The mean response time per execution V/M The Variance-to-mean ratio of response time Item The distilled query
pt-query-digest slow.log \
--no-report \
--output slowlog \
--filter '$event->{fingerprint} \
&& make_checksum($event->{fingerprint}) eq "FDEA8D2993C9CAF3"'
Notice that you must remove the 0x prefix from the checksum.
Finally, in case you want to find a sample of the query in the log file, there’s the byte offset where you can look. (This is not always accurate, due to some anomalies in the slow log format, but it’s usually right.) The position refers to the worst sample, which we’ll see more about below.
Next is the table of metrics about this class of queries.
# pct total min max avg 95% stddev median
# Count 0 2
# Exec time 13 1105s 552s 554s 553s 554s 2s 553s
# Lock time 0 216us 99us 117us 108us 117us 12us 108us
# Rows sent 20 6.26M 3.13M 3.13M 3.13M 3.13M 12.73 3.13M
# Rows exam 0 6.26M 3.13M 3.13M 3.13M 3.13M 12.73 3.13M
2) pt-archiver,
- How to efficiently purge data from a huge table without putting too much load on your server
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)'
3) pt-table-checksum,
- How to check which tables are affected when someone accidentally wrote to a replica and fix the problem without rebuilding the tables
pt-table-checksum [OPTIONS] [DSN]
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the master host. The tool’s “EXIT STATUS” is non-zero if any differences are found, or if any warnings or errors occur.
The following command will connect to the replication master on localhost, checksum every table, and report the results on every detected replica:
pt-table-checksum
This tool is focused on finding data differences efficiently. If any data is different, you can resolve the problem with pt-table-sync.
4) pt-stalk
- How to gather data for performance problems that happen randomly and last only a few seconds(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.
5) pt-online-schema-change
ALTER tables without locking them
How to run ALTER TABLE on your largest tables without downtime
Usage
pt-online-schema-change [OPTIONS] DSN
pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.
Add a column to sakila.actor:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
Change sakila.actor to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table:
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
댓글 없음:
댓글 쓰기