Overview
This article discusses MySQL performance optimization techniques implemented at Pinterest, achieving a significant increase in throughput and a reduction in latency variance. Key improvements included upgrading the Linux kernel and optimizing MySQL configurations, resulting in a 500% increase in read and write throughput.
What You'll Learn
1
How to optimize MySQL performance through kernel upgrades and configuration changes
2
Why Linux kernel version impacts MySQL performance on AWS
3
How to conduct performance testing using sysbench
Prerequisites & Requirements
- Understanding of MySQL and database performance metrics
- Familiarity with sysbench for performance testing(optional)
Key Questions Answered
How much did MySQL performance improve after optimizations?
After implementing the optimizations, MySQL performance improved from approximately 5000 QPS to 26000 QPS, achieving roughly 500% more read and write throughput. This was accomplished while reducing p99 latency by over 500ms.
What kernel version is recommended for MySQL on AWS?
The article highlights that the 3.8 kernel is significantly better than the default 3.2 kernel, achieving 350MB/sec in sysbench tests. Upgrading to even newer kernels could yield further performance improvements.
What were the latency changes observed after MySQL optimizations?
Client-perceived latency dropped from a highly-variable 15–35ms with outliers over 100ms to a stable 15ms with outliers at 80ms or less. Server-measured latency also decreased from 5–15ms to a flat 5ms.
Key Statistics & Figures
Read throughput
22000–25000 QPS
Increased from approximately 4100–4600 QPS after optimizations.
Write throughput
5100–6000 QPS
Increased from approximately 1000 QPS after optimizations.
p99 latency reduction
over 500ms
Latency decreased significantly on both read and write operations after implementing optimizations.
Peak throughput increase
50%
The peak throughput on primary clusters increased about 50% since the beginning of the year.
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Mysql
Used for managing data and running queries at Pinterest.
Cloud Platform
AWS
Hosting environment for MySQL instances.
Operating System
Linux
The kernel version impacts MySQL performance on AWS.
Performance Testing Tool
Sysbench
Used for benchmarking MySQL performance under various configurations.
Key Actionable Insights
1Upgrading the Linux kernel can lead to significant performance improvements for MySQL on AWS.The article demonstrates that moving from the default 3.2 kernel to 3.8 resulted in a substantial increase in throughput, suggesting that kernel upgrades should be a priority for performance tuning.
2Conducting thorough performance testing with tools like sysbench is crucial for identifying bottlenecks.The author ran nearly 60 different configurations to find the optimal setup, emphasizing the importance of systematic testing in performance optimization.
3Optimizing MySQL configurations can dramatically enhance read and write capabilities without additional hardware costs.The optimizations led to a 500% increase in throughput, showcasing how software tuning can be more cost-effective than hardware scaling.
Common Pitfalls
1
Relying on outdated kernel versions can severely limit database performance.
The article illustrates that using the default 3.2 kernel resulted in poor performance, highlighting the necessity of keeping software up to date.