OnLineEntries ... Going Down

General slalom chatter...rant about the bad, rave about the good
Post Reply
Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

OnLineEntries ... Going Down

Post by Dee » Thu Jan 14, 2021 11:36 am

Not permanently, but I need to upgrade the database and it will be easier and safer if I can take the system down and then take backups etc before doing the work.

All being well, I plan to take it down Friday.
It might be up by Sunday, but if I hit any awkward snags it might stay down for a couple of weeks whilst they get resolved.

I was not going to say anything but in spite of lack of slaloms there seems to be a few different people logging on most days.

Wish me luck!! :P

(PS for the techies - I'm upgrading MySQL from 5.5 to 5.7, hints and tips welcome (it is possible that I might also need to upgrade an npm package that uses mysql to one using mysql2 if I run into problems, but hoping I can do this as a separate exercise afterwards)
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

JFNG
Posts: 6
Joined: Sat Jan 09, 2021 4:26 pm

Re: OnLineEntries ... Going Down

Post by JFNG » Thu Jan 14, 2021 9:46 pm

Do you have a staging environment? This would really help you in this situation, as you can prototype changes to the the core infrastructure / codebase without breaking real stuff! You could even switch to failover infrastructure while you complete the work on the core infrastructure, then revert back once you have verified the update was successful.

With regards to Mysql 5.5 to 5.7, I have not heard of any difficulties (as of yet anyway). We migrated the majority of our customers last summer, and even some from MS SQL without issue. Keep backups at every stage though, just to be safe! If you are using Oracle, and have a support contract in place (not sure if you would given the nature of the platform), then now would be the time to use the tools that they provide for this very task!

A little disclaimer, my company develops cloud systems, and runs a datacenter with public cloud access, so feel free to drop me a message and we can go through it together! I would also be happy to support the platform, by providing free computing space if you are interested?

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Thu Jan 14, 2021 10:09 pm

Thanks

We currently use AWS for hosting the db, which I've paid for in advance to keep costs down, though moving could be something to consider in the future. Support is somewhat limited and their user interface is a bit daunting (I'm really more of a Project Manager/Business Analyst than a true techy - I've pretty much learnt the tech to develop the system (with some help from Forbes who helps when I get completely stuck and deals with the main security stuff)). AWS will force the upgrade shortly so I will quickly reach the point where a fall back is not an option.

Although we have a staging schema we only have one db instance. I could create another db instance in new version and import the schema to test, but this would be extra work for an upgrade that in theory at least should be reasonably low risk. Also given the current state of slalom I don't think that the system being down even for a few weeks is likely to have any impact :( so I'm going for the "easy/lazy" route and diving in the deep end.

I will be taking lots of dumps and backups so it should all be ok. (Famous last words)
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

JFNG
Posts: 6
Joined: Sat Jan 09, 2021 4:26 pm

Re: OnLineEntries ... Going Down

Post by JFNG » Thu Jan 14, 2021 11:29 pm

Are you using AWS EC2? Either way their support is almost non, unless you have a support contract in place. When their US-EAST region had a bad week a few months ago, they told us that it was our clients fault for not 'developing highly available infrastructure across multiple zones', which of course translates directly to 'should have bought 3 times as much'. Thankfully the client finally saw sense and moved everything onto our platform :)

I must say that you are in a position of luxury, being able to take a platform offline for weeks at a time without complaints, if only that were the case in all IT :D :D . But after all, I completely agree, people do not really need the platform given the current pandemic. Have you considered listing COVID safe events / training facilities that have been made available available to athletes overseas? Alongside general information on how to travel legally as an athlete?

Either way, I would be more than happy to help you run the migration, its what I live and breath all day :) I can send my details over in a PM if its any help?

Good luck!

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Fri Jan 15, 2021 6:15 pm

Currently using a db.t2.micro on eu-west, single zone.
Their pricing structure is as clear as mud, but once it was up and running it appears stable enough for our purposes and we could pretty much ignore the core db side of things (using workbench for schema management) ... until now! Trouble is their interface covers so many different types of systems and scenario that it is complex to navigate.

I started the lazy way and got an instant failure (not using the default parameter group). So I'm now creating a 5.7 instance and importing the data into that for testing (which is what I should have done in the first place :roll: ). I could leave the old system up in the meantime but taking it down means that I can know my dumps are still valid

As you say total luxury, being able to take the system down for a couple of weeks. In a normal year, it would be quite tricky to do so, as by the time the calendar is agreed, I need to initialise the competitions so that organisers can get the set up completed and by now we would be opening for entries. Even now most days there are a handful of people logging in.

I think the idea of listing Covid safe training facilities etc could be worth considering for the website at www.canoeslalom.co.uk if someone gathered the information, but realistically it just doesn't make sense to add it on to the entry system. www.canoeslalomentries.co.uk is primarily about entering and administrating entries for competitions whereas www.canoeslalom.co.uk is the go to place for general information. I think keeping the two website roles distinct is quite important and both parts have a useful role to play.
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Sat Jan 16, 2021 7:10 pm

Well, it is now backup and, as far as I can tell, everything is working again. :D

It seems to be running slower than usual which is puzzling me :? but I might just leave it to settle for now and have a look next weekend. It's really time I got dinner on now and I think I could do with a day away from screens tomorrow before returning to the grindstone on Monday! No rest for the wicked.

If anyone spots anything odd do let me know.

Thanks
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

JFNG
Posts: 6
Joined: Sat Jan 09, 2021 4:26 pm

Re: OnLineEntries ... Going Down

Post by JFNG » Sun Jan 17, 2021 6:12 pm

Do you have query cache enabled? And if so how much volatile object storage are you running to house it? I think I remember the requirements of MYSQL 5.7 being higher than 5.5, however we usually run super high spec systems for SQL, so I have never really researched the minimums much.

Have fun chasing :)

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Thu Mar 04, 2021 6:50 pm

Having checked this out ... query cache is deprecated in 5.7 and removed altogether in v8. Also reading various docs, from what I can understand (which is limited!), I'm not sure that it would help - implication is that it wars with InnoDb. Also our tables are small - the entries table (which is the biggest that we actually query from) is only up to 41K rows.

My aim is to go to v8 later in the year, so need to look for other possibilities!
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

djberriman
Posts: 806
Joined: Tue Apr 03, 2007 8:19 pm

Re: OnLineEntries ... Going Down

Post by djberriman » Fri Mar 05, 2021 9:22 pm

you might want to run ANALYZE TABLE on all your tables and see if it makes any difference.

If you continue to have a slow down I might be able to help as I've been a DBA for about 30 years and its the sort of thing I sort.

A quick google shows there are quite a lot of known issues with performace on 5.7, one of which is to stop it using certain indexes, this vaguely rings a bell as I had to force mysql to use a particular index on something I worked on recently to increase performance, that probably explains why I had to do it. Didn't understand it at the time.

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Sun Mar 14, 2021 7:42 pm

I think I have managed to speed things up quite a bit. I'm not sure that any of the slowing down was due to MySQL upgrade, so much as a combination of loosing some local caching due to a package change and the fact that I've not really tried to help performance at all over last couple of years and therefore not really taken into account the degradation due to all the extra information and functionality that I've added :oops: :oops:

I know there is more I can do, but it might have to wait a while now!
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

Dee
Posts: 1444
Joined: Sun May 08, 2005 8:34 pm

Re: OnLineEntries ... Going Down

Post by Dee » Sun Mar 14, 2021 7:45 pm

PS I should say it's only certain places that I've succeeded in speeding things up. There is still a lot that is much slower
Kit Washer, Entry Clerk, Chauffeur, Reluctant Organiser, Online Entry Advocate .....
Anything I post under this user is my personal opinion; I am not posting as a member of the Slalom Committee!

Post Reply