Downloaded 20 times
Uploaded byMariaDB plc
Maximizing performance via tuning and optimization
The document discusses performance tuning and optimization strategies for MariaDB servers, covering best practices in server configuration, storage options, and connection handling. It emphasizes monitoring metrics, configuring settings, and query tuning to maximize database performance. Additional resources and references for further reading on tuning techniques and tools are provided.
In this document
Powered by AI
Overview of maximizing MariaDB performance through tuning and optimization techniques, indicating the importance of server infrastructure.
Discusses best practices including SLAs, metric capturing, and stress testing to ensure optimal performance and avoid potential performance 'cliffs'.
Focuses on server, storage, network, and OS optimizations that enhance database performance, emphasizing memory, storage types, and efficient network setups.
Addresses connection management in MariaDB, back pressure implications, and configurations that impact connection pooling and resource management.
Examines key configuration settings in MariaDB that significantly influence performance, stressing the importance of proper configurations.
Emphasizes the importance of query tuning, finding slow queries, indexing strategies, and using performance tools for optimization.
Final slide for questions and concluding remarks as well as additional resources in the appendix for further learning.
Maximizing performance via tuning and optimization
- 1. Maximizing Performance via Tuning and Optimization Gettingthe most from MariaDB Server
- 2. Agenda • GeneralBest Practices • Server, Storage, Network and O/S • Connections & Pooling • MariaDB Config Settings • Query Tuning • Q&A
- 3. MariaDB Server +InnoDB
- 4. General Best Practices Maybeobvious, but worth repeating
- 5. • Service LevelAgreements (SLAs) – Individual Biz/App Transactions – Throughput – Latency (at percentile) – Peaks of peaks or favorable scheduling? • Translate to Database Transactions Define Target
- 6. Capture Metrics • Biz/AppTransactions – Code Instrumentation – Synthetic Transactions – Compare to defined SLAs • Database Transactions • Sub-system level – Servers (Web, App, DB, etc…) – Storage – Network – Database
- 7.
- 8. Avoiding Cliffs •Understand expected business volumes • Watch system-level stats for saturation • Stress testing – Sysbench – HammerDB – TPC – Many others…. – Custom
- 9. Server, Storage, Networkand O/S Core Infrastructure
- 10. • Dedicated Server •Memory – More usually helps (up to ~dataset size) – Important with read-heavy + slow disk • More CPUs – Highly concurrent use cases – Usually favored over faster CPUs • Faster CPUs – Less concurrent use cases – Dataset fits in memory Database Server
- 11. • Local orSAN over NAS – Performance • SSD over HHD – Performance and MTBF – SSD wear not usually a factor • SSDs – Consumer – Prosumer – PCIe – NVMe Storage
- 12. • Can beBandwidth Hungry – Regular client traffic – Replication Traffic – Rebuilding replicas from snapshots • Stability matters for Replication • Sometimes overlooked as potential bottleneck • Efficient DNS setup* Network
- 13. • vm.swappiness =10 • ext4 or xfs for data files • noatime for filesystem mounts • ulimit changes OS (Linux) mariadb.com/kb/en/library/operating-system-optimizations/
- 14. Connections & Pooling ApplyingBack Pressure
- 15. Back Pressure inthe Full Stack Firewall/LB Web Servers App Servers MaxScale MariaDB Server(s)
- 16. MariaDB Connection Controls AppServers MaxScale MariaDB Server(s) max_connections wait_timeout thread_handling thread_pool_max_threads thread_pool_min_threads thread_pool_idle_timeout ... MariaDBDataSource MariaDBPoolDataSource maxPoolSize minPoolSize ... Outbound persistpoolmax persistmaxtime Inbound max_connections connection_timeout
- 17. Further Reading • JavaConnector Pooling mariadb.com/kb/en/library/pool-datasource-implementation/ • MaxScale mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/maxscale-configuration- usage-scenarios/#server mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/maxscale-configuration- usage-scenarios/#server • Server Thread Pools mariadb.com/kb/en/library/thread-pool-in-mariadb/ mariadb.com/kb/en/library/thread-pool-system-and-status-variables/
- 18. Configuration Settings Common Settingswith Performance Impact
- 19. • Runtime changesvia SET GLOBAL • Make permanent with changes to my.cnf – Make sure you have right my.cnf – Verify with SHOW GLOBAL • One change at a time • Production changes – tested, reviewed, version controlled Changing Config Settings my.cnf
- 20. Config Settings (1of 2) innodb_buffer_pool_size innodb_log_file_size innodb_file_per_table query_cache_size max_connections
- 21. Config Settings (2of 2) tmp_table_size max_heap_table_size join_buffer_size sort_buffer_size
- 22. Query Tuning There arealways more queries to tune
- 23. Finding Slow Queries slow_query_log =1 slow_query_log-file = /var/lib/mysql/myslow.log long_query_time = 10 Pay attention to similar queries and the query count
- 24. Analyzing Slow Queries EXPLAIN SELECT * FROMemployees WHERE MONTH(birth_date) = 8 G id: 1 select_type: SIMPLE table: employees type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 299587 Extra: Using where
- 25. • Poor Indexing#1 Reason for poor performance • Basics of B-Tree Indexing same across relational systems • Space/Performance Tradeoff • Write/Read Tradeoff Indexing
- 26. • PRIMARY KEY= Clustered Index • Secondary Indexes reference hold primary key – key PRIMARY KEY small • InnoDB Table Stats • Optimizer Hints – https://mariadb.com/kb/en/library/optimizer-hints/ InnoDB Indexing Tips
- 27. Query Tuning SHOW STATUS Globalor Session ● Returns List of Internal Counters ● GLOBAL for System-Wide Status — Since Start-Up ● SESSION for Local to Client Connection ● FLUSH STATUS Resets Local Counters ● Monitor Changes to Counters to Identify Hot Spots ● Collect Periodically Status Snapshots to Profile Traffic
- 28. Query Tuning PERFORMANCE_SCHEMA ● Similarto INFORMATION_SCHEMA , but Performance Tuning ● Monitors MariaDB Server Events ● Function Calls, Operating System Waits, Internal Mutexes, I/O Calls ● Detailed Query Execution Stages (Parsing, Statistics, Sorting) ● Some Features Storage Engine Specific ● Monitoring Lightweight and Requires No Dedicated Thread ● Designed to be Used Iteratively with Successive Refinement
- 29.
- 30.
- 31.