WIMPY POINT SCALABILITY TEST

by Rocael Hernández (roc@viaro.net), 2003.

The test was performed with the HEAD CVS for openacs 4.6.1, having the performance improvements done by Don Baccus for the permissions system. TAG: oacs-4-6-1-b2

The Easy Plan:
with populate I'll upload something like 10000 users
add those users to wp read
then, create an script that will create a new wp for each user in the DB (the same structure of presentation)
then check page by page how it works, if something is slow try to figure out what's going on... and try solve it!
The test will be performed in PG and Oracle (note: if you exchange the order for each couple of letters in Oracle "or-ac-le"you get Rocael, my name!)

Testing Box:
Pentium III 800Mhz, 256RAM, IDE HDD.
redhat 7.2
aolserver oacs distribution
oacs 4.6.1


Steps (first with PG):

first get the fresh code from CVS:

cvs -z3 -d :pserver:anonymous@openacs.org:/cvsroot co -r oacs-4-6-1-b2 acs-core

then in the packages directory

cvs -z3 -d :pserver:anonymous@openacs.org:/cvsroot co -r oacs-4-6-1-b2 wp-slim

and populate package from the HEAD because wasn't available in oacs-4-6-1-b2 tag

cvs -z3 -d :pserver:anonymous@openacs.org:/cvsroot co populate
cvs -z3 -d :pserver:anonymous@openacs.org:/cvsroot co acs-developer-support

Install openacs as usual

after adding 10000 & vacuum analyze pg db, wp was practically unusable, the permission system was not fast enough.
vacuumdb -v -z -f wimpy

the admin page for presentation admin, presentation-top.tcl
someone wrote this query

select first_names || ' ' || last_name as full_name,
person_id,
acs_permission__permission_p(:pres_item_id, person_id, 'wp_view_presentation') as view_p,
acs_permission__permission_p(:pres_item_id, person_id, 'wp_edit_presentation') as edit_p,
acs_permission__permission_p(:pres_item_id, person_id, 'wp_admin_presentation') as admin_p
from persons
where acs_permission__permission_p(:pres_item_id, person_id, 'wp_view_presentation') = 't'
or acs_permission__permission_p(:pres_item_id, person_id, 'wp_edit_presentation') = 't'
or acs_permission__permission_p(:pres_item_id, person_id, 'wp_admin_presentation') = 't'


the first trial, was to split this query in three, so in this way you avoid triple call for the same thing to acs_permission__permission_p

something like this:

VIEW

select first_names || ' ' || last_name as full_name,
person_id
from persons
where acs_permission__permission_p(:pres_item_id, person_id, 'wp_view_presentation') = 't'


EDIT

select first_names || ' ' || last_name as full_name,
person_id
from persons
where acs_permission__permission_p(:pres_item_id, person_id, 'wp_edit_presentation') = 't'


ADMIN


select first_names || ' ' || last_name as full_name,
person_id
from persons
where acs_permission__permission_p(:pres_item_id, person_id, 'wp_admin_presentation') = 't'

but its still slow acs_permission__permission_p because is a where clause, and the function will go through all the 10K users to check permissions?

Changed a little bit the query to directly check with acs_permissions and the code and now is like:

select distinct on (p.person_id) p.person_id,
p.first_names || ' ' || p.last_name as full_name,
perm.privilege
from persons p,
acs_permissions perm
where perm.object_id = :pres_item_id
and perm.grantee_id = p.person_id
and
(perm.privilege = 'wp_view_presentation'
or perm.privilege = 'wp_edit_presentation'
or perm.privilege = 'wp_admin_presentation'
)
order by p.person_id, perm.privilege ASC

Quite fast!


Then we will populate the DB with some thousands of presentations.

1. Create presentation and join it to specific users.

presentations-populate.tcl

It tooked like one second to create a new presentation...
was planned for 4000 presentations

after 2440 I got this from the DB:
ERROR: deadlock detected (I was running pg in the foreground, tried to search for an error in the aolserver log but didn't find anything...)


2. Populate the presentations with data (bullets, slides, etc.). 5 slides per presentation, the last two slides added one revision each one.

presentation-data-populate.tcl

3. Give permissions to next 5 people to each presentation created, 1 admin, 2 edits, 2 views.

presentation-permission-populate.tcl


I ran part of step 2 & 3 together, the webserver was not giving responses to any other page ...

 

Now, on index.tcl page I also get tired waiting for it!

the problem with the query is basicly the function acs_permission__permission_p which when is called it starts a sequencial
scan, even if you have the index, so I decided to change to explicit query joins with acs_permissions table.


from having this non-return query:

select i.item_id as pres_item_id,
pres.pres_title,
to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
ao.creation_user,
p.first_names || ' ' || p.last_name as full_name,
acs_permission__permission_p(i.item_id, 2121, 'wp_edit_presentation') as edit_p
from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao
where i.live_revision = pres.presentation_id
and ao.object_id = i.item_id
and ao.creation_user <> 2121
and ao.creation_user = p.person_id
and acs_permission__permission_p(i.item_id, 2121, 'wp_view_presentation') = 't'
and ao.context_id = 2319

just explain it, no time to explain analyze that will take a lot of time:

wimpy=# explain select i.item_id as pres_item_id,
wimpy-# pres.pres_title,
wimpy-# to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
wimpy-# ao.creation_user,
wimpy-# p.first_names || ' ' || p.last_name as full_name,
wimpy-# acs_permission__permission_p(i.item_id, 2121, 'wp_edit_presentation') as edit_p
wimpy-# from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao
wimpy-# where i.live_revision = pres.presentation_id
wimpy-# and ao.object_id = i.item_id
wimpy-# and ao.creation_user <> 2121
wimpy-# and ao.creation_user = p.person_id
wimpy-# and acs_permission__permission_p(i.item_id, 2121, 'wp_view_presentation') = 't'
wimpy-# and ao.context_id = 2319
wimpy-# ;
NOTICE: QUERY PLAN:

Nested Loop (cost=521.39..640.50 rows=1 width=107)
-> Nested Loop (cost=521.39..636.74 rows=1 width=78)
-> Hash Join (cost=521.39..599.23 rows=12 width=62)
-> Seq Scan on cr_wp_presentations pres (cost=0.00..65.46 rows=2446 width=54)
-> Hash (cost=521.29..521.29 rows=37 width=8)
-> Seq Scan on cr_items i (cost=0.00..521.29 rows=37 width=8)
-> Index Scan using acs_objects_pk on acs_objects ao (cost=0.00..3.05 rows=1 width=16)
-> Index Scan using persons_pk on persons p (cost=0.00..3.01 rows=1 width=29)

EXPLAIN

with a few changes avoiding the use the acs_permission__permission_p mega function, and having a distinct
to not repeat presentations, I wrote this:

select distinct on(i.item_id) i.item_id as pres_item_id,
pres.pres_title,
to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
ao.creation_user,
p.first_names || ' ' || p.last_name as full_name,
perm.privilege
from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao, acs_permissions perm
where i.live_revision = pres.presentation_id
and ao.object_id = i.item_id
and ao.creation_user <> 2121
and ao.creation_user = p.person_id
and ao.context_id = 2319
and perm.object_id = i.item_id
and (perm.grantee_id = 2121 or perm.grantee_id = -1)
and (perm.privilege = 'wp_view_presentation' or perm.privilege = 'wp_edit_presentation')
order by i.item_id, perm.privilege ASC

and doing an explain analyze:

wimpy=# explain analyze select distinct on(i.item_id) i.item_id as pres_item_id,
wimpy-# pres.pres_title,
wimpy-# to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
wimpy-# ao.creation_user,
wimpy-# p.first_names || ' ' || p.last_name as full_name,
wimpy-# perm.privilege
wimpy-# from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao, acs_permissions perm
wimpy-# where i.live_revision = pres.presentation_id
wimpy-# and ao.object_id = i.item_id
wimpy-# and ao.creation_user <> 2121
wimpy-# and ao.creation_user = p.person_id
wimpy-# and ao.context_id = 2319
wimpy-# and perm.object_id = i.item_id
wimpy-# and (perm.grantee_id = 2121 or perm.grantee_id = -1)
wimpy-# and (perm.privilege = 'wp_view_presentation' or perm.privilege = 'wp_edit_presentation')
wimpy-# order by i.item_id, perm.privilege ASC
wimpy-# ;
NOTICE: QUERY PLAN:

Unique (cost=307.33..307.33 rows=1 width=121) (actual time=65.30..66.74 rows=444 loops=1)
-> Sort (cost=307.33..307.33 rows=1 width=121) (actual time=65.30..65.62 rows=444 loops=1)
-> Nested Loop (cost=0.00..307.32 rows=1 width=121) (actual time=0.59..63.77 rows=444 loops=1)
-> Nested Loop (cost=0.00..304.29 rows=1 width=67) (actual time=0.48..43.54 rows=444 loops=1)
-> Nested Loop (cost=0.00..301.27 rows=1 width=38) (actual time=0.44..30.45 rows=444 loops=1)
-> Nested Loop (cost=0.00..298.20 rows=1 width=30) (actual time=0.40..18.07 rows=444 loops=1)
-> Index Scan using acs_permissions_grantee_idx, acs_permissions_grantee_idx on acs_permissions perm (cost=0.00..281.24 rows=6 width=14) (actual time=0.06..4.38 rows=453 loops=1)
-> Index Scan using acs_objects_pk on acs_objects ao (cost=0.00..3.04 rows=1 width=16) (actual time=0.02..0.02 rows=1 loops=453)
-> Index Scan using cr_items_pk on cr_items i (cost=0.00..3.06 rows=1 width=8) (actual time=0.02..0.02 rows=1 loops=444)
-> Index Scan using persons_pk on persons p (cost=0.00..3.01 rows=1 width=29) (actual time=0.02..0.02 rows=1 loops=444)
-> Index Scan using cr_wp_presentations_id_idx on cr_wp_presentations pres (cost=0.00..3.01 rows=1 width=54) (actual time=0.01..0.02 rows=1 loops=444)
Total runtime: 67.43 msec

EXPLAIN

Really impresive, now is 67.43 msec when before was more than 15 minutes! Just note the full use of the indexes.

Well, the actual query was changed because the permissions checking wasn't done quite well, so I use the view acs_object_party_privilege_map like this

select i.item_id as pres_item_id,
pres.pres_title,
to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
ao.creation_user,
p.first_names || ' ' || p.last_name as full_name,
acs_permission__permission_p(i.item_id, :user_id, 'wp_edit_presentation') as edit_p
from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao
where i.live_revision = pres.presentation_id
and ao.object_id = i.item_id
and ao.creation_user <> :user_id
and ao.creation_user = p.person_id
and ao.context_id = :package_id
and exists (select 1
from acs_object_party_privilege_map m
where m.object_id = i.item_id
and m.party_id = :user_id
and m.privilege = 'wp_view_presentation')

The performance was not so good, it took like 6 seconds... but MUCH better than the original query.

 

ORACLE history:

did all the wp fill up of data with this single (joined) script presentations-oracle-populate.tcl, and of course populate the DB with users.

in oracle I got an error telling me this:

[15/Mar/2003:10:59:20][2771.7176][-conn4-] Error: SQL():
ora8.c:3568:ora_tcl_command: error in `OCIStmtExecute ()': ORA-0165\
4: unable to extend index WIMPY.ACS_OBJECTS_PK by 8 in tablespace WIMPY
ORA-06512: at "WIMPY.ACS_OBJECT", line 68
ORA-06512: at "WIMPY.CONTENT_ITEM", line 174
ORA-06512: at "WIMPY.WP_SLIDE", line 151
ORA-06512: at line 3

...

seemed that the DB has no more space left, even if I followed the standard oacs installations instructions (http://openacs.org/doc/openacs-4/openacs.html):

create tablespace birdnotes
datafile '/ora8/m02/oradata/ora8/birdnotes01.dbf'
size 50M
autoextend on
next 10M
maxsize 300M
extent management local
uniform size 32K;

the maxsize when I created it doesn't seem logical to me, but I did it, what I usually do is something like:

create tablespace service_name datafile '/ora8/m02/oradata/ora8/service_name01.dbf' size 50m autoextend on default storage (pctincrease 1);


ok, no problem, extend the db:
alter database datafile '/ora8/m02/oradata/ora8/wimpy01.dbf' resize 600M;

and now we have enough space for now!



Then, with the new queries fixed to use the improvements (because I tried with the same queries, but had the same experience that in PG) there was not too much improvement, ahh...! forgot to analyze the DB, and how to do that (http://www.think-forward.com/sql/analyze8.htm), basicly:

$ svrmgrl
SVRMGR> connect internal
SVRMGR> execute dbms_utility.analyze_database('COMPUTE');
SVRMGR> execute dbms_utility.analyze_schema('WIMPY','ESTIMATE');
SVRMGR> execute dbms_utility.analyze_schema('WIMPY','COMPUTE');

$ sqlplus sys/rocael @analyze.sql

took like 15 minutes or so...
=o)


but, with the proper changes in the queries that with did in PG the index page is still slow for 10,000 presentations.

for this query in index-oracle.xql:

select i.item_id as pres_item_id,
pres.pres_title,
to_char(ao.creation_date, 'Month DD, YYYY') as creation_date,
ao.creation_user,
p.first_names || ' ' || p.last_name as full_name,
acs_permission.permission_p(i.item_id, :user_id, 'wp_edit_presentation') as edit_p
from cr_items i, cr_wp_presentations pres, persons p, acs_objects ao
where i.live_revision = pres.presentation_id
and ao.object_id = i.item_id
and ao.creation_user <> :user_id
and ao.creation_user = p.person_id
and ao.context_id = :package_id
and exists (select 1
from acs_object_party_privilege_map m
where m.object_id = i.item_id
and m.party_id = :user_id
and m.privilege = 'wp_view_presentation')

took: almost 281 seconds
without acs_permission.permission_p(i.item_id, :user_id, 'wp_edit_presentation') took 11280 ms
so you see where is the problem now... in acs_permission.permission_p

Actually, I'm not too worriend, now added to be able to show the presentations in the last week, two weeks, month or all, and you know, is like if you have this options in the forums, and you want to see all the threads it will take a long time...

Now I would like to find a way to avoid acs_permission.permission_p in oracle & pg.

 

....