HomeMySQL Page 3 - MySQL Benchmarking Concepts and Strategies
Benchmarking Concepts - MySQL
MySQL is the world’s most popular open source relational database management system (RDBMS). As a result, over ten million installations are spread around the globe. Nevertheless, in reality only a small percentage of those are actually high performance, optimized, and tuned servers. This four-part article series targets the MySQL database and system administrators, covering various strategies to help benchmark and optimize databases, and tune servers to yield an outstanding performance.
On the previous page we mentioned the so-called "trio" of performance factors. Throughput is, by definition, the measure of the amount of data transferred in a specific amount of time. In our case, this refers to transactions; specifically the throughput of client-server connections of the MySQL server. We need to realize that simultaneous transactions are an absolute necessity to avoid starvation.
Starvation is a DBA technical term that means one of the users is waiting for his or her request to be served. This wait needs to be as low as possible to avoid starvation altogether. But when this delay gets quite large, we call the situation starvation. The user is starving for data and the server is unable to respond.
An extension of the previous performance factor is latency/ response times. Slow response can happen due to the overall high load of the server. When this happens, the server simply tries to respond instantaneously, but it takes a while for the results to be given to the user. This can happen due to unnecessary DNS resolutions, lags throughout the network infrastructure (too many uncontrolled hops?), and then, of course, the most obvious reason: not enough query cache. Increasing its size can help.
The third and last factor is scalability. Perhaps for some, taking scalability into consideration is unnecessary, but in the real world this factor should never be neglected. There is always room for improvement, and capacity planning is something that no business should ever ignore. This is where we benchmark how well our infrastructure scales.
For example, how does adding new hardware change the performance of our server? What would happen if our database size is doubled, or even increases tenfold? The possibility that the number of queries and simultaneous connections also increases shouldn't be excluded either. What happens if the count of transactions becomes eight times as much as it is now? Does adding more memory alleviate the problem?
As you can see, each of these factors should be taken seriously. And yes, the last factor mentioned is akin to stress-testing, but usually scalability testing shouldn't be taken that far. During stress-testing we want to find the extremes of our current server configuration. During scalability testing we consider possible real-world situations (i.e.; database size is doubled) and then run benchmarks on them.
Moreover, we need to understand that benchmarking is a fantastic strategy for measuring performance, but can often be very misleading, especially if the benchmarks aren't run accurately. The input data with which benchmarking utilities are being fed should always be checked multiple times and multiple runs (at least five times) of each benchmark must be executed; averaging is necessary after completing these runs.
Anybody that's already familiar with benchmarking knows that the outcomes of benchmarking applications are very sensitive. There is nothing more dangerous than assuming your benchmarked score is accurate after one run. After playing around a while, you will realize that your results are frequently influenced by something that you may or may not know. Oh, and thankfully, query caching does happen. It's recommended that you restart your MySQL server to eliminate the unwanted caching factors.
Beginners sometimes fall into the trap of positively surprising results. These happen when, for whatever reason(s), your benchmarking tool runs multiple times faster than usual, giving you an unrealistic outcome. For example, you have run the benchmark five times, and four of those times the results were very similar, but only one time they are stupendously different. In that case, it's advisable to simply throw out all of your results and re-bench.
Benchmarking databases in professional environments should never be taken as a competition. Assuring heightened, optimized, and scalable performance of servers is not a joke; these tasks should be deployed with a great deal of dedication and interest. Therefore, don't fall into any traps or fool yourself.
Now that we're talking about the causes of poor benchmarking, let's name a few others: always running benchmarks from the single host, especially if it's run from the server system (this ignores the effects of network infrastructure); using the default settings of the MySQL server (not configuring your server to the specific unique needs of the company or corporation is a mistake); hesitating to eliminate caching artifacts; and there are others.