As the load and file size grow, performance tends to decrease. Remember the following 7 key points to keep your MySQL running smoothly.
Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.
One of the ways to measure an application is to measure its performance. User experience is a measure of application performance, which means whether users can get the content they need in a reasonable time.
Many studies have shown that performance has a great impact on user behavior:
79% of users said it is unlikely to open a slow website again;
47% of users expect the web page to load within 2 seconds;
40% of users said that if the loading time exceeds three seconds, they will abandon this website;
A one-second delay in page load time may result in a 7% conversion loss and a 11% reduction in page views.
Regardless of the standard, it is very necessary to maintain good
application performance. Otherwise, the user will complain (or go to another application). A major factor affecting application performance is database performance. The interaction between applications, websites, and databases is critical to application performance.
The core part of this interaction is how the application queries the database and how the database responds to requests. No matter from which aspect, MySQL is one of the most popular database management systems. Many companies are using MySQL (and other open source databases) as a database solution in their production environment.
There are many ways to configure MySQL to help ensure that your database responds quickly to queries and reduce application performance degradation.
The following are some important tips to help you optimize the performance of the MySQL database.
MySQL optimization key 1: understand how to use EXPLAIN
For the database, the two most important decisions you make are: 1. How to map the relationship between application entities to tables (database schema); 2. How to design the application in the required format (query) The required data.
Complex applications may have complex queries and patterns. If you want the performance and scalability required by your application, you can't just intuitively understand how the query is executed.
You should learn how to use the EXPLAIN command. This command shows you how the query should be executed, and gives you insight into the performance that can be expected and how the query scales as the data size changes.
Tools similar to MySQL Workbench can display EXPLAIN output for you, but you still need to learn the basics to understand it.
The EXPLAIN command provides output in two different formats: the old table format and the more modern structured JSON document, the latter can provide more details (as shown below):
7 tricks to help you easily improve MySQL performance.
For a component, the "query cost" should be concerned. Query cost means that based on many different factors, the overall cost of query execution by MySQL takes into account the cost of that particular query.
The query cost of a simple query is usually less than 1,000. Queries that cost between 1,000 and 100,000 are considered medium-cost queries, and if you only run hundreds of these queries per second (instead of tens of thousands), they are usually considered fast.
More than 100,000 queries are considered high-cost queries. Usually, when you are a single user on the system, these queries still run very fast, but you must take into account the frequency of using these queries in interactive applications (especially as the number of users grows).
Although these are some rough figures, they show general principles. The architecture and configuration may affect the system's processing query workload.
The main factor in determining the cost of a query is whether the query uses the correct index. The EXPLAIN command can tell you whether to use an index for the query. This is why it is important to learn to use EXPLAIN.
MySQL optimization key 2: create the correct index
Index can reduce the amount of data that the query must scan to improve query performance. Indexes in MySQL are used to accelerate access in the database and help implement database constraints (such as UNIQUE and FOREIGN KEY).
Database index is much like book index. They are kept in their own location and contain information already in the main database. They are a reference method where the data is located. The index does not change any data in the database, but points to the location of the data.
In the system running queries, you should always check the index.
A missing index may also slow down the speed of the database. But do you want to add unnecessary indexes! Unnecessary indexes will slow down the running speed of the database.
MySQL optimization key 3: Do not use the default mode!
Like any software, MySQL has many configurable settings that can be used to modify behavior. But the administrator ignores many configurable settings and always runs in the default mode.
In order to get the best performance of MySQL, it is very important to understand the configurable settings, and more importantly, set them to the most suitable database environment for you.
By default, MySQL is suitable for small-scale development and installation, not for production scale. You usually configure MySQL to use all the memory resources available and allow the number of connections required by the application.
There are three MySQL performance tuning settings:
Innodb_buffer_pool_size: The buffer pool is where data and indexes are cached. This is the main reason for using a system with a large amount of RAM as a database server. If you only run the InnoDB storage engine, usually about 80% of the memory is allocated for the buffer pool. If you run very complex queries, have a large number of parallel database connections, or have a large number of tables, you may need to lower this value by one level to allocate more memory for other runs.
When you set the size of the InnoDB buffer pool, do not set it too large or it will cause swapping. This will definitely destroy database performance. A simple way to check is to look at the exchange activity in the overview diagram of the Percona monitoring and management system:
7 tricks to help you easily improve MySQL performance.
As shown in the picture, some exchanges are very frequent. If you see continuous swap activity of 1MB or more per second, then you will need to reduce the buffer pool size (or other memory usage).
If the correct value of innodb_buffer_pool_size is not obtained the first time, don't worry. Starting from MySQL 5.7, the size of the InnoDB buffer pool can be changed dynamically without restarting the database server.
Innodb_log_file_size: This is the size of a single InnoDB log file. By default, InnoDB uses two values so that you can double this number to get the size of the circular redo log space to ensure that the transaction runs persistently. This also optimizes application changes to the database. Setting innodb_log_file_size is a trade-off. The larger the redo space allocated, the better the performance of write-intensive workloads. However, if the system encounters power loss or other problems, the recovery time will be longer.
How do you know that MySQL performance is limited by the current InnoDB log file size? You can judge by viewing the actual redo log space used. The easiest way is to view the Percona Monitoring and Management InnoDB Metrics dashboard. In the figure below, the InnoDB log file size is not large enough because the used space is very close to the available redo log space (indicated by the red line). The log file size should be at least 20% larger than the space used to maintain optimal system performance.
7 tricks to help you easily improve MySQL performance.
Max_connections: Large applications usually require much more connections than the default. Unlike other variables, if not set correctly, there will be no performance problems (essentially). Conversely, if the number of connections is not enough to meet the needs of the application, then your application will not be able to connect to the database (this looks like a downtime to the user). It is very important to obtain this regime variable.
In a complex application with many components running on multiple servers, it may be difficult to know how many connections are required. Fortunately, MySQL can easily see how many connections are used during peak operations. Generally, to ensure that the maximum number of available connections used by the application is at least 30% larger than the maximum number of connections. An easy way to view these numbers is to use the MySQL connection diagram in the MySQL overview dashboard monitored and managed by Percona. The figure below shows a healthy system with many additional connections available.
7 tricks to help you easily improve MySQL performance.
One thing to keep in mind is that if your database is running slowly, the application usually creates too many connections. In this case, you should deal with database performance issues instead of simply allowing more connections. Too many connections may make the underlying performance problem worse.
(Note: When you set the max_connections variable to be significantly higher than the default value, you usually need to consider adding other parameters, such as the size of the table cache and the number of open files allowed by MySQL)
MySQL optimization key 4: save the database in memory
In recent years, we have seen the transformation of solid state drives (SSD). Even though SSDs are much faster than spinning hard drives, they are still incompatible with the data in RAM. The difference comes not only from storage performance itself, but also from other tasks that the database must perform when retrieving data from disk or SSD storage.
With the improvement of hardware, whether you are running in the cloud or managing your own hardware, it is increasingly possible to store your database in memory -.
The better news is that you don’t need to load all databases into memory, just put the frequently accessed working data collections into memory.
Check the number of I/O that the database is running in a steady state (usually a few hours after startup). In the picture below you can monitor and manage InnoDBI/O on the InnoDB Metrics dashboard in Percona.
7 tricks to help you easily improve MySQL performance.
In the image above, you can see a peak of 2000 I/O per second, which indicates (at least for some parts of the workload) that the database working set does not match the memory.
