Jul 6, 2011 - The tutorial vm's. Everything but the ... Example (Dell PowerEdge Expandable RAID controller 5) ...... Mar
MySQL DBA Swiss Army Knife
Percona Live London 2011
Who am I ? Frédéric Descamps @lefred http://about.be/lefred Percona Consultant Managing mysql since 3.23 (as far as I remember) devops believer
www.percona.com
Thank You to Our Sponsors Platinum Sponsor
Gold Sponsor
Silver Sponsors
www.percona.com
Percona Live London Sponsors Exhibitor Sponsors
Friends of Percona Sponsors
Media Sponsors
www.percona.com
www.percona.com
www.percona.com
prerequisites Laptop ● VirtualBox ● The tutorial vm's ●
Everything but the laptop is on the key
www.percona.com
prerequisites
www.percona.com
prerequisites
www.percona.com
Agenda Know you environment ● Know the evolution of your environment ● Save your environment ● Maintain your environment ●
www.percona.com
Know your environment: Operating System
www.percona.com
11
Know your environment (OS) We need to find the answers to these questions: ● ● ● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ?
www.percona.com
12
Toolbox The first tool is part of Percona Toolkit. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually.
www.percona.com
13
Percona Toolkit ●
● ● ●
Percona Toolkit is derived from Maatkit and Aspersa, two of the best-known toolkits for MySQL server administration. It is developed and supported by Percona Inc. Opensource GNU GPL v2 https://launchpad.net/percona-toolkit http://www.percona.com/software/percona-toolkit/
www.percona.com
14
Tool #1 Name: pt-summary Function: summarize system information in a nice way Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-summary.html
www.percona.com
15
Exercises Start kermit virtual machine, login and run ptsummary Login: pluk Password: pluk2k11
www.percona.com
16
Back to our questions ●
●
● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ? www.percona.com
17
Tool #2 The tool #2 is more a set of tools to use depending of your raid controller. Name: MegaCli | megactl | arcconf | ? Function: find information about raid, disk AND write cache policy
www.percona.com
18
MegaCli
Get some general information about the controller's capabilities
www.percona.com
19
Example (Dell PowerEdge Expandable RAID controller 5) $ MegaCli AdpAllInfo aALL Adapter #0 ============================================================================== Versions ================ Product Name : PERC 5/i Integrated Serial No : 12345 FW Package Build: 5.2.20072 Mfg. Data ================ Mfg. Date : 00/00/00 Rework Date : 00/00/00 Revision No : @��A Battery FRU : N/A Image Versions In Flash: ================ Boot Block Version : R.2.3.12 BIOS Version : MT289 MPT Version : MPTFW00.10.62.00IT FW Version : 1.03.500461 WebBIOS Version : 1.0304 CtrlR Version : 1.04019A
www.percona.com
20
Example (2) Pending Images In Flash ================ None PCI Info ================ Vendor Id : 1028 Device Id : 0015 SubVendorId : 1028 SubDeviceId : 1f03 Host Interface : PCIE Number of Frontend Port: 0 Device Interface : PCIE Number of Backend Port: 8 Port : Address 0 1221000000000000 1 1221000001000000 2 1221000002000000 3 1221000003000000 4 1221000004000000 5 1221000005000000 6 0000000000000000 7 0000000000000000
www.percona.com
21
Example (3)
HW Configuration ================ SAS Address : 5001e4f017ce7a00 BBU : Present Alarm : Absent NVRAM : Present Serial Debugger : Present Memory : Present Flash : Present Memory Size : 256MB
www.percona.com
22
Example (4) Settings ================ Current Time : 2:26:38 7/6, 2011 Predictive Fail Poll Interval : 300sec Interrupt Throttle Active Count : 16 Interrupt Throttle Completion : 50us Rebuild Rate : 30% PR Rate : 30% Resynch Rate : 30% Check Consistency Rate : 30% Reconstruction Rate : 30% Cache Flush Interval : 4s Max Drives to Spinup at One Time : 2 Delay Among Spinup Groups : 12s Physical Drive Coercion Mode : 128MB Cluster Mode : Disabled Alarm : Disabled Auto Rebuild : Enabled Battery Warning : Enabled Ecc Bucket Size : 15 Ecc Bucket Leak Rate : 1440 Minutes Restore HotSpare on Insertion : Disabled Expose Enclosure Devices : Disabled Maintain PD Fail History : Disabled Host Request Reordering : Enabled Auto Detect BackPlane Enabled : SGPIO/i2c SEP Load Balance Mode : Auto
www.percona.com
23
Example (5) Capabilities ================ RAID Level Supported : RAID0, RAID1, RAID5, RAID10, RAID50 Supported Drives : SAS, SATA Allowed Mixing: Mix In Enclosure Allowed Status ================ ECC Bucket Count : 0 Limitations ================ Max Arms Per VD : 32 Max Spans Per VD : 8 Max Arrays : 128 Max Number of VDs : 64 Max Parallel Commands : 1008 Max SGE Count : 80 Max Data Transfer Size : 8192 sectors Max Strips PerIO : 84 Min Stripe Size : 8kB Max Stripe Size : 128kB
www.percona.com
24
Example (6) Device Present ================ Virtual Drives : 3 Degraded : 0 Offline : 0 Physical Devices : 7 Disks : 6 Critical Disks : 0 Failed Disks : 0
Supported Adapter Operations ================ Rebuild Rate : Yes CC Rate : Yes BGI Rate : Yes Reconstruct Rate : Yes Patrol Read Rate : Yes Alarm Control : Yes Cluster Support : No BBU : Yes Spanning : Yes Dedicated Hot Spare : Yes Revertible Hot Spares : No Foreign Config Import : Yes Self Diagnostic : Yes Allow Mixed Redundancy on Array : No Global Hot Spares : Yes Deny SCSI Passthrough : No Deny SMP Passthrough : No Deny STP Passthrough : No
www.percona.com
25
Example (7) Supported VD Operations ================ Read Policy : Yes Write Policy : Yes IO Policy : Yes Access Policy : Yes Disk Cache Policy : Yes Reconstruction : Yes Deny Locate : No Deny CC : No Supported PD Operations ================ Force Online : Yes Force Offline : Yes Force Rebuild : Yes Deny Force Failed : No Deny Force Good/Bad : No Deny Missing Replace : No Deny Clear : No Deny Locate : No Disable Copyback : No Enable Copyback on SMART : No
Error Counters ================ Memory Correctable Errors : 0 Memory Uncorrectable Errors : 0 Cluster Information ================ Cluster Permitted : No Cluster Active : No
www.percona.com
26
Example (8) Default Settings ================ Phy Polarity : 0 Phy PolaritySplit : 0 Background Rate : 30 Stripe Size : 64kB Flush Time : 4 seconds Write Policy : WB Read Policy : None Cache When BBU Bad : Disabled Cached IO : No SMART Mode : Mode 6 Alarm Disable : No Coercion Mode : 128MB ZCR Config : IDSEL Dirty LED Shows Drive Activity : No BIOS Continue on Error : No Spin Down Mode : None Allowed Device Type : SAS/SATA Mix Allow Mix In Enclosure : Yes Allow Mix In VD : No Allow SATA In Cluster : No
Max Chained Enclosures : 1 Disable CtrlR : No Enable Web BIOS : No Direct PD Mapping : No BIOS Enumerate VDs : No Restore Hot Spare on Insertion : No Expose Enclosure Devices : No Maintain PD Fail History : No Disable Puncturing : No Zero Based Enclosure Enumeration : No PreBoot CLI Enabled : Yes LED Show Drive Activity : No Cluster Disable : Yes SAS Disable : No Auto Detect BackPlane Enable : SGPIO/ i2c SEP
www.percona.com
27
MegaCli
Get information about BBU (Backup Battery Unit)
www.percona.com
28
Example (9) $ MegaCli AdpBbuCmd GetBbuStatus aALL BBU status for Adapter: 0 BatteryType: BBU Voltage: 4000 mV Current: 0 mA Temperature: 29 C Firmware Status: 00000000 Battery state: GasGuageStatus: Fully Discharged : No Fully Charged : No Discharging : Yes Initialized : Yes Remaining Time Alarm : No Remaining Capacity Alarm: No Discharge Terminated : No Over Temperature : No Charging Terminated : No Over Charged : No
Relative State of Charge: 96 % Charger Status: Complete Remaining Capacity: 817 mAh Full Charge Capacity: 854 mAh isSOHGood: Yes
www.percona.com
29
MegaCli
Get information about RAID configuration
www.percona.com
30
Example (10) $ /opt/MegaRAID/MegaCli/MegaCli64 LDInfo LAll aAll Adapter 0 Virtual Drive Information: Virtual Disk: 0 (Target Id: 0) Name:root RAID Level: Primary1, Secondary0, RAID Level Qualifier0 Size:1900000MB State: Optimal Stripe Size: 64kB Number Of Drives:2 Span Depth:1 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default Virtual Disk: 1 (Target Id: 1) Name:swap RAID Level: Primary1, Secondary0, RAID Level Qualifier0 Size:7200MB State: Optimal Stripe Size: 64kB
www.percona.com
31
Example (11) Number Of Drives:2 Span Depth:1 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default Virtual Disk: 2 (Target Id: 2) Name:data RAID Level: Primary1, Secondary3, RAID Level Qualifier0 Size:3814400MB State: Optimal Stripe Size: 64kB Number Of Drives:2 Span Depth:2 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default
www.percona.com
32
MegaCli
Get information about disks
www.percona.com
33
Example (12) $ /opt/MegaRAID/MegaCli/MegaCli64 PDList a0 Adapter #0 Enclosure Device ID: 8 Slot Number: 0 Device Id: 0 Sequence Number: 2 Media Error Count: 0 Other Error Count: 0 Predictive Failure Count: 0 Last Predictive Failure Event Seq Number: 0 PD Type: SATA Raw Size: 1907729MB [0xe8e088b0 Sectors] Non Coerced Size: 1907217MB [0xe8d088b0 Sectors] Coerced Size: 1907200MB [0xe8d00000 Sectors] Firmware state: Online SAS Address(0): 0x1221000000000000 Connected Port Number: 0 Inquiry Data: ATA Hitachi HDS72202A28A JK1130YAHJHSXX Foreign State: None
www.percona.com
34
Example (13) Enclosure Device ID: 8 Slot Number: 1 Device Id: 1 Sequence Number: 2 Media Error Count: 0 Other Error Count: 0 Predictive Failure Count: 0 Last Predictive Failure Event Seq Number: 0 PD Type: SATA Raw Size: 1907729MB [0xe8e088b0 Sectors] Non Coerced Size: 1907217MB [0xe8d088b0 Sectors] Coerced Size: 1907200MB [0xe8d00000 Sectors] Firmware state: Online SAS Address(0): 0x1221000001000000 Connected Port Number: 1 Inquiry Data: ATA Hitachi HDS72202A28A JK1130YAHGDHXX Foreign State: None [...]
www.percona.com
35
megactl
Get what we can retrieve
www.percona.com
36
Example (Dell PowerEdge Expandable RAID controller 4 (rev 06))
Information about RAID level configuration, # of disks and battery # ./megactl a0 PERC 4e/Di chan:2 ldrv:1 batt:good a0d0 838GiB RAID 5 1x4 optimal hot spares : a0c0t4 a0c0t0 279GiB a0d0 online a0c0t1 279GiB a0d0 online a0c0t2 279GiB a0d0 online a0c0t3 279GiB a0d0 online a0c0t4 279GiB hotspare
www.percona.com
37
Example (2) Same information + disk model #./megactl v a0 PERC 4e/Di bios:H433 fw:5A2D chan:2 ldrv:1 rbld:30% batt:good a0d0 838GiB RAID 5 1x4 optimal row 0: a0c0t0 a0c0t1 a0c0t2 a0c0t3 hot spares : a0c0t4 a0c0t0 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t1 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t2 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t3 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t4 SEAGATE ST3300655LC 279GiB hotspare
Same information + disk serial
# ./megactl v v v 1 adapters, driver version 02200207
a0 PERC 4e/Di bios:H433 fw:5A2D chan:2 ldrv:1 rbld:30% mem:256MiB batt:good a0d0 838GiB RAID 5 1x4 optimal row 0: a0c0t0 a0c0t1 a0c0t2 a0c0t3 hot spares : a0c0t4 a0c0t0 SEAGATE ST3300655LC rev:0005 s/n:6LM01KQQ0000B20272XX 279GiB a0d0 online a0c0t1 SEAGATE ST3300655LC rev:0005 s/n:6LM01KPC0000B20273XX 279GiB a0d0 online a0c0t2 SEAGATE ST3300655LC rev:0005 s/n:6LM01KJN0000B2025TXX 279GiB a0d0 online a0c0t3 SEAGATE ST3300655LC rev:0005 s/n:6LM01KJX0000B20273XX 279GiB a0d0 online a0c0t4 SEAGATE ST3300655LC rev:0005 s/n:6LM01KGF0000B20272XX 279GiB hotspare
www.percona.com
38
Example (3) # ./megactl e a0 PERC 4e/Di chan:2 ldrv:1 batt:good a0d0 838GiB RAID 5 1x4 optimal hot spares : a0c0t4 a0c0t0 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:246Ki delay: 0 reread: 0 tot/corr:246Ki tot/uncorr: 0 verify errors: corr: 4Mi delay: 0 revrfy: 0 tot/corr: 4Mi tot/uncorr: 0 a0c0t1 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:108Ki delay: 0 reread: 0 tot/corr:108Ki tot/uncorr: 0 verify errors: corr: 1Mi delay: 0 revrfy: 0 tot/corr: 1Mi tot/uncorr: 0 a0c0t2 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:220Ki delay: 1 reread: 0 tot/corr:220Ki tot/uncorr: 0 verify errors: corr: 3Mi delay: 0 revrfy: 0 tot/corr: 3Mi tot/uncorr: 0 a0c0t3 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:125Ki delay: 0 reread: 0 tot/corr:125Ki tot/uncorr: 0 verify errors: corr: 2Mi delay: 0 revrfy: 0 tot/corr: 2Mi tot/uncorr: 0 a0c0t4 279GiB hotspare write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr: 5 delay: 0 reread: 0 tot/corr: 5 tot/uncorr: 0 verify errors: corr: 11Mi delay: 0 revrfy: 0 tot/corr: 11Mi tot/uncorr: 0
www.percona.com
39
Back to our questions ●
●
● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ? www.percona.com
40
IOPS
What about IOPS ?
www.percona.com
41
IOPS ●
●
Input/Output Operations Per Second is usually associated with the measurement of a storage system performance. To calculate the average IOPS check the datasheet of your disk to the manufacturer's webiste. You need: ➢ ➢ ➢
Rotation speed Average latency Average seek time www.percona.com
42
IOPS formula The formula to calculate IOPS is:
IOPS =
1 [average latency in sec + average read/write seek time in dec ]
This is for ONE disk, there is a write penalty associated with RAID configuration.
www.percona.com
43
IOPS formula : RAID penalty List of RAID configurations & their penalties:
RAID configuration
Penalty
RAID 0
1
RAID 1
2
RAID 10
2
RAID 5
4
RAID 6
6
www.percona.com
44
IOPS formula :rotation latency List of rotation latency average
HDD Spindle [rpm]
Average rotational latency [ms] 4,200
7.14
5,400
5.56
7,200
4.17
10,000
3.00
15,000
2.00
www.percona.com
45
Workload system IOPS formula The formula to calculate workload system IOPS (with the RAID) is:
1
(Total Workload IOPS * Percentage of workload read operations) + ( (Total Workload IOPS * Percentage of workload write operations) / RAID IOPS Penalty )
www.percona.com
46
Example From previous raid information we know... ● ● ● ● ● ● ●
Disk : SEAGATE ST3300655LC (279GB) RAID : 5 # of disks: 4 Spin Speed (RPM): 15k Average latency : 2.0ms Random read seek time : 3.50ms Random write seek time : 4.0ms www.percona.com
47
Example (2)
RAID : 5 -> penalty = 4 # of disks: 4 Spin Speed (RPM): 15k Average latency : 2.0ms Random read seek time : 3.50ms Random write seek time : 4.0ms
IOPS = 1 / [ 0.002 + (0.0035+0.004)/2 ] = 173.91 Total Workload IOPS = 4 x 173.91 = 695 (695.64) Let's assume 80% reads, 20% writes: Workload system IOPS = (695 x 0.8) + ( (695 x 0.2)/4 ) = 590.75 IOPS
www.percona.com
48
Tool #3 Name: sysbench Function: System performance benchmark Url: http://sysbench.sourceforge.net/ License: GPLv2
www.percona.com
49
Sysbench Let's check on that system the output of random writes:
# sysbench --test=fileio --file-test-mode=rndrw --file-total-size=10G --file-extra-flags=direct --num-threads=8 prepare sysbench 0.4.10: multi-threaded system evaluation benchmark 128 files, 81920Kb each, 10240Mb total Creating files for the test...
www.percona.com
50
# sysbench test=fileio filetestmode=rndrw filetotalsize=10G file extraflags=direct numthreads=8 run sysbench 0.4.10: multithreaded system evaluation benchmark Running the test with following options: Number of threads: 8 Extra file open flags: 16384 128 files, 80Mb each 10Gb total file size Block size 16Kb Number of random requests for random IO: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Threads started! Done. Operations performed: 6003 Read, 3997 Write, 12800 Other = 22800 Total Read 93.797Mb Written 62.453Mb Total transferred 156.25Mb (10.366Mb/sec) 663.40 Requests/sec executed Test execution summary: total time: 15.0738s total number of events: 10000 total time taken by event execution: 112.9758 perrequest statistics: min: 0.13ms avg: 11.30ms max: 951.96ms approx. 95 percentile: 44.12ms Threads fairness: events (avg/stddev): 1250.0000/46.64 execution time (avg/stddev): 14.1220/0.10
www.percona.com
51
Exercises Run sysbench on kermit ➢
➢
Try different fileio tests: – seqwr (sequential write) – seqrewr (sequential rewrite) – seqrd (sequential read) – rndrd (random read) – rndwr (random write) – rndrw (combined random read/write) Try different threads and fine the maximum www.percona.com
52
Tool #4 Name: pt-diskstats Function: Aggregate and summarize /proc/diskstats Part of: percona-toolkit Url:
http://www.percona.com/doc/percona-toolkit/pt-diskstats.html
www.percona.com
53
Exercises ● ● ●
Test ptdiskstats on kermit Try to output only the hard disk Press “c” and
www.percona.com
54
Know your environment: MySQL
www.percona.com
55
Know your environment (MySQL) We need to find the answers to these questions: ● ● ● ● ● ●
Which version is running ? 64Bits ? How many database ? Where is the datadir ? What about replication ? Do you use some Key Percona Server features ? Some general InnoDB parameters (buffer pool size, log file size, flush method, flush log at commit, R/W I/O Threads, I/O Capacity, Pending counters)
● ● ● ● ● ● ● ●
What the size of the database ? Which engines are used ? What type of fields are used ? Do we use foreign keys ? Do we use views ? Do we use full text search ? Do we use partitioning ? Who has access from where ?
www.percona.com
56
Tool #5 Name: pt-mysql-summary Function: Summarize MySQL information in a nice way Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-mysql-summary.html
www.percona.com
57
Back to our questions Did we answer those questions: ● ● ● ● ● ●
Which version is running ? 64Bits ? How many database ? Where is the datadir ? What about replication ? Do you use some Key Percona Server features ? Some general InnoDB parameters (buffer pool size, log file size, flush method, flush log at commit, R/W I/O Threads, I/O Capacity, Pending counters)
● ● ● ● ● ● ● ●
What the size of the database ? Which engines are used ? What type of fields are used ? Do we use foreign keys ? Do we use views ? Do we use full text search ? Do we use partitioning ? Who has access from where ?
www.percona.com
58
Tool #6 Name: pt-show-grants Function: print MySQL grants so you can effectively replicate, compare and versioncontrol them. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-show-grants.html
www.percona.com
59
Exercises ●
●
Retrieve the information related to MySQL on kermit and try to answer the previous questions Print all the grants and save them on a file called plukgrants.txt
www.percona.com
60
It's now time to be intimate with our database Let's try to find the answer to these indiscreet questions: ●
● ●
Which queries are the most resource eater ? How are the indexes used ? Are they duplicate keys ?
www.percona.com
61
Capture data For the following tools, we need to fetch some data. There are several solutions: ●
●
●
use slow query log (generally it stores only “slow” queries, but some fast queries can also be wrong) use general query log (not the best solution, some attributes like Query_time are missing) use a network dump (maybe the best solution)
www.percona.com
62
tcpdump ●
●
Capture data a different period of time ➢ during peak and non peak time Data with many drop packets are useless # time tcpdump i any s 65535 x n q tttt 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' > $(hostname)_queries_$ (date +%Y%m%d_%H%M).tcp www.percona.com
63
Tool #7 Name: pt-query-digest Function: Analyze query execution logs and generate a query report, filter, replay, or transform queries for MySQL, PostgreSQL, memcached, and more. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-query-digest.html
www.percona.com
64
pt-query-digest ptquerydigest watchserver "x.x.x.x:xxxx" type tcpdump file_name.tcp use watchserver if you have multiple servers on the same machine or if you use a different port
www.percona.com
65
Tool #8 Name: pt-index-usage Function: Read queries from a log and analyze how they use indexes. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-index-usage.html Note: the query log needs to be in MySQL‘s slow query log format. If you need to input a different format, you can use pt-query-digest to translate the formats. If you don’t specify a filename, the tool reads from STDIN.
www.percona.com
66
Tool #9 Name: pt-duplicate-key-checker Function: Find duplicate indexes and foreign keys on MySQL tables. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-duplicate-key-checker.html
www.percona.com
67
Exercises ● ●
●
Make some queries From a second terminal capture 5 minutes of mysql traffic Test the following tools: ➢ pt-query-digest ➢ pt-index-usage ➢ pt-duplicate-key-checker Do you see something unexpected ? www.percona.com
68
Know the evolution of your environment
www.percona.com
69
Know the evolution of your environment Now that we are familiar with the environment, it's time to check and understand its behavior.
Monitoring vs Trending
www.percona.com
70
Monitoring
Tool #10 Name: innotop Function: top clone for MySQL License: GNU GPLv2 Url: http://code.google.com/p/innotop
www.percona.com
71
Monitoring
Tool #11 Name: pt-mext Function: Look at many sample og MySQL SHOW GLOBAL STATUS side-by-side License: GNU GPLv2 Url:
http://www.percona.com/doc/percona-toolkit/ptmext.html
www.percona.com
72
pt-mext pt-mext -r -- mysqladmin ext -i10 -c5 All values are interesting but you should pay even more attention to: ● ● ● ● ● ● ● ● ● ● ● ●
Created_tmp_* Handler_read_* Innodb_buffer_pool_read* Innodb_os_log_written Key_read_requests Key_reads Qchache* Select_full_*join Select_range Select_scan Table_locks_waited Threads_running
www.percona.com
73
Exercises ● ● ● ●
Run innotop on kermit Change the refresh interval to 0.5 Check InnoDB information Run ptmext and get 3 columns of values with an interval of 30 seconds
www.percona.com
74
Trending
Tool #12 Name: cacti & better cacti templates Function: graphing solution, frontend to RRDTool. License: GNU GPLv2 Url: http://cacti.net http://code.google.com/p/mysql-cacti-templates
www.percona.com
75
Table scans, backup?
www.percona.com
76
Take backup here
www.percona.com
77
max_connections not reached yet
Why more incoming connections?
www.percona.com
78
In 2 hours, almost 6900 seconds replication lag
www.percona.com
79
Why this sudden peak in rangescans?
www.percona.com
80
DB Restart Time to fill buffer
www.percona.com
81
Save your environment: Backup
www.percona.com
82
Tool #13 Name: mydumper Function: high-performance multi-threaded backup (and restore) toolset for MySQL and Drizzle License: GNU GPLv3 Url: http://mydumper.org https://launchpad.net/mydumper/
www.percona.com
83
mydumper ● ● ● ● ●
Written in C 10x faster than mysqldump Compression on-the-fly Multi-threaded Works fine for MyISAM time ./mydumper h localhost u percona p mypass t 8 c regex '^(?!(mysql|test))' www.percona.com
84
Tool #14 Name: Percona XtraBackup Function: Open source hot backup tool for InnoDB and XtraDB databases License: GNU GPLv2 Url:
http://www.percona.com/software/percona-xtrabackup/ https://launchpad.net/percona-xtrabackup
www.percona.com
85
xtrabackup ● ● ● ● ● ● ● ● ●
O’Reilly award MySQL Application of Year Streaming Compression Incremental/Delta Filtering Throttling Parallel Copying Statistics Export/Import Table www.percona.com
86
Tool #15 Name: pt-archiver Function: Archive rows from a MySQL table into another table or a file by a low-impact, forward-only job to nibble old data out of the table without impacting OLTP queries much. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-archiver.pl
www.percona.com
87
Exercises ●
● ●
●
Backup the employees database using mydumper with 4 threads and store it in /home/pluk/backups Take a full backup using xtrabackup Insert some data while backup is running (if you have time :-) ) Take an incremental backup
www.percona.com
88
Maintain your environment
www.percona.com
89
Maintenance ● ● ●
Daily maintenance Find problems Replication
www.percona.com
90
Daily maintenance ● ● ● ●
Everything from the MySQL console Painless database changes Tested upgrades ? Extra
www.percona.com
91
From the console
Tool #16 Name: common-schema Function: provides useful views and routines to assists in schema analysis, monitoring management & SQL code generation License: New BSD License Url: http://code.google.com/p/common-schema
www.percona.com
92
Database changes
Tool #17 Name: pt-online-schema-change Function: Perform online, non-blocking table schema changes. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html
www.percona.com
93
Tested upgrades
Tool #18 Name: pt-upgrade Function: execute queries on multiple servers and check for differences Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-upgrade.html
www.percona.com
94
Extra
Tool #19 Name: pt-deadlock-logger Function: extract and log MySQL deadlock information Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-deadlock-logger.html
www.percona.com
95
Extra
Tool #20 Name: pt-kill Function: kill MySQL queries that match certain criteria Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-kill.html
www.percona.com
96
Exercises ● ●
●
●
Find the biggest table per database Change the engine of table .... to engine innodb. This change must be done online. Using information_schema what's the size of the dataset ? Examine the values of the auto_increments (from common_schema)
www.percona.com
97
Find problems ● ●
What do you advise me doctor ? Get the symptoms when it hurts !
www.percona.com
98
Tool #21 Name: pt-variable-advisor Function: analyzse MySQL variables and advise on possible problems Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-variable-advisor.html Note: this tool considers only static values and not the evolution of values, it should be used in addition of tool #11: pt-mext
www.percona.com
99
Get the symptoms when it hurts ! To perform this action, we will use 3 new tools : ● ● ●
pt-stalk pt-collect pt-sift
www.percona.com
100
Extra
Tool #22 Name: pt-stalk Function: wait for a condition to occur then begin collecting data. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-stalk.html
www.percona.com
101
Extra
Tool #23 Name: pt-collect Function: collect information from a server for some period of time. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-collect.html
www.percona.com
102
Extra
Tool #24 Name: pt-sift Function: browses files created by pt-collect. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-collect.html
www.percona.com
103
Replication Tools focusing on replication: ● ● ● ● ● ●
pt-slave-delay pt-slave-find pt-slave-restart pt-heartbeat pt-table-checksum pt-table-sync
www.percona.com
104
Tool #25 Name: pt-slave-delay Function: make a MySQL slave server lag behind its master. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-delay.html
www.percona.com
105
Tool #26 Name: pt-slave-find Function: find and print replication hierarchy tree of MySQL slaves. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-find.html
www.percona.com
106
Tool #27 Name: pt-slave-restart Function: watch and restart MySQL replication after errors. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-restart.html
www.percona.com
107
Tool #28 Name: pt-heartbeat Function: monitor MySQL replication delay. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-restart.html
www.percona.com
108
Tool #29 Name: pt-table-checksum Function: perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-table-checksum.html
www.percona.com
109
Tool #30 Name: pt-table-sync Function: synchronize MySQL table data efficiently. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-table-sync.html
www.percona.com
110
Exercises ● ●
●
●
●
● ●
Start the machine gonzo Setup “gonzo” as slave of kermit using XtraBackup Prepare stalk on gonzo to collect data when the replication breaks Remove one row on kermit, add it back to gonzo and then add it again on kermit Remove some rows in some tables in employees database on gonzo Run checksum on kermit Fix the tables by syncing them www.percona.com
111
Annual MySQL Users Conference Presented by Percona Live The Hyatt Regency Hotel, Santa Clara, CA April 10th-12th, 2012 Featured Speakers Mark Callaghan, Facebook Jeremy Zawodny, Craigslist Marten Mickos, Eucalyptus Systems Sarah Novotny, Blue Gecko Peter Zaitsev, Percona Baron Schwartz, Percona
The Call for Papers is Now Open! Visit www.percona.com/live/mysql-conference-2012/ www.percona.com
[email protected] @lefred
We're Hiring! www.percona.com/about-us/careers/
Thank you ! 114
MySQL DBA Swiss Army Knife
Percona Live London 2011
Who am I ? Frédéric Descamps @lefred http://about.be/lefred Percona Consultant Managing mysql since 3.23 (as far as I remember) devops believer
www.percona.com
v d liG P s S tO u Y k h T m a n rco e .p w
fM tF b h x E s S d iv L P m a n rco e .p w
www.percona.com
www.percona.com
prerequisites Laptop VirtualBox ● The tutorial vm's ● ●
Everything but the laptop is on the key
www.percona.com
prerequisites
www.percona.com
prerequisites
www.percona.com
Agenda Know you environment Know the evolution of your environment ● Save your environment ● Maintain your environment ● ●
www.percona.com
Know your environment: Operating System
www.percona.com
11
Know your environment (OS) We need to find the answers to these questions: ● ● ● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ?
www.percona.com
12
Toolbox The first tool is part of Percona Toolkit. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually.
www.percona.com
13
Percona Toolkit ●
● ● ●
Percona Toolkit is derived from Maatkit and Aspersa, two of the best-known toolkits for MySQL server administration. It is developed and supported by Percona Inc. Opensource GNU GPL v2 https://launchpad.net/percona-toolkit http://www.percona.com/software/percona-toolkit/
www.percona.com
14
Tool #1 Name: pt-summary Function: summarize system information in a nice way Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-summary.html
www.percona.com
15
Exercises Start kermit virtual machine, login and run ptsummary Login: pluk Password: pluk2k11
www.percona.com
16
Back to our questions ●
●
● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ? www.percona.com
17
Tool #2 The tool #2 is more a set of tools to use depending of your raid controller. Name: MegaCli | megactl | arcconf | ? Function: find information about raid, disk AND write cache policy
www.percona.com
18
MegaCli
Get some general information about the controller's capabilities
www.percona.com
19
Example (Dell PowerEdge Expandable RAID controller 5) $ MegaCli AdpAllInfo aALL Adapter #0 ============================================================================== Versions ================ Product Name : PERC 5/i Integrated Serial No : 12345 FW Package Build: 5.2.20072 Mfg. Data ================ Mfg. Date : 00/00/00 Rework Date : 00/00/00 Revision No : @��A Battery FRU : N/A Image Versions In Flash: ================ Boot Block Version : R.2.3.12 BIOS Version : MT289 MPT Version : MPTFW00.10.62.00IT FW Version : 1.03.500461 WebBIOS Version : 1.0304 CtrlR Version : 1.04019A
www.percona.com
20
Example (2) Pending Images In Flash ================ None PCI Info ================ Vendor Id : 1028 Device Id : 0015 SubVendorId : 1028 SubDeviceId : 1f03 Host Interface : PCIE Number of Frontend Port: 0 Device Interface : PCIE Number of Backend Port: 8 Port : Address 0 1221000000000000 1 1221000001000000 2 1221000002000000 3 1221000003000000 4 1221000004000000 5 1221000005000000 6 0000000000000000 7 0000000000000000
www.percona.com
21
Example (3)
HW Configuration ================ SAS Address : 5001e4f017ce7a00 BBU : Present Alarm : Absent NVRAM : Present Serial Debugger : Present Memory : Present Flash : Present Memory Size : 256MB
www.percona.com
22
Example (4) Settings ================ Current Time : 2:26:38 7/6, 2011 Predictive Fail Poll Interval : 300sec Interrupt Throttle Active Count : 16 Interrupt Throttle Completion : 50us Rebuild Rate : 30% PR Rate : 30% Resynch Rate : 30% Check Consistency Rate : 30% Reconstruction Rate : 30% Cache Flush Interval : 4s Max Drives to Spinup at One Time : 2 Delay Among Spinup Groups : 12s Physical Drive Coercion Mode : 128MB Cluster Mode : Disabled Alarm : Disabled Auto Rebuild : Enabled Battery Warning : Enabled Ecc Bucket Size : 15 Ecc Bucket Leak Rate : 1440 Minutes Restore HotSpare on Insertion : Disabled Expose Enclosure Devices : Disabled Maintain PD Fail History : Disabled Host Request Reordering : Enabled Auto Detect BackPlane Enabled : SGPIO/i2c SEP Load Balance Mode : Auto
www.percona.com
23
Example (5) Capabilities ================ RAID Level Supported : RAID0, RAID1, RAID5, RAID10, RAID50 Supported Drives : SAS, SATA Allowed Mixing: Mix In Enclosure Allowed Status ================ ECC Bucket Count : 0 Limitations ================ Max Arms Per VD : 32 Max Spans Per VD : 8 Max Arrays : 128 Max Number of VDs : 64 Max Parallel Commands : 1008 Max SGE Count : 80 Max Data Transfer Size : 8192 sectors Max Strips PerIO : 84 Min Stripe Size : 8kB Max Stripe Size : 128kB
www.percona.com
24
Example (6) Device Present ================ Virtual Drives : 3 Degraded : 0 Offline : 0 Physical Devices : 7 Disks : 6 Critical Disks : 0 Failed Disks : 0
Supported Adapter Operations ================ Rebuild Rate : Yes CC Rate : Yes BGI Rate : Yes Reconstruct Rate : Yes Patrol Read Rate : Yes Alarm Control : Yes Cluster Support : No BBU : Yes Spanning : Yes Dedicated Hot Spare : Yes Revertible Hot Spares : No Foreign Config Import : Yes Self Diagnostic : Yes Allow Mixed Redundancy on Array : No Global Hot Spares : Yes Deny SCSI Passthrough : No Deny SMP Passthrough : No Deny STP Passthrough : No
www.percona.com
25
Example (7) Supported VD Operations ================ Read Policy : Yes Write Policy : Yes IO Policy : Yes Access Policy : Yes Disk Cache Policy : Yes Reconstruction : Yes Deny Locate : No Deny CC : No Supported PD Operations ================ Force Online : Yes Force Offline : Yes Force Rebuild : Yes Deny Force Failed : No Deny Force Good/Bad : No Deny Missing Replace : No Deny Clear : No Deny Locate : No Disable Copyback : No Enable Copyback on SMART : No
Error Counters ================ Memory Correctable Errors : 0 Memory Uncorrectable Errors : 0 Cluster Information ================ Cluster Permitted : No Cluster Active : No
www.percona.com
26
Example (8) Default Settings ================ Phy Polarity : 0 Phy PolaritySplit : 0 Background Rate : 30 Stripe Size : 64kB Flush Time : 4 seconds Write Policy : WB Read Policy : None Cache When BBU Bad : Disabled Cached IO : No SMART Mode : Mode 6 Alarm Disable : No Coercion Mode : 128MB ZCR Config : IDSEL Dirty LED Shows Drive Activity : No BIOS Continue on Error : No Spin Down Mode : None Allowed Device Type : SAS/SATA Mix Allow Mix In Enclosure : Yes Allow Mix In VD : No Allow SATA In Cluster : No
Max Chained Enclosures : 1 Disable CtrlR : No Enable Web BIOS : No Direct PD Mapping : No BIOS Enumerate VDs : No Restore Hot Spare on Insertion : No Expose Enclosure Devices : No Maintain PD Fail History : No Disable Puncturing : No Zero Based Enclosure Enumeration : No PreBoot CLI Enabled : Yes LED Show Drive Activity : No Cluster Disable : Yes SAS Disable : No Auto Detect BackPlane Enable : SGPIO/ i2c SEP
www.percona.com
27
MegaCli
Get information about BBU (Backup Battery Unit)
www.percona.com
28
Example (9) $ MegaCli AdpBbuCmd GetBbuStatus aALL BBU status for Adapter: 0 BatteryType: BBU Voltage: 4000 mV Current: 0 mA Temperature: 29 C Firmware Status: 00000000 Battery state: GasGuageStatus: Fully Discharged : No Fully Charged : No Discharging : Yes Initialized : Yes Remaining Time Alarm : No Remaining Capacity Alarm: No Discharge Terminated : No Over Temperature : No Charging Terminated : No Over Charged : No
Relative State of Charge: 96 % Charger Status: Complete Remaining Capacity: 817 mAh Full Charge Capacity: 854 mAh isSOHGood: Yes
www.percona.com
29
MegaCli
Get information about RAID configuration
www.percona.com
30
Example (10) $ /opt/MegaRAID/MegaCli/MegaCli64 LDInfo LAll aAll Adapter 0 Virtual Drive Information: Virtual Disk: 0 (Target Id: 0) Name:root RAID Level: Primary1, Secondary0, RAID Level Qualifier0 Size:1900000MB State: Optimal Stripe Size: 64kB Number Of Drives:2 Span Depth:1 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default Virtual Disk: 1 (Target Id: 1) Name:swap RAID Level: Primary1, Secondary0, RAID Level Qualifier0 Size:7200MB State: Optimal Stripe Size: 64kB
www.percona.com
31
Example (11) Number Of Drives:2 Span Depth:1 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default Virtual Disk: 2 (Target Id: 2) Name:data RAID Level: Primary1, Secondary3, RAID Level Qualifier0 Size:3814400MB State: Optimal Stripe Size: 64kB Number Of Drives:2 Span Depth:2 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy: Read/Write Disk Cache Policy: Disk's Default
www.percona.com
32
MegaCli
Get information about disks
www.percona.com
33
Example (12) $ /opt/MegaRAID/MegaCli/MegaCli64 PDList a0 Adapter #0 Enclosure Device ID: 8 Slot Number: 0 Device Id: 0 Sequence Number: 2 Media Error Count: 0 Other Error Count: 0 Predictive Failure Count: 0 Last Predictive Failure Event Seq Number: 0 PD Type: SATA Raw Size: 1907729MB [0xe8e088b0 Sectors] Non Coerced Size: 1907217MB [0xe8d088b0 Sectors] Coerced Size: 1907200MB [0xe8d00000 Sectors] Firmware state: Online SAS Address(0): 0x1221000000000000 Connected Port Number: 0 Inquiry Data: ATA Hitachi HDS72202A28A JK1130YAHJHSXX Foreign State: None
www.percona.com
34
Example (13) Enclosure Device ID: 8 Slot Number: 1 Device Id: 1 Sequence Number: 2 Media Error Count: 0 Other Error Count: 0 Predictive Failure Count: 0 Last Predictive Failure Event Seq Number: 0 PD Type: SATA Raw Size: 1907729MB [0xe8e088b0 Sectors] Non Coerced Size: 1907217MB [0xe8d088b0 Sectors] Coerced Size: 1907200MB [0xe8d00000 Sectors] Firmware state: Online SAS Address(0): 0x1221000001000000 Connected Port Number: 1 Inquiry Data: ATA Hitachi HDS72202A28A JK1130YAHGDHXX Foreign State: None [...]
www.percona.com
35
megactl
Get what we can retrieve
www.percona.com
36
Example (Dell PowerEdge Expandable RAID controller 4 (rev 06))
Information about RAID level configuration, # of disks and battery # ./megactl a0 PERC 4e/Di chan:2 ldrv:1 batt:good a0d0 838GiB RAID 5 1x4 optimal hot spares : a0c0t4 a0c0t0 279GiB a0d0 online a0c0t1 279GiB a0d0 online a0c0t2 279GiB a0d0 online a0c0t3 279GiB a0d0 online a0c0t4 279GiB hotspare
www.percona.com
37
Example (2) Same information + disk model #./megactl v a0 PERC 4e/Di bios:H433 fw:5A2D chan:2 ldrv:1 rbld:30% batt:good a0d0 838GiB RAID 5 1x4 optimal row 0: a0c0t0 a0c0t1 a0c0t2 a0c0t3 hot spares : a0c0t4 a0c0t0 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t1 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t2 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t3 SEAGATE ST3300655LC 279GiB a0d0 online a0c0t4 SEAGATE ST3300655LC 279GiB hotspare
Same information + disk serial
# ./megactl v v v 1 adapters, driver version 02200207
a0 PERC 4e/Di bios:H433 fw:5A2D chan:2 ldrv:1 rbld:30% mem:256MiB batt:good a0d0 838GiB RAID 5 1x4 optimal row 0: a0c0t0 a0c0t1 a0c0t2 a0c0t3 hot spares : a0c0t4 a0c0t0 SEAGATE ST3300655LC rev:0005 s/n:6LM01KQQ0000B20272XX 279GiB a0d0 online a0c0t1 SEAGATE ST3300655LC rev:0005 s/n:6LM01KPC0000B20273XX 279GiB a0d0 online a0c0t2 SEAGATE ST3300655LC rev:0005 s/n:6LM01KJN0000B2025TXX 279GiB a0d0 online a0c0t3 SEAGATE ST3300655LC rev:0005 s/n:6LM01KJX0000B20273XX 279GiB a0d0 online a0c0t4 SEAGATE ST3300655LC rev:0005 s/n:6LM01KGF0000B20272XX 279GiB hotspare
www.percona.com
38
Example (3) # ./megactl e a0 PERC 4e/Di chan:2 ldrv:1 batt:good a0d0 838GiB RAID 5 1x4 optimal hot spares : a0c0t4 a0c0t0 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:246Ki delay: 0 reread: 0 tot/corr:246Ki tot/uncorr: 0 verify errors: corr: 4Mi delay: 0 revrfy: 0 tot/corr: 4Mi tot/uncorr: 0 a0c0t1 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:108Ki delay: 0 reread: 0 tot/corr:108Ki tot/uncorr: 0 verify errors: corr: 1Mi delay: 0 revrfy: 0 tot/corr: 1Mi tot/uncorr: 0 a0c0t2 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:220Ki delay: 1 reread: 0 tot/corr:220Ki tot/uncorr: 0 verify errors: corr: 3Mi delay: 0 revrfy: 0 tot/corr: 3Mi tot/uncorr: 0 a0c0t3 279GiB a0d0 online write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr:125Ki delay: 0 reread: 0 tot/corr:125Ki tot/uncorr: 0 verify errors: corr: 2Mi delay: 0 revrfy: 0 tot/corr: 2Mi tot/uncorr: 0 a0c0t4 279GiB hotspare write errors: corr: 0 delay: 0 rewrit: 0 tot/corr: 0 tot/uncorr: 0 read errors: corr: 5 delay: 0 reread: 0 tot/corr: 5 tot/uncorr: 0 verify errors: corr: 11Mi delay: 0 revrfy: 0 tot/corr: 11Mi tot/uncorr: 0
www.percona.com
39
Back to our questions ●
●
● ● ● ● ● ● ●
On what kind of machine runs my db ? Physical/Virtual ? How much memory is used/free ? Does it swap ? Can I add more ? How many cores ? Is this a 64bit machine ? How many disks ? Do we have a RAID controller ? Which RAID level ? What are my IOPS ? Which IO scheduler is in use ? On which filesystem is the data stored ? www.percona.com
40
IOPS
What about IOPS ?
www.percona.com
41
IOPS ●
●
Input/Output Operations Per Second is usually associated with the measurement of a storage system performance. To calculate the average IOPS check the datasheet of your disk to the manufacturer's webiste. You need: ➢ ➢ ➢
Rotation speed Average latency Average seek time www.percona.com
42
IOPS formula The formula to calculate IOPS is:
IOPS =
1 [average latency in sec + average read/write seek time in dec ]
This is for ONE disk, there is a write penalty associated with RAID configuration.
www.percona.com
43
Rotational speed: Measured in revolutions per minute (RPM), a higher rotational speed is associated with a higher performing disk. Most enterprise disks rotate at speeds of 7,200, 10,000 or 15,000 RPM. ● Average latency: This is the time it takes for the sector of the disk being accessed to rotate into position under a read/write head. ● Average seek time: The time (in ms) it takes for the hard drive’s read/write head to position itself over the track being read or written. There are both read and write seek times. ●
IOPS formula : RAID penalty List of RAID configurations & their penalties:
RAID configuration
Penalty
RAID 0
1
RAID 1
2
RAID 10
2
RAID 5
4
RAID 6
6
www.percona.com
44
Rotational speed: Measured in revolutions per minute (RPM), a higher rotational speed is associated with a higher performing disk. Most enterprise disks rotate at speeds of 7,200, 10,000 or 15,000 RPM. ● Average latency: This is the time it takes for the sector of the disk being accessed to rotate into position under a read/write head. ● Average seek time: The time (in ms) it takes for the hard drive’s read/write head to position itself over the track being read or written. There are both read and write seek times. ●
IOPS formula :rotation latency List of rotation latency average
HDD Spindle [rpm]
Average rotational latency [ms] 4,200
7.14
5,400
5.56
7,200
4.17
10,000
3.00
15,000
2.00
www.percona.com
45
Rotational speed: Measured in revolutions per minute (RPM), a higher rotational speed is associated with a higher performing disk. Most enterprise disks rotate at speeds of 7,200, 10,000 or 15,000 RPM. ● Average latency: This is the time it takes for the sector of the disk being accessed to rotate into position under a read/write head. ● Average seek time: The time (in ms) it takes for the hard drive’s read/write head to position itself over the track being read or written. There are both read and write seek times. ●
Workload system IOPS formula The formula to calculate workload system IOPS (with the RAID) is:
1
(Total Workload IOPS * Percentage of workload read operations) + ( (Total Workload IOPS * Percentage of workload write operations) / RAID IOPS Penalty )
www.percona.com
46
Rotational speed: Measured in revolutions per minute (RPM), a higher rotational speed is associated with a higher performing disk. Most enterprise disks rotate at speeds of 7,200, 10,000 or 15,000 RPM. ● Average latency: This is the time it takes for the sector of the disk being accessed to rotate into position under a read/write head. ● Average seek time: The time (in ms) it takes for the hard drive’s read/write head to position itself over the track being read or written. There are both read and write seek times. ●
Example From previous raid information we know... ● ● ● ● ● ● ●
Disk : SEAGATE ST3300655LC (279GB) RAID : 5 # of disks: 4 Spin Speed (RPM): 15k Average latency : 2.0ms Random read seek time : 3.50ms Random write seek time : 4.0ms www.percona.com
47
Example (2)
RAID : 5 -> penalty = 4 # of disks: 4 Spin Speed (RPM): 15k Average latency : 2.0ms Random read seek time : 3.50ms Random write seek time : 4.0ms
IOPS = 1 / [ 0.002 + (0.0035+0.004)/2 ] = 173.91 Total Workload IOPS = 4 x 173.91 = 695 (695.64) Let's assume 80% reads, 20% writes: Workload system IOPS = (695 x 0.8) + ( (695 x 0.2)/4 ) = 590.75 IOPS
www.percona.com
48
Tool #3 Name: sysbench Function: System performance benchmark Url: http://sysbench.sourceforge.net/ License: GPLv2
www.percona.com
49
Sysbench Let's check on that system the output of random writes:
# sysbench --test=fileio --file-test-mode=rndrw --file-total-size=10G --file-extra-flags=direct --num-threads=8 prepare sysbench 0.4.10: multi-threaded system evaluation benchmark 128 files, 81920Kb each, 10240Mb total Creating files for the test...
www.percona.com
50
The following I/O operations are supported: seqwr sequential write seqrewr sequential rewrite seqrd sequential read rndrd random read rndwr random write rndrw combined random read/write Also, the following file access modes can be specified, if the underlying platform supports them: Asynchronous I/O mode At the moment only Linux AIO implementation is supported. When running in asynchronous mode, SysBench queues a specified number of I/O requests using Linux AIO API, then waits for at least one of submitted requests to complete. After that a new series of I/O requests is submitted. Slow mmap() mode In this mode SysBench will use mmap'ed I/O. However, a separate mmap will be used for each I/O request due to the limitation of 32-bit architectures (we cannot mmap() the whole file, as its size migth possibly exceed the maximum of 2 GB of the process address space). Fast mmap() mode On 64-bit architectures it is possible to mmap() the whole file into the process address space, avoiding the limitation of 2 GB on 32-bit platforms. Using fdatasync() instead of fsync() Additional flags to open(2) SysBench can use additional flags to open(2), such as O_SYNC, O_DSYNC and O_DIRECT.
# sysbench test=fileio filetestmode=rndrw filetotalsize=10G file extraflags=direct numthreads=8 run sysbench 0.4.10: multithreaded system evaluation benchmark Running the test with following options: Number of threads: 8 Extra file open flags: 16384 128 files, 80Mb each 10Gb total file size Block size 16Kb Number of random requests for random IO: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Threads started! Done. Operations performed: 6003 Read, 3997 Write, 12800 Other = 22800 Total Read 93.797Mb Written 62.453Mb Total transferred 156.25Mb (10.366Mb/sec) 663.40 Requests/sec executed Test execution summary: total time: 15.0738s total number of events: 10000 total time taken by event execution: 112.9758 perrequest statistics: min: 0.13ms avg: 11.30ms max: 951.96ms approx. 95 percentile: 44.12ms Threads fairness: events (avg/stddev): 1250.0000/46.64 execution time (avg/stddev): 14.1220/0.10
www.percona.com
51
Exercises Run sysbench on kermit ➢
➢
Try different fileio tests: – seqwr (sequential write) – seqrewr (sequential rewrite) – seqrd (sequential read) – rndrd (random read) – rndwr (random write) – rndrw (combined random read/write) Try different threads and fine the maximum www.percona.com
52
Tool #4 Name: pt-diskstats Function: Aggregate and summarize /proc/diskstats Part of: percona-toolkit Url:
http://www.percona.com/doc/percona-toolkit/pt-diskstats.html
www.percona.com
53
Exercises ● ● ●
Test ptdiskstats on kermit Try to output only the hard disk Press “c” and
www.percona.com
54
Know your environment: MySQL
www.percona.com
55
Know your environment (MySQL) We need to find the answers to these questions: ● ● ● ● ● ●
Which version is running ? 64Bits ? How many database ? Where is the datadir ? What about replication ? Do you use some Key Percona Server features ? Some general InnoDB parameters (buffer pool size, log file size, flush method, flush log at commit, R/W I/O Threads, I/O Capacity, Pending counters)
● ● ● ● ● ● ● ●
What the size of the database ? Which engines are used ? What type of fields are used ? Do we use foreign keys ? Do we use views ? Do we use full text search ? Do we use partitioning ? Who has access from where ? www.percona.com
56
Tool #5 Name: pt-mysql-summary Function: Summarize MySQL information in a nice way Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-mysql-summary.html
www.percona.com
57
Back to our questions Did we answer those questions: ● ● ● ● ● ●
Which version is running ? 64Bits ? How many database ? Where is the datadir ? What about replication ? Do you use some Key Percona Server features ? Some general InnoDB parameters (buffer pool size, log file size, flush method, flush log at commit, R/W I/O Threads, I/O Capacity, Pending counters)
● ● ● ● ● ● ● ●
What the size of the database ? Which engines are used ? What type of fields are used ? Do we use foreign keys ? Do we use views ? Do we use full text search ? Do we use partitioning ? Who has access from where ? www.percona.com
58
Tool #6 Name: pt-show-grants Function: print MySQL grants so you can effectively replicate, compare and versioncontrol them. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-show-grants.html
www.percona.com
59
Exercises ●
●
Retrieve the information related to MySQL on kermit and try to answer the previous questions Print all the grants and save them on a file called plukgrants.txt
www.percona.com
60
It's now time to be intimate with our database Let's try to find the answer to these indiscreet questions: ●
● ●
Which queries are the most resource eater ? How are the indexes used ? Are they duplicate keys ?
www.percona.com
61
Capture data For the following tools, we need to fetch some data. There are several solutions: ●
●
●
use slow query log (generally it stores only “slow”
queries, but some fast queries can also be wrong) use general query log (not the best solution, some attributes like Query_time are missing) use a network dump (maybe the best solution)
www.percona.com
62
tcpdump ●
●
Capture data a different period of time ➢ during peak and non peak time Data with many drop packets are useless # time tcpdump i any s 65535 x n q tttt 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' > $(hostname)_queries_$ (date +%Y%m%d_%H%M).tcp www.percona.com
63
Devananda Van Der Veen explained on the MySQL Performance Blog how to capture traffic without dropping packets on busy servers. Dropped packets cause pt-query-digest to miss the response to a request, then see the response to a later request and assign the wrong execution time to the query. You can change the filter to something like the following to help capture a subset of the queries. (See http://www.mysqlperformanceblog.com/?p=6092 for details.) tcpdump -i any -s 65535 -x -n -q -tttt \ 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' All MySQL servers running on port 3306 are automatically detected in the tcpdump output. Therefore, if the tcpdump out contains packets from multiple servers on port 3306 (for example, 10.0.0.1:3306, 10.0.0.2:3306, etc.), all packets/queries from all these servers will be analyzed together as if they were one server. If you’re analyzing traffic for a MySQL server that is not running on port 3306, see --watch-server.
Tool #7 Name: pt-query-digest Function: Analyze query execution logs and generate a query report, filter, replay, or transform queries for MySQL, PostgreSQL, memcached, and more. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-query-digest.html
www.percona.com
64
pt-query-digest ptquerydigest watchserver "x.x.x.x:xxxx" type tcpdump file_name.tcp use watchserver if you have multiple servers on the same machine or if you use a different port
www.percona.com
65
Tool #8 Name: pt-index-usage Function: Read queries from a log and analyze how they use indexes. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-index-usage.html Note: the query log needs to be in MySQL‘s slow query log format. If you need to input a different format, you can use pt-query-digest to translate the formats. If you don’t specify a filename, the tool reads from STDIN.
www.percona.com
66
Tool #9 Name: pt-duplicate-key-checker Function: Find duplicate indexes and foreign keys on MySQL tables. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-duplicate-key-checker.html
www.percona.com
67
Exercises ● ●
●
Make some queries From a second terminal capture 5 minutes of mysql traffic Test the following tools: ➢ pt-query-digest ➢ pt-index-usage ➢ pt-duplicate-key-checker Do you see something unexpected ? www.percona.com
68
Know the evolution of your environment
www.percona.com
69
Know the evolution of your environment Now that we are familiar with the environment, it's time to check and understand its behavior.
Monitoring vs Trending
www.percona.com
70
Monitoring
Tool #10 Name: innotop Function: top clone for MySQL License: GNU GPLv2 Url: http://code.google.com/p/innotop
www.percona.com
71
Monitoring
Tool #11 Name: pt-mext Function: Look at many sample og MySQL SHOW GLOBAL STATUS side-by-side License: GNU GPLv2 Url:
http://www.percona.com/doc/percona-toolkit/ptmext.html
www.percona.com
72
pt-mext pt-mext -r -- mysqladmin ext -i10 -c5 All values are interesting but you should pay even more attention to: ● ● ● ● ● ● ● ● ● ● ● ●
Created_tmp_* Handler_read_* Innodb_buffer_pool_read* Innodb_os_log_written Key_read_requests Key_reads Qchache* Select_full_*join Select_range Select_scan Table_locks_waited Threads_running
www.percona.com
73
Exercises ● ● ● ●
Run innotop on kermit Change the refresh interval to 0.5 Check InnoDB information Run ptmext and get 3 columns of values with an interval of 30 seconds
www.percona.com
74
Trending
Tool #12 Name: cacti & better cacti templates Function: graphing solution, frontend to RRDTool. License: GNU GPLv2 Url: http://cacti.net http://code.google.com/p/mysql-cacti-templates
www.percona.com
75
Table scans, backup?
www.percona.com
76
Take backup here
www.percona.com
77
max_connections not reached yet
Why more incoming connections?
www.percona.com
78
In 2 hours, almost 6900 seconds replication lag
www.percona.com
79
Why this sudden peak in rangescans?
www.percona.com
80
DB Restart Time to fill buffer
www.percona.com
81
Save your environment: Backup
www.percona.com
82
Tool #13 Name: mydumper Function: high-performance multi-threaded backup (and restore) toolset for MySQL and Drizzle License: GNU GPLv3 Url: http://mydumper.org https://launchpad.net/mydumper/
www.percona.com
83
mydumper ● ● ● ● ●
Written in C 10x faster than mysqldump Compression on-the-fly Multi-threaded Works fine for MyISAM time ./mydumper h localhost u percona p mypass t 8 c regex '^(?!(mysql|test))' www.percona.com
84
Tool #14 Name: Percona XtraBackup Function: Open source hot backup tool for InnoDB and XtraDB databases License: GNU GPLv2 Url:
http://www.percona.com/software/percona-xtrabackup/ https://launchpad.net/percona-xtrabackup
www.percona.com
CREATE TABLE `rental` ( mysql> `rental_id` int(11) NOT NULL AUTO_INCREMENT, ... ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8; mysql> SET FOREIGN_KEY_CHECKS=0; mysql> SET GLOBAL innodb_expand_import=1; mysql> ALTER TABLE sakila.rental DISCARD TABLESPACE; # cp /data/backups/2011-02-03_21-26-08/sakila/rental. {exp,ibd} /var/lib/mysql/sakila/ # chown -R mysql:mysql /var/lib/mysql/sakila/rental. {exp,ibd} mysql> ALTER TABLE sakila.rental IMPORT TABLESPACE; mysql> SET FOREIGN_KEY_CHECKS=1;
85
xtrabackup ● ● ● ● ● ● ● ● ●
O’Reilly award MySQL Application of Year Streaming Compression Incremental/Delta Filtering Throttling Parallel Copying Statistics Export/Import Table www.percona.com
86
Tool #15 Name: pt-archiver Function: Archive rows from a MySQL table into another table or a file by a low-impact, forward-only job to nibble old data out of the table without impacting OLTP queries much. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-archiver.pl
www.percona.com
87
Exercises ●
● ●
●
Backup the employees database using mydumper with 4 threads and store it in /home/pluk/backups Take a full backup using xtrabackup Insert some data while backup is running (if you have time :-) ) Take an incremental backup
www.percona.com
88
Maintain your environment
www.percona.com
89
Maintenance ● ● ●
Daily maintenance Find problems Replication
www.percona.com
90
Daily maintenance ● ● ● ●
Everything from the MySQL console Painless database changes Tested upgrades ? Extra
www.percona.com
91
From the console
Tool #16 Name: common-schema Function: provides useful views and routines to assists in schema analysis, monitoring management & SQL code generation License: New BSD License Url: http://code.google.com/p/common-schema
www.percona.com
92
Schema analysis no_pk_innodb_tables: List InnoDB tables where no PRIMARY KEY is defined redundant_keys: List indexes which are made redundant (or duplicate) by other (dominant) keys. sql_alter_table: Generate ALTER TABLE SQL statements per table, with engine and create options sql_foreign_keys: Generate create/drop foreign key constraints SQL statements table_charset: List tables, their character sets and collations text_columns: List textual columns character sets & collations Data dimensions auto_increment_columns: List AUTO_INCREMENT columns and their capacity data_size_per_engine: Present with data size measurements per storage engine data_size_per_schema: Present with data size measurements per schema Monitoring global_status_diff: Status variables difference over time, with interpolation and extrapolation per time unit global_status_diff_clean: Status variables difference over time, with spaces where zero diff encountered global_status_diff_nonzero: Status variables difference over time, only nonzero findings listed Processes & transactions innodb_locked_transactions: List locked transactions, the locks they are waiting on and the transactions holding those locks. innodb_simple_locks: Listing of locks, simplifying INFORMATION_SCHEMA.INNODB_LOCKS innodb_transactions: Listing of active (InnoDB Plugin) transactions, which are currently performing queries innodb_transactions_summary: A one line summary of InnoDB's transactions: count, state, locks processlist_per_userhost: State of processes per user/host: connected, executing, average execution time processlist_repl: Listing of replication processes: the server's slave threads and any replicating slaves processlist_summary: Number of connected, sleeping, running connections and slow query count processlist_top: Listing of active processes sorted by current query runtime, desc (longest first) Security routine_privileges: INFORMATION_SCHEMA-like view on routines privileges sql_grants: Generate SQL GRANT/REVOKE statements for existing accounts; provide with GRANT metadata sql_show_grants: Generate complete accounts SHOW GRANTS FOR -like output Internals innodb_index_rows: number of row cardinality per keys per columns in InnoDB tables innodb_index_stats: estimated InnoDB depth & split factor of key's B+ Tree
Database changes
Tool #17 Name: pt-online-schema-change Function: Perform online, non-blocking table schema changes. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html
www.percona.com
93
Tested upgrades
Tool #18 Name: pt-upgrade Function: execute queries on multiple servers and check for differences Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-upgrade.html
www.percona.com
94
Extra
Tool #19 Name: pt-deadlock-logger Function: extract and log MySQL deadlock information Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-deadlock-logger.html
www.percona.com
95
Extra
Tool #20 Name: pt-kill Function: kill MySQL queries that match certain criteria Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-kill.html
www.percona.com
96
Exercises ● ●
●
●
Find the biggest table per database Change the engine of table .... to engine innodb. This change must be done online. Using information_schema what's the size of the dataset ? Examine the values of the auto_increments (from common_schema)
www.percona.com
97
SELECT * FROM common_schema.data_size_per_schema; SELECT * FROM common_schema.innodb_index_stats WHERE TABLE_NAME='employees';
Find problems ● ●
What do you advise me doctor ? Get the symptoms when it hurts !
www.percona.com
98
Tool #21 Name: pt-variable-advisor Function: analyzse MySQL variables and advise on possible problems Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-variable-advisor.html Note: this tool considers only static values and not the evolution of values, it should be used in addition of tool #11: pt-mext
www.percona.com
99
Get the symptoms when it hurts ! To perform this action, we will use 3 new tools : ● ● ●
pt-stalk pt-collect pt-sift
www.percona.com
100
Extra
Tool #22 Name: pt-stalk Function: wait for a condition to occur then begin collecting data. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-stalk.html
www.percona.com
101
Extra
Tool #23 Name: pt-collect Function: collect information from a server for some period of time. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-collect.html
www.percona.com
102
Extra
Tool #24 Name: pt-sift Function: browses files created by pt-collect. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-collect.html
www.percona.com
103
Replication Tools focusing on replication: ● ● ● ● ● ●
pt-slave-delay pt-slave-find pt-slave-restart pt-heartbeat pt-table-checksum pt-table-sync
www.percona.com
104
Tool #25 Name: pt-slave-delay Function: make a MySQL slave server lag behind its master. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-delay.html
www.percona.com
105
Tool #26 Name: pt-slave-find Function: find and print replication hierarchy tree of MySQL slaves. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-find.html
www.percona.com
106
Tool #27 Name: pt-slave-restart Function: watch and restart MySQL replication after errors. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-restart.html
www.percona.com
107
Tool #28 Name: pt-heartbeat Function: monitor MySQL replication delay. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-slave-restart.html
www.percona.com
108
Tool #29 Name: pt-table-checksum Function: perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-table-checksum.html
www.percona.com
109
Tool #30 Name: pt-table-sync Function: synchronize MySQL table data efficiently. Part of: percona-toolkit Url: http://www.percona.com/doc/percona-toolkit/pt-table-sync.html
www.percona.com
110
Exercises ● ●
●
●
●
● ●
Start the machine gonzo Setup “gonzo” as slave of kermit using XtraBackup Prepare stalk on gonzo to collect data when the replication breaks Remove one row on kermit, add it back to gonzo and then add it again on kermit Remove some rows in some tables in employees database on gonzo Run checksum on kermit Fix the tables by syncing them www.percona.com
111
/q !V zO G B N E kJZ F -2 0 ,1 g R H h T iv b td fP C s U L Q S y lM u A m a n rco e .p w
[email protected] @lefred
We're Hiring! www.percona.com/about-us/careers/
Thank you ! 114
