Relative use of mysql queries and _SESSION

The site I’m working on has to use a lot of data from a mysql
database. In order to reduce the number of php mysql calls I’m
caching data in $_SESSION between page requests (and Ajax requests).

Can anyone tell me about the trade-off between keeping large amounts
of data in $_SESSION and performing queries from both a server load
and a response time point of view? I’m wondering when/if putting more
and more in $_SESSION will be less performance effective than
reloading from the database.

The site framework is produced with Freeway, but a named div or two
are filled with php gathered data.

David


David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Session relies on filesystem calls to get the data, it’s similar to a
key/value store like memcached or the like. Database calls can be
faster, especially if you have a solid set of indexes and aren’t doing
a lot of joins. I tend to keep a key in the session, and then restore
from the database using that key. It’s also more secure, because those
session files are readable from another account on a shared server. So
I put a salted MD5 or a simple numerical ID in there and little more.

If you’re having to do a lot of joins and are doing this on a per-view
basis, you might want to refactor your application to hold these
joined data in a temporary sessions table row. Have a separate
sessions controller and use that to re-populate the stuff that doesn’t
need to be computed on every page-load. That could be a huge win,
performance-wise, although I wouldn’t recommend you do this without
some real profiling to be certain that you do in fact have a
bottleneck there. Premature optimization is the Devil’s playground,
and all that.

Walter

On Dec 3, 2010, at 1:30 PM, David Ledger wrote:

The site I’m working on has to use a lot of data from a mysql
database. In order to reduce the number of php mysql calls I’m
caching data in $_SESSION between page requests (and Ajax requests).

Can anyone tell me about the trade-off between keeping large amounts
of data in $_SESSION and performing queries from both a server load
and a response time point of view? I’m wondering when/if putting
more and more in $_SESSION will be less performance effective than
reloading from the database.

The site framework is produced with Freeway, but a named div or two
are filled with php gathered data.

David


David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

At 13:59 -0500 3/12/10, Walter Lee Davis wrote:

Session relies on filesystem calls to get the data, it’s similar to
a key/value store like memcached or the like. Database calls can be
faster, especially if you have a solid set of indexes and aren’t
doing a lot of joins. I tend to keep a key in the session, and then
restore from the database using that key. It’s also more secure,
because those session files are readable from another account on a
shared server. So I put a salted MD5 or a simple numerical ID in
there and little more.

As I understood it, part of the final phase of a php process run that
has had a session started is to serialize (session_encode) the
$_SESSION array and save the result to a file. When a php invocation
starts a session it reads in the file and unserializes the contents
to populate $_SESSION. That would mean one filesystem access and a
bit of processing at session_start() and then again at session close.
Looks like there’s a lot more file i/o than that going on.

The database lookups have no joins. It would if the database was
designed for the purpose, but it’s exported from a FileMaker database
that is interrogated manually during phone calls. It has developed
slowly and simply. The mysql copy accessed in php has one large table
indexed by product code. (The FM db is accessed mainly by item name,
which may not be unique). There are also small tables for currency
and tax rates, supplier info, and variant data that I currently read
into $_SESSION when first required and then leave cached there. Maybe
I should just call it from the database each time it’s required.

If you’re having to do a lot of joins and are doing this on a
per-view basis, you might want to refactor your application to hold
these joined data in a temporary sessions table row. Have a separate
sessions controller and use that to re-populate the stuff that
doesn’t need to be computed on every page-load. That could be a huge
win, performance-wise, although I wouldn’t recommend you do this
without some real profiling to be certain that you do in fact have a
bottleneck there. Premature optimization is the Devil’s playground,
and all that.

The quantity and complexity of the data I am keeping in $_SESSION is
such that I’d have to session_encode() it before putting it in a
table, and as I wouldn’t be saving join lookups it’s probably not
relevant. It does sound as if db lookups are more efficient and
$_SESSION less efficient than I had expected.

David

Walter

On Dec 3, 2010, at 1:30 PM, David Ledger wrote:

The site I’m working on has to use a lot of data from a mysql
database. In order to reduce the number of php mysql calls I’m
caching data in $_SESSION between page requests (and Ajax requests).

Can anyone tell me about the trade-off between keeping large
amounts of data in $_SESSION and performing queries from both a
server load and a response time point of view? I’m wondering
when/if putting more and more in $_SESSION will be less performance
effective than reloading from the database.

The site framework is produced with Freeway, but a named div or
two are filled with php gathered data.

David

–David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options