WARNING: This web is not used anymore. Please use PDBService.OracleRACLoadTestingTuto instead!

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.


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.


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.

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

 <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 name="oracle://oracle_service_name_node2/oracle_client_username">
  <parameter name="user" value="oracle_client_username" />
  <parameter name="password" value="oracle_client_password" />

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

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
  • and the RAC node hostnames
  • you can enable the different monitoring graphs
                cpu="1"   -- 0 to disable
                waitEvents="1"/>   -- 0 to disable
  • you can tweak the ramping-up of the connections
                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
                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
                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
                <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

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
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.


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.

Topic attachments
I Attachment History Action Size Date Who Comment
Compressed Zip archivetgz PerfTests.tgz r1 manage 350.2 K 2007-12-05 - 17:17 RomainBasset  
Edit | Attach | Watch | Print version | History: r22 < r21 < r20 < r19 < r18 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r22 - 2010-06-11 - PeterJones
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2023 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback