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@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@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™ 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?