Processwire site running very slow

[Nishant]

Hello All,

My processwire website is running very slow, I had a word with my server support team and they said that there are multiple "GET_LOCK" queries running which is causing the server to slow down.

Attaching a screenshot of the MySQL queries below

if anyone had similar problems or know the solution then please do help.

[netcarver]

Are you running the SessionDB Handler module?

[Nishant]

Yes @netcarver, I am using the SessionDBHandler module

[netcarver]

It might be that module as it uses GET_LOCK. Is it feasable to switch this site back to using file-based sessions?

[Nishant]

26 minutes ago, netcarver said:

It might be that module as it uses GET_LOCK. Is it feasable to switch this site back to using file-based sessions?

Yes, I have uninstalled that module and the site seems to be working fine now. Will test for couple of more days and see if that was the primary cause.

[netcarver]

@Nishant happy to hear you have your site up and running again. Out of interest, and to help try and track this down...

1. what version of the SessionDBHandler module were you running (if you happen to know?)
2. are you using the MyISAM or InnoDB engine in your MySQL/MariaDB installation?

[teppo]

Just wondering if it might be related to this: https://github.com/processwire/processwire-issues/issues/1760. Basically on some systems (mainly Ubuntu) sessions are cleared in a somewhat non-standard way, which works for disk based sessions but not work SessionHandlerDB. Site running slow could mean that the sessions table has grown so large that queries take very long time.

If that's the case, you may want to apply the solution that Bernhard suggested in aforementioned issue. Or alternatively disable SessionHandlerDB.

(Just for the record: in most cases I would advise against database sessions, unless there's a specific reason for them. Disk is usually — in my experience at least — faster and also tends to have fewer quirks. If disk based session storage is not an option, I would look into setting up Redis for session storage.)

[bernhard]

15 hours ago, teppo said:

(Just for the record: in most cases I would advise against database sessions, unless there's a specific reason for them. Disk is usually — in my experience at least — faster and also tends to have fewer quirks. If disk based session storage is not an option, I would look into setting up Redis for session storage.)

This is interesting. I thought it would be the other way round. Thought that ryan wrote that somewhere and also thought that as SessionHandlerDB is newer it has a reason that it has been built and would therefore be preferable over disk sessions.

Do you have any more insights/references about your statement? Maybe I should switch ? 

[teppo]

10 hours ago, bernhard said:

This is interesting. I thought it would be the other way round. Thought that ryan wrote that somewhere and also thought that as SessionHandlerDB is newer it has a reason that it has been built and would therefore be preferable over disk sessions.

First of all, there are other benefits than performance — such as the fancy process view that SessionHandlerDB comes with (if I recall correctly, it's been a long time since I've tried it). Also if you have multiple servers but shared database, it may be easier to use DB sessions. (We use Redis in part for this reason.)

I don't have any numbers, and I'm afraid those wouldn't be very helpful. Performance depends on a number of factors and thus numbers from one use case may not be applicable to another. That being said, in my personal experience for typical use cases disk is often faster and has smaller chance of running into scalability issues; I've never had scalability issues with disk based sessions, but I've run into them multiple times using database sessions. Though again, that's just my experience from the setups and use cases I've worked with ?

Overall, comparing session files stored on local disk vs. MySQL/MariaDB that stores data on the same disk, I would expect database to have more overhead; it has to do much more than just read a file from the disk, after all. But then again database can make use of in-memory caching to mitigate such issues. And of course if your database is on a separate machine (or a faster disk) that would again change things, though that's also where latency due to connections and data transfer may step into the picture.

Finally, the native PHP session handling mechanism is in some ways less likely to cause issues, especially compared to something you've cooked up yourself. (Just for the record, PHP has built-in support for storing sessions in Redis, so I would consider that "native"). It should probably be noted, though, that if you let PHP handle garbage cleaning, that is likely to cause some amount of overhead; the approach that Ubuntu takes (separate cron job) does not suffer from this, at least not in the same way.

My personal preference for session storage is Redis — which, again in my experience, is also the fastest option of those mentioned here — and if that's not available then disk ?

[Nishant]

On 6/26/2023 at 6:27 PM, netcarver said:

@Nishant happy to hear you have your site up and running again. Out of interest, and to help try and track this down...

1. what version of the SessionDBHandler module were you running (if you happen to know?)
2. are you using the MyISAM or InnoDB engine in your MySQL/MariaDB installation?

Hi @teppo @netcarver,

1. The version for SessionDBHandler was 0.0.6
2. Database engine is InnoDB

[Nishant]

On 6/26/2023 at 9:02 PM, teppo said:

Just wondering if it might be related to this: https://github.com/processwire/processwire-issues/issues/1760. Basically on some systems (mainly Ubuntu) sessions are cleared in a somewhat non-standard way, which works for disk based sessions but not work SessionHandlerDB. Site running slow could mean that the sessions table has grown so large that queries take very long time.

If that's the case, you may want to apply the solution that Bernhard suggested in aforementioned issue. Or alternatively disable SessionHandlerDB.

(Just for the record: in most cases I would advise against database sessions, unless there's a specific reason for them. Disk is usually — in my experience at least — faster and also tends to have fewer quirks. If disk based session storage is not an option, I would look into setting up Redis for session storage.)

Not sure if the session had grown large, as after uninstalling the SessionDBHandler module, the table is also removed from the database. But last time I checked under processwire panel, It was showing somewhere around 200 active sessions. (I have an RDS and ASG setup on AWS, and on the load tests it was able to handle 10000+ concurrent users. Not sure if 200 active sessions can slow down the performance so drastically)

[bernhard]

2 hours ago, Nishant said:

I have an RDS and ASG setup on AWS, and on the load tests it was able to handle 10000+ concurrent users.

That sounds like an interesting setup. I'd be very interested in a tutorial/showcase if you want and find the time ? 

[Nishant]

1 hour ago, bernhard said:

That sounds like an interesting setup. I'd be very interested in a tutorial/showcase if you want and find the time ? 

Definitely, I will have a word with the server team and can gather the required details you need for the tutorial

[teppo]

8 hours ago, Nishant said:

Not sure if the session had grown large, as after uninstalling the SessionDBHandler module, the table is also removed from the database. But last time I checked under processwire panel, It was showing somewhere around 200 active sessions.

If you stick with DB sessions you should see this pretty soon. If sessions are not cleared properly, data will likely just keep accumulating.

I'm not sure if stale data remains viewable in admin, so might want to take a peek at the database as well, just in case.

[Nishant]

@bernhard @teppo @netcarver, though disabling the SessionHandlerDB module resolved the website speed issue, but that created another major problem. As my site is running on an ASG setup, there are multiple server instances running and delivering site contents at the same time. Now the session is stored in the file system, it randomly loggs-out the processwire user when switching between the instances (Switching of instances happens on its own, we can't handle it, if I turn off all the instances and keep only 1 then it will solve the problem, but will create performance issues as the website has large number of visitors).
The only possible and reliable solution is to store the sessions on the database, do ProcessWire supports this natively without using any module?

 

[flydev]

HI, I am jumping in the thread. Just some questions. (By ASG did you mean Azure ?)

- About the query SELECT GET_LOCK:

• What's is the database server version, model (MySQL or MariaDb) and the database server max_connection settings value ?

- Using file system based sessions, ask the team details about:

• Value and permissions set of the php.session.save_path and the number of files in the target.
• Value of php.session.save_handler ?

 

Edit: FI, I just checked on three setups using file system session with the bug-issue-1760, there is 4416, 7428 and 5651 session-files and growing without slowing down the system at this moment of writing. 

Edit2: @Nishant There is also an article that could be interesting and might help of tweaking the session database handling:

https://processwire.com/blog/posts/pw-3.0.175/#read-only-and-db-driven-sessions

Edited June 30, 2023 by flydev
numbers // pw article link

[Nishant]

Hi, @flydev,

ASG stands for Auto Scaling Groups. (Basically there are multiple server instances running and delivering the website content. In simple terms, let's say the website is running on one server and the CPU usage has hit 60% it will automatically activate another server instance and now two instances will distribute the work.)

Below are the required details,

• Database: MySQL
• Server version: 8.0.23 - Source distribution
• max_connections: 1000
• session.save_path: /var/lib/lsphp/session/lsphp81
• session.save_handler: files

regards

[flydev]

Ok, thanks for the information, I thought that you was talking about Azure Security Group which reminded me of the session handler issue (I missed the AWS details on one of your previous message).

I understand now better about the logout issue you encounter with file handler and you should avoid it. I am not experienced with RDS, and maybe @nbcommunication could have a better insight regarding his scaling setup presented recently on the forum.

[nbcommunication]

Hi @flydev,

Unfortunately we're not there yet with the scaling setup.

However this is an interesting thread, this could be potentially related to https://processwire.com/talk/topic/26070-error-exception-unable-to-obtain-lock-for-session/ which we've never been able to figure out.

Cheers,

Chris

 

[teppo]

There are basically two options here:

1. Use a separate session storage. That could be MySQL (in which case you may need to beef it up even more), or it could be something else, e.g. Redis. Redis is relatively easy to configure as a session storage: set up a Redis server, make sure that you have Redis extension installed for PHP, and point PHP to the Redis server via settings in php.ini.
2. Store files on shared disk. From what you're describing it sounds like the disk is not shared in this case, which is not going to work for session files. With AWS it would typically mean EFS, i.e. making sure that the location where session files are stored is mounted on EFS.

If you were already using SessionHandlerDB, the easiest approach would probably be to keep using it, unless the scalability issue is basically instantaneous. Just make sure that session data is cleared automatically, so that the size of the session table won't become a bottleneck.

The key thing here is that if you're, for an example, using Ubuntu (or similar setup) then PHP may have session garbage cleaning disabled by default and instead it is handled via cron job. This job will not automatically apply to SessionHandlerDB, so you would need to either a) tweak PHP settings and enable normal garbace cleaning (as Bernhard mentions in issue https://github.com/processwire/processwire-issues/issues/1760) or b) set up a custom cron job or something like that to clean up SessionHandlerDB database table manually.

[teppo]

By the way, how do you currently handle site assets? Are those shared between instances?

[flydev]

Wouldn't the system be too slow with EFS? It's a network drive, isn't it? Curious to see how locks are managed with this setup.

I read a bit about ElastiCache which support Memcached and Redis protocols you ( @teppo) suggested, it look like a great service, with almost no code modification needed.

[teppo]

16 hours ago, flydev said:

Wouldn't the system be too slow with EFS? It's a network drive, isn't it? Curious to see how locks are managed with this setup.

EFS has been getting considerably faster in recent years, but yes, there is always latency. Whether it's noticeable/problematic, I don't really know.

To be clear using EFS for session files is not something I've ever done myself.

[Robin S]

On 6/28/2023 at 1:33 AM, teppo said:

First of all, there are other benefits than performance — such as the fancy process view that SessionHandlerDB comes with (if I recall correctly, it's been a long time since I've tried it).

For anyone interested, I've released a module that provides information about active sessions similar to SessionHandlerDB, but for file-based sessions:

 

[bernhard]

Hey @Robin S very cool! Thank you ? 

I'm wondering how you guys are using this information and why? As you have built a module for it there seems to be a need for it, right? I've always found that interesting to see, especially when I had to deploy something to a live system and wanted to see if there are any active users that might get kicked out by the deployment. But maybe there's more to it? Can that information be helpful in other situations as well?

Thx ?