Hello Deepak,
Here are the queries you asked for:
> Can you fire up psql, point it at your puppetdb database, and run "EXPLAIN
> ANALYZE SELECT COUNT(*) AS c FROM certname_catalogs cc, catalog_resources
> cr, certnames c WHERE cc.catalog=cr.catalog AND c.name=cc.certname AND
> c.deactivated IS NULL;"? That will profile the query, and we can see where
> the problem is.
>
>
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3132186.09..3132186.10 rows=1 width=0) (actual
time=348426.375..348426.376 rows=1 loops=1)
-> Hash Join (cost=247.37..3096532.80 rows=14261314 width=0) (actual
time=24059.151..348255.933 rows=1604253 loops=1)
Hash Cond: ((cr.catalog)::text = (cc.catalog)::text)
-> Seq Scan on catalog_resources cr (cost=0.00..2871450.86
rows=16444286 width=41) (actual time=24053.542..347250.967 rows=2107982
loops=1)
-> Hash (cost=216.42..216.42 rows=2476 width=41) (actual
time=5.555..5.555 rows=2476 loops=1)
-> Hash Join (cost=94.84..216.42 rows=2476 width=41)
(actual time=1.850..4.555 rows=2476 loops=1)
Hash Cond: (cc.certname = c.name)
-> Seq Scan on certname_catalogs cc
(cost=0.00..82.55 rows=2855 width=62) (actual time=0.010..0.941 rows=2855
loops=1)
-> Hash (cost=63.71..63.71 rows=2490 width=21)
(actual time=1.818..1.818 rows=2490 loops=1)
-> Seq Scan on certnames c (cost=0.00..63.71
rows=2490 width=21) (actual time=0.013..0.823 rows=2490 loops=1)
Filter: (deactivated IS NULL)
Total runtime: 348446.364 ms
(12 rows)
Also worth examining is the GC query (modified to be non-destructive):
>
> EXPLAIN ANALYZE SELECT * FROM resource_params WHERE NOT EXISTS (SELECT *
> FROM catalog_resources cr WHERE cr.resource=resource_params.resource);
>
>
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..1710681.18 rows=360492 width=126)
(actual time=1967.182..1967.182 rows=0 loops=1)
-> Seq Scan on resource_params (cost=0.00..13249.23 rows=389623
width=126) (actual time=0.013..76.403 rows=389693 loops=1)
-> Index Scan using idx_catalog_resources_resource on catalog_resources
cr (cost=0.00..21429.14 rows=5291 width=41) (actual time=0.004..0.004
rows=1 loops=389693)
Index Cond: ((cr.resource)::text =
(resource_params.resource)::text)
Total runtime: 1967.253 ms
(5 rows)
And the resource duplication query:
>
> EXPLAIN ANALYZE SELECT COUNT(*) AS c FROM (SELECT DISTINCT resource FROM
> catalog_resources cr, certname_catalogs cc, certnames c WHERE
> cr.catalog=cc.catalog AND cc.certname=c.name AND c.deactivated IS NULL) r;
>
>
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3129997.71..3129997.72 rows=1 width=0) (actual
time=474369.420..474369.420 rows=1 loops=1)
-> HashAggregate (cost=3129927.78..3129958.86 rows=3108 width=41)
(actual time=474351.730..474365.144 rows=49466 loops=1)
-> Hash Join (cost=248.48..3094726.38 rows=14080561 width=41)
(actual time=21536.467..473678.479 rows=1604237 loops=1)
Hash Cond: ((cr.catalog)::text = (cc.catalog)::text)
-> Seq Scan on catalog_resources cr (cost=0.00..2871450.86
rows=16444286 width=82) (actual time=21531.355..472692.303 rows=1934212
loops=1)
-> Hash (cost=217.36..217.36 rows=2490 width=41) (actual
time=5.071..5.071 rows=2476 loops=1)
-> Hash Join (cost=94.84..217.36 rows=2490 width=41)
(actual time=1.700..4.048 rows=2476 loops=1)
Hash Cond: (cc.certname = c.name)
-> Seq Scan on certname_catalogs cc
(cost=0.00..83.08 rows=2908 width=62) (actual time=0.010..0.603 rows=2855
loops=1)
-> Hash (cost=63.71..63.71 rows=2490 width=21)
(actual time=1.670..1.670 rows=2490 loops=1)
-> Seq Scan on certnames c
(cost=0.00..63.71 rows=2490 width=21) (actual time=0.014..0.785 rows=2490
loops=1)
Filter: (deactivated IS NULL)
Total runtime: 474370.129 ms
(13 rows)
> And finally, can you give us a dump of your database settings? You can do
> this from within psql by doing "SHOW ALL;"
>
http://pastebin.com/raw.php?i=HfhqfVa4
Thank you for your help!
Cheers,
ak0ska
--
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to puppet-users+unsubscr...@googlegroups.com.
To post to this group, send email to puppet-users@googlegroups.com.
Visit this group at http://groups.google.com/group/puppet-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
