How Percona Maintains Customer Health

1 . How Percona Maintains the Optimal Health of Customer Author: Tim Sharp Date: 2018/03/08 • © 2016 Percona 

2 . Technical Account Managers • Client • • DBA TAM • Team • © 2018 Percona 

3 . Technical Account Managers • Vision: Proactive Support Oz of Prevention = Lb of Cure Project Management Resource Optimization • © 2018 Percona 

4 . Technical Account Managers • Percona DBA Services 24x7x365 Monitoring & Support Proactive deliverables Percona Platform Dedicated Account Manager Targeting cloud based environments • © 2018 Percona 

5 . Technical Account Managers • Proactive Monthly Deliverables: Regular health status checks Application Query Reviews Top Query Review Performance Tuning Routine Maintenance Report card • © 2018 Percona 

6 . Technical Account Managers • Proactive Quarterly Deliverables: Business Continuity Audit Security Assessment Capacity Planning • © 2018 Percona 

7 . Tools & Methodologies • Index review Query Review File management Backups & Testing Backups Replication Fail-over testing Miscellaneous checks • © 2018 Percona 

8 . Index Review: Duplicate indices • Performance degradation Unnecessary WRITE operations Optimizer needs to examines more query plans Update more index statistics Less data in the Buffer Pool Larger backups; slow backup restoration Percona Toolkit: pt-duplicate-key-checker • © 2018 Percona 

9 . Index Review: Unused Indices • Unused indices -> performance killer Percona toolkit: pt_index_usage Checks the usage of indices from the slow query log Executes every query with EXPLAIN • © 2018 Percona 

10 . Index Review: Primary Keys • Tables without Primary Keys InnoDB will create its own PK’s Only one semaphore is used for concurrent access Contention issues: dict_sys mutex • Potential bottleneck • © 2018 Percona 

11 . Index Review: Primary Keys • Best Practices: Use auto_increment Saves work Useful information for the query optimizer • © 2018 Percona 

12 . Query Review • Tool: pt-query-digest Analyzes queries from slow, general, & binary logs Identifies problem queries Queries that take too long Fast queries that occur with high frequency • © 2018 Percona 

13 .File Management: Optimize <Table> Benefits Defrags: reclaims delete space Re-organizes database pages: more efficient packing Reduces file sizes Improves performance! Risks: Locks down table for the duration Tool: Optimize table or ALTER TABLE <tablename> engine=innodb • © 2018 Percona 

14 .Backups Are you doing regular backups? There are NO reasons not to have regular backups! • © 2018 Percona 

15 .Backups Logical Why: Better granularity Risks: Larger files, slower restoration Tool: mydumper Physical Why: Faster backup, faster restoration Tool: Xtrabackup • © 2018 Percona 

16 .Backups Binlogs Why: Point in time recovery Bad: increase disk IO Delayed Slave Why: enables faster recovery Bad: requires additional hardware • © 2018 Percona 

17 .Testing Backups Testing is Knowing • © 2018 Percona 

18 .Testing Backups: Considerations Which backup type do you want to test? How large is your backup? How long will it take to import? Do you have the necessary hardware • © 2018 Percona 

19 .Testing Backups: Process Import most recent backup Attach to a master & sync Run pt_table_checksum • © 2018 Percona 

20 .Replication Consistency Check Benefits: Ensures database consistency between nodes Tools: pt-table-checksum & pt-table-sync Delayed Slave Benefits: Allows for faster recovery Tool: pt-slave-delay • © 2018 Percona 

21 .Replication REPLICATION IS NOT A BACKUP • © 2018 Percona 

22 .Fail-Over Testing Verifies ability to provide continuous database services Testing: Correct slave get promoted? Data loss? Slave performance • © 2018 Percona 

23 .Miscellaneous Checks Memory Management • innodb_buffer_pool_size • 80% of RAM • Benefits: Larger dataset in memory File Management: • innodb_file_per_table should be in (1, ON) • Benefits: partial backups, less contention • © 2018 Percona 

24 .Miscellaneous Checks kernel swappiness • Should be 0 or 1 • Benefits: avoids swapping processes out of physical memory Engine Scheduler • Default: CFQ (Completely Fair Queuing) • Should be NOOP • Benefits: Improved performance • © 2018 Percona 

25 . Final Gotcha’s Max Integer check Check auto_increment columns Are you in danger of maxing out? Has burned more than 1 seasoned professional • © 2018 Percona 

26 .Use code 10ATTENDPL18 for 10% discount! 26 © 2018 Percona 

27 . Questions? • • © 2018 Percona 

28 . • DATABASE • Database Performance Matters PERFORMANCE • MATTERS