Re: SESSIONS vs. MySQL

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

 



On Sep 19, 2008, at 4:01 PM, Stut wrote:

On 19 Sep 2008, at 21:44, Philip Thompson wrote:

On Sep 19, 2008, at 1:12 PM, Stut wrote:

On 19 Sep 2008, at 18:47, Philip Thompson wrote:
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.

Fair enough, but I would suggest this is an ideal candidate for being kept in the session.

Yes, I agree - these can prob be moved into the session.

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....

Sounds like a lot of work for little benefit, but it sounds like it might be hard to remove so I'd probably live with it for a while too.

It may be some work... but it doesn't make sense to have session stuff in two different places. (I inherited this architecture, so I've been limited as to what I can do to some extent.) The question I have to ask myself now... will it be worth it in the future to have moved the session stuff to 1 class now? And do I have the time/resources to? =D

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?

Yes, you only need the update statement.

Ok, here, only the update is needed. But for other locations where multiple users may be accessing the same record, I should lock it.....

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.

That's not even close to a large number of users, but it depends a lot on what else the servers you're hosting it on are being used for.

A client may have 1 user or 50 users. It's not the user-size I'm concerned about. This software is for doctor's offices. So, last week when we had our first import from another practice management system (aptly acronym'd, PMS), our patient records jumped from about 1,000 to 65,000. That's just 1 client! Now, I still know that's not a whole lot, but multiply that by 100 clients in the next year: 64000 * 100 = 6.4 million patient records. That's more of a significant number.

We're using a dedicated server that hosts the website and the database. I *know* we're going to need to expand... but that's beyond my control as a mere pawn. As of today, it's okay.

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?

Sure. With an architecture like this you start to assume that X is available anywhere in your code because at the moment you know the framework loads it for you. This makes it exceedingly difficult to strip the initialisation code down if you end up needing to optimise the crap out of it.

As far as scaling goes you're placing all the load on the database so if you get to a stage where you can no longer vertically scale the DB hardware you're left with a major rewrite of your entire codebase to allow it to scale horizontally. It's possible that your app is capable of being sharded across multiple servers but chances are that's still going to take major surgery to achieve.

Some on the list may have noticed I'm a bit anal about scalability issues, but it's only because I've inherited several systems now that were never designed with scalability in mind and I ended up almost completely rewriting each one. Every new site I develop now is built so it's modular, can spread across multiple servers if/when needed and doesn't waste resources. No doubt most web developers never hit these problems, but I guess I've just been unlucky in that respect.

-Stut

Thanks for the explanation.

~Philip

--
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