Essential Performance Tips For MySQL

Posted on - Last Modified on

MySQL is an open source relational database and the chances are good you have come to rely on it. It is not the sort of program you can afford to run inefficiently. Your MySQL server might operate at high speed, consistently and stably if you eliminate various mistakes many database developers commit. Some of the structures in your configuration and workload may harbor such mistakes.

Are you stuck finding a good MySQL database developer? Visit freelancer.com for quality service at affordable rates.

The good news is that tuning and troubleshooting MySQL is simple, as most performance issues require similar solutions. Here are eight tips to help you upgrade MySQL to its top performance.

Workload profiling

Profiling the server’s workload helps you understand the server’s time expenditure mannerism, determining which queries are most expensive so you can fine tune them. Time is the most critical factor at this point as, when you submit a query, your greatest concern is the duration your server takes to respond.

You can profile the workload using various tools, including Percona Toolkit’s pt-query-digest, or query analyzer from MySQL Enterprise Monitor. The tools help you find areas you need to prioritize by bringing up tasks that are most time-consuming.

They achieve this by keeping records of each executed query, giving you detailed output of queries ranked from top to bottom, in response time order. This will give you a clear picture of groups and categories of queries, arranged in terms of frequency and speed of execution.

Comprehend the four fundamental resources

Any database server requires four basic resources if it has to function. They include memory, network, disk and a CPU, and they need to be strong and reliable. If any of these resources are weak, the database will yield poor performance. Understanding this will help you in two ways; troubleshooting problems and choosing hardware.

Make sure the hardware you purchase for your database has a top-performance guarantee. Most organizations go for the fastest disks and CPUs for their servers, but struggle with limited memory. It is important to strike a reasonable balance between these resources to enjoy great MySQL.

Some circumstances may call for memory to be added, which increases performance with minimal costs. This is often applicable for disk-bound workloads, as the server lacks adequate memory for storing working data.

Another resource lies in the CPU server application. Fast CPUs serve MySQL better, as it means a single thread is available for each query, without the necessity for running them parallel to each other in the CPU.

You need to pay close attention to each resource during troubleshooting, and establish whether each has poor performance or simply too much work to do. This will play a major role in effective troubleshooting, making the process fast and fruitful.

Avoid Using MySQL as a Queue

People find queue-like and real queue access patterns in their applications, without any apparent progress; for example, when you place an item's status so a different worker process has to claim it before any further action.It’s like marking your emails ‘unsent’ and later marking them ‘sent’ after sending them.

There are two main reasons why queues bring about problems. They result in workload serialization, which means you cannot execute tasks simultaneously, and they yield a table of historical data and work in progress. Both issues introduce extra MySQL load and application latency.

The cheapest results should appear first in your filter

If you set your database to first execute imprecise and cheap work and then move to the precise and hard work, you increase its level of optimization. Let’s say you want to find an object within a particular radius of a given location. The Haversine (great-circle) sphere’s surface distance computing formula is common for most toolboxes used by programmers.

This formula applies myriad trigonometric operations which have a big downside because of their high CPU levels. This kind of calculation results in excessive CPU use, making the server query executions even slower.

You need to pare your records down to a total subset, trimming the consequent set to an accurate circle, before executing the Haversine formula. An imprecise or precise square containing the circle will give you an easy way out. By doing so, you exclude all parts of the world located outside the square from going through the vigorous calculations, making the exercise lighter than it would otherwise be.

Understand two death traps of scalability

Most people believe scalability is a vague subject, but that is not true. Mathematical scalability definitions expressed by equations exist, explaining why various systems fail to rank as high as they ought to.

The Universal Scalability Law summarizes scalability issues in two basic costs; crosstalk and serialization. This principle plays a pivotal role in system analysis, with the aim of quantifying and expressing their scalability characteristics.

If simultaneous processes need to pause in order for serialized processes to run, they get limited scalability. If they run in parallel, but need to communicate with each other to run properly, they still have limited scalability. Avoiding crosstalk and serialization will help your application scale better. This translates into avoiding row exclusive locks in MySQL.

Do not give configuration too much attention

Spending too much time tweaking configurations may not yield significant results on DBAs, and may cause damage at times. Some servers might run out of memory, crash and ultimately perform poorly with a little more intense workload.

There is no need to configure everything, even if MySQL is badly outdated and has one-size-fits-none defaults. A wise decision would involve altering other settings after getting the basics right. Ten options set right will get your server to top performance in most instances.

Pagination queries

Applications of this nature cause collapsing in most servers. This is because they deploy an offset and LIMIT that give the server excessive work, then generate and discard rows repetitively.

The user interface gives options to optimize, allowing you to establish a barrier for people who want to go to pages that are not anywhere near the first page. Use an extra row instead of offset, and LIMIT for a query.

Save alert reluctantly, statistics eagerly

Alerting and monitoring are essential, but the latter generates false positives, forcing administrators to introduce email filtering with the aim of halting the noise. Sooner or later, your monitoring system becomes completely useless.

We can view monitoring in two different perspectives; alerting and capturing metrics. Someday, you might need to wrap your head around certain system changes and the captured metrics might come in handy. Alerting, on the other hand, is a bit tricky. There is no clear line between excessive and ‘just enough’ alerting. The best practice would be cutting down on alerting, focusing on issues that point to real and actionable problems.

For your MySQL server to serve your business in the most desirable way, you need to keep it on top of its game. This is only possible if you observe due diligence in its development, installation and maintenance. Most people pay little or no attention to these pivotal aspects of their database, because they think it’s a lot of work or just don't care. Those who follow these tips will reap the full benefits.

Did you find this article helpful? Please drop us your feedback in the comments section below.

 

Posted 14 November, 2017

TomCoulter

Designer // Writer // Creative

Tom is a Design Correspondent for Freelancer.com. He is currently based in Melbourne and spends most of his non-work moments trying to find the best coffee.

Next Article

What Is SQL?