One may think changing PHP session handler from file based to database driven is fully transparent. In many cases it is, sometimes however it may cause some unexpected problems as happened to one of our customers.
If you use file based sessions PHP will lock session file for whole script execution duration, which means all requests from the same sessions will be serialized on PHP level, which means they also will be serialized for single user on database level. If you change to store PHP sessions in MySQL instead this effect may be no more true and you may have number of requests executing for the same session at the same time. First of course means you may have your session data damaged because you will have lost session variables update from one of the script, in addition however you may run into database related issues of modifying user profile or other user/session related data in parallel, if you do not use transactions or lock tables.
So how you can get back your old file based session behavior with MySQL Sessions ?
If you have dedicated connection to session database and use Innodb tables for your session storage you can start transaction on the session start and use SELECT … FOR UPDATE to lock the session row in the session table for whole request length. On the end of the session the same row is updated and transaction is committed.
If you share session connection with other modules or do not use transactional tables for session you can use GET_LOCK to get same behavior. In the start of the session you can do SELECT GET_LOCK(‘
SELECT RELEASE_LOCK(‘
This approach assumes you do not use GET_LOCK in other places in your application as as soon as it is called second time previous lock is automatically released. The good thing about it however – you can use it as an extra to your current MySQL Sessions system without need to change how it works internally. If you do not use persistent connections you even do not have to release lock – as soon as connection is closed the lock is automatically released.
The value 10 in GET_LOCK is timeout in seconds – if lock can’t be granted for this amount of time it will return “0” indicating lock was not granted in this case you can select to continue without session or may do something else, like logging error as this generally should not happen in well tuned applications.
While it’s a completely different approach, you can also store sessions within memcached by djanga. Then you would not need to worry about the writes and table locking.
SELECT … FOR UPDATE
yeah… and if site gets slow for any reason and/or visitor gets impatient, you can always run SHOW PROCESSLIST on the session server and watch hundreds of such threads (several for the same session id), waiting for each other… 😉
Thank you so much for that information. I’ve been looking for a solution to that problem for several months! Also, thank you for a great blog!
Bobby,
Just curious would not memcached session have the same problem as MySQL sessions in terms of locking ?
In general there are many ways to store sessions, including MySQL Cluster – my point is not to advertise one approach over another but simply to show there are differences which you might not think about
Dmitry,
SELECT * FROM SESSIONS WHERE SESSION_ID=”aabbcc” FOUR UPDATE; will only lock that row if it is primary key.
So you should have no more process waiting on the same session as you would have processes waiting on the same file in case of file based PHP sessions.
If you have high concurrency within single session it of course does not work well but in this case plain MySQL sessions based session storage may not work for you either because there will be large chance to loose session variables updates.
Hi,
I use MySQL session for over 7 years (when php3 was at it’s start) and my sessions ids are md5 of special tokens. I never had problems with mysql bottleneck and it helped me a lot when cluster websites needed authentication.
Also I used memcached sessions but you do not have same flexibility as MySQL (but u do have performance).
I never user mmsessions altough they say that are better than file sessions.
Peter,
Everything is fine with MySQL itself 🙂
It’s just my experience with an application design problem – when PHP issues locking select for the session right at the beginning and releases (if finishes) at the end. So if it takes several seconds to run, user can start “jumping” around, sending more and more “select for update” for the same id (PK), which results in a nice bunch of threads on a sessiondb server, and if others do the same, the situation I described occurs… then the session purgers kicks in, and I get called 🙂
Dmitry,
I think you miss the point. Consider the case when you had 1 Web box and file sessions and it can’t keep up so you’re moving to several Web boxes but you’d like to avoid having sessions set up on NFS file system. So you end up storing them in database and your application breaks.
Now the question is why it may break and how to make it fixed easiest way – this is what it is all about.
If you have reloads happening because of slow page loads surely you should fix these first. You also can consider killing previous page view queries in some cases.
Regarding session purge – this is another thing which is often implemented wrong way – you can make them to be very gentle in terms of locking – locking one row at the time, plus if you have index on (last_active) column the delete should only traverse rows which were not active for a while and so chance they are active now is pretty small.
“PHP will lock session file for whole script execution duration”
If you know that the script will not need to write anything into the session any more, it is possible to call session_write_close(). Reading is still possible though.
Jakub,
Thank you for your comment good point. I’m mainly describing default behavior here, while it would be of course good idea to do all session changes as early as possible and “unlock” the session.
“you should fix these first” – sure 🙂
Peter, I just wanted to share some of my real-world experience – a possible side-effect of proposed strategy.
I use same connection for sessions/data with InnoDB but before I register a shutdown function which explicitly rolls back any pending transactions and writes the session data to db. It works without any issues, I wasn’t aware of the concurrent session use issue though.
I use a session table with a varchar primary key that PHP uses as the identifier but I was thinking about switching to a longint one and using that as a foreign key. I might need to prevent some sessions from being deleted for an extended period of time in the future. Would that force me to execute 2 queries with LOCK FOR UPDATE to make use for row level locking though? First SELECT id… WHERE name = ‘abcd’, and then SELECT… WHERE id = ? LOCK FOR UPDATE?
We store our PHP sessions (and everything else we can) in Memcached.
I would HIGHLY recommend it.
Sorry, I noticed how stupid my question is. SELECT… FOR UPDATE doesn’t play nice across transactions, while GET_LOCK() does. Unfortunately you can use only 1 GET_LOCK() per thread.
I needed to share session data across multiple web servers – so I decided to store in mysql.
All sorts of problems arose – locking, queries taking ages, etc (really high load site).
So I took Aaron’s advice to use memcached and it works fantastically. I also highly recommend it over over mysql for storing sessions!
What is the difference between file based applications and database systems and the pros and cons?
Well of course sessions are more suitable in database than in files.
What is main concept of Seassion in PHP
For security purpose i also to save sessions in database rather than using flat files. Thou you would delete the file when you delete your sessions, but the file will remains readable by any script or human.
Wow! i just read about memcached in post made by some earlier. I need to read up on memcached for caching and even storing sessions an d cookies
Yes, memcached is great, but it is a cache nonetheless that is stored in memory. In case you run out of memory for the memcached daemon, your session data might end up getting garbage collected. I personally recommend using an memcached session handler backed by a mysql database.
Hi,
Looks like a great article as i want hunting for sharing the same sessions over subdomains.
(Pramod Mane)
Hi,
This is what i was looking for my new site.
Simple setting in php.ini file
session store memcache is a problem :count sessions ?????how to?????
Only consistent way how to count users online, if sessions are stored in memcached is
to have your own session handler and implement full database backup, so new sessions are also created in db as references and cleaned standard way if memcache session is destroyed in runtime or by garbage collector. Under these conditions you can use standard queries to retrieve number of users online and other usefull stats that are problematic to get from MC.
You can also create file based backup with simple counter (tried it myself and works), depends what level of flexibility and possible statistics you want to have.
Another approach is to create backend collector that fetches content of MC server and stores results in MC object. This is however against MC nature, so i would stick with DB or file backup.
Hey, nice article. I’ve tried doing something similar in a project of mine using something called mSess from:
http://www.virtualthinking.com/loadhtml.php?where=scripts&what=art_show.php&db_target=00000000024
It doesn’t implement a custom session handler, but it works in a similar manner, and has the added benefit of being able to work with traditional PHP sessions at the same time. Fairly easy to use if you use the sample files as a reference.
It’s a CC based license so it’s flexible to incorporate.
I cant find function GET_LOCK on php.net 🙁
Kosher,
This is MySQL function not PHP
Just installed unbuntu 9.04 on a Compaq Pressario C714NR notebook pc and have everything working. This is the first version of unbubtu that I have been able to figure out how to make everything work. Thanks for the list I have been wanting to try wine but never liked any version of linux enough to stick with it for more than a few weeks until now.
I used MySQL for user identification since my first day in php development because it felt natural thing to do. Why should I use PHP sessions instead? Is there any advantage?
Hi . Hello i am enjoying.
On a Compaq Pressario C714NR notebook pc and have everythingworking. This is the first version ofunbubtu that I have been able to figure out how to make everything work. Thanks for the list I have been wanting to try wine but never liked any version of linux enough to stick with it for more than a few weeks until now.
Hi,
This is what i was looking for my new site.
Simple setting in php.ini file
With suggested mysql backed memcached as session handler, there still exists problem with concurrency between simultaneous requests overwriting each others $_SESSION variable. On a multi-frontend environment client’s rapid consecutive ajax requests might end to different frontends accessing and modifying $_SESSION’s contents at same time. And when these frontends push their data back to shared memcached, last one overwrites all previous changes.
The problem is not actually with memcached or mysql, but on using the $_SESSION that can be operated only as a single entity. It’s good for reliably locked simple single server installation, but not for distributed environments.
I would recommending scache’s approach ( http://scache.nanona.fi ) to completely drop using the generic session handler and instead split data on small enough fragments to reduce risk of threads overwriting each others data.
Can anyone let me know, which one is better if I have dedicated hosting and also which one is faster in performance ?
Does anybody tested/benchmark-ed the performance of PHP script using file-based vs InnoDB-based session handlers ?
Here I found an implementation of MySQL-based session save handler:
https://github.com/netom/php-mysql-sessionhandler/blob/master/mysql-session.php
Beware that storing the session in the database can be a huge performance hit if you’re using the session a lot and have high traffic.
It’s great if you’re only storing authentication and userid for example, but if you’re using it to remember state and parameters for every page, then a high number of users will quickly chew up your CPU cycles.
I totally agree. Moreover I don’t use sessions in database even for small variables size. I set up separate file system for ’em with some special options – lazy writes, etc to allow frequent session updates to be cached in the RAM.