Everything about Web and Network Monitoring
Everything about Web and Network Monitoring
MySQL Benchmarking is a useful branch of database performance optimization. This article expects the readers to have a basic understanding of relational database systems like MySQL and administrative use of Linux. So why should database administrators run benchmark tests on their MySQL systems? The answer is simply to see if the designed system is able to withstand stress and meet performance goals in a simulated real time environment.
Before delving deep into the mechanics of MySQL benchmarking processes, it is important to understand what benchmarking is? In a nutshell, benchmarking is ‘the act of running a computer program, or operations, in order to assess the relative performance of an object, normally by running a number of standard tests and trails against it’.
There are CPU benchmarks to test hardware and computational power whereas software benchmarks test more conceptual elements. In our scenario, database management system benchmarking is a good example of software benchmarking. The purpose of this type of benchmarking is to measure the throughput and response times of database management systems. The end result of a benchmark test is a score. The score is used for comparison measures.
Database administrators usually fail to recognize the difference between benchmarking and stress testing. This is usually understandable as both of these terms entail similar goals, that is, to test a database’s capacity. However, there is a key distinction. Benchmarking gives results in the shape of numbers, which upon each benchmark test and server/system setting tweak, can change. This shows the database administrator whether the tweak failed or succeeded. Stress testing, on the other hand, is pushing the system to the extreme edge, in order to find the system’s limits.
Since the focus of our article is on MySQL benchmarking and not stress testing, the latter will not be discussed. So what are the performance factors that a database administrator should take into account while running benchmark tests on a MySQL server. The answer is: throughput, latency times and scalability. These three factors, when combined, represent the overall performance of the MySQL server.
After considering the above performance factors, it is worthwhile to run benchmark tests on a MySQL server to test the system. This is because benchmarking is a fantastic strategy for measuring performance. So how should one go about MySQL benchmarking? In order to achieve proper results, some rules are pointed out below.
Here are a few examples of bad benchmarking, and at all times should be avoided:
You cannot ignore the effects of a different network infrastructure.
Not configuring your server to the specific and unique business needs.
Running benchmark tests, disregarding any unwanted caching issues.
MySQL’s development website has documentation on utilizing the existing technologies built into MySQL for benchmarking. One such useful tool is the benchmark function.
As MySQL’s development team explains, the BENCHMARK () function is used to time how quickly MySQL processes the supplied expression. It can be used from within the MySQL client. The function is as shown below in more detail.
BENCHMARK (count, expr): The BENCHMARK () function executes the expression expr repeatedly count times. The result value is always 0. The most important number from the benchmark test is the time in seconds. An example is shown below.
There are some tricks involved in getting the right results for the BENCHMARK () function. These are highlighted below.
The guys at MySQL themselves recommend the use of a popular benchmarking tool called SysBench (built by a MySQL employee). The software can be accessed at: https://sysbench.sourceforge.net/
Let us do a walkthrough of what SysBench has to offer. SysBench is a comprehensive tool allowing database administrators to benchmark CPU, file IO and MySQL performance.
We will now look at CPU and MySQL performance benchmarking with SysBench.
The command for benchmarking the CPU performance is as shown below. The command starts a process and produce lots of numbers.
sysbench --test=cpu --cpu-max-prime=20000 run
However, the most important number is the total time (in seconds). This is the time it takes to compute the required computing parameter being tested. It is vital to compare the benchmarks across multiple systems to know the number’s worth.
SysBench can be used to measure MySQL performance. To do that, one could create a test table in the database ‘paid-monitor’ with 1,000,000 rows of data. This can be achieved by the following command.
sysbench --paid-monitor=oltp --oltp-table-size=1000000 --mysql-db=paid-monitor --mysql-user=root --mysql-password=yourrootsqlpassword prepare
Following the previous command, the MySQL benchmark commands should be entered as follows:
sysbench --paid-monitor=oltp --oltp-table-size=1000000 --mysql-db=paid-monitor –mysql-user=root –mysql-password=yourrootsqlpassword –max-time=60 –oltp-read-only=on –max-requests=0 –num-threads=8 run
After the benchmark results are displayed, the most important thing to look for is in the transaction column. This value is the number of transactions per second.
To clean up the system after all requisite benchmark tests have been run, it only takes a short command.
sysbench -- paid-monitor =oltp --mysql-db= paid-monitor --mysql-user=root --mysql-password=yourrootsqlpassword cleanup
Although there can be a dozen more types of benchmark tests for MySQL performance, the information above is enough for a primer.
In conclusion it can be clearly said that MySQL performance benchmarking varies for each and every organisation. This is because of the varying demands, time frames and goals. However, regardless of this, it is necessary to run benchmark tests on MySQL systems when the technology itself is vital to the day to day functioning of the business.
