{"action":"created","issue":{"url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1549","labels_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1549/labels{/name}","comments_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1549/comments","events_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1549/events","html_url":"https://github.com/puppetlabs/puppetdb/pull/1549","id":96110622,"number":1549,"title":"(PDB-1812) Analyze all pg tables at least once","user":{"login":"rbrw","id":10120698,"avatar_url":"https://avatars.githubusercontent.com/u/10120698?v=3","gravatar_id":"","url":"https://api.github.com/users/rbrw","html_url":"https://github.com/rbrw","followers_url":"https://api.github.com/users/rbrw/followers","following_url":"https://api.github.com/users/rbrw/following{/other_user}","gists_url":"https://api.github.com/users/rbrw/gists{/gist_id}","starred_url":"https://api.github.com/users/rbrw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rbrw/subscriptions","organizations_url":"https://api.github.com/users/rbrw/orgs","repos_url":"https://api.github.com/users/rbrw/repos","events_url":"https://api.github.com/users/rbrw/events{/privacy}","received_events_url":"https://api.github.com/users/rbrw/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-07-20T17:03:38Z","updated_at":"2015-07-20T17:05:54Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1549","html_url":"https://github.com/puppetlabs/puppetdb/pull/1549","diff_url":"https://github.com/puppetlabs/puppetdb/pull/1549.diff","patch_url":"https://github.com/puppetlabs/puppetdb/pull/1549.patch"},"body":"Investigation of a performance problem revealed that analyzing the\r\nvalue_types table improved a PostgreSQL backed http:/.../fact-values\r\nquery by a factor of about 4x when run against a ~10m fact benchmark DB\r\non a tmpfs on Linux.\r\n\r\nIt appeared that some tables had never been analyzed, and further\r\ntesting revealed that the autovacuum process ignored them.  In some\r\ncases, it looks like this is because it ignores small tables that don't\r\nchange much, like value_types, which has 6 rows and effectively never\r\nchanges.\r\n\r\nSo make sure that all tables are analyzed at least once (after any\r\nstructure changes) by running a \"vacuum (analyze, verbose)\" whenever we\r\nhave to run any migration, and add a dummy migration so that all\r\nexisting databases will run the analysis too."},"comment":{"url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/comments/122953580","html_url":"https://github.com/puppetlabs/puppetdb/pull/1549#issuecomment-122953580","issue_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1549","id":122953580,"user":{"login":"rbrw","id":10120698,"avatar_url":"https://avatars.githubusercontent.com/u/10120698?v=3","gravatar_id":"","url":"https://api.github.com/users/rbrw","html_url":"https://github.com/rbrw","followers_url":"https://api.github.com/users/rbrw/followers","following_url":"https://api.github.com/users/rbrw/following{/other_user}","gists_url":"https://api.github.com/users/rbrw/gists{/gist_id}","starred_url":"https://api.github.com/users/rbrw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rbrw/subscriptions","organizations_url":"https://api.github.com/users/rbrw/orgs","repos_url":"https://api.github.com/users/rbrw/repos","events_url":"https://api.github.com/users/rbrw/events{/privacy}","received_events_url":"https://api.github.com/users/rbrw/received_events","type":"User","site_admin":false},"created_at":"2015-07-20T17:05:53Z","updated_at":"2015-07-20T17:05:53Z","body":"I was able to reproduce this with a database created via something like `benchmark -F resources/puppetlabs/puppetdb/benchmark/samples/facts -n 3000 -N 1 -r 10`.  On a tmpfs a `time curl -o /dev/null -sX GET http://localhost:8080/pdb/query/v4/fact-paths` took about 1m before the analyze, and about 15s after it."}}