MySQL performance optimization
In our blog, we write a lot about building a cloud service 1cloud , but many interesting things can be learned from the experience of working with the infrastructure of other companies.
We have already talked about the imgix photoservice data center, and today we will touch on the topic of improving MySQL performance and take a look at the tips of the Pinterest social network engineers.
/ photo by Jason Cartwright CC
')
Newcomers to Pinterest are based on their own choice of a team of engineers in which they would like to join. New employees solve a large number of tasks, one of which is to optimize the performance of MySQL deployed on Amazon Web Services (AWS).
Even with a fairly low workload of 2000 QPS (requests per second), the company's engineers could not reach any adequate levels of I / O performance — exceeding a threshold of 800 IOPS (I / O operations per second) resulted in an unacceptable increase in latency.
This problem could be solved in two ways: by switching to a more efficient instance, which doubles costs and reduces efficiency; or improving the performance of the existing system.
The essence of the solution that Pinterest eventually applied lies in the marked influence of the Linux kernel version: neither the standard 3.2 nor the recommended 3.8 gave sufficient efficiency; but the fact of the influence of settings at the operating system level pushed engineers to search for various optimization options. As part of this work, more than 60 different SysBench test configurations were tested with output to a file.
To assess the impact of changes on the transaction processing speed in the SysBench test, the 99th percentile of response time was calculated for different system configurations and different flow numbers: 16 and 32. As a result, the read / write performance was increased by about 500% for both flows.
The speed of processing read requests has increased from 4100-4600 QPS to more than 22000-25000 QPS, depending on the degree of parallelism. Write request processing speed increased from 1000 QPS to 5100-6000 QPS.
On the client side, the 99th percentile of latency has been reduced from 15-35 ms (with individual emissions to 100 ms) to a stable 15 ms (with emissions to 80 ms and less). On the server side, the latency has decreased from 5-15 ms to a stable 5 ms, with a daily 18 millisecond peak for the system maintenance time. The number of reported incidents related to system performance or server overload dropped from 300 to a total of 10 (within a couple of months).
PS We try to share not only our own experience on the service of providing virtual infrastructure 1cloud , but also to talk about related areas of knowledge in our blog on Habré. Do not forget to subscribe to updates, friends!
We have already talked about the imgix photoservice data center, and today we will touch on the topic of improving MySQL performance and take a look at the tips of the Pinterest social network engineers.
/ photo by Jason Cartwright CC
')
Newcomers to Pinterest are based on their own choice of a team of engineers in which they would like to join. New employees solve a large number of tasks, one of which is to optimize the performance of MySQL deployed on Amazon Web Services (AWS).
Even with a fairly low workload of 2000 QPS (requests per second), the company's engineers could not reach any adequate levels of I / O performance — exceeding a threshold of 800 IOPS (I / O operations per second) resulted in an unacceptable increase in latency.
This problem could be solved in two ways: by switching to a more efficient instance, which doubles costs and reduces efficiency; or improving the performance of the existing system.
The essence of the solution that Pinterest eventually applied lies in the marked influence of the Linux kernel version: neither the standard 3.2 nor the recommended 3.8 gave sufficient efficiency; but the fact of the influence of settings at the operating system level pushed engineers to search for various optimization options. As part of this work, more than 60 different SysBench test configurations were tested with output to a file.
To assess the impact of changes on the transaction processing speed in the SysBench test, the 99th percentile of response time was calculated for different system configurations and different flow numbers: 16 and 32. As a result, the read / write performance was increased by about 500% for both flows.
The speed of processing read requests has increased from 4100-4600 QPS to more than 22000-25000 QPS, depending on the degree of parallelism. Write request processing speed increased from 1000 QPS to 5100-6000 QPS.
On the client side, the 99th percentile of latency has been reduced from 15-35 ms (with individual emissions to 100 ms) to a stable 15 ms (with emissions to 80 ms and less). On the server side, the latency has decreased from 5-15 ms to a stable 5 ms, with a daily 18 millisecond peak for the system maintenance time. The number of reported incidents related to system performance or server overload dropped from 300 to a total of 10 (within a couple of months).
PS We try to share not only our own experience on the service of providing virtual infrastructure 1cloud , but also to talk about related areas of knowledge in our blog on Habré. Do not forget to subscribe to updates, friends!
Source: https://habr.com/ru/post/261741/
All Articles