Facts end point slower with PuppetDB 8 (>4)

[traylenator]

Versions:

• puppet-agent 7.24.0
• puppetdb-termini 8.7.0
• puppetdb 4.4.0 and 8.7.0

We have been stuck on PuppetDB 4 for a long time due to https://puppet.atlassian.net/browse/PDB-4830

To recap taking one of our more complicated queries we have in production:

# query.pp
$_hostgroups = ['alpha', 'beta', 'gamma', 'delta', 'epsilon', 'zeta', 'eta' , 'theta', 'iota', 'kappa']
$_hostgroup_query = $_hostgroups.map | $_hg | { "hostgroup_0=${_hg}" }.join(' or ')
$_facts      = ['hostgroup', 'os', 'service_name', 'networking', 'ec2_metadata', 'agent_specified_environment']

$_foo = query_facts($_hostgroup_query,$_facts)

notify{"FOO ${_foo}":}

• On PuppetDB 4 time puppet apply query.pp is 5.73s

• On PuppetDB 8 time puppet apply query.pp times out at 35s for us on the haproxy in front of puppetdb.

• Recently we have again been looking at trying to upgrade PuppetDB since we know we must.

• PuppetDB 4 is of course totally unsupported and we are already patching it to keep it working in today's world.

  • Use newer JDBC driver - https://github.com/puppetlabs/puppetdb/commit/983c184f63d44ac063c1aaeef11d88d01de2407f
  • Strip unrecognised keys - https://github.com/puppetlabs/puppetdb/commit/406356162f2ad0f97fef437f216b5f814130baa4

It was previously suggested to use the inventory endpoint instead and indeed rewriting the above query.

# inventory.pp
$_facts      = ['hostgroup', 'os', 'service_name', 'networking', 'ec2_metadata', 'agent_specified_environment']
$_factpaths = $_facts.map | $_f | { "facts.${_f}" }

$_hostgroups = ['alpha', 'beta', 'gamma', 'delta', 'epsilon', 'zeta', 'eta' , 'theta', 'iota', 'kappa']
$_query = $_hostgroups.map | $_hg | { "facts.hostgroup_0 = \"${_hg}\"" }.join(' or ')

$_result = puppetdb_query("inventory${_factpaths}{${_query}}")

notify{"Result "${_result}":}

• On PuppetDB 4 total failure since the inventory endpoint is a very early version.
• On PuppetDB 8 time puppet apply inventory.pp is 5.3 seconds so very comparable to facts endpoint of PDB4.

So to the questions:

• Is the facts endpoint ever going to get better and not run a full table scan ?
• Should we go through the pain of migrating everything away from the facts end point to the inventory endpoint?

The pain of migrating is major - we have > 100 calls to query_facts in our manifests and also a load of external infrastructure that calls the facts end point to populate help desk information for instance. Given of course PDB4 does not have a inventory endpoint to the required functionality this of course makes any migration super hard.

For info:

• We are contemplating how to migrate - it seems we can run with PuppetDB 8 and PupeptDB 4 as a submit_only.
• For the facts endpoint we can redirect the facts endpoint queries via haproxy to PuppetDB4 while we migrate away from the facts endpoint.
• Running in this mode for several months is all a bit complicated.

[bastelfreak]

Some thoughts:

• What's your postgresql version?
• How many nodes / did you check for unnecessary facts?
• Do you have facts that change on each puppet run / multiple times per day?
• can you enable the slow query log in postgres to get the query / rerun it with explain analyze?
• can you run pgbadger on the logs?
• do you know if there are many dead tuples in the relates tables / can you check for that in the postgresql log or share the log for a day?
• can you run puppetdb_query() (and output the query?) and compare it with the query_facts() function?
• do you run a single puppetdb or multiple instances?

[traylenator]

Just closing.

So the facts endpoint is slower with 8 than 4 but it is faster to write to. It will remain like that.

Migrate to inventory endpoint for all queries and avoid use of the query_facts and query_nodes functions.

To facilitate migration we will run puppetdb 8 but redirecting the /nodes and /facts point on to a PDB4 instance which is also being maintained via double publication - remove all our calls /nodes and /facts and drop the PDB4 instance.