Mentat

This may also be an issue if cancelling long-running queries using pg_cancel_backend(), what is considered to be implemented for #4384.

An obvious candidate for the fix is the EventStorageService where the data access methods reside. The approach using a decorator looks quite elegant. Unfortunately (for an automatic heal), EventStorageCursor needs a modification also to support cursor recreation.

Lotta work. Mek, please, take a look sometimes, preferably before meeting, if possible.

Notes to discussion:
If I read it correctly, it retries right away three times. I think that second and subsequent retries should be delayed couple of seconds.

And probably would be fine to put these values somewhere into config.

This was partially (not enough time) discussed online, Mek likes the decorator approach, so this is good to go for further development and polishing.

1. Retry count for renewing the DB connection should be configurable.
2. A sleep time after unsuccessful connection renewal should be added and made configurable.
3. The exception thrown after all DB reconnects time out should be based on original psycopg2.OperationalError (with details) exception.
4. The data access methods should be looked into. They explicitly state that in case of exception a rollback is performed, this is:
   a) not the case anymore as the decorator is responsible for the task;
   b) not the only way to handle exceptions as the new added approach is DB reconnection;
5. The issue of psycopg2.DatabaseError should be analyzed. This is a super-class of psycopg2.OperationalError that indicates DB disconnect in the background. What other uses are there?

Any other comments?

The SQLAlchemy part was not diagnosed yet. This regards the event data access only.

• no retry/timeout, try just once (hence not blocking... too much)
• docstrings don't need to be updated
• own exception (with encapsulated original exception) (StorageConnectionException)
• OperationalError - reconnect, DatabaseError - rollback

Radko Krkoš wrote:

Jan Mach wrote:

I have just deployed the attached patch to the mentat-alt test system and sadly I have to report, that the issue remains. If you restart postgresql database without restarting apache and all Mentat daemons, the connection does not get recreated and system fails (daemons terminate and apache returns 500). Could you please look into that again? You are free to test this on mentat-alt system.

Apache indeed does return 500 for the first time, this is expected. The patch was supposed to fix the failure on event database access. The first failure is due to SQLAlchemy (user data access) and will be more complicated to fix. SQLAlchemy however recovers on retry (sometimes 2 retries are needed - seems to be related to some timing of failed transaction rollback in SQLAlchemy, this will be a true delight to debug), handing execution to the event table access code, where this patch comes into action, allowing for instant recovery if the connection was lost. (Without the patch, the recovery would only happen on apache restart - hence eternal 500.)

Sadly I can confirm that some Mentat daemons (I have seen enricher) sometimes fail after DB restart. I was not able to reproduce this reliably however, it seems to happen very rarely for me.

Enricher has internal reload timer, that triggers fetching fresh enrichment data from database to internal in-memory cache. I have observed daemon failure always after each database restart, it just does not occur always immediately after the restart.

As for the apache restart, it does not seem necessary for me in any case (after applying the patch). Can you describe the case?

If you restart postgresql without restarting apache, for example the event search form https://mentat-alt.cesnet.cz/mentat/events/search?dt_from=&dt_to=&source_addrs=&source_ports=&submit=Search starts throwing 500s. But I now see, that the problem described in traceback is related to not yet patched sqlalchemy code, not the already fixed psycopg2 code. Sorry for the misdirection.

Mentat is now installed in its own virtual environment and I have verified that the patch code was successfully applied to /var/mentat/venv/lib/python3.5/site-packages/mentat/services/eventstorage.py file.

Thanks, this was useful info.

Jan Mach wrote:

Radko Krkoš wrote:

Sadly I can confirm that some Mentat daemons (I have seen enricher) sometimes fail after DB restart. I was not able to reproduce this reliably however, it seems to happen very rarely for me.

Enricher has internal reload timer, that triggers fetching fresh enrichment data from database to internal in-memory cache. I have observed daemon failure always after each database restart, it just does not occur always immediately after the restart.

Yes, that makes sense. Should we create a different issue for that as it is not strictly Hawat related or do you want this issue converted to an umbrella DB conn drop recovery one?
In any case, do you want me to look into that?

As for the apache restart, it does not seem necessary for me in any case (after applying the patch). Can you describe the case?

If you restart postgresql without restarting apache, for example the event search form https://mentat-alt.cesnet.cz/mentat/events/search?dt_from=&dt_to=&source_addrs=&source_ports=&submit=Search starts throwing 500s. But I now see, that the problem described in traceback is related to not yet patched sqlalchemy code, not the already fixed psycopg2 code. Sorry for the misdirection.

OK, but those disappear on their own after a few requests. Is apache restart really necessary?

Radko Krkoš wrote:

Jan Mach wrote:

Radko Krkoš wrote:

Sadly I can confirm that some Mentat daemons (I have seen enricher) sometimes fail after DB restart. I was not able to reproduce this reliably however, it seems to happen very rarely for me.

Enricher has internal reload timer, that triggers fetching fresh enrichment data from database to internal in-memory cache. I have observed daemon failure always after each database restart, it just does not occur always immediately after the restart.

Yes, that makes sense. Should we create a different issue for that as it is not strictly Hawat related or do you want this issue converted to an umbrella DB conn drop recovery one?

I think we should continue to resolve the matter in this issue to reduce the management overhead. I think, that the issue title is ok, when we resolve the matter for Hawat, we resolve it also for Mentat backend.

In any case, do you want me to look into that?

Yes please, look into it. I am occupied elsewhere and I would be grateful, if you could look into it further.

As for the apache restart, it does not seem necessary for me in any case (after applying the patch). Can you describe the case?

If you restart postgresql without restarting apache, for example the event search form https://mentat-alt.cesnet.cz/mentat/events/search?dt_from=&dt_to=&source_addrs=&source_ports=&submit=Search starts throwing 500s. But I now see, that the problem described in traceback is related to not yet patched sqlalchemy code, not the already fixed psycopg2 code. Sorry for the misdirection.

OK, but those disappear on their own after a few requests. Is apache restart really necessary?

I am glad it helped. With these patches, all known points of failure regarding DB connection are addressed, resulting in automatic recovery after connection loss. The only edge case is the user data access code using SQLAlchemy, which only recovers after the second try. We should solve that at some point.
In the meantime, I would like an intensive test to validate the patches and possibly find other points of failure. Could we run a series of regular DB restarts (using cron probably) to see how the Mentat as a whole would perform? This have to be timed to not interfere with your work so maybe a weekend is a good idea?
Also, I am unsure whether mentat-alt performs all the tasks that production mentat does, particularly the reporting. It would be beneficial to make it as similar to mentat-hub as possible, of course with a different set of addressees.
Unfortunately this would be more work for you Mek, I can help very little as my knowledge of the whole Mentat is lacking severely, I would not know where to look (well, I would probably monitor the logs for errors). So take this as a suggestion and decide at your discretion. Of course I will help in any way I can.

Radko Krkoš wrote:

Also, I am unsure whether mentat-alt performs all the tasks that production mentat does, particularly the reporting. It would be beneficial to make it as similar to mentat-hub as possible, of course with a different set of addressees.

AFAIK, mentat-alt Reporter does indeed generate reports, however mails are intentionally sent only to admin (for spot inspection). Reports are not exactly same as on mentat-hub, I presume it's because of slight differences in start/relapse windows.

Radko, you are free to experiment as you like, at least we will possibly identify some other points of failure after unexpected restart. So feel free to add some restarting jobs to cron, just do not leave them indefinitely and perhaps make them output some content, perhaps even with some echo comment, so that I am aware that the restart was intentional.

Note: Reporter is indeed running on mentat-alt, but all reports are redirected to local root for possible inspection and to prevent from spamming. Different report contents on -alt and -hub are due to different content/state of thresholding caches, configuration is the same.

Latest patch is currently deployed to mentat-alt.