February 2012 Edition
A SQL Anywhere Performance Testing Example
Database applications rely on the database to perform processing and return results. These concepts are usually tied together, performance testing of the underlying database server is important to understand the usability characteristics of your database application. Usability of a database application is typically considered a qualitative evaluation more than a quantitative one, but the difficulty is addressing this observation without quantitative results. Quite often, performance testing is about addressing users’ expectations of how fast the software should work versus the perceived performance of the software in reality. Typically this expectation has been formed because the software used to work more quickly in older versions and now it has regressed in performance in a more recent version.
Due to this end-user expectation, the goal of database performance testing is typically to determine if there is a measurable performance difference between two software versions of the database server. Preferable times to perform this testing would be when you are upgrading major/minor versions of the database server (e.g. from SQL Anywhere 11 to version 12) or upgrading from an older EBF version level to newer EBF version. Performance testing also allows for the creation of a performance benchmark time that can be used as a standardized performance indicator when testing between different hardware configurations or different operating systems.
A well-defined performance suite should consider and control for as many performance factors as possible (making them constant across tests), while manipulating a single performance factor of interest for a single test. While these individualized testing conditions may not be necessarily duplicated in a production environment, these conditions may be required in order to perform the test in a deterministic way and to allow for comparisons across test runs. Making these testing factors constant is particularly important when running a test on the two database files built by two different versions of SQL Anywhere (e.g. version 11 and 12). Some common differences in databases from two versions would include the number of pages in the tables and indexes, the density of the indexes, the number of free pages, the overall size of the database file, and the contents of the database cache at the time of execution. All of these performance factors can affect query plans, and change the underlying performance of the database queries. For a more in-depth explanation of the various performance factors to consider in SQL Anywhere, please see our whitepaper ‘Capacity Planning with SQL Anywhere’
It is important to make sure that a sequence of events in the test suite is predictable – examples include making sure that each test begins with the same cache state and ensure there are no random events running in the background of the database server at any given time. Many automatic server events, as cache warming, cache resizing, executing the database cleaner, automatic view refreshes, backups or any other user defined event, can randomly impact performance and skew testing results.
In the default operation of the database server, the actual content of the cache at one particular time can be highly unpredictable and may not be reproduced from one test to another. For this reason, it is recommended to disable following cache events and user events using the following database server start-up switches:
- Disable cache warming ( -cr- )
Remove all table pages from cache:
Load all table pages for table T:
Load all index pages from index ‘xx’, on table T:
This will help guarantee that the content of the database server cache is determined before each test run.
Here is an example of a performance testing sequence:
• Start the server with fixed cache size , without cache warming/collecting, and disable events:
• Run “call sa_flush_cache()” right before the query is run or preload cache with specific pages
Performance Test Example
In our example we are going to simulate blocking with the following using SQL Anywhere 12 installed:
2. Open a Command Prompt/Terminal and create 2 subdirectories (SA11 and SA12) :
e.g Using Microsoft Windows 7 x64:
3. Create test.sql file in the same directory with sa_flush_cache() run first and then all other queries. E.g: 4. In the same directory, create a batch file Test.bat that will auto-start the database server, execute a query, exit, and shut the database server down. 5. Compare results side by side, e.g. 6. Check the total clock time of each query instance and total times across different test suite runs for any performance timing discrepancies of statistical significance.
3. Create test.sql file in the same directory with sa_flush_cache() run first and then all other queries. E.g:
4. In the same directory, create a batch file Test.bat that will auto-start the database server, execute a query, exit, and shut the database server down.
5. Compare results side by side, e.g.
6. Check the total clock time of each query instance and total times across different test suite runs for any performance timing discrepancies of statistical significance.