Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issues with session table in database for large site #19146

Closed
feltkamptv opened this issue Dec 23, 2017 · 15 comments

Comments

Projects
None yet
6 participants
@feltkamptv
Copy link

commented Dec 23, 2017

On large spikes in user traffic our database gets stuck due to the session handler in Joomla. We get hundreds of the same query waiting for the lock to be released on the rows in MySQL. This is seen with show processlist:

DELETE
FROM vk9wz_session
WHERE time < '1514040959'

We have a few million visitors a day on our Joomla site and have continued upscaling our MySQL server, but this does not seem to fix this issue. After a while the max number of connections is hit and the site breaks.

System information

  • Load balanced servers on AWS
  • MySQL server with 64 GB mem and 16 CPU
  • Cloudflare

We are going to look into using Memcached to store the sessions and hopefully that fixes everything. Using database as session handler for a large Joomla! site seems to be an issue.

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 23, 2017

We use PHP 7.1 btw. We already use Memcached successfully to save other cache items in our site, but does it work for sessions? Because after switching to Memcached for the sessions, we cannot login anymore into frontend and backend.

@alikon

This comment has been minimized.

Copy link
Contributor

commented Dec 24, 2017

try using redis for session

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 24, 2017

We will try out redis later. We have now memcached working, unfortunately the sessions table is still being used, but the hundreds/thousands of delete session queries have disappeared. We are still looking into disabling guest sessions without hacking Joomla, which should also help a lot for performance.

Conclusion seems to be for now: do not use database as session handler for sites with lots of traffic.

Happy holidays everyone.

@mbabker

This comment has been minimized.

Copy link
Member

commented Dec 24, 2017

#19165 should be able to assist with this.

However, note that Joomla internally is reliant on sessions and therefore it is essentially impossible to disable them in full. Likewise, Joomla arbitrarily logs certain metadata about the session regardless of the handler in use (there is a proposal for 4.0 to be able to turn this off however it hasn't seen any activity in quite some time) therefore there needs to be some logic to clean this data out of the database as it expires too. The linked PR tries to help with that part of things.

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 25, 2017

Unfortunately Memcached is giving problems too. Users are being logged out/in randomly. We only use a single Memcached instance with ElastiCache because a cluster doesn't work (missing autodiscovery).

We are now writing our own session handler, based on DB, used only for public site and disabled for our webservices.

Is it just as simple as copying the DB session handler file, renaming the file and class and adding it to config?
public $session_handler = 'databasecustom';

How does Joomla.org solve these session issues?

@joomdonation

This comment has been minimized.

Copy link
Contributor

commented Dec 25, 2017

If I understand your issue correctly, you can try this solution:

  1. Apply the change from PR ##19165 which @mbabker proposed

  2. Change php.ini setting session.gc_probability to 0

  3. Setup cron job to run this PHP function http://php.net/manual/en/function.session-gc.php

Basically, with that setup, the DELETE command which is using to delete session data won't be run base on user's requests (which is a lots because you have high traffic website but base on pre-defined amount of times you configure for cron job. In theory, it will reduce number of DELETE command and hopefully, it will solve your issue.

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 25, 2017

@joomdonation Thank you for your answer! Even on Christmas! This looks like a very good solution and we are looking into it right now!

@joomdonation

This comment has been minimized.

Copy link
Contributor

commented Dec 25, 2017

One thing I should mention is that PR #19165 only support database handler now, so you need to back to use database handler if you want to use this solution.

@mbabker

This comment has been minimized.

Copy link
Member

commented Dec 25, 2017

How does Joomla.org solve these session issues?

I don't know what the JED or forums do for sessions, but the sites that I'm running are all using the PHP filesystem handler and Joomla's conservative caching enabled, they are handling things pretty well in general.

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 25, 2017

Most of our Joomla sites are on shared hosting and do also use the filesystem handler, but for loadbalanced sites we use the database and that has been going well, except for this site only which has lots of traffic.

We are now implementing the PR and testing the solution of @joomdonation. The application runs on 130+ servers within 12 server groups, so it's going to take some time to test and deploy.

@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 26, 2017

@joomdonation @mbabker Thank you very much! We deployed the solution to our platform and have a cron job running every minute calling the session GC function. Our database connections are down to low levels again and the site is on fire! We will keep on monitoring everything, but hopefully we do not encounter any issues anymore with this.

Thanks for the quick support (even with Christmas), it is very much appreciated! Happy holidays!

@franz-wohlkoenig

This comment has been minimized.

Copy link
Member

commented Dec 26, 2017

@feltkamptv can you please close the Issue as its solved?

@feltkamptv feltkamptv closed this Dec 26, 2017

@joomdonation

This comment has been minimized.

Copy link
Contributor

commented Dec 27, 2017

@feltkamptv Good to hear that it helps solving your issue and happy to hear that Joomla can handle that kind of big site.

Just want add that you might want to check your cron job and make sure it clean up expired session data. As I understand (and quick tested), if your cron job just run PHP script like below, it won't trigger gc method of custom session handle which we are using in Joomla (database in this case)

session_start();
session_gc();

To make sure it works, I think your cron job will need to trigger a CLI application in Joomla. I my test, I modified cli/garbagecron.php to the below code, run it and gc method of database storage handler is triggered properly:

public function doExecute()
{
	$session = JFactory::getSession();
	$session->start();
	session_gc();
}
@feltkamptv

This comment has been minimized.

Copy link
Author

commented Dec 27, 2017

We already had it working, because we noticed the DELETE query issued by the cron job in the DB. We initialise the Joomla site application and session on the request, but just to be sure we changed it to your method.

We have a lot of cron jobs and background tasks (not through cli) which work on Joomla framework but they do not need to use a session. Being able to disable sessions for some scripts would be a nice to have, to decrease the session inserts to the DB. Something like this:

$params = array('session'=>false);
$mainframe = JFactory::getApplication('site', $params);
@mbabker

This comment has been minimized.

Copy link
Member

commented Dec 27, 2017

Some code already exists for that in the application constructor, see

// Enable sessions by default.
if ($this->config->get('session') === null)
{
$this->config->set('session', true);
}
// Set the session default name.
if ($this->config->get('session_name') === null)
{
$this->config->set('session_name', $this->getName());
}
// Create the session if a session name is passed.
if ($this->config->get('session') !== false)
{
$this->loadSession();
}

But, Joomla\CMS\Factory::createSession() isn't aware of this config value so even if the application is told to not initialize the session then the first call to Joomla\CMS\Factory::getSession() will do it arbitrarily. Unfortunately Joomla is heavily coupled to a session so there isn't a way to kill them off in full.

In 4.0, things are a little better. The session service will use a runtime based storage system that doesn't actually create a persistent session for command line environments, so CLI utilities written using the new Console package or at least defining a CLI application environment will not start "real" sessions.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.