503 Service Unavailable error for confluence project

Our project https://confluence.org/ is currently inaccessible. The web server returns:

503 Service Unavailable

No server is available to handle this request.

@rf-confluence The issue was the same database issue as before. Site is back now.

FYI, it has gone down again (as of 2:39pm EDT Sunday)

@rf-confluence Whoever was running the problematic queries is probably continuing to do so. Site is back again for now. I’ll check again later.

OK, thanks, but please stop just blaming database queries. In principle, nothing should cause a database to hang and become unresponsive - just like nothing should cause an operating system to hang and become unresponsive. If the database is hanging because of certain queries, then it is at fault - most likely due to being underprovisioned in some way.

This is something that only you folks can figure out and fix, unfortunately, because only you have access to the database’s internal diagnostic/logging mechanism.

The only part of your statement I agree with is that we have not granted you access to the internal rdbms logging. We can, and frequently have, sent you examples of slow queries and logs associated with problems, and you have credentials to access your database and review its schema. Additionally, you control your application and the queries it makes against that schema.

We at ibiblio are doing our best to stretch limited resources for a range of public interest projects, which in the case of your project has meant allocating an entire four core VM with 8GB of RAM and rdbms, plus a separate VM with the httpd for your site, something that we do for very few collections. Please respect that above-average provisioning of resources by reviewing the information we have already given you, or by recruiting to your project individuals with the requisite expertise to review your queries.

I have reviewed your VMs and found that updates were pending, so I have just now applied those updates and rebooted. If you noticed a brief outage in the last 10 minutes, that was me rebooting your two VMs.

If you need help setting up a local-to-you development environment to evaluate your database schema and database queries for potential improvements, the best way to do that would probably be an RT ticket. Just let us know and we’ll set one up for you.

CM,

I really appreciate the work that you (and Karen Milberger) have put in to address this issue. The example query prefixes that you’ve sent me have been helpful (although full queries, rather than short prefixes, would have been better). I have used these examples to rework our web site (PHP scripts) to reduce the complexity of some queries, and offload some computation from the database to PHP - in order to try to work around the problem with your database server. This has reduced the frequency of database server hangs, but (as we’ve seen) has not eliminated them.

But it’s wrong to completely blame the queries here. Again, a database query should never, ever, cause the database server to hang, making it incapable of responding to subsequent queries. At worst, the query should fail (perhaps with some sort of ‘insufficient resources’ error) or time out, which might affect the web page for one web browser, but should not affect subsequent access for other web clients. It would be nice to find out exactly why the database server is hanging (not just which query/queries might be triggering the (currently unknown) problem). When I say that I suspect that the database server might be under-provisioned, I’m referring to the provisioning of the database server itself, not the VM in which it’s running. Fixing this might be as simple as changing a line or two in a database configuration file.

For an analogy, suppose you had a car whose engine which - if you pressed down too hard on the accelerator pedal - would occasionally stall out (rather than the more normal behavior of no longer accelerating, or accelerating only sluggishly). If you had a car like this, the solution would be to try to get it fixed, rather than just telling the driver to drive with less of a ‘lead foot’.

FYI, the “503 Service Unavailable” error has returned (as of 2:53am EDT, Tuesday 4/16/24)

It got fixed a few hours later, but has now returned (as of 8:07pm EDT Wed Apr 17)

As of 12:12pm EDT Thu Apr 18, there’s a new error: The web site is alive, but reports that it can’t connect to the database.

Could someone please reboot the database??

The problem has returned (as of 5:25am EDT Thursday April 25)

Once again - could someone please reboot the database?

The confluence.org website is again unavailable: 503 Service Unavailable

No server is available to handle this request.

Could you fix this?

Best regards,
Rainer Mautz

@rf-confluence The database host has been rebooted.

Thanks Karen.

Could you set up a cron job that automatically reboots the database at a certain time (e.g., midnight or early morning) each day, regardless of its status? That way, it’ll guarantee that it never stays down for more than a day, even if iBiblio staff happen to be too busy (or are not around) to reboot it manually.

@rf-confluence yes, we can set up some automatic restarts - we’ll discuss details with you in a ticket.

Yes, please do. The " 503 Service Unavailable" error has returned (as of 4:22pm EDT Sunday 4/28)

Stuck queries cleared manually, and daily restart scheduled - details sent in ticket.