Sessions table got big

Hello,

I am running a laravel based site (laravel 7.28.4 on php 7.2) and I am battling with an issue that made the site quite slow. As I'm not the original programmer of the site I'm having a hard time debugging the issue since I really don't know where to start.

So here is a brief breakdown: The website was working just fine till the 27th of July. From that point on it seemed that we were getting increasingly more traffic on the site and CPU utilisation started climbing. It still was within reasonable limits though since we host it on a single VPS without any other sites to use resources.

You can see how the traffic spiked on the 27th of July and was starting to get worse as days were passing:

Today I setup a bot-fight rule on cloudflare (limit bot requests to 1 per 10 seconds) and also added the "I'm under attack!" setting on traffic comming from countries outside my own.

This is the result in the traffic:

It seems that rate limiting bots and unexpected traffic, helped a lot with the server. CPU usage is down to normal levels.

I saw that many of these bots seemed legitimate, like amazon bots and similar. But they caused a huge issue on the site. The site kept crashing even on low CPU usage and even with varnish cache enabled. The crashes were happening with messages like the following:

Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: update `sessions` set `payload` = YToxMTp7czoxMzoic3htUGFnaW5hdGlvbiI7aT---Partial Payload redacted---PhSI7fX19, `last_activity` = 1691428645, `user_id` = ?, `ip_address` = 17x.x.x.1x, `user_agent` = Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36 where `id` = FoCA---Partial ID redacted---XHiuP)

All issues were on transactions like that (Lock wait timeouts) on either update or delete SQL commands on the SESSIONS table. That did not use to happen before. It's something we picked up the past couple of days when bots were bombarding the site. The Table SESSIONS is 8GB now and contains 411000 rows more or less.

It's also really slow to run any MySQL command that involves that table. I have a table with 1000000 rows that is really fast to transact with and much much smaller, but this SESSIONS table is a pain.

What's the deal with that table? Is it ok if I wipe it clean and let it rebuild? Shouldn't that table clean itself? why do I need to keep a full record of all sessions with their entire payload? (each payload is huge!)