Re: SESSIONS vs. MySQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

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?

3. Grab page id

What does this do, how is it used, is it needed?

4. Grab user privs

IMHO you should only grab these when you need them.

5. Grab user session (for application)

Again, why isn't this already in $_SESSION for every page request expect the first per visit?

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.

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.

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!

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

--
http://stut.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux