DevChunks

Use Benchmarking to Improve Performance

One of the best ways to check the performance of your MySQL application is to benchmark it regularly. Put the application under as much load as you can to simulate the worst possible reasonable load and check the performance.

Several functions within MySQL can help you to test your databases. One of the most useful of these is the Benchmark() function, which tests the time taken to perform an expression a defined number of times. The function calculates the time on the client, not the server, so the test should be run several times and the average taken.

{code type=php}
mysql> Benchmark(1000000, SELECT CURRENTDATE());
0
1 row in set (7.15 sec)
{/code}

MySQL also comes complete with a Benchmark suite that will tell a user what operations your SQL implementation performs well or badly. You can find this in the sql-bench folder of your MySQL source distribution. This benchmark will only run on a single thread so will only measure the minimum time for the operations to perform. The benchmark scripts are coded in Perl and that must be installed to run them.

To execute the benchmark tests, navigate to the sql-bench directory and run the run-all-tests script.

{code type=php}
shell> cd sql-bench
shell> perl run-all-tests –server=myserver
{/code}

Another useful script is crash-me, which attempts to determine the features that a database supports by running queries against it. The crash-me script can find out:

  • Supported data types
  • Number of supported indexes
  • Supported functions
  • Maximum query size
  • Maximum size of a VARCHAR column

For the maximum benefit, run these scripts on a regular basis and use the results to improve the performance of your database. When you find one bottleneck, replace that piece of code with dummy code that executes fast to find the next bottleneck. Once all the bottlenecks are identified, look to rewrite the code segment to improve performance.

Share:

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the