Database issues - occurring occasionally - merged topic for 2015 and 2016

[whole diary]

[JayMac]

Can we please move to a more stable platform?

The frequent down times are making the use of the forum less and less enjoyable.

I had some time pertinent info I wished to post last evening. Little point doing so now.

[grahame]

Can we please move to a more stable platform?

The frequent down times are making the use of the forum less and less enjoyable.

I had some time pertinent info I wished to post last evening. Little point doing so now.

We have a thorough and frequent backup procedure in place, and as the forum has grown and the server got busier, there are becoming times that no connection is available.  At these times, it's not so much of a stability issue but rather like turning up at the station expeccting to find there's always a train waiting with spaces available, and finding that the only one there is already filled.  I will take a look at renicing the backups to help, but changing the platform to sort it ... (a) what do you suggest we switch to, (b) Who's resource will do it, espcially data conversion, and (c) it would be a real "bull at a gate" way if we can re-arrange the background stuff instaed.

There *are* some occasional database issues - I'm sure they're not what you're taking about as they're effecting us perhaps once a month, and you said "frequent", and there wasn't such an issue last night - there was, though, this morning and hence my comment on this thread.  I would dearly like to avoid these completely, but little information / evidence is left around to tell me what's happening;  I should probably take the server down, install a fresh database version and fresh PHP ... but I wouldn't like to predict if that would result in no change, an improvement, or fresh problems as I made the changes which forced me to roll back the work I did.  Complete guess.

I have looked at Google Ananlytics since 1st January, and I can only see one major disturbance visible in the pattern - therefore concude thet it'a mainly the first issues that's frustrating you.

[JayMac]

and there wasn't such an issue last night

Sorry, there was.

Yesterday evening at around 9pm I tried to read unread posts. That could not be done. 'Database error' message. I contacted an administrator as the message requested, to say the forum was down. There were no posts between 8.55pm last night and 10.08am this morning. Very quiet or inability to post? I certainly couldn't post, logging in from different locations (home and mobile 4G), just to check the problem wasn't my end.

Same if I clicked on individual boards. Couldn't read them. I could only read posts by going to 'Forum Stats' at the foot of the homepage and clicking on 'View the most recent posts on the forum'.

Unable to start a topic either as the 'Database Error' was shown.

Of all forums I contribute to, this one has more down time than others. Hence it is a more frequent occurrence compared to them. Comparing this forum's down times to trains at stations is a specious analogy and not at all relevant.

I spend a lot of time on this forum. Would you like me to record all instances when there are 'Database error' or 'Connection Problem' messages? They are regular and frequent. More so in recent months than the distant past. I not sure whatever analytics you are running are picking up these frequent problems.

I fully appreciate the forum is hosted by yourself grahame and you bear all the costs and have to fix the regular problems. There are ways of addressing that though.

[ellendune]

For the purposes of understanding the issue and not wishing to put pressure on you Graham. I too as unable to access posts on the forum from about 8:30pm last night though strangely I could get the home page. 

[BerkshireBugsy]

For the purposes of understanding the issue and not wishing to put pressure on you Graham. I too as unable to access posts on the forum from about 8:30pm last night though strangely I could get the home page. 

I'm not privy to the inner workings for this forum but could the home page work because it does not have dynamic content which needs to be pulled from the database?

[grahame]

To confirm - there are two issues.  One is connections running out (in effect server overload) during backups and I have added further backups recently, and the database has grown - which tips the balance.  These connection errors can show up in odd ways as (I think) the software grabs several connections and / or takes them over from previous connections in the same thread.  This can be fixed be reducting the backups / non backing up / lowering the pririty of the backups, and I am inclined to do the latter as previously explained - but not in the middle of the day.

The second issue is the odd database faults.   I was on the forum soon after midlight last night, for about an hour.  I didn't attempt to post anything but had no issues in what I did.  I'm aware of problems just after breakfast time this morning, which I think I've fixed but as I was travelling it was a quick and finger-crossed fixed.

It seems from reports (and there are now several of them, and I believe yous!) the problem actulaly occurred yesterday evening, went away or was in areas I didn't explore, and came back this morning.   I'm not meaning to sound 'odd' about that - it reads improbable, but it probably did happen.

First thing for me to do is to hit the overloads. They may well be the root of the problem - the trigger. I have, however, another train to catch and there's a big gap to the next one so be back later.

[ChrisB]

Personally, I think it needs saying that I'm perfectly happy with Graham's offering to us....it's his time & more importantly, cost, that keeps this going.

As I don't contribute to this time or cost, I'm happy with what is offered by way of up-time.

If I was paying towards this, then comments upthread would apply, but as I'm not, they don't.

Thanks for keeping this going Graham!

[BerkshireBugsy]

Personally, I think it needs saying that I'm perfectly happy with Graham's offering to us....it's his time & more importantly, cost, that keeps this going.

As I don't contribute to this time or cost, I'm happy with what is offered by way of up-time.

If I was paying towards this, then comments upthread would apply, but as I'm not, they don't.

Thanks for keeping this going Graham!

Seconded. Couldn't agree more!

[grahame]

Many thanks for all those +ve messages ... overnight I'll take a look at the backup schedule and priority which I think may trigger the failures as well as running connections out.  As the problem isn't triggerable (and appears to right itself sometimes!) though I'm on educated guesses so cannot promise a fast solution - nor to know when it's fixed.  One of those with potential to be longwinded.

I do appreciate the apparent frustration that started this current discussion I would feel a bit the same ... I don't think you would be surprised, bignosemac, to know that I find this issue frustrating too and would really rather not have the issue(s) manifesting as it/they are.

[grahame]

... overnight I'll take a look at the backup schedule and priority which I think may trigger the failures as well as running connections out.  ...

One of the very unfortunate things about running a forum to which the public can post is that you have to be defensive - not only to protect your data from corruption by sotware bugs, but also from user (and admin!) errors, from automata that are erronously or maliciously loading the site, from users and potential users looking to use the sit efor purposes outside your stated remit (and perhaps outside the law) and indeed from the occasional 'inside' user who goes off the rails.  We have a number of flags and check processes running to help alert us to things goind wrong, a number of tools we can pull in when we suspect a problem to analyse and/or gather extra data and send up email alerts. At this point I can assure you I have zero evidence of any current / recent / ongoing forum / server activity arisng from any members, though one person who we had to ban here 7 years ago has been around and with some not-entirely-positive stuff on a page I look after hosted elsewhere.

Mechanisms are in place not only to alert us to issues should they occur, but also to ensure that (should they occur) we have a current "checkpoint" / data we can revert back to.  For the mostpart, these are "backups" - data copies taken from time to time so that we can roll back to a known point if the need arises.  There's a considerable degree of pragmatism needed here - judgement as to risks than can be taken and how far back data can slip in particular circumstances.  I'm sure there are those who will remind me that with a second server we could mirror all data, and that with a third server we could introduce a system which alleviated problems where one system went bad and damaged the second - but I would remind them of extra time resource and cost in doing this, which could be considerable.

As it stands, our main databases are backed up on the server three times a day, our most dynamic tables twice an hour, and our web sites and local OS^▸ (Ordnance Survey) application installations twice a week, wth a further full backup once a month. Certain backups are automatically copied to another system in a different location so that we can still get running again even if there's a catastrophic failure - but the data will almost certainly be more than just 30 minutes lost.

As time goes by, resources build up. You're looking at 190,000 posts and 12,000 personal messages in the dynamic tables. Plus 10,000 images (tend to be quite large) ... and at present the compressed backups are around 50 Mbytes for dynamic, 3.7 Gbytes for databases and 29 Gbytes for web sites on our shared server with local OS folders.  Not only do these backup operations take space, but they take io and cpu time, and compression takes noticable cpu resource. Each table is locked while being backed up - done to ensure the integrity of each backup, and we have to keep jobs waiting for the lock to release if they come in at that time.  We then have to decide whether to do a short, sharp compression that will continue to effect the server significantly, or to compress at a lower priority having less effect for longer.

If you take a look at http://www.wellho.net/mouth/3027_What-proportion-of-our-web-traffic-is-robots-.html , you'll see how the loading of our system was in 2010 from an image I caputured then, and how it is now (Feb 2016 as I write). Overnight, I have moved around a number of the backup times to clear the 3 x per day from happening at peak time, through our peak is not as sharp as a commuter peak so there is still going to be some of this activity at busy-ish times. I do bear in mind schemes like update logging and incremental backups, and I would welcome input from one or two trusted techically competent members to brainstorm pragmatically through the various issues; I have done some further research (not published here) on which peer review would be good, and I'm trying to make just one set of changes at a time in order to see / learn the effect of each.   We chose with this forum to carry on with existing databases and software where others have archived old forumns and dropped historic content from easy access ... so we're certainly not alone in having to consider these issues.  I'm glad of the opportunity afforded by the request yesterday to take a look at this, and note the follow ups from the requester.  I still await an answer as to what software to switch over to and proposals as to how to resource that, but I suspect that such routes will be paved with significant resource requrements and implications. But I'm getting no younger and as and when the time comes I would look to seeing a new generation or a new method take on the forum's role, and if a robust, supported way of doing that (including technical skill and resources) should come from the member(s) who would like to see an end to resource limits and backup queues, then that's a positive step.   The irony of the whole thing is, of course, that less that 1 in 100 backups is even looked at after it's completed!

[TaplowGreen]

Keep up the good work Graham!

[grahame]

Thanks for keeping this going Graham!

Thanks for keeping this going Graham!
{like} liked by GBM, IndustryInsider, 47714, John R, Chris from Nailsea, Bob_Blakey, rogerw, ellendune, Timmer, PhilWakely, SandTEngineer, Richard Fairhurst, Surrey 455, trainer, tomL, Phil Farmer, Oxman, old original, 81F, Oxonhutch, Adelante_CCT, Four Track, Now!

My pleasure - I'm touched.  We've only once had a post with more likes!

[Red Squirrel]

Can we please move to a more stable platform?

Maybe Platform 14 at BRI^» (Bristol Temple Meads - next trains)?

[SandTEngineer]

Can we please move to a more stable platform?

Maybe Platform 14 at BRI^» (Bristol Temple Meads - next trains)?

Yes, good idea https://s-media-cache-ak0.pinimg.com/236x/7f/35/93/7f3593b1f72e0889c6825de3f3943053.jpg 

[ellendune]

Can we please move to a more stable platform?

Maybe Platform 14 at BRI^» (Bristol Temple Meads - next trains)?

Indeed it has been stable - in that it has been stably absent for many years.