Most liked posts in thread: SQL error

I think everyone does it tbh even the likes of myself where I know better. You are so used to sites loading at a certain speed that when they don't you start thinking 'is it my device or connection' so you keep refreshing to make sure

 

I'm assuming you know what a database is, in a database you have tables and inside the tables data is stored. One of the tables for the site is a session table, this stores certain persistent data on each user which along with cookies is what prevents you having to login every time you load a new page. Each session entry has an 'expiry' date which if you haven't set the 'Stay Logged in' option on login is why you will get logged out from time to time.

In MySQL which is the database the site uses when a table's data becomes corrupted it gets labelled as 'crashed', on the 11th the final time the site went down before I increased the resources when MySQL came back up it logged an error of the Session table being marked as crashed. This was preventing the session entries from expiring, at this moment we have 3,057 active sessions but last night it was over 150k because over the last 10 days it hadn't been able to expire sessions due to the corruption. There was also duplication taking place of some of your session data making it worse and causing additional issues.

As long as the cause was just the issues on the 11th and not something else it should now be fine as I repaired the table which allowed it to expire the entries it needed to and shouldn't corrupt again

 

Just before anyone posts about errors, I know. I am just applying some security updates

 

Yeah we had a spike in disk activity which then caused a cpu bottleneck, trying to work out what caused it but it seems to have dipped again now

 

Already floated that to @king karl in email

 

Going to wait until after the match as the site may go down for a bit but we are going to bump the CPU resources up. Hopefully, that will also solve the RAM usage

 

Those are the probably the kind of games where increased usage is easier to predict. If there was a match like those I would probably spinup a brand new instance with significantly more resources and then migrate the site over for the day. Then the next day when usage falls back to normal migrate it back

It's the unexpected ones that are the problem as you assign resources so that it can support peak but where you are only using 10% for 6 days and 21 hours

 

It seems like it was a different error that was caused by the last time. During the previous issue, it seems one of the Database tables was marked as crashed. It should have repaired itself when the next restart happened (when I added more resources) but the cause of it being marked crashed was preventing either an auto repair and worse of all from it purging session data it no longer needed. So the table just kept getting bigger and it seems we hit that point where there is one too many records and when it tried to write that it caused a restart for the database. When it came back up it was fine until that next person without session data in the table already accessed it so it was restarting almost as soon as it came back up. Its why it handled Saturday when more were on fine as the table was still within range

Anyway repaired the table. It should clear out the old sessions and now be fine