Pretesting Preparation

 < Day Day Up > 

This section takes some time to review the important checkpoints you should complete before beginning your performance analysis.


Each component in your hardware environment plays an important role in the responsiveness of your MySQL solutions. Ideally, your production and performance testing environments will match as many of the following items as closely as possible:

  • Processors Given the rapid increases in processor speed, you are likely to receive very different performance numbers if your production machine and test machine contain processors manufactured just one year apart. The same type of erroneous outcome will occur if your production machine has four processors and your testing machine only has one. This type of CPU disparity really becomes a problem when setting engine parameters.

  • Memory A 10-year-old computer with ample memory often outperforms a 10-day-old machine configured with insufficient memory. Prior to initiating your testing, it's crucial to ensure that the memory profiles of the production and test platforms are as similar as possible. If someone else sets up your hardware for you, be certain to examine the platform's configuration before starting: Don't take anything for granted, especially memory capacity and speed.

  • Mass storage Disk drive capacity and speed dramatically improve each year. You have many more choices when implementing your mass storage architecture. However, these choices can introduce anomalies when it comes to performance testing. For example, imagine that you want to examine why bulk data operations take so long on your production server. You don't have enough disk capacity on your test platform to unload/reload your 100-GB production data set, so you purchase an inexpensive 200-GB external drive. You then attach this device to your test machine using USB 1.1. Unfortunately, USB 1.1 is orders-of-magnitude slower than either USB 2.0 or internal storage. This means that any disk-based performance numbers you receive will not be meaningful, and could lead you to make incorrect assumptions.

  • Attached devices Every attached device (for example, printers, plotters, scanners, terminals) can exact a performance penalty on your MySQL production server. The same holds true for your test environment. Be careful that your test platform isn't serving double, triple, or even quadruple duty as a print server, firewall, and so on. These extra responsibilities can render any performance information useless.

In an ideal world, your test system will be a mirror image of your production server. Unfortunately, most readers will be fortunate if they even have a test machine, let alone a dedicated performance analysis platform. If this is the challenge you face, it makes more sense to run your tests on the production platform during periods of low activity.

Regrettably, this is likely to translate into a series of ruined nights and weekends for you. Testing on the production systems is discussed in the "Organizational Support" section later in this chapter.


Many MySQL installations service a wide cross section of users, including those connected via a local area network (LAN), a corporate wide area network (WAN), and over the Internet. Accurate performance testing demands that you attempt to replicate the connectivity profiles of all your users.

For example, suppose that remote users are connecting to your application via a web browser over the Internet and complaining of performance bottlenecks. To further complicate things, only a certain percentage of all remote browser users have performance complaints; the majority of the user community has no objections.

Before you even begin testing, you should do all that you can to describe and simulate all facets of the user experience, especially the connectivity components. You might be surprised to learn that the users who complain the most about browser access suffer from external performance impediments beyond your control, such as firewall issues, slow dial-up lines, proxy server problems, and so on. In certain cases, the users might even be running old, underpowered, or badly configured client machines. MySQL tuning will have little impact on these types of predicaments. Knowing these handicaps in advance will save you a tremendous amount of time, as well as greatly reduce the risk of taking action based on incorrect analysis.


After you've obtained the right hardware and connectivity profile for your testing, the next step is to locate, install, and configure the correct software.

  • Database One of the most attractive aspects of MySQL's open source architecture is how frequently new releases and bug fixes are delivered to the marketplace. However, it's important to use the same version of MySQL in your test environment as in your production environment. MySQL releases can often change the underlying performance characteristics of an application as new features are provided and old features are made obsolete.

    For example, in versions prior to 4.1.4, launching the server with mysqld caused the engine to automatically start in debug mode. This consumed additional memory and CPU. From version 4.1.4 onward, the server now starts without debugging enabled; you use mysql-debug to launch the server with debugging turned on. This clearly has an impact on engine performance, and could skew your test results if your production and testing platforms differ. MySQL will also introduce new system and session variables, many of which might impact performance.

    Speaking of system and session variables, be certain that your production and test platforms match as closely as possible; a difference in even a single setting can render all of your testing irrelevant.

    Of course, after you have completed your performance testing, it is fine to upgrade your test server to a newer version of MySQL to see if this benefits responsiveness.

  • Application If you're using a packaged application (for example, enterprise software, web servers), try to have the same version of the software installed on your test platform. You should also apply whatever application patches are running in production onto your test server because these patches often affect responsiveness. Many enterprise software vendors allow you to run multiple instances of their products as long as the additional copies are not used for production. Try not to mix your development server with your test server: Development efforts can often interfere with scientific testing.

    Our earlier suggestion about keeping MySQL's settings consistent between production and test also applies here: Many application vendors offer numerous performance-related settings, which can introduce inconsistencies if they aren't kept in sync between the production and test environments.

  • Operating system MySQL's cross-platform portability is another benefit of its open source heritage. However, this portability can play havoc with determining the cause of performance issues. For example, assume that your production server is based on Windows Server 2003, whereas your testing server is running Linux. Given these differences, you can never be fully confident that the performance results you obtain from one platform will transfer to the other: There are obviously significant architectural and configuration differences between these operating systems that can affect MySQL application performance. The same holds true even for homogenous operating systems if they differ in installed patches or kernel modifications. To reduce the potential for confusion, try to have both platforms as similar as possible.


At this point, you have done your best to replicate your production hardware, software, and connectivity profiles onto a test platform. Your next task is to come up with a data set for examining during the testing process. The ideal scenario is to replicate the entire production database, including tables, storage engine selection, indexes, views, triggers, and stored procedures.

Using the production schema with a small data set is a common error during testing. If your test database is too small, you might get an incorrect view of your true performance profile: The most inefficient database operation will blaze through a tiny set of information, even if the underlying database itself is poorly designed. In addition, the MySQL query optimizer might generate different plans for two identical databases if the number of rows is vastly different between them.

Fortunately, it's easy to make a full copy of a database. Use MySQL's mysqldump utility to export the data from the production instance to the test instance. Of course, if you're only concerned with a performance problem that affects a small subset of your production tables, it's probably safe to replicate only that portion of your database. However, it's still essential to duplicate the indexes, views, and so on.

Your Test Plan

Putting together a structured test plan takes time and discipline. You might feel that this work is a distraction from your performance-tuning tasks. However, if you invest the effort up front, you will realize significant benefits through the entire testing cycle. Your test plan will give you a clear sense of direction. If you have a multiperson testing team, a test plan is essential to bypassing the typical duplication of work and confusion that plague group performance analysis projects. This test plan will also form the nucleus of your report after you finish your analysis.

Test plans don't need to be masterpieces of design that take weeks to implement: You only need a road map that shows the steps you plan to take when conducting your experiments, along with any external assistance you might need.

For example, look at these entries from a sample test plan:

 Test #:            203 Date:              5-October-2005 Start time:        11:14 Finish time:       11:19 Platform:          Production 2 Action:            Changed join syntax between 'customer' and 'problem' table Special needs:     None Test dependencies: 175, 182 Team members:      Bernard Bernbaum, Edward Dane Test status:       Complete Test results:      Query response improved 27% Test #:            204 Date:              6-October-2005 Start time:        09:29 Finish time:       09:47 Platform:          Testing 1 Action:            Raised 'sort_buffer_size' by 25%; reran marketing's problem                    'ORDER BY'  Special needs:     None Test dependencies: 201 Team members:      Tom Reagan Test status:       Complete Test results:      No impact on problem query Test #:            205 Date:              17-October-2005 Start time:        17:00 Finish time:       18:00 Platform:          Production 1 Action:            Create additional index on 'salesProbability' column; rerun                    problem query  Special needs:     May impact reports Test dependencies: <None> Team members:      Dale Levander, John Caspar Test status:       <not yet started> Test results:      <not completed> 

You can store your test plan in a spreadsheet or within a MySQL database for widespread access.

Change One Variable at a Time

The fastest way to an ulcer is to attempt to make sense of your results after changing two, three, or even more performance-related variables at one time. Your testing experience will be much more successful and pleasant if you methodically change one variable at a time, run your test(s), record results, and then move on to the next candidate.

For example, suppose that you are faced with a transaction that takes too long to complete. You suspect a number of problems, including the following:

  • Excessive indexing

  • Incorrect database memory parameters

  • An overloaded server

  • Suboptimal SQL

In your eagerness to fix all of these problems at once, you make radical alterations in each of the preceding areas. You are happy to see a dramatic boost in responsiveness of your slow transaction. Your glee is short-lived, however: Your changes have damaged performance for other types of operations. You must now go back and undo your changes, and determine which ones heightened performance and which ones caused collateral damage. It would have been far better to make one change and then measure its impact before proceeding.

Organizational Support

By this time, it's fairly safe to say that you've invested a significant amount of effort obtaining and configuring hardware, installing the correct software and patches, ensuring connectivity, and building your test plan. The last hurdle is often the most difficult to overcome: gaining sufficient operational support for your performance-tuning efforts.

Management might be reluctant to grant you adequate time and resources. Users might be unwilling to help isolate a system performance problem, even though their daily lives are negatively impacted by these types of issues. The original designers and developers of the MySQL-based solution might assemble roadblocks, fearing the ramifications of your report on their careers. These obstacles might be magnified if the solution was provided by a third-party vendor.

Your performance testing and tuning mission will fail without ample support from all of these communities. Before beginning your work, you have the right to ask for assistance in each of these areas:

  • User cooperation You will always get a more realistic picture of a performance problem by witnessing it in action: Lab replication only goes so far. Sitting next to a user who is dealing with live response problems might save you days of labor. Bring a stopwatch, listen, and watch carefully. After you have implemented your performance solution(s), you should also observe the user to see if it really made an impact. Sometimes, the mere act of watching a user work convinces them that you have performed magic and solved their problem, when, in fact, all you have done is listen to them.

  • System load and processing profile If your production environment supports thousands of worldwide users running an application during office hours from Monday through Friday, performance testing on a quiet Saturday morning cannot possibly replicate the real-world conditions. If at all possible, try to either replicate the system load during your testing or gain access to the production system during live activity hours.

  • Production system access In the absence of a similarly configured, dedicated test bed, you might have no alternative but to run your research on the production system. If management is unenthusiastic about granting you access to these resources, point out the continued productivity drain caused by these performance problems. The sooner you conduct your experiments, the sooner you will be able to make the necessary corrections and move on.

  • Peer support Chances are you will need the cooperation from your fellow IT professionals. Alas, this support is often withheld from people tasked with resolving performance problems. This is especially true if you are an external consultant who was engaged specifically for this job. It's an unfortunate fact that a significant percentage of these types of projects degenerate into blame-shifting and political quagmires.

    The only way for you to minimize these types of situations is to conduct yourself with a positive attitude and complete professionalism. Requirements often change from the time a system was designed to the current moment. Many times, these changes cause performance degradation that could not have been predicted. As the person tasked with fixing things, you are not trying to assign guilt or shame to anyone. Instead, you are simply trying to improve the working life of your customer: the user of the MySQL-based solution.

     < Day Day Up > 

    MySQL Database Design and Tuning
    MySQL Database Design and Tuning
    ISBN: 0672327651
    EAN: 2147483647
    Year: 2005
    Pages: 131 © 2008-2017.
    If you may any questions please contact us: