[Openinteract-help] SPOPS question

[Nick F. <Ni...@st...>]

Hi All,

SPOPS has become a nightmare for us, and I am wondering if it is because =
we used the wrong tool for the job, or if it is because we did not use =
it correctly. Since SPOPS is pretty dynamic and capable in lots of ways, =
I'm leaning towards the second.

Our issue is simple. We use SPOPS both for wrapping our database into =
neat little objects... sort of the first four letters of the name (SPOP) =
and we use it for security. That is the area we are having issues. SPOPS =
is too thorough, perhaps.

Let us pretend we have one class. That class has four basic security =
rights associated with it: no access by default (0 world access), =
read-only access to the class, add-access to the class, and full write =
access to the class. Heck, let's drop the 'add' right and just say we =
have the other three rights. So, a user without read or write access is =
denied access. A user with either of the other two has the appropriate =
access.

SPOPS works quite well at allowing and denying based on these rules.

Now, lets say that the class I spoke of is to objectify a table, a table =
with 100,000 records. So, the SPOPS table is now roughly 300,000 records =
in size, just to maintain the security information for the records of =
that one table.

Now, a user with access (read or write) decides to initiate a search =
through those records (select * from where foo =3D bar). Even if we =
allow the user to only see 25 records per page of our web application, =
SPOPS needs to do security checks on all 100,000 records for that table. =
Normally, running a select on 100,000 records for a PC class machine is =
not entirely fast, but not so slow either. However, once we throw in the =
SPOPS security checks, things start to bog down significantly.

Usually, we won't have a user doing a select on 100,000 records, but it =
could happen. I say usually, right now, because we are just starting =
with our application and have not had to grow it too much beyond its =
initial deployment. As we continue further deployment, we will be =
dealing with far more than 100,000 records in a table.

Of course, indexing tables and so on has been done, so that is a bit =
faster. But it sure seems from viewing our MySQL debug log that SPOPS =
makes a whole lot of security queries for each object access, and those =
start to build up. As our CGI processes start to wait for the database =
operations to finish, they all start to gang up and hang around together =
in memory and the process table (i.e. as more users are using the system =
and doing searches). Eventually, with enough users, the server dies.

I know that this could happen without SPOPS involved at all. But, it =
sure seems from reviewing stats and logs and such that SPOPS is =
responsible for such an increase in database usage based solely on =
security checks that we experience dramatic slow down a lot 'easier' =
than we would like.

I've been thinking for a while that the best plan would be to keep the =
'SPOP' part of 'SPOPS' and re-implement security checks from scratch. =
But, you all seem to be getting along with OI and lots of users so =
swimmingly, that I must wonder what more I can do to squeeze extra =
performance out of SPOPS.

So, how about it?

I am still a relative novice to being a DBA and I'm only moderately good =
at getting what I want to out of SPOPS. Maybe not even. I've read the =
manual, but have not yet seen the alternatives.

Thanks for any guidance.

Nicholas Floersch