[rt-users] RT - Test performance (I) (fwd)

[Tomas Olaj]

This test performance was done by our Postgres DBA Rafael Martinez. I am 
translating this so You can agree or disagree if this makes sense or not.

Adding indexes can for sure improve performance.

-- 
________________________________________________________________________
Tomas A. P. Olaj, email: tomas.olaj at usit.uio.no, web: folk.uio.no/tomaso
  University of Oslo / USIT (Center for Information Technology Services)
    System- and Application Management / Applications Management Group

---------- Forwarded message ----------
Date: Wed, 29 Mar 2006 11:30:14 +0200
From: Rafael Martinez Guerrero
To: rt-drift at usit.uio.no
Subject: RT - Test performance (I)

Hello

I'm finished with the performance test of RT with and without the new 
indexes.

The test has been done on a test server with 2 x Intel(R) Xeon(TM) CPU 
2.40GHz and 4GB RAM. We installed a copy of the production environment on 
this server with RT.3.4.2 and postgresql.7.4.12.

The backup file rtprod-000012-all-2006-03-27_140101.sql (includes the new 
indexes) was installed and a 'VACUUM VERBOSE ANALYZE' was run before we 
started to test.

Both postgresql and apache has been stopped and restarted before every 
test type. We assumend that things that was cached by the OS did not 
affect the result.

We have tested both from command line and web, with and without the new 
indexes.

We have run some of the most common tasks done in RT on every test. We 
generated a SQL file with all the sql-statement tasks sent to the 
database, so we could to the test from the command line without web 
processing of data (this file contained 1920 sql-statements).

The test on the command line was run with:

  -bash-2.05b$ time psql rtprod < /postgres/log_test_rt.sql > /dev/null

The test via web was done using RT via web, the time that was used was the 
time that is showned down on the RT pages when they were finished 
processing.

The test contucted like this:

- Postgres/apache stopped/started
- Commandline test with indexes run 3 times after each other.
- Postgres/apache stopped/started
- Web test with indexes run 3 times after each other.

- DROP new indexes
- 'VACUUM VERBOSE ANALYZE' run

- Postgres/apache stopped/started
- Commandline test without indexes run 3 times after each other.
- Postgres/apache stopped/started
- Web test without indexes run 3 times after each other.
- Postgres/apache stopped/started

Total sum of times:

- without indexes via commandline: 28.166s
- with indexes via commandline: 11.473s
- without indexes via web: 31.5618s
- with indexes via web: 20.2351s

Total result:

****************************************
Some numbers from the RT database
****************************************
Num.tickets: 34 096
Num.transactions: 610 556
Num.users: 15 880
Num.queues: 53
Num.groups: 152 548
****************************************


****************************************
Tasks:
****************************************
1- Load main page
2- Login
3- Open queue (www-drift)
4- Open ticket from queue side (#34054)
5- Open 'Home'
6- Open 'Tickets'
7- Load saved search (rafa_resolved)
8- Run Search
9- Open 'Home'
10- Logout
*****************************************


*****************************************
New indexes:
*****************************************
   CREATE INDEX acl2 on acl (principalid,principaltype);
   CREATE INDEX acl3 on acl (objecttype);
   CREATE INDEX groups4 on groups (domain);
   CREATE INDEX attributes3 on attributes (objectid,objecttype);
   CREATE INDEX groupmembers1 on groupmembers (groupid,memberid);
   CREATE INDEX groups3 on groups(LOWER(Domain), LOWER(Type));
   CREATE INDEX users5 on users (LOWER(Name));
   CREATE INDEX groups5 on groups (instance,LOWER(Domain),LOWER(Type));
   CREATE INDEX groupmembers2 on groupmembers (memberid);
   CREATE INDEX tickets6 on tickets (status);
   CREATE INDEX objectcustomfieldvalues3 on
 	       objectcustomfieldvalues(disabled,objectid,objecttype);
*****************************************


****************************************
Tests:
****************************************
-----------------------------
* Without new indexes / via psql:
-----------------------------
real    0m28.191s
real    0m28.133s
real    0m28.175s

Average: 84.499 / 3 = 28.166s

--------------------------
* With new indexes / via psql:
--------------------------
real    0m11.576s
real    0m11.419s
real    0m11.426s

Average: 34.421 / 3 = 11.473s

----------------------------
* Without new indexes / via web:
----------------------------

1- 0.174703
2- 3.505841
3- 0.944324
4- 4.735964
5- 2.444698
6- 5.307809
7- 5.160225
8- 7.987324
9- 2.648068
10- 0.025291

Total: 32.9338s


1- 0.026303
2- 2.99801
3- 0.824755
4- 4.546013
5- 2.47372
6- 4.927333
7- 4.756017
8- 7.743986
9- 2.647696
10- 0.045746

Total: 30.9892s


1- 0.025872
2- 3.037854
3- 0.710582
4- 4.559498
5- 2.415181
6- 4.685646
7- 4.944309
8- 7.686661
9- 2.660198
10- 0.037399

Total: 30.7625

Average: 94.6855 / 3 = 31.5618s


--------------------------
* With new indexes / via web:
--------------------------
1- 0.03583
2- 2.897222
3- 0.394071
4-2.198648
5- 2.08015
6- 4.457699
7- 4.458629
8- 2.215953
9- 2.255062
10- 0.024364

Total: 21.1072s


1- 0.01439
2- 2.736695
3- 0.191751
4- 2.068953
5- 2.022578
6- 4.413129
7- 4.427378
8- 1.981398
9- 2.047436
10- 0.035024

Total: 19.9381s


1- 0.026164
2- 2.6012
3- 0.184858
4- 2.052559
5- 2.0721
6- 4.269218
7- 4.326582
8- 2.027836
9- 2.07491
10- 0.02493

Total: 19.66s

Average: 60.7053 / 3 = 20.2351s
****************************************

Comments?