[Spacewalk-list] Postgres spacewalk
John Hodrien
J.H.Hodrien at leeds.ac.uk
Thu Nov 3 15:40:30 UTC 2011
On Wed, 2 Nov 2011, Gerald wrote:
> I'm using spacewalk 1.5/postgres with about 100 hosts.
>
> It's horrible slow with postgres compared to oracle, but with oracle I've
> hit the limits of the free Oracle XE edition so
> I had to update. I'm not a big oracle fan, but now I'm wishing I had that
> performance again.
>
> Problems:
> I hit problems on registering new systems, with osad and importing large
> repos like rpmforge takes now about a week.
> It seems that a lot of open transactions (from osa?) are causing performance
> problems on postgres.
> Hope that could be improved in the next version.
An example of a query that takes far too long. I'm not in any way a DBA, and
really do not have a good understanding of postgresql so I'm going to be
suitably vague here, because I can't really manage anything else.
On the client I do:
rhncfg-client diff
As I'd said before, this can take 5 minutes.
So I put a log in for long queries, and it hits ones like below. The bit that
looks distinctly odd to me is:
-> Hash (cost=61008.92..61008.92 rows=1610013 width=77) (actual time=3354.189..3354.189 rows=1610013 loops=1)
-> Hash Join (cost=1.11..61008.92 rows=1610013 width=77) (actual time=0.020..1907.443 rows=1610013 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Seq Scan on rhnchecksum c (cost=0.00..38870.13 rows=1610013 width=78) (actual time=0.003..319.821 rows=1610013 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.008..0.008 rows=5 loops=1)
So in pulling out info for a single file, we're doing a join on 1.6 million
rows or am I completely misreading that?
Does Oracle make this acceptably fast using auto-materialized views or
something?
jh
=======================
spaceschema=# explain ANALYZE select E'/var/lib/sss/db/cache_default.ldb' path,
spaceschema-# cc.label config_channel,
spaceschema-# ccont.contents file_contents,
spaceschema-# ccont.is_binary is_binary,
spaceschema-# c.checksum_type,
spaceschema-# c.checksum,
spaceschema-# ccont.delim_start, ccont.delim_end,
spaceschema-# cr.revision,
spaceschema-# cf.modified,
spaceschema-# ci.username,
spaceschema-# ci.groupname,
spaceschema-# ci.filemode,
spaceschema-# cft.label,
spaceschema-# cct.priority,
spaceschema-# ci.selinux_ctx,
spaceschema-# case
spaceschema-# when cft.label='symlink' then (select path from rhnConfigFileName where id = ci.SYMLINK_TARGET_FILENAME_ID)
spaceschema-# else ''
spaceschema-# end as symlink
spaceschema-# from rhnConfigChannel cc,
spaceschema-# rhnConfigInfo ci,
spaceschema-# rhnConfigRevision cr
spaceschema-# left join rhnConfigContent ccont
spaceschema-# on cr.config_content_id = ccont.id
spaceschema-# left join rhnChecksumView c
spaceschema-# on ccont.checksum_id = c.id,
spaceschema-# rhnServerConfigChannel scc,
spaceschema-# rhnConfigFile cf,
spaceschema-# rhnConfigFileType cft,
spaceschema-# rhnConfigChannelType cct
spaceschema-# where scc.server_id = 1000010132
spaceschema-# and scc.config_channel_id = cc.id
spaceschema-# and cf.config_channel_id = cc.id
spaceschema-# and cf.config_file_name_id = lookup_config_filename(E'/var/lib/sss/db/cache_default.ldb')
spaceschema-# and cr.config_file_id = cf.id
spaceschema-# and cr.config_info_id = ci.id
spaceschema-# and cf.latest_config_revision_id = cr.id
spaceschema-# and cr.config_file_type_id = cft.id
spaceschema-# and cct.id = cc.confchan_type_id
spaceschema-# order by cct.priority, scc.position
spaceschema-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=122099.14..122099.15 rows=1 width=753) (actual time=3607.380..3607.380 rows=1 loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=101623.13..122099.13 rows=1 width=753) (actual time=3359.261..3607.364 rows=1 loops=1)
-> Nested Loop (cost=101623.13..122095.09 rows=1 width=709) (actual time=3359.179..3607.280 rows=1 loops=1)
-> Nested Loop (cost=101623.13..122094.72 rows=1 width=709) (actual time=3359.153..3607.252 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Hash Join (cost=101623.13..122093.65 rows=1 width=710) (actual time=3359.132..3607.228 rows=1 loops=1)
Hash Cond: ((cr.config_file_id = cf.id) AND (cr.id = cf.latest_config_revision_id))
-> Hash Left Join (cost=101585.36..122054.87 rows=133 width=689) (actual time=3354.989..3604.152 rows=133 loops=1)
Hash Cond: (ccont.checksum_id = c.id)
-> Hash Left Join (cost=11.27..16.12 rows=133 width=629) (actual time=0.217..0.543 rows=133 loops=1)
Hash Cond: (cr.config_content_id = ccont.id)
-> Seq Scan on rhnconfigrevision cr (cost=0.00..3.33 rows=133 width=42) (actual time=0.002..0.044 rows=133 loops=1)
-> Hash (cost=10.01..10.01 rows=101 width=601) (actual time=0.203..0.203 rows=101 loops=1)
-> Seq Scan on rhnconfigcontent ccont (cost=0.00..10.01 rows=101 width=601) (actual time=0.004..0.073 rows=101 loops=1)
-> Hash (cost=61008.92..61008.92 rows=1610013 width=77) (actual time=3354.189..3354.189 rows=1610013 loops=1)
-> Hash Join (cost=1.11..61008.92 rows=1610013 width=77) (actual time=0.020..1907.443 rows=1610013 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Seq Scan on rhnchecksum c (cost=0.00..38870.13 rows=1610013 width=78) (actual time=0.003..319.821 rows=1610013 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.008..0.008 rows=5 loops=1)
-> Seq Scan on rhnchecksumtype ct (cost=0.00..1.05 rows=5 width=13) (actual time=0.003..0.005 rows=5 loops=1)
-> Hash (cost=37.76..37.76 rows=1 width=49) (actual time=2.863..2.863 rows=1 loops=1)
-> Nested Loop (cost=0.00..37.76 rows=1 width=49) (actual time=0.574..2.860 rows=1 loops=1)
-> Nested Loop (cost=0.00..29.48 rows=1 width=56) (actual time=0.551..2.834 rows=1 loops=1)
Join Filter: (cc.id = cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf (cost=0.00..28.25 rows=1 width=29) (actual time=0.544..2.820 rows=1 loops=1)
Filter: (config_file_name_id = lookup_config_filename('/var/lib/sss/db/cache_default.ldb'::character varying))
-> Seq Scan on rhnconfigchannel cc (cost=0.00..1.10 rows=10 width=27) (actual time=0.002..0.006 rows=10 loops=1)
-> Index Scan using rhn_servercc_sid_ccid_uq on rhnserverconfigchannel scc (cost=0.00..8.27 rows=1 width=14) (actual time=0.020..0.022 rows=1 loops=1)
Index Cond: ((scc.server_id = 1000010132::numeric) AND (scc.config_channel_id = cc.id))
-> Seq Scan on rhnconfigchanneltype cct (cost=0.00..1.03 rows=3 width=13) (actual time=0.004..0.006 rows=3 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype cft (cost=0.00..0.36 rows=1 width=14) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (cft.id = cr.config_file_type_id)
-> Index Scan using rhn_confinfo_id_pk on rhnconfiginfo ci (cost=0.00..0.36 rows=1 width=58) (actual time=0.006..0.008 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..3.67 rows=1 width=27) (actual time=0.020..0.053 rows=1 loops=1)
Filter: (id = $0)
Total runtime: 3607.640 ms
(40 rows)
More information about the Spacewalk-list
mailing list