Mentat

I have already done following work:

• When logged in on mentat-alt|hub check new version of system-status script. I have improved the section for PostgreSQL monitoring. These new health checks are done with the help of Nagios plugin check_postgres, so when we tune them to we can turn the checks on in our organization-wide Nagios instance. So now both databases mentat_events and mentat_main are checked for number of connections, hit-ratio, worst query time, bloat and last (auto)vacuum and (auto)analyze operations. I have discovered following problems/issues to discuss:
  • [A] What are the optimal thresholds for each check. I have used something made up to start with, but we should tune them if necessary.
  • [B] Current results suggest, that the mentat_main database is not optimally configured to run periodical vacuums and analyzes.
  • [C] Was the PostgreSQL configuration tweaked to accommodate recent schema changes, namely the recent split and addition of events_json table? Doesn`t the database documentation page need some improvements to reflect these changes?
• I have installed new server monitoring stack to play with. It is accessible at https://griffin.cesnet.cz/ (access protected with eduID) and built on top of Telegraf - TimescaleDB - Grafana. I played for a while with setting up some basic server monitoring dashboards, but there is still a lot of work for them to be really usable. The idea is to have better server monitoring solution/platform than current Nagios system to better spot system performance bottlenecks, to better judge the effect of various system/database changes and/or upgrades to system performance, etc. I think that with the monitoring system fully under our control the possibilities are endless. I also base the usefulness of such system on the lectures from P2D2 conference (https://p2d2.cz/files/zeleny-stehule-monitoring.pdf). I see another pro in an opportunity to check out the TimescaleDB extension, that could potentially also be used in Mentat system database for storing IDEA events, reports or periodical statistics.
• I have improved following documentation pages:
  • administration - upgraded information about monitoring and maintenance, implemented convenient database maintenance script sqldb-maintenance.sh
  • upgrading - updated update procedures

Could you please look into check which make sense, or are duplicit, have sane limits, or show real problems (autovacuum etc.)
Is mentat_main sanely configured?
Is mentat_events (specifically events_json) sanely configured after db split?

The database mentat_main was checked, for the sqldb-maintenance.sh script to work, there must be indices set for tables to be CLUSTERed according to. Right now the script would do nothing on mentat_main. This was fixed on mentat-alt. Also, an alembic revision is prepared to implement this. It does not perform the CLUSTERing itself, just sets the appropriate indices. See the attachment for patch. As the tables in mentat_main are much smaller (than in mentat_events) and INSERT only, there is not much more to do there. Just make sure to perform an occasional CLUSTER during maintenance.

In mentat_events, the configuration for autovacuum was set for events_json table. This was documented, the patches were sent by e-mail.

As Radko noted, the check_postgres_query_time is pretty much useless. Instead we will implement simple Nagios plugin that will parse the postgresql log file and look for the long running queries there.

I have created simple shell script/NRPE plugin for PostgreSQL log file monitoring. It is currently already deployed on all our installations, it was incorporated into our Ansible PostgreSQL role.

A have incorporated patch attached by Radko Krkoš containing ALTER TABLE commands for optimizations of tables in mentat_main database, but I have moved the commands from migration script to documentation. It is not possible to define ALTER TABLE commands in SQLAlchemy ORM models and it would be necessary to run migrations for fresh installations, which is not supported by our workflow, there would be problems with stamping the database schema version.

I think this issue can be closed now.