I have more questions/responses throughout...
On Sep 19, 2008, at 1:12 PM, Stut wrote:
On 19 Sep 2008, at 18:47, Philip Thompson wrote:
I've narrowed it down to 10 initial queries...
1. Grab system config data (that's used in lots of places)
Does it change often? No? Then cache it in a PHP script. Use
var_export to create a file that you can include which will create
the configuration array. Alternatively cache it in a Memcache
instance which is where my system-wide config usually lives.
Good idea.
2. Grab session data (for SESSION array)
Meaning what? You say below that this is after the initial session
load. What are you loading here and why is it being loaded on every
page request if it's ending up in the $_SESSION array?
Because I'm using your class, Stut, (at least as a reference) to store
my sessions in the database. Hence, I have to pull them from the
database.
3. Grab page id
What does this do, how is it used, is it needed?
I was able to add this to the SESSION.
4. Grab user privs
IMHO you should only grab these when you need them.
I will need these on most pages anyway. Because of the architecture,
the security class (which uses these a lot) is a separate part.
5. Grab user session (for application)
Again, why isn't this already in $_SESSION for every page request
expect the first per visit?
This "user session" deals with merely keeping up with the time - how
long has it been since this user accessed the site? Keep logged in?
Logged in elsewhere? This uses the db and cookies. Note, this was
designed into the app from the beginning... using the _SESSION var is
new to the app as of this week. Yes, we can probably move this
functionality into the new _SESSION stuff....
6. Begin transaction
7. Lock user session row
8. Update user session
9. Commit transaction
If all you're doing is issuing an update command there is no need to
do so in a transaction and definitely no need to lock the row. An
update is atomic.
Maybe what you actually mean to do here is lock it before you get
the session data, make changes to it and then unlock it once you're
done changing it. Doing that would likely keep the row locked for
the entire duration of a request which can start causing problems as
traffic increases.
I'm starting the transaction because MySQL "SELECT... FOR UPDATE"
requires a transaction to lock the row. But now that I think about
it... the reason we use the lock is so that we don't have collisions
in data - specifically here the user session. However, the user
session row is only accessed by a single user (his/her own). And since
they can only be logged in at one location, there's virtually no way
for a collision. Right? I can remove queries 6, 7, and 9, right?
10. Add page tracking (an insert-only table that keeps track of
pages you visit)
I handle this using files and then have an offline processor to push
that data into the database. If all you're doing is adding a row to
the table you probably don't need this, but we do a fair amount of
work for each page view to record the data in a set of tables
designed for meaningful and speedy retrieval.
Note that these are the 10 queries that happen after the initial
SESSION load. I supposed I could reduce this by 1 or 2 queries - I
could store the page id/information in the session. Now with that
said, the queries are negligible (in elapsed time) and required.
However, I'm always open up to suggestions/improvements.... =D
You may think they're required, but I'm betting they're not if you
really think about it. However, if your DB can handle it then why
fix something that ain't broken.
It can handle it now. But I'm not worried about now. We have less than
10 clients/offices using the app. This may grow up to 100 within the
next year. That's when there's gonna be lots and lots of data and we
may start to see a slow down.
The way I approach this stuff is always with the knowledge that the
database is the most expensive resource in the infrastructure, so
anything I can do to avoid using it when it's not strictly necessary
is something I consider well-worth the effort.
With the rise of frameworks and the lazy architectures it's pretty
common to end up with this mass of DB access at the start of each
request, but it won't scale and it leads to assumptions that are
extremely expensive to find and fix when you do need to scale. Trust
me, I've been there many times and it's been painful every time!
Can you explain why it won't scale and may lead to assumptions?
Oh, and by scale I don't necessarily mean to tens of millions of
page views a month. Scalability is as much about going from 10
visitor a day to 1000 as it is from 1000 to several million.
-Stut
Thanks,
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
