![]() The problem with this technique is that the formula requires a lot of trigonometric operations, which are very CPU-intensive. ![]() The first tool in many programmers’ toolbox is the great-circle (Haversine) formula for computing distance along the surface of a sphere. 4: Filter results by cheapest firstĪ great way to optimize MySQL is to do cheap, imprecise work first, then the hard, precise work on the smaller, resulting set of data.įor example, suppose you’re looking for something within a given radius of a geographical point. Both add latency to the application and load to MySQL. Queues cause problems for two major reasons: They serialize your workload, preventing tasks from being done in parallel, and they often result in a table that contains work in process as well as historical data from jobs that were processed long ago. Marking emails as unsent, sending them, then marking them as sent is a common example. For example, if you set the status of an item so that a particular worker process can claim it before acting on it, then you’re unwittingly creating a queue. Queues and queue-like access patterns can sneak into your application without your knowing it. This knowledge can help solve problems quickly. ![]() When it comes to troubleshooting, check the performance and utilization of all four resources, with a careful eye toward determining whether they are performing poorly or are simply being asked to do too much work. In most cases, MySQL will perform well with fast CPUs because each query runs in a single thread and can’t be parallelized across CPUs. This might seem counterintuitive, but in many cases disks are overutilized because there isn’t enough memory to hold the server’s working set of data.Īnother good example of this balance pertains to CPUs. In some cases, adding memory is a cheap way of increasing performance by orders of magnitude, especially on workloads that are disk-bound. Often, organizations will select servers with fast CPUs and disks but that are starved for memory. Just as important, balance them reasonably well against each other. When choosing hardware for MySQL, ensure good-performing components all around. Understanding the fundamental resources is important in two particular areas: choosing hardware and troubleshooting problems. If any of these is weak, erratic, or overloaded, then the database server is very likely to perform poorly. To function, a database server needs four fundamental resources: CPU, memory, disk, and network. 2: Understand the four fundamental resources Workload-profiling tools group similar queries together, allowing you to see the queries that are slow, as well as the queries that are fast but executed many times. These tools capture queries the server executes and return a table of tasks sorted by decreasing order of response time, instantly bubbling up the most expensive and time-consuming tasks to the top so that you can see where to focus your efforts. The best way to profile your workload is with a tool such as MySQL Enterprise Monitor’s query analyzer or the pt-query-digest from the Percona Toolkit. Here, time is the most important metric because when you issue a query against the server, you care very little about anything except how quickly it completes. By profiling your workload, you can expose the most expensive queries for further tuning. The best way to understand how your server spends its time is to profile the server’s workload. Here are 10 tips for getting great performance out of MySQL. Luckily, many MySQL performance issues turn out to have similar solutions, making troubleshooting and tuning MySQL a manageable task. To ensure your MySQL server hums along at top speed, providing stable and consistent performance, it is important to eliminate these mistakes, which are often obscured by some subtlety in your workload or a configuration trap. The truth is, common mistakes underlie most MySQL performance problems. As with all relational databases, MySQL can prove to be a complicated beast, one that can crawl to a halt at a moment’s notice, leaving your applications in the lurch and your business on the line.
0 Comments
Leave a Reply. |