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- )
- Disable cache collection ( -cc- )
- Disable dynamic cache auto-sizing – e.g. set to a fixed cache size ‘1G’: ( -ca 0 -cl 1G -c 1G -ch 1G )
- Disable all user-defined events ( -hV ) *
- Disable the background database cleaner process (-hW EnableCleaner) *
*Note: This is an undocumented switch and should only be used for performance testing purposes. Do not use this switch in a production scenario!
Since the server’s cache contents can be variable from test to test, it is recommend to either empty cache completely by running “sa_flush_cache()” stored procedure (i.e. for ‘cold cache testing’) or preload it with specific table pages prior to running a test (i.e. ‘warm cache testing’). E.g.
Remove all table pages from cache:
Load all table pages for table T:
select count(*) from T with (no index)
Load all index pages from index ‘xx’, on table T:
select count(*) from T with ( index (xx) )
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:
dbsrv12 -cw- -cc- -ca 0 -cl 512M -c 512M -ch 512M -hV -hW EnableCleaner -n demo demo.db
• Run “call sa_flush_cache()” right before the query is run or preload cache with specific pages
• Run a test query through a query application, such as ‘fetchtst’ (example below)
• Compare timing results from the tests
Performance Test Example
1. Make sure that SQL Anywhere was installed with the optional ‘Samples’, and ‘fetchtst’ is available on your system (See here).For Linux/UNIX, the utility may need to be compiled first (see ‘readme.txt’ with the fetchtst utility).
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:
copy "%sqlanysamp11%\demo.*" .\SA11
copy "%sqlanysamp12%\demo.*" .\SA12
3. Create test.sql file in the same directory with sa_flush_cache() run first and then all other queries. E.g:
select * from Customers
select * from SalesOrders
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.
REM set path to include fetchtst
path="C:\Users\Public\Documents\SQL Anywhere 11\Samples\SQLAnywhere\PerformanceFetch";%path%
fetchtst -c "UID=dba;PWD=sql;ENG=Test11;ASTOP=YES;DBF=.\SA11\demo.db;START=dbsrv11.exe -cs -cv+ -c 512M -ca 0 -n Test11" test.sql > fetch_results-SA11.txt
fetchtst -c "UID=dba;PWD=sql;ENG=Test12;ASTOP=YES;DBF=.\SA12\demo.db;START=dbsrv12.exe -cs -cv+ -c 512M -ca 0 -n Test12" test.sql > fetch_results-SA12.txt
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.