How Percona Maintains Customer Health

如何保证关键应用程序和服务的最佳数据库性能?主动监测和干预是一种方法。
Percona技术客户经理(TAM)是专业的DBA,为我们的托管服务客户提供主动帮助,帮助确保性能、可用性和可靠性。
在今天的网络研讨会上,我们将讨论Percona的技术客户经理(TAM)团队使用的一些工具和方法,以确保我们客户的数据库基础架构既健康又优化。这些实践包括以下活动:
-定期测试备份
-执行索引审阅
-产能规划
请与我们一起探讨Percona TAMS为维护我们的托管服务客户而采用的一些最佳实践。

展开查看详情

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