Trade off between user data in session vs database?

Question

When developing a larger user driven site where each logged in user has a large amount of account data needed as they navigate around the site, what is a good balance between having this data easily accessible in the session, vs needing to read it from the database. It's tempting to hold most of it in session to reduce what will already be frequent db accesses. I'm specifically thinking of scenario like an online game, where there are many users with a lot of data for their player.

Answer 1

I suggest you to use a simple identifier in session that you use to retrieve info from db, but for performance issue use a cache. So if the data is in cache take that, otherwise take it from db.

Example:

class User {
   public $username ="";
   public $moredata ="";
   public function logged() { // return true if logged }
}

$ttl = 10000 // cache time to live 

if($_POST['action'] == 'login') {
   $user = new User();
   // populate $user, check credential...
   $_SESSION['loggeduser'] = $user->username;
   // saved data on db
   apc_add ( $_SESSION['loggeduser'] , $user ,$ttl )
}

// ....


if($_SESSION['loggeduser']) {
  $user = new User();
  $user->username =  apc_fetch( $_SESSION['loggeduser'],$success )
  if(!$success)
     $user = populateFromDb( $_SESSION['loggeduser']);
}

Answer 2

If the data you're talking about is being used on each and every page load then storing it in the session will likely be OK. If most of this data is not used on every page load, then storing it in the DB (I.E., running queries every time) is likely the way to go. You can also use a session cache (something like memcache) to query the data 1 time per session, then grab from cache the next time. The problem with storing stuff in session is that each and every pageload this data is in memory, which, if it's a significant amount of data, can severely effect the load on your server if you have a lot of connections or long running connections.

Answer 3

It really depends on how much values each user has. Also if there are dynamic values, as username (which can be changed, or First & Last Name.)

I always use the UserID as season and use some queries to display the important information.

Use both :)

Answer 4

Answer: both.

Data in the session is lost after expiry, so you restore the data from the db into the session each time the sessino is lost.

A site which handles many users at a time, with much user data, I'd always use a RAM key/value driven database (Redis, Memcached, ..) as your backend (session.handler).

The data needs to be accessable fast.

But instead of putting everything into the SESSION, you only put the ID there. The rest still goes into Memcached and you only load the data you really need, thus, your PHP process on the server does consume a little less memory.

So you have the benefit of ultra fast data, now do some maths:

If all your users, lets say 1kk, have each 1 KB of data, this is ~ 1 GB. How much memory do servers have nowaways? 16-256 GB Memory - plenty of space for all the user data in the RAM.