Performance Tuning overview _ MySQL

Source: Internet
Author: User
Tags mysql index website performance e5620
Performance Tuning overview:

I. Overview

2. what is performance tuning? (What)

3. Why performance optimization? (Why)

4. When do I need performance optimization? (When)

5. where do I need performance tuning? (Where)

6. Who will perform performance optimization? (Who)

7. how to optimize performance? (How)

VIII. Summary

Note: hardware configuration: CPU Xeon E5620 x 2 8 core, 16 GB memory, hard disk RAID 10, operating system: CentOS 6.4 x86_64 (64-bit ).

Originally, this blog post should have been written in the last week, but recently the project was tight and I always worked overtime at night. so I made some summary by taking some time to work overtime. The main content is to summarize the project practices in the last three months. Finally, the lead gave up a project summary meeting to share with the team. This blog post is a summary of some of my colleagues' practical experience shared by the team. I will share it with you now. I have explained a lot about clusters, server load balancer, cache servers, LAMP platform construction, MySQL clusters, and so on in the previous blog, that is, there is no detailed content about performance tuning, in this topic, I will talk about performance tuning.

In this blog post, I don't want to use abstract concepts to talk about performance tuning problems. I just want to use the most popular language to express my thoughts as accurately as possible. I am very limited. if you have any questions or do not know anything, please let us know. To better understand our upcoming performance tuning topic, I would like to share with you the "5 w + 1 h" method of writing this article.

Note: 5 w + 1 h is a scientific analysis of the work done. based on investigation and research on a job, What is the content of the work and Who is the responsible person) where, When, How, and Why, that is, a written description of "5 W" and "1 H, follow this description to complete the job. (Source: "Baidu Encyclopedia ")

2. what is performance tuning? (What)

Before talking about performance tuning, let's talk about the computer architecture. For example, there are three parts: hardware, operating system, and application. In fact, performance tuning is to adjust this content, including hardware, operating systems, and applications. Among them, these three aspects contain a number of content. Hardware includes CPU, memory, disk, network card, and others ......, Operating systems include processes, virtual memory, file systems, networks, and others ......, I don't need to talk about the application. we all know that Common applications include Apache, MySQL, Nginx, and Memcahed. So what is performance tuning? Performance Tuning is to have a deep understanding of computer hardware, operating systems, and applications, adjust the relationship between the three, and maximize the performance of the entire system (including hardware, operating systems, and applications, and can continuously meet existing business needs. This is what we call performance tuning. what do you know about the customer service?

3. Why performance optimization? (Why)

Next, let's talk about why performance optimization is required. In the end, there are two reasons: first, to achieve better system performance (that is, your existing system runs well, but it can run better ). Second, performance tuning is used to meet increasing business needs. To help you better understand why performance tuning is required? In three aspects:

  • Hardware selection (select servers based on the server application type)

  • Operating system release version (select release version)

  • Applications (Nginx, MySQL, etc)

  1. Hardware selection

Whether you are renting a server or buying a server by yourself, you have to have a problem. what kind of hardware configuration server should we choose. Generally, we select servers based on the application type, because you cannot configure hardware to meet all application requirements, because the specific requirements of each application are different. Next, let's take a look at the application types in project implementation:

  • Server load balancer: The performance requirement is relatively low, because it is only responsible for data forwarding, but you must select a network card with outstanding performance. (Recommended configuration: CPU E5620 x 1 Memory 8G hard disk 500G (RAID5 ))

  • Web servers: Generally, they only process some static pages or images. Therefore, they are not very demanding and can be used by mainstream servers. (Recommended configuration: CPU E5620 x 1 Memory 16G hard disk 500G (RAID5 ))

  • Application Server: generally, an application server is responsible for implementing website functions and occupies a relatively important position in the architecture. in particular, there is only one application server in the website architecture, high requirements on CPU, memory, and disks. (Recommended configuration: CPU E5620 x 2 memory 32G hard disk 500G (RAID10 ))

  • Cache servers: they are divided into front-end page cache and back-end data cache. their typical applications are Varnish and Memcached, which have high memory requirements. generally, we use large memory when configuring the server. (Recommended configuration: CPU E5620 x 1 Memory 32G hard disk 500G (RAID10 ))

  • Database server: The data server has high requirements on CPU, memory, and disk. Once a hardware is short, performance problems may occur. (Recommended configuration: CPU E5620 x 2 memory 64 gb ssd 500 GB (RAID10 ))

  • Backup Server: Generally, the backup server has no requirements, but it must have enough hard disk space. (Recommended configuration: CPU E5620 x 1 Memory 4 GB hard disk 2 TB (RAID5 ))

  • Monitoring server: Generally, there is no need for a monitoring server. (Recommended configuration: CPU E5620 x 1 Memory 4G hard drive 500 (RAID5 ))

  • Other servers: for other servers, you can see the specific needs of specific analysis.

  • ......

Now you know what is the performance tuning of the hardware. according to your specific application, perform specific analysis, especially for servers like MySQL, which have high requirements on CPU, memory, and disk. Therefore, to optimize the performance of hardware, we must select the appropriate hardware configuration. This is the first problem to be solved for website architecture or project implementation!

2. operating system

This book is called "Linux Performance Tuning" (Linux Performance Tuning), written by Fernando Apesteguia. Why do we need performance tuning? He concluded that "when a release is packaged and sent to the customer's hand, it is designed to be fully compatible with most computers in the market. This is a collection of rather mixed hardware (hard disks, video cards, network cards, and so on ). Therefore, Red Hat, SUSE, Mandriva, U buntu, and some other release vendors have chosen some conservative settings to ensure the installation is successful ." Simply put, your operating system is running well, but you can adjust it to achieve higher performance. for example, if you have a high-performance disk, however, if some option parameters in your operating system are not enabled by default, these advanced features cannot be implemented to improve hard disk performance.

Also, I want to talk about the selection of operating system releases. RedHat or CentOS are used in many projects or website architectures, the operating system is mainly developed for enterprise applications. Ubuntu and other operating systems have better desktop support, so pay attention to the release version. (CentOS is widely used in enterprises.) then, we generally do not select the latest release version, because the released version has many bugs, don't be a "mouse" first. for example, if CentOS 7 has been stable for a while before use, we can choose CentOS 6.4 or 6.5. (However, the new version also has many benefits. The new version adds many new features and removes known bugs. you can try to use the new operating system for some unimportant applications)

3. Applications

Finally, let's talk about the application. let's take a look at the Apache MPM configuration file,

Prefork model:

  StartServers 8 MinSpareServers5 MaxSpareServers 20 ServerLimit256 MaxClients 256 MaxRequestsPerChild4000 

You can see from the configuration file that eight processes are enabled when apache starts to start, with a minimum of five processes and a maximum of 20 processes. each process has a maximum of 256 requests, A maximum of 4000 requests can be accepted. if this limit is exceeded, the request is automatically destroyed.

Worker model:

  StartServers 2 MaxClients 150 MinSpareThreads 25 MaxSpareThreads 75 ThreadsPerChild 25 MaxRequestsPerChild0 

Take a look at the configuration file of the worker model. by default, two processes are started, each of which can accept 150 requests, and each process has a minimum of 25 threads, the maximum number of threads is 75. by default, the number of threads is 25. each thread can accept a request with no limit of 0. Now, after reading the configuration file above, we can see that the default Apache configuration file is relatively conservative and only suitable for some small and medium websites, to obtain a high-performance Apache server, performance tuning is also required, including apache compilation options and configuration file optimization. we will not elaborate on the specific optimization here. Through the above explanation, we will start from three aspects: hardware, operating system, and application. we will discuss with you why performance tuning is required. I believe you know and understand this, I believe everyone can't wait. Hey hey, let's talk about a lot of problems without making it clear. let's talk to you next, when do we need performance tuning?

4. When do I need performance optimization? (When)

It is generally divided into two time periods:

  • Before going online (basic optimization)

  • After going online (continuous optimization)

In this case, we usually need to prepare hardware servers, install the operating system, and set up the environment during the project implementation period to launch the project. another important issue is performance optimization, including operating system optimization and application environment optimization. I call the optimization before going online as basic optimization, also known as experience optimization. Based on your project and work experience, you can optimize the performance of the server or architecture before the launch to meet your business needs. There is also the optimization after the project goes online. before going online, we have gone through basic performance optimization to solve most of the performance problems, after all, tests before the launch are simulated and related performance optimization, which is quite different from the real environment after the launch, the first thing we need to do is to monitor the performance of the launched projects, including server performance monitoring and service performance monitoring, server performance monitoring includes CPU usage, CPU load, memory usage, disk I/O, disk space usage, network traffic, and system processes, service performance monitoring includes performance monitoring for all services in the apache, nginx, mysql, and other architectures. once any problems are found, we have to optimize the performance, in this process, continuous optimization is also called monitoring optimization. Next, let's take a look at the specific points where performance tuning is required?

5. where do I need performance tuning? (Where)

In the above section, we can only talk about some major aspects, including the hardware, operating system, and application modules. In fact, there is also the optimization of the program itself, the programs developed by developers based on their needs require performance optimization, but there is little access for our O & M personnel. Let's take a look at these three major blocks:

  • Hardware (CPU, memory, disk, network card)

  • Operating system (process, file system, kernel ......)

  • Applications (Nginx, MySQL ......)

1. hardware

Hardware optimization generally includes two parts:

  • Before going online (hardware selection)

  • After launch (hardware expansion)

Generally, you need to select hardware configurations based on specific applications during project construction. in this regard, you need some O & M experience and new contacts may be somewhat lacking, however, after one or two projects, you can select the hardware configuration. But with an unwritten experience, the higher the hardware configuration, the better (not to mention what I said ). Why do we need to select a type based on specific applications? what kind of applications do we need? what kind of hardware configurations do we need? the important thing is to save costs, we can't spend anything we should not spend on cutting edge. it also saves costs for the company and maximizes resource utilization.

The above is the initial stage of project construction. you are lucky to be here at the beginning of the project. Generally, experienced O & M engineers do not have any problems in hardware selection. Therefore, we do not consider hardware during performance optimization, theoretically, the hardware configuration of our servers will not occur in this performance issue. However, as our business is getting better and better, we didn't take into account such a high performance requirement (access volume) at the initial stage of project creation. Currently, some hardware cannot meet business requirements, in this case, we need to replace a better CPU, a larger memory, and a faster disk. As for how to identify hardware as a performance bottleneck, we will not elaborate on it here. we will elaborate on it later. Finally, let's look at a hardware architecture diagram to help you better understand hardware optimization, for example (Dell R 710 architecture ):

2. operating system

Next we will talk about the operating system. In fact, most of the optimizations are in the optimization of the operating system and applications. apart from the hardware selection before and after the launch, next, let's take a look at what operating system optimization includes:

  • Operating system installation optimization

  • System initialization

  • Process optimization

  • Memory optimization

  • IO optimization

  • File system optimization

  • Network adjustment

  • ......

Let's take a look at a picture to help us understand it more intuitively, such:

3. Finally, we will talk about application optimization. here we will talk about the MySQL optimization example to give you a more intuitive understanding.

  • MySQL compilation and installation optimization

  • MySQL configuration file optimization

  • Index optimization

  • MySQL engine optimization

  • Query cache optimization

  • SQL statement optimization

  • Optimize the table type (MyISAM or InnoDB)

  • Lock mechanism optimization

  • MySQL Server Optimization (for SSD)

  • ......

I believe that you have a deeper understanding of performance optimization in terms of hardware, operating systems, and applications. next we will discuss an important issue, who will optimize performance?

6. Who will perform performance optimization? (Who)

When talking about performance optimization, we first think of O & M engineers who will optimize the performance. In fact, I want to say that one-sided performance optimization is not just about O & M engineers. In fact, performance optimization is a task of the team. Why do I say this? Let's take a look at the following. if a company needs to create a project, let's take the most common e-commerce mall project as an example, the company confirmed that due to business needs, we need to create a building materials mall online. what is the specific process of the project? It may not be very detailed, but the general process is as follows:

  • Operational Requirements

  • Product sorting requirements

  • Develop and develop specific business applications

  • O & M to build a development environment

  • QA for project testing

  • Launch the O & M project

  • Monitoring for project monitoring

  • ......

The development of a specific application requires the participation of the operation department, product department, development department, O & M Department, QA (test), monitoring department, and so on. The same project (business) has performance problems, performance tuning is not only required by the O & M Department, but also solved this performance problem by the department. It may occur in products, programs (*. php), business needs, or O & M environments. However, most of the performance tuning activities include development, O & M, testing, and monitoring.

7. how to optimize performance? (How)

The following describes how to perform performance tuning. the specific steps are as follows:

  • Performance indicator> confirm measurement criteria

  • Performance Test> verify performance indicators

  • Performance Analysis> identify performance bottlenecks

  • Performance Tuning> solving performance problems

  • Performance monitoring> test the optimization effect

1. performance indicators

As we mentioned above, our optimization aims to achieve better performance. What are the performance indicators? How can we measure it? generally, there are three indicators to measure a project (the website here:

  • Throughput> is the number of user or system requests completed per unit time.

  • Concurrency> The number of user access requests that can be accepted at the same time

  • Response time> The interval between the time when the user sends a request and receives the response.

2. performance testing

The purpose of our products or projects (more simply websites) is to allow users to use them. we need to analyze what performance users need to pay attention to from the perspective of users. for users, when you click a button, link, or send an Operation Command, the system will process the request and send it to the user in the form of a webpage, the time spent in this process is a visual impression of the website's performance. That is to say, the response time. when the response time is small, the user experience will be relatively good. of course, the response time for the user experience includes personal subjective factors and objective response time. When developing and building websites, we need to consider how to better combine these two parts to achieve the best user experience. Users focus on the corresponding time of user operations. Second, we should consider the performance points that need to be concerned from the O & M perspective. Once again, we have to consider website performance from the perspective of developers (design. Finally, QA tests and feedbacks on our website performance. After the above description, we need to collect the throughput, concurrency, and response time of the system to test the system performance. The procedure is as follows:

  • Confirm the throughput, concurrency, and response time values.

  • Find or develop corresponding performance testing tools

  • Perform performance testing

  • Feedback results and submit test report

As a result, there are two ways to achieve our expected performance goals, so that we do not need to complete the performance optimization task to be handed over to O & M for release, but only need to perform relevant performance monitoring, it facilitates performance optimization after the product is launched. The other is that we have not achieved our expected goal. we need to find performance bottlenecks and optimize the performance.

3. Performance analysis

Through the above performance test, we found that the website did not meet our expected performance goal. what we need to do is to monitor the existing system (server), including hardware and software monitoring, it provides effective performance monitoring data for performance tuning. Next we will focus on what tools can be used to identify performance bottlenecks:


  • Use vmstat, sar, and iostat to detect CPU bottlenecks

  • Use free and vmstat to detect memory bottlenecks

  • Use iostat to check whether it is a disk I/O bottleneck

  • Use netstat to check whether it is a network bandwidth bottle

  • ......

Operating system:

  • Process

  • File system

  • SWAP partition

  • Kernel parameter adjustment

  • ......

Applications (MySQL, etc ):

  • Mysqlreport performance analysis report

  • Mysqlsla slow query log analysis

  • ......

4. Performance Tuning

  • Determine optimization objectives

  • Optimization steps

  • Detection optimization result

(1). determine the optimization target

The goal of our performance optimization is to improve the website performance by 10% or 20%. the boss cannot say that if you optimize the website performance for me today, you will be able to double the website performance. First, you need to ask him what we need to achieve. Next, let's take a look at the entire environment (architecture) including code (of course, you need to understand the business logic and the general understanding, which is certainly no harm). We have time to communicate with developers, it is important to ask how many pitfalls are required in the code. It is often better to query SQL statements in the code than how many days your system has been optimized. haha.

(2). specific optimization steps

  • If you do not understand system parameters, do not modify them at will. Otherwise, you will regret it.

  • Only one system resource is debugged at a time, such as CPU, memory, or disk.

  • We recommend that you modify the parameter values as few as possible for each change.

  • When analyzing a system resource, multiple tools often have unexpected results.

  • Better than others (I would rather do less than overhead it. if the performance has reached the requirement, do not do anything at will and do your monitoring well ).

(3). detection and optimization results

After each performance optimization, you must check the performance process. for example, the AB tool of the Web server is a good detection tool. after each optimization, you can see the specific changes.

5. performance monitoring

Performance monitoring is very important, including server performance monitoring and performance monitoring of specific services. The following describes the specific performance metrics:

(1). server performance monitoring

  • CPU usage

  • CPU load

  • Memory usage

  • Disk I/O

  • Network traffic

  • Disk space

  • System process

  • ......

(2). Service Performance Monitoring (MySQL)

  • MySQL Query throughput, including Change DB, Select, Insert, Update, and Delete

  • MySQL persistent connection utilization

  • MySQL queries cache space usage

  • MySQL Query cache hit rate

  • MySQL cache query count

  • MySQL index cache hit rate

  • MySQL index read statistics

  • MySQL connection throughput

  • MySQL connection cache hit rate

  • MySQL concurrent connections, including the maximum number of allowed connections, the actual maximum number of connections, the current number of connections, the number of active connections, and the number of cached connections

  • MySQL traffic statistics

  • MySQL table statistics lock

There are many specific monitoring tools, including open-source monitoring tools such as Cacti, Nagios, and Zabbix, as well as some paid monitoring tools such as "monitoring treasure.

VIII. Summary

In this "performance optimization overview" blog, I just want to explain the specific optimization ideas and help you understand performance optimization. This makes it easier for you to understand, let everyone know that performance optimization is not as difficult as it is to be done. as long as we have a good grasp of the method, we can solve any problem. However, the specific optimization process is not described in this blog. As for the specific optimization process, we will explain it in the following article. Okay, so much. I hope you will get something! ^_^ ......

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.