10 tips for improving MySQL baccarat performance Baijiale

From a workload analysis to an index of three rules, these expert insights will definitely make your MySQL server scream.

In all relational databases, MySQL has been proven to be a complete beast, as long as the notification stops running it will never allow you to wait a second, put your application in a dilemma, and your work will be exposed to great risk.

But the truth is that normal errors are within range of MySQL performance errors. So in order for your MySQL server to run at high speed and provide a stable and continuous service, it is very necessary to eliminate these errors, but this can often be obscured by your busy work or configuration traps.

Fortunately, many MySQL performance problems have a similar solution, find and solve the problem, and then your MySQL use more handy.

Next, share with you 10 tips for improving MySQL performance.

MySQL performance boost Tip # 1: Configure your work

The best way to understand how your server is going to spend time is to configure the server's work. By configuring your server, you can expose the most expensive query to prepare for future tuning. From this point of view, time is the most important measure, because when you start a query on your server, you don't care about anything else but the completion of many blocks.

The best solution for configuring your working file is MySQL Enterprise Monitor's Query Analyzer or Percona Toolkit's pt-query-digest. These tools can help you capture the queries your server is performing and return a task table sorted in descending order of response time, and it will continually update the most expensive and costly tasks at the top, so you can see where your energy should be more focused.

The Work File Configuration tool places similar queries in a group that you can easily view at low speeds or tell a run but multiple queries.

MySQL Performance improvement Tip 2: In-depth understanding of four basic resources

A database server requires the following 4 resources to function properly: CPU, memory, hard disk, and network. If any of these performance is inadequate, poorly functioning, or overloaded, the database server is likely to perform poorly.

Understanding the underlying resources is very important to the following two levels: Select hardware and troubleshooting questions.

When selecting hardware for MySQL, make sure that all the components are performing well. It is also important to put them in a reasonable configuration. Most of the time, some agencies choose high-speed CPUs and hard drives, but they usually don't have enough memory. In some cases, increasing memory by order of magnitude is the cheapest way to improve performance, especially if the workload is bound to disk. This may sound counterintuitive, but in many cases the hard disk is overused because there is not enough memory to store the data working set.

Another example of a balance is CPU. In most cases, MySQL uses high-speed CPUs to work well, because each query runs in a single thread and cannot be parallelized between CPUs.

When it comes to troubleshooting, be aware of the performance and usage of all resources, and use your cautious gaze to determine whether they are inherently poor or because they carry too many tasks. This posture should allow you to solve the problem faster.

MySQL Performance improvement Tip 3: Don't do it. MySQL as a queue to make

Queue and queue access patterns can sneak into your app without your awareness. For a chestnut, if you set the state of an item so that a particular worker process can declare it before calling it, you inadvertently create a queue. Marking messages as unsent, sending them, and then marking them as sent is a good understanding of chestnuts.

There are 2 main reasons why queues can cause problems: they run your work continuously, preventing them from being parallel, so this usually results in a table that contains historical data about the work in the process and the work that has been done a long time ago. This will not only delay your application but will also add unnecessary load to MySQL.

MySQL Performance improvement Tip 4: the least expensive results are filtered first

The best way to optimize MySQL performance is to do cheap, uncertain work first, and then complete the hard, accurate work in the smallest result data set.

For example, you want to find what you want through a given geographic radius. In most programmers ' toolboxes, the first thing they'll think about is a big circle formula (Haversine) that calculates the distance on the sphere. But the problem with this formula is that there may be a lot of trigonometric calculations, which are very high on the CPU. Large circle calculations tend to run slowly, making the machine's CPU utilization soar.

Before you start applying the big circle formula, reduce your records to the smallest subset in the set and combine the result sets into an exact circle. A square that contains a circle (exact or imprecise) is the simplest way to solve the problem. In this way, everything outside the square does not come back to these costly trigonometric functions.

MySQL Performance boost Tip 5: Learn about two types of scalable death traps

Scalability is not as unpredictable as you might think. In fact, there is a very clear definition of scalability as an equation in mathematics. These equations highlight why the system does not scale as well as expected.

See General Extensibility (Universal Scalability Law)-a very clear explanation and quantification of the scalability characteristics of a system. It illustrates scalability issues from two basic cost aspects: serialization (serialization) and Crosstalk (crosstalk).

Multiple processes must stop working for serialization that has inherent limitations on scalability. Similarly, if multiple processes must communicate with each other at all times to match their work, they are restricting each other.

Avoid serialization and crosstalk, and your application scalability will be greatly improved. So what does that mean for MySQL? Depending on the situation, some examples can avoid row locking. With regard to queues, see Tip 3, which tends to get worse because of queue scalability.

MySQL Performance boost Tip 6: Don't focus too much on configuration

DBAs often spend a lot of time tuning configurations. The result is sometimes hurt rather than big ascension. I've seen a lot of the most optimized servers that crash, get out of memory, and behave poorly when the workload is a little bit more.

The default configurations that are installed on MySQL are one-size-fits-all and are heavily outdated, but they do not need to be fully reconfigured. As long as the most basic settings are correct, if necessary, then make a small adjustment. In most cases, you can get 95% of the server's peak performance by properly setting about 10 options. Other situations where this method cannot be applied should be a very special case, so you don't have to worry about him.

In most cases, the server "transform" tool is not recommended because they often have rules that do not apply in specific situations. Some are even dangerous and inaccurate coding-such as cache hit ratio and memory consumption formulas. These are all wrong, and they become even more wrong with the progress of the Times.

MySQL Performance improvement Tips 7: Be careful about paging

Paging applications often paralyze the server. In the page that shows you the results, there are links to the next page, which are usually not sorted in an indexed way, and then they use a limit and offset to make the server do a lot of work, and then discard the rows.

Optimization options are often found in the user interface itself. Instead of showing the exact page results and the individual links for each page, just show the next column of links. You can also prevent people from turning to pages that are too far behind.

From a question point of view, you can select a row more than you want, and then when you click on the "Next" link, you can specify the last line as the starting point for the next set of results instead of using the LIMIT with offset. For a chestnut, when the user looks at line 101th in row 120, you will select the 121th line at the same time; To submit the next page, you can ask the server 121th line or more than 121 lines, limited to 21.

MySQL Performance improvement Tip 8: Save data in a timely manner, caution

Regulation and early warning are essential, but what about the typical surveillance system? It starts sending some wrong gestures, and then the system administrator sets up the spam filtering rules to stop these hassles. And soon your regulatory system will be completely paralyzed.

I tend to look at regulation in two ways, get indicators and alert. It's important to get and save the metrics as much as possible, because you'll be glad you saved them when you want to know what the system has changed. One day there will be a strange mistake, and then you will be glad you have the ability to point out a section of the server's workload and then show the change.

By contrast, warnings can be a bit more. People often warn about cache hit ratios or tables created every second in a short period of time. The problem is that there is no appropriate threshold for this cache hit rate. The correct thresholds do not change with the server, but are different every one hours as your workload varies.

This leads to a warning that can only be done in moderation and only when it is indicative of a specific, actionable problem. A low cache hit ratio is not an operational issue, and he does not point to a real problem, but a server that does not respond to connection attempts is the problem that really requires an outcome.

MySQL Performance improvement Tip 9: Learn the three rules of Index

Index may be the most difficult concept for database summarization because it's easy to be confused about how index works and how the server uses them. It's really going to take some effort to really understand what's going on.

After the index has been properly designed, the following three services are provided primarily in the database server:

1. Index lets the server find a collection of adjacent rows instead of individual rows. Many people may think of index as a way to find individual rows, but finding a separate line causes confusing hard disk operations and slows down. And it's much easier to find a collection of rows, and all or baccarat tricks say most are much more interesting than just finding one line at a time.

2. Index eliminates the process of finishing by arranging according to Reading preferences. Finishing is a huge expense. It is also more efficient to read according to your preferences.

3. Index completely satisfies the query of the server, there is no need to connect the table. This is a well-known overwrite index or index-only query.

If you can define your own index and ask to take advantage of these three opportunities, you can make queries a few orders of magnitude faster.

MySQL performance boost Tip # 10: Leverage your peers ' expertise

Don't take a risk alone. If you're upset about a problem and are doing something logical and isolated for you, that's good. This may be valid 19 times in 20 times. But the remaining 1 times, you may fall into the rabbit hole, it will be very time-consuming and laborious, it is entirely because the effort you are making now just seems to be meaningful.

Build a MySQL-related resource network that goes beyond tools and troubleshooting guidelines. There are some very knowledgeable people lurking in mailing lists, forums, quiz sites, and so on. Meetings, exhibitions and local user groups offer valuable opportunities to connect with those who help you in critical moments.

If you want to find tools to practice the above techniques, you can look at Percona Configuration Wizard for MySQL, Percona Query Advisor for MySQL, and Percona monitoring Plugins. (You need to register for a Percona account to enter the first two links free of charge.) The configuration Wizard generates a benchmark MY.CNF file for the new server, which is superior to the sample files on the server. Query Advisor parses your SQL query to detect potentially bad patterns, such as paged queries (Tip 7). Percona monitoring Plugins is a collection of regulatory and graphical drawing plugins that help you keep your data up-to-date and alert (Tip 8). All these tools are free of charge Oh.

10 tips for improving MySQL baccarat performance Baijiale