PGSQL - PosgreSQL Database Appliance - Test Plan

Preparation

Host platform

These tests must be launched from a remote host, that has an SSH access to the AppLogic controller.

Tools

All necessary tools are supplied within the test suite. You need SSH installed and ssh keyfile for your AppLogic grid to perform the tests.

pg_regress

source: http://ftp8.us.postgresql.org/postgresql/v8.2.9/postgresql-8.2.9.tar.bz2

RPM: http://ftp8.us.postgresql.org/postgresql/binary/v8.2.9/linux/rpms/redhat/rhel-5-i386/postgresql-test-8.2.9-1PGDG.rhel5.i386.rpm

pgbench

source: http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/pgbench
(available in source code only, use full Postgres sources to build; postgresql-8.2.9/contrib/pgbench/)

Tests Summary

Configuration Tests

• Configuration propagation test: verify the PostgreSQL conf file is updated according to PGSQL configuration
• Database auto-creation test: verify start with an existing database, empty volume, maintenance mode
• Read-only database test: verify that the proper optimizations are accounted for
• Connection limit test: max connections, able to serve specified connections
• Memory management test: test simple and complex query configurations with different memory configurations
• Sync on write test: flush data to disk on database updates
• Auto-vacuum test: test that the vacuum is executed for non-read-only databases
• Client connection failure test: verifies that PGSQL can be accessed only through the IN terminal

Logging Tests

• Log file and log level tests: verifies log path, log levels, log lifecycle

Resource Tests

• CPU/Memory resource tests: tests PGSQL performance under various CPU/Memory configurations.

Endurance Tests

• Start-Stop test of the PGSQL appliance: verifies that appliance can be started and shut down multiple times
• Parallel regression test: performs parallel regression test with pg_regress
• Serial regression test: performs serial regression test with pg_regress

Benchmarks

• Benchmark: verifies the maximum PGSQL performance under various CPU/Memory/Bandwidth resources available

Running the Tests

To run the tests on AppLogic2.AppLogic grid, copy and un-compress the tests archive file to a directory of your choice. Launch the test suite running the pgsql-test.sh file. Test suite will import the test application, or prompt you to use an existing, created previously, one.

Example test aplication:

If you choose to create a new test application, the test suite will import a new application and configure it, assuming that the PGSQL instance that you want to test is located in the /proto/ catalog and in named PGSQL. If you want to test another instance of PGSQL, you can either copy it to the /proto/ catalog or open the test suite application in AppLogic GUI and manually drag the necessary PGSQL instance over an existing one.

Design

Structure

• IN gateway used to access MON statistics
• LINUX5 appliance used as a client for PosgtreSQL?
• NAS appliance used for log storage
• MON appliance used for monitoring statistics

The test script is fully automated and includes the ability to install the necessary test software on the LINUX5 instance. The user does not need to install any software on any of the appliances; the test script takes care of everything.

Test Details

• For successful operation, test suite must be the only application running on the grid, or some tests, mainly performance ones, may fail.

• Some tests, especially resource-intensive ones, may fail if you do not have enough resources available. This is not a PGSQL fault.

• Most of the tests should not be interrupted by a user. If it happened that you had to interrupt the test, stop the test application with "application stop" before proceeding to the next test.

Configuration propagation test

Reconfigures PGSQL with different settings and verifies that all settings in PosgtreSQL? configuration file are updated accordingly

Test changes multiple properties of PGSQL, launches the application, verifies successful changes in PostgreSQL? config file and verifies that PGSQL is operational with these settings. Every possible value of every property is tested.

Database auto-creation test

Verifies database auto-creation, maintenance mode and starting with existing database

Test first starts the PGSQL with empty volume formatted with ext3, checks for successful new database creation and verifies PGSQL functionality. After that, test starts PGSQL with the same database volume and verifies that the existing database is used. For the last step, test launches PGSQL with unformatted raw data folume and verifies that PGSQL starts in maintenance mode.

Read-only database test

Verifies proper operation while using read-only database

Test configures PGSQL for read-only database and verifies that auto-vacuum and statistics collection is disabled.

Connection limit test

Verifies connection enforcement

Test configures PGSQL to support a maximum of 20 simultaneous connections and verifies that connections limit is enforced. pgbench test with less than 20 clients must succeed, and the one with more than 20 - fail.

Memory management test

Tests PGSQL configuration under various memory resources available

Test detects the amount of free memory available on the grid where the test application is running, and verifies proper updates to the work_mem, shared_buffers, effective_cache_size and maintenance_work_mem values with simple and complex query_complexity values. PGSQL is tested with 128Mb, 192Mb, 256Mb, 384Mb, 512Mb, 1Gb, 1.5Gb, 2Gb, 3Gb, 4Gb, 6Gb, 8Gb, 16Gb and 32Gb of memory, subject to available free memory on the grid. Any mismatches between reported property values and reference values are reported.

This test may fail at some point when there are not enough free resources available. This is not a PGSQL fault.

Sync on write test

Verifies PGSQL performance with fsync on and off

Calculates and reports PGSQL performance with sync_on_write on and off.

Log file and log level tests

Verifies PGSQL operations with different log levels and log aging settings

This test verifies all aspects of log management and statistics reporting:

• no log file is created when log_filename is empty, log_level set to none, log terminal is not connected or NAS appliance is not accessible.
• log file is created with the name and path specified in log_filename
• various log levels report appropriate messages to the log file
• log aging is working - log_size and log_age properties
• statistics is collected when log_smd_stats is enabled
• no statistics is collected for read-only database and when log_cmd_stats is set to no

Auto-vacuum test

Verifies that auto-vacuum is performed

This test verifies that auto-vacuum is performed. To test this, a regression tests are run on a newly created database, and verifies the last_autovacuum field of the PostgreSQL? tables.

CPU/Memory resource tests

Tests PGSQL performance under various memory, cpu and bandwidth resources available

This test detects the amount of memory, cpu and bandwidth resources available on the grid, and tests the PGSQL performance with all possible combinations to the limits imposed by resource availability. PGSQL is tested with the following settings:

• Memory: 128Mb, 192Mb, 256Mb, 384Mb, 512Mb, 1024Mb, 1536Mb, 2048Mb, 3072Mb, 4096Mb, 6144Mb, 8192Mb, 16384Mb, 32768Mb
• CPU: 0.10, 0.20, 0.40, 0.50, 1, 1.5, 2, 4, 8, 10, 12, 16
• Bandwidth: 1Mbit, 10Mbits, 100MBits, 200MBits, 500MBits, 1Gbit, 1.5GBits, 2Gbits
• 10 clients, 100 transactions

Client LINUX5 appliance is configured with 512Mb of memory, 1 cpu and 500Mbit of bandwidth for this test. This test may fail if there are not enough free resources available on the grid to run all required appliances.

Client connection failure test

Verifies that PGSQL is accessbile only through the "in" terminal

This test tries to connect to PGSQL through other than "in" terminals. All attempts must fail.

Benchmark

Verifies maximum PGSQL performance

For this test PGSQL is configured with 2 cpus, 500Mbits of bandwidth and 2Gb of memory, and optimized settings (sync_on_write=no, query_complexity=complex, log_filename="", log_level=none, log_cmd_stats=no). Then the pgbench test is used to verify PGSQL performance with 1,10,100 clients and 100,1000 queries.

This test may fail at some point when there are not enough free resources available. This is not a PGSQL fault.

Start-Stop test of the PGSQL appliance

Verifies that PGSQL could be safely started and stopped multiple times.

This test verifies that the PGSQL can be successfully started and stopped multiple times. It is an important test that verifies that all log rotation is performed properly. Amount of start/stop cycles may be specified and be unlimited (in this case the test can be interrupted using control-c).

Parallel regression test

Executes pg_regress regression test in parallel mode with 90 simultaneous connections

pg_regress is a standard test for PostgreSQL? that verifies all aspects of database server functionality.

Serial regression test

Executes pg_regress regression test in serial mode with a single connection.

Tests 1-10

Run the most important tests (1-10) in automated mode without any details except success/failure messages

-- EricT - 10 Mar 2006