Load testing scenario : inserting COOL data

This test consists of inserting data into the database with the COOL package. The metric measured is the number of objects inserted per second. To reproduce this test, you should follow the steps described in this document.

Requirements

The framework is coupled to several tools available at CERN. You will need to check if everything is set up correctly.

• Every client machine should have an AFS access (You could use another shared file system, such as NFS, but this is not explained here).

• One of the client machines should be able to access all the other nodes via ssh and without password. This one will be used to start the manager script.

Installation

Copy the source code

You can uncompress the attached archive (including all the source code) in your home folder or follow the installation procedure (the test scenario example is in PerfTests/bin/sample_tests/COOL_insertions/).

$ tar zxf PerfTests.tgz

Configure the website

A web site should be configured in order to publish the web based report. An AFS based website is easily created using this page : https://www.cern.ch/WebServices/Services/CreateNewSite/ . Choose AFS folder for the Site type and put the log folder path (eg: /afs/cern.ch/user/j/jsmith/PerfTests/logs/).

Modify the privileges of the log directories to enable access from web :

$ fs setacl -dir ~/PerfTests/logs/ ~/PerfTests/logs/jpgraph/src/ ~/PerfTests/logs/jpgraph/src/lang/ -acl system:anyuser rl

Create a monitoring schema on the Oracle database

You should create a specific schema on the Oracle database in order to use the monitoring feature. A script called install.sql is provided. You should execute it with the required privileges (a DBA account). You will be asked for a username and a password which will be needed by the framework afterward (in authentication.xml).

$ sqlplus "sys@oracle_service_name / as sysdba" @install
[...]
Enter password:
[...]
Enter value for monitoring_user: oracle_monitoring_username
Enter value for password: oracle_monitoring_password
[...]

Create the Oracle user account for the client

Create the Oracle user with the SQL script install_client.sql (specify the username and the password, they will be needed after during the configuration).

$ sqlplus "sys@oracle_service_name / as sysdba" @install_client
[...]
Enter password:
[...]
Enter value for new_tablespace: oracle_client_tablespace
[...]
Enter value for clientusername: oracle_client_username
Enter value for password: oracle_client_password
[...]

Configure the database access

You'll have to modify the configuration files authentication.xml and dblookup.xml. They are needed by the monitoring extension and the test client.

You need to specify the database service name for each node (the node does not matter for the monitoring) : oracle_service_name and oracle_service_name_node1, the monitoring account parameters : oracle_monitoring_username and oracle_monitoring_password and the client account parameters : oracle_client_username and oracle_client_password.

• authentication.xml, gathers the database usernames & passwords for Persistency and COOL :
  • for the monitoring account :

<?xml version="1.0" ?>
<connectionslist>
 <connection name="oracle://oracle_service_name/oracle_monitoring_username">
  <parameter name="user" value="oracle_monitoring_username" />
  <parameter name="password" value="oracle_monitoring_password" />
 </connection>

• • and for the client account (one entry per node - see why) :

 <connection name="oracle://oracle_service_name_node1/oracle_client_username">
  <parameter name="user" value="oracle_client_username" />
  <parameter name="password" value="oracle_client_password" />
 </connection>

 <connection name="oracle://oracle_service_name_node2/oracle_client_username">
  <parameter name="user" value="oracle_client_username" />
  <parameter name="password" value="oracle_client_password" />
 </connection>
</connectionslist>

• dblookup.xml, defines a service name for the monitoring account using CORAL :

<?xml version="1.0" ?>
<servicelist>
 <logicalservice name="/OracleStressReport">
  <service name="oracle://oracle_service_name/oracle_monitoring_username"
  accessMode="readonly" authentication="password" />
 </logicalservice>
</servicelist>

It is important that each RAC node is made accessible via a unique service name such as "RACn" where n is the number of the node (starting from 1). This enables the program to distribute homogeneously the sessions among the RAC nodes.

Configure the test

You will have to modify two different files : testConf.xml, the framework configuration file and coolSetup.py, a configuration file specific to this test case.

Modify testConf.xml to configure the test environment.

• Indicate the client machine hostnames

        <testNodes>
                <client>node1</client>
                <client>node2</client>
                <client>node3</client>
        </testNodes>

• and the RAC node hostnames

        <monitoring>
                <server>server1</server>
                <server>server2</server>
        </monitoring>

• you can enable the different monitoring graphs

        <statistic
                cpu="1"   -- 0 to disable
                numRACNodes="2"
                waitEvents="1"/>   -- 0 to disable

• you can tweak the ramping-up of the connections

        <execution
                numBoxes="1"   -- number of machines used for this test run
                numMaxClientsPerBox="2"   -- total number of client programs which will be started on each client machine
                numClientsPerBoxInit="1"   -- number of client programs which will be started on each client machine on the first step 
                numClientsPerStep="1"   -- number of client programs which will be started on each client machine on the following steps
                stepPeriod="20"   -- waiting time between each "ramping-up step"
                testMaxDuration="3600"/>   -- maximum duration of the test, after this period the clients are killed and the test is shut down

• then you should indicate the folders used by the program

        <locations
                baseDir="/afs/cern.ch/user/j/jsmith/PerfTests/"   -- directory where you downloaded the framework
                clientLogDir="/afs/cern.ch/user/j/jsmith/PerfTests/logs/"/>   -- directory where you planned to store all the log folders 
                                (can be in another place than the base directory but should remain on a shared filesystem.

• here you should indicate the information related to the client program

        <clientConf
                db_connection="/dbdev/OracleStress"   -- connection string used by the monitoring script (declared in dblookup.xml and authentication.xml)
                db_username="ST_CLIENT"   -- name of the Oracle user which will be used as client for the test
                testClientCmd="client.py"   -- command used to start the test program on the different client nodes
                testClientExe="python"   -- real name as seen by the operating system (eg: python for a python script)
                reportEveryXSecs="10"/>   -- time between each monitoring output (should also be used in the client program, eg: writes results on this same frequency)

• you can save meaningful files in the log folder

        <fileToSave>
                <file name="Client execution script">client.py</file>   -- the client program should be saved in order to reproduce the test easily
                <file name="Configuration file">testConf.xml</file>   -- a saved version of the configuration file is also needed to generate the report
        </fileToSave>

Modify coolSetup.py to adapt the test to your needs (once again the connection string and some variables to modify the size of the test).

Set the environment variables

There are two files provided depending on your shell. For bash, sh or zsh use bashrc and for csh or tcsh use cshrc.

You can copy them in your .bashrc (or .cshrc) or source them from there :

source ~/PerfTests/bin/bashrc

Don't forget to modify the following variables if needed :

export CMTCONFIG=slc4_amd64_gcc34  -- eg can be changed to slc4_ia32_gcc34 
export COOL_VERSION=COOL_2_2_2

Initialisation of the database

The script createCoolDb.py drops the database (if it exists) and recreate a new one with no data in it.

This takes about 5 min.

$ ./createCoolDb.py
Drop the test database
Done
Create the test database
Database created ! 
10 folders created
20 folders created
[...]
All the folders have been created

Start a test

The manager script is used with one parameter : the test name.

$ ./startTest.py 2007_12_01_COOL_2_2_2

During the test, some monitoring information is displayed on the output. (number of sessions, objects inserted, RAC CPU stats, wait events stats)

You can also use the script statClients.py to check the sessions on the RAC.

Generate a report

The script reportTest.py needs the same parameter used for startTest.py (the name of the test). You will be asked to fill a little description explaining what is done during this test. Then it will generate a web page you can visit from the website you created previously.

Abort / stop a test

stopTest.py allows you to stops the test whenever you want. The parameter is still the test name but you can also use the word clean to abort the test.

Notes

If you want to run the test a second time yyou'll need to re-create the database (especially if you change the number of folders) or you can simply increase the first insertion key value in coolSetup.py (firstKey). Increase it by the number of objects (nIOV) times the length of those objects (iovValidity).

Changing the parameters in coolSetup.py allows you to modify the test length, workload, size.