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 = 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
-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
|
