Automatic server maintenance

[Tromador]

It would be nice if the listener could manage to perform a couple of simple DB maintenance tasks without me doing it by hand.

Very simple SQL -

VACUUM; PRAGMA optimize;

The vacuum clears out any empty space and defragments all the data structures within the DB file. This can take a couple of minutes. The optimize will normally be a no-op, unless there is something SQLite thinks needs doing. I've yet to see it take any time at all.

Every 12 hours would be good, but perhaps a config option?

If I were going to do it with my minimal python (7h3y r l337, ph34r m3) skills, I would just copy paste teh check_update thread and use that as a starting point for another thread, but there's maybe a better way to do it within check_update even.

In fact, for me - and as we've discussed that setting it in server mode isn't meant to be for non-technical users, doing it every n (configurable) ticks of update would be more efficient as it could simply be done with a counter in check_update. Let's assume that the server admin is capable of putting sensible numbers for update interval and number of ticks per db admin cycle.

[eyeonus]

It should be in the export_listings thread, not the check_update thread, since the exporter is the one that's only run in server mode, but yeah, sounds simple enough.

[eyeonus]

I'm adding the config server_maint_every_x_hour now.

What would say is a reasonable upper limit on the number of hours?

I'm thinking any value between 1 and 24 would be a valid config value, but do you think I should allow higher numbers?

[Tromador]

I can't imagine why anyone would want a higher number. Or lower than 6 to be honest.

If someone is that desperate to be different then I suggest they can hack the code, so 1-24 is fine.

[eyeonus]

Okay, so in testing, I keep getting "Database is locked, waiting for access." when the server maintenance code runs, but IIRC that won't be a problem on the server because WAL mode.

So, it looks good to me. Pushing now.

[Tromador]

Hmm.. WAL mode allows reads, not writes. We'll see. I been too sick to do anything about it the past 48 hours. I'll have look today.

[Tromador]

I think we might need to deal with the server lock.. I just tried this manually with the listener running. Took 3 attempts to get the VACUUM to run, which will itself have locked the DB for writing.

[elite@mort tradedangerous.server]$ sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open data/TradeDangerous.db
sqlite> VACUUM;
Error: database is locked
sqlite> VACUUM;
Error: database is locked
sqlite> VACUUM;

[Tromador]

Yup - here is the log from when I ran that VACUUM. So, that's fine - it copes with the maintenance, but the maintenance needs to be able to retry on a lock too.

Market update for CEOS/BRUNEL HUB finished in 1.0 seconds.
Market update for V1581 CYGNI/PEARY DOCK finished in 0.44 seconds.
Database is locked, waiting for access.
Database is locked, waiting for access.
Export completed in 0:00:33.820183
Database is locked, waiting for access.
Database is locked, waiting for access.
Database is locked, waiting for access.
Database is locked, waiting for access.
Market update for POEMAKU/SLADEK STATION finished in 46.62 seconds.
Market update for DHANHOPI/PLUCKER ENTERPRISE finished in 0.0 seconds.
Market update for MAIA/MONI'S HUB finished in 0.005 seconds.

[eyeonus]

It does retry on lock. Both the VACUUM and the PRAGMA are run through the db_execute method, which was built specifically to try again on a DB locked exception.

                    print("Performing server maintenance tasks.")
                    try:
                        db_execute(db, "VACUUM")
                        db_execute(db, "PRAGMA optimize")
                    except Error as e:
                        print("Error performing maintenance: " + str(e) )
                    maintenance_time = time.time() + (config['server_maint_every_x_hour'] * 3600)
                    print("Server maintenance tasks completed.")

[Tromador]

That code snippet looks like

Try maintenance. Print fail if locked. Restart timer and try again in CONFIG hours.

Not

Try maintenance If locked retry until it works. Restart timer.

[eyeonus]

As I said, it's using the db_execute method, which retries when the DB is locked:

def db_execute(db, sql_cmd, args = None):
    cur = db.cursor()
    success = False
    result = None
    while go and not success:
        try:
            if args:
                result = cur.execute(sql_cmd, args)
            else:
                result = cur.execute(sql_cmd)
            success = True
        except sqlite3.OperationalError as e:
                if "locked" not in str(e):
                    success = True
                    raise sqlite3.OperationalError(e)
                else:
                    print("Database is locked, waiting for access.", end = "\r")
                    time.sleep(1)
    return result

[Tromador]

Okay now I am following you.

I installed this yesterday, so I will check with the log files later on and report back.

[Tromador]

Well, it had fun, but yes - the maintenance did happen :)

No update, checking again in 1 hour.
Database is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MDatabase is locked, waiting for access.^MServer maintenance tasks completed.

[eyeonus]

Hmmm. Maybe having it turn on a busy signal would prevent that. I don't think it's strictly needed, so it's up to you.

[Tromador]

No, it's fine. Hmm... I added a "completion" time in the log, I'll add a start time as well, but so long as it happens, say in a window of an hour I'm good as it stands.

[Tromador]

This is good :)

Performing server maintenance tasks.2018-08-09 14:19:21.907696
Server maintenance tasks completed. 2018-08-09 14:21:28.850932
Maintenance cycle took 0:02:06.943236.

[eyeonus]

Cool beans.