November 24, 2014

PHP Sessions – Files vs Database Based

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(‘‘,10) and in in the end of the request
SELECT RELEASE_LOCK(‘‘) where session_id is current session identifier. Note – setting this external lock on session name should be done before session data is read from database for things to work properly.

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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. pathan sms says:

    Hi . Hello i am enjoying.

  2. pathan sms says:

    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.

  3. pathan sms says:

    Hi,

    This is what i was looking for my new site.

    Simple setting in php.ini file

  4. dvh says:

    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?

  5. bobby says:

    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.

  6. Dmitry says:

    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… ;)

  7. Rasmus says:

    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!

  8. peter says:

    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

  9. peter says:

    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.

  10. catalinux says:

    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.

  11. Dmitry says:

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

  12. peter says:

    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.

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

  14. peter says:

    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.

  15. Dmitry says:

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

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

  17. We store our PHP sessions (and everything else we can) in Memcached.

    I would HIGHLY recommend it.

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

  19. hamish says:

    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!

  20. Heather says:

    What is the difference between file based applications and database systems and the pros and cons?

  21. Well of course sessions are more suitable in database than in files.

  22. madhuka says:

    What is main concept of Seassion in PHP

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

  24. Learn PHP says:

    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

  25. bash says:

    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.

  26. Pramod says:

    Hi,

    Looks like a great article as i want hunting for sharing the same sessions over subdomains.

    (Pramod Mane)

  27. Hi,

    This is what i was looking for my new site.

    Simple setting in php.ini file

  28. session store memcache is a problem :count sessions ?????how to?????

  29. iko says:

    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.

  30. Digital Lynx says:

    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.

  31. koshker says:

    I cant find function GET_LOCK on php.net :(

  32. peter says:

    Kosher,

    This is MySQL function not PHP

  33. Seo Lahore says:

    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.

  34. Stefan says:

    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.

  35. Can anyone let me know, which one is better if I have dedicated hosting and also which one is faster in performance ?

Speak Your Mind

*