#1492 bundle db maintenance script to hub
Merged 2 years ago by tkopecek. Opened 2 years ago by tkopecek.
tkopecek/koji issue1478  into  master

@@ -115,6 +115,8 @@ 

  is a proposal of the :doc:`Content Generator

  Metadata <content_generator_metadata>` format available for review.

  

+ .. _cg_api:

+ 

  API

  ===

  

@@ -497,6 +497,40 @@ 

  

  The sql commands you need to use vary by authentication mechanism.

  

+ Maintaining database

+ --------------------

+ 

+ For now, there is one table which needs periodical cleanup. As postgres doesn't

+ have any mechanism for this, we need to do it via some other mechanism. Default

+ handling is done by cron, but can be substituted by anything else (Ansible

+ tower, etc.)

+ 

+ Script is by default installed on hub as `/usr/sbin/koji-sweepd-db`. On systemd

+ systems it also has corresponding `koji-sweep-db` service and timer. Note, that

+ timer is not enabled by default, so you need to run usual `systemctl` commands:

+ 

+ ::

+ 

+    systemctl enable --now koji-sweep-db.timer

+ 

+ If you don't want to use this script, be sure to run following SQL with

+ appropriate age setting. Default value of one day should be ok for most

+ deployments. As there will be tons of freed records, additional VACUUM can be

+ handy.

+ 

+ .. code-block:: sql

+ 

+    DELETE FROM sessions WHERE update_time < NOW() - '1 day'::interval;

+    VACUUM ANALYZE sessions;

+ 

+ Optionally (if you're using :ref:`reservation API <cg_api>` for

+ content generators), you could want to run also reservation cleanup:

+ 

+ .. code-block:: sql

+ 

+    DELETE FROM build_reservations WHERE update_time < NOW() - '1 day'::interval;

+    VACUUM ANALYZE build_reservations;

+ 

  Set User/Password Authentication

  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  

file modified
+6 -1
@@ -450,7 +450,7 @@ 

  make DESTDIR=$RPM_BUILD_ROOT PYTHON=%{__python3} %{?install_opt} install

  # alter python interpreter in koji CLI

  scripts='%{_bindir}/koji %{_sbindir}/kojid %{_sbindir}/kojira %{_sbindir}/koji-shadow

-          %{_sbindir}/koji-gc %{_sbindir}/kojivmd'

+          %{_sbindir}/koji-gc %{_sbindir}/kojivmd %{_sbindir}/koji-sweep-db'

  for fn in $scripts ; do

      sed -i 's|#!/usr/bin/python2|#!/usr/bin/python3|' $RPM_BUILD_ROOT$fn

  done
@@ -534,6 +534,11 @@ 

  %dir /etc/koji-hub

  %config(noreplace) /etc/koji-hub/hub.conf

  %dir /etc/koji-hub/hub.conf.d

+ %{_sbindir}/koji-sweep-db

+ %if %{use_systemd}

+ %{_unitdir}/koji-sweep-db.service

+ %{_unitdir}/koji-sweep-db.timer

+ %endif

  

  %if 0%{py2_support} > 1

  %files -n python2-%{name}-hub

file modified
+3 -1
@@ -1,4 +1,4 @@ 

- BINFILES = kojira koji-gc koji-shadow

+ BINFILES = kojira koji-gc koji-shadow koji-sweep-db

  SYSTEMDSYSTEMUNITDIR = $(shell pkg-config systemd --variable=systemdsystemunitdir)

  TYPE = systemd

  
@@ -29,6 +29,8 @@ 

  install-systemd: _install

  	mkdir -p $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)

  	install -p -m 644 kojira.service $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)

+ 	install -p -m 644 koji-sweep-db.service $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)

+ 	install -p -m 644 koji-sweep-db.timer $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)

  

  install-sysv: _install

  	mkdir -p $(DESTDIR)/etc/rc.d/init.d

file added
+88
@@ -0,0 +1,88 @@ 

+ #!/usr/bin/python2

+ 

+ import os

+ 

+ from optparse import OptionParser

+ from six.moves.configparser import RawConfigParser

+ 

+ import koji.db

+ 

+ def clean_sessions(cursor, vacuum):

+     q = " FROM sessions WHERE update_time < now() - '1 day'::interval"

+     if options.verbose:

+         cursor.execute("SELECT COUNT(*) " + q)

+         rows = cursor.fetchall()[0][0]

+         print("Deleting %d sessions" % rows)

+ 

+     cursor.execute("DELETE " + q)

+     if vacuum:

+         cursor.execute("VACUUM ANALYZE sessions")

+ 

+ 

+ def clean_reservations(cursor, vacuum):

+     q = " FROM build_reservations WHERE create_time < now() - '1 day'::interval"

+     if options.verbose:

+         cursor.execute("SELECT COUNT(*) " + q)

+         rows = cursor.fetchall()[0][0]

+         print("Deleting %d build reservations" % rows)

+ 

+     cursor.execute("DELETE " + q)

+     if vacuum:

+         cursor.execute("VACUUM ANALYZE build_reservations")

+ 

+ 

+ if __name__ == "__main__":

+     global options

+     parser = OptionParser("%prog cleans koji database")

+     parser.add_option('-v', '--verbose', action="store_true", help="Be verbose")

+     parser.add_option('-c', '--conf', default='/etc/koji-hub/hub.conf',

+                       action='store', help="Path to koji's hub.conf")

+     parser.add_option('--no-vacuum', action="store_false", dest="vacuum",

+                       default=True,

+                       help="Don't run vacuum on affected tables")

+     options, args = parser.parse_args()

+ 

+     if not os.path.exists(options.conf):

+         parser.error("Config file doesn't exist")

+ 

+     config = RawConfigParser()

+     config.read(options.conf)

+ 

+     cfgmap = [

+         #option, type, default

+         ['DBName', 'string', None],

+         ['DBUser', 'string', None],

+         ['DBHost', 'string', None],

+         ['DBhost', 'string', None],   # alias for backwards compatibility

+         ['DBPort', 'integer', None],

+         ['DBPass', 'string', None],

+     ]

+ 

+     opts = {}

+     for name, dtype, default in cfgmap:

+         key = ('hub', name)

+         if config and config.has_option(*key):

+             if dtype == 'integer':

+                 opts[name] = config.getint(*key)

+             elif dtype == 'boolean':

+                 opts[name] = config.getboolean(*key)

+             else:

+                 opts[name] = config.get(*key)

+             continue

+         opts[name] = default

+     if opts['DBHost'] is None:

+         opts['DBHost'] = opts['DBhost']

+ 

+ 

+     koji.db.provideDBopts(database=opts["DBName"],

+                           user=opts["DBUser"],

+                           password=opts.get("DBPass", None),

+                           host=opts.get("DBHost", None),

+                           port=opts.get("DBPort", None))

+ 

+     conn = koji.db.connect()

+     cursor = conn.cursor()

+ 

+     clean_sessions(cursor, options.vacuum)

+     clean_reservations(cursor, options.vacuum)

+     conn.commit()

@@ -0,0 +1,7 @@ 

+ [Unit]

+ Description=Daily maintenance script for koji db

+ Documentation=https://pagure.io/docs/koji/

+ 

+ [Service]

+ Type=simple

+ ExecStart=/usr/sbin/koji-sweep-db

@@ -0,0 +1,11 @@ 

+ [Unit]

+ Description=Daily maintenance script for koji db

+ Documentation=https://pagure.io/docs/koji/

+ 

+ [Timer]

+ OnCalendar=daily

+ Accuracy=1h

+ Persistent=true

+ 

+ [Install]

+ WantedBy=timers.target

@tkopecek Why not install this into some place like /usr/libexec/koji-hub and have a small wrapper to call it in cron or systemd timers or whatever? That way people can easily call it, or it can be done from Ansible, or whatever easily.

Yep, maybe libexec is better location. What about default installation? I still hesitate between implicit installation (as in current PR) and just documentation and explicit based on (installation) docs.

pretty please pagure-ci rebuild

2 years ago

for a cron job, I'd probably suggest documentation. For systemd timers, we should install, since they don't get enabled by default.

rebased onto 5d07c8e60b511a1d7907647f14b6ecbf88b66541

2 years ago

We assume systemd in other places in our documentation, so I think we could remove the references to cron and just give the exact systemd steps here.

Code-wise, LGTM :thumbsup:

This part isn't strictly necessary--if enabled, it will run every time multi-user.target is reached. Hit the "comment" icon on the source. "This part" refers to the [Install] section of the service unit (not the timer unit).

2 new commits added

  • improve docs
  • remove Install section
2 years ago

Why is this here? We don't need a Unit= statement here, and it has no value set anyway...

1 new commit added

  • remove typo
2 years ago

1 new commit added

  • Add vaccum to sessions cleanup
2 years ago

rebased onto 37bcb83

2 years ago

6 new commits added

  • clean build_reservations table
  • Add vaccum to sessions cleanup
  • remove typo
  • improve docs
  • remove Install section
  • bundle db maintenance script to hub
2 years ago

Added build_reservation cleanup.

Commit 6c97ff4 fixes this pull-request

Pull-Request has been merged by tkopecek

2 years ago