[Spacewalk-list] Postgres spacewalk
Parsons, Aron
parsonsa at bit-sys.com
Mon Nov 21 22:27:57 UTC 2011
I did some digging into this query because it is one that is very obviously slow when working with real systems.
It seems that the left join to rhnchecksum is the culprit of this slow query. Here's the effect on execution time by changing it to an inner join:
LEFT JOIN: 2973.886 ms
INNER JOIN: 1.444 ms
Based on what this query is used for and the columns we're selecting, this seems safe to me; the inner join is just going to exclude rows that don't exist in both, but that should never happen under normal operation. I have not tested this on Oracle, but have verified that client operations are still operating normally. The real world effect is that this takes a ~50s 'rhncfg-client diff' on the client down to ~3s. I tested by updating the query in /usr/share/rhn/server/handlers/config/rhn_config_management.py.
Jan,
What's your take on this?
Details:
## LEFT JOIN rhnChecksumView:
explain analyze select E'/etc/ssh/sshd_config' path,
cc.label config_channel,
ccont.contents file_contents,
ccont.is_binary is_binary,
c.checksum_type,
c.checksum,
ccont.delim_start, ccont.delim_end,
cr.revision,
cf.modified,
ci.username,
ci.groupname,
ci.filemode,
cft.label,
cct.priority,
ci.selinux_ctx,
case
when cft.label='symlink' then (select path from rhnConfigFileName where id = ci.SYMLINK_TARGET_FILENAME_ID)
else ''
end as symlink
from rhnConfigChannel cc,
rhnConfigInfo ci,
rhnConfigRevision cr
left join rhnConfigContent ccont
on cr.config_content_id = ccont.id
left join rhnChecksumView c
on ccont.checksum_id = c.id,
rhnServerConfigChannel scc,
rhnConfigFile cf,
rhnConfigFileType cft,
rhnConfigChannelType cct
where scc.server_id = 1000010017
and scc.config_channel_id = cc.id
and cf.config_channel_id = cc.id
and cf.config_file_name_id = lookup_config_filename(E'/etc/ssh/sshd_config')
and cr.config_file_id = cf.id
and cr.config_info_id = ci.id
and cf.latest_config_revision_id = cr.id
and cr.config_file_type_id = cft.id
and cct.id = cc.confchan_type_id
order by cct.priority, scc.position;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=154053.71..154053.72 rows=1 width=886) (actual time=2973.534..2973.534 rows=1 loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 26kB
-> Nested Loop (cost=130288.39..154053.70 rows=1 width=886) (actual time=2665.864..2973.483 rows=1 loops=1)
-> Nested Loop (cost=130288.39..154051.84 rows=1 width=812) (actual time=2665.848..2973.465 rows=1 loops=1)
-> Nested Loop (cost=130288.39..154051.23 rows=1 width=812) (actual time=2665.820..2973.436 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Hash Join (cost=130288.39..154050.16 rows=1 width=813) (actual time=2665.791..2973.405 rows=1 loops=1)
Hash Cond: ((cr.config_file_id = cf.id) AND (cr.id = cf.latest_config_revision_id))
-> Hash Left Join (cost=130284.93..154046.41 rows=36 width=793) (actual time=2611.257..2973.268 rows=36 loops=1)
Hash Cond: (ccont.checksum_id = c.id)
-> Hash Left Join (cost=8.79..10.64 rows=36 width=751) (actual time=0.197..0.246 rows=36 loops=1)
Hash Cond: (cr.config_content_id = ccont.id)
-> Seq Scan on rhnconfigrevision cr (cost=0.00..1.36 rows=36 width=42) (actual time=0.009..0.016 rows=36 loops=1)
-> Hash (cost=8.35..8.35 rows=35 width=723) (actual time=0.170..0.170 rows=35 loops=1)
-> Seq Scan on rhnconfigcontent ccont (cost=0.00..8.35 rows=35 width=723) (actual time=0.015..0.073 rows=35 loops=1)
-> Hash (cost=78887.89..78887.89 rows=2210980 width=59) (actual time=2610.697..2610.697 rows=2211051 loops=1)
-> Hash Join (cost=1.11..78887.89 rows=2210980 width=59) (actual time=0.055..1619.600 rows=2211051 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Seq Scan on rhnchecksum c (cost=0.00..48485.80 rows=2210980 width=60) (actual time=0.021..245.576 rows=2211051 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.015..0.015 rows=5 loops=1)
-> Seq Scan on rhnchecksumtype ct (cost=0.00..1.05 rows=5 width=13) (actual time=0.007..0.008 rows=5 loops=1)
-> Hash (cost=3.45..3.45 rows=1 width=48) (actual time=0.065..0.065 rows=1 loops=1)
-> Nested Loop (cost=0.00..3.45 rows=1 width=48) (actual time=0.051..0.057 rows=1 loops=1)
Join Filter: (cc.id = scc.config_channel_id)
-> Nested Loop (cost=0.00..2.35 rows=1 width=55) (actual time=0.031..0.035 rows=1 loops=1)
Join Filter: (cc.id = cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf (cost=0.00..1.26 rows=1 width=29) (actual time=0.020..0.022 rows=1 loops=1)
Filter: (config_file_name_id = 8::numeric)
-> Seq Scan on rhnconfigchannel cc (cost=0.00..1.04 rows=4 width=26) (actual time=0.004..0.004 rows=4 loops=1)
-> Seq Scan on rhnserverconfigchannel scc (cost=0.00..1.06 rows=3 width=14) (actual time=0.013..0.015 rows=3 loops=1)
Filter: (scc.server_id = 1000010017::numeric)
-> Seq Scan on rhnconfigchanneltype cct (cost=0.00..1.03 rows=3 width=13) (actual time=0.019..0.019 rows=3 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype cft (cost=0.00..0.60 rows=1 width=14) (actual time=0.024..0.025 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.60 rows=1 width=88) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..1.25 rows=1 width=20) (never executed)
Filter: (id = $0)
Total runtime: 2973.886 ms
(41 rows)
## INNER JOIN rhnChecksumView:
explain analyze select E'/etc/ssh/sshd_config' path,
cc.label config_channel,
ccont.contents file_contents,
ccont.is_binary is_binary,
c.checksum_type,
c.checksum,
ccont.delim_start, ccont.delim_end,
cr.revision,
cf.modified,
ci.username,
ci.groupname,
ci.filemode,
cft.label,
cct.priority,
ci.selinux_ctx,
case
when cft.label='symlink' then (select path from rhnConfigFileName where id = ci.SYMLINK_TARGET_FILENAME_ID)
else ''
end as symlink
from rhnConfigChannel cc,
rhnConfigInfo ci,
rhnConfigRevision cr
left join rhnConfigContent ccont
on cr.config_content_id = ccont.id
inner join rhnChecksumView c
on ccont.checksum_id = c.id,
rhnServerConfigChannel scc,
rhnConfigFile cf,
rhnConfigFileType cft,
rhnConfigChannelType cct
where scc.server_id = 1000010017
and scc.config_channel_id = cc.id
and cf.config_channel_id = cc.id
and cf.config_file_name_id = lookup_config_filename(E'/etc/ssh/sshd_config')
and cr.config_file_id = cf.id
and cr.config_info_id = ci.id
and cf.latest_config_revision_id = cr.id
and cr.config_file_type_id = cft.id
and cct.id = cc.confchan_type_id
order by cct.priority, scc.position;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=321.58..321.58 rows=1 width=886) (actual time=1.077..1.077 rows=1 loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 26kB
-> Nested Loop (cost=2.92..321.57 rows=1 width=886) (actual time=0.987..1.029 rows=1 loops=1)
-> Nested Loop (cost=2.92..319.70 rows=1 width=812) (actual time=0.961..1.002 rows=1 loops=1)
-> Nested Loop (cost=2.92..319.09 rows=1 width=812) (actual time=0.929..0.969 rows=1 loops=1)
Join Filter: ((cf.id = cr.config_file_id) AND (cf.latest_config_revision_id = cr.id))
-> Nested Loop (cost=0.00..4.52 rows=1 width=47) (actual time=0.079..0.088 rows=1 loops=1)
Join Filter: (cc.id = scc.config_channel_id)
-> Nested Loop (cost=0.00..3.42 rows=1 width=54) (actual time=0.057..0.064 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Nested Loop (cost=0.00..2.35 rows=1 width=55) (actual time=0.033..0.037 rows=1 loops=1)
Join Filter: (cc.id = cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf (cost=0.00..1.26 rows=1 width=29) (actual time=0.020..0.021 rows=1 loops=1)
Filter: (config_file_name_id = 8::numeric)
-> Seq Scan on rhnconfigchannel cc (cost=0.00..1.04 rows=4 width=26) (actual time=0.004..0.005 rows=4 loops=1)
-> Seq Scan on rhnconfigchanneltype cct (cost=0.00..1.03 rows=3 width=13) (actual time=0.017..0.018 rows=3 loops=1)
-> Seq Scan on rhnserverconfigchannel scc (cost=0.00..1.06 rows=3 width=14) (actual time=0.015..0.017 rows=3 loops=1)
Filter: (scc.server_id = 1000010017::numeric)
-> Hash Join (cost=2.92..314.03 rows=36 width=793) (actual time=0.292..0.852 rows=36 loops=1)
Hash Cond: (ccont.id = cr.config_content_id)
-> Hash Join (cost=1.11..311.68 rows=35 width=765) (actual time=0.216..0.744 rows=35 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Nested Loop (cost=0.00..310.09 rows=35 width=766) (actual time=0.130..0.621 rows=35 loops=1)
-> Seq Scan on rhnconfigcontent ccont (cost=0.00..8.35 rows=35 width=723) (actual time=0.021..0.053 rows=35 loops=1)
-> Index Scan using rhnchecksum_pk on rhnchecksum c (cost=0.00..8.61 rows=1 width=60) (actual time=0.014..0.014 rows=1 loops=35)
Index Cond: (c.id = ccont.checksum_id)
-> Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.021..0.021 rows=5 loops=1)
-> Seq Scan on rhnchecksumtype ct (cost=0.00..1.05 rows=5 width=13) (actual time=0.009..0.011 rows=5 loops=1)
-> Hash (cost=1.36..1.36 rows=36 width=42) (actual time=0.054..0.054 rows=36 loops=1)
-> Seq Scan on rhnconfigrevision cr (cost=0.00..1.36 rows=36 width=42) (actual time=0.011..0.026 rows=36 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype cft (cost=0.00..0.60 rows=1 width=14) (actual time=0.026..0.027 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.60 rows=1 width=88) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..1.25 rows=1 width=20) (never executed)
Filter: (id = $0)
Total runtime: 1.444 ms
(39 rows)
/aron
------------------------------
Message: 7
Date: Mon, 21 Nov 2011 10:41:06 +0000 (GMT)
From: John Hodrien <J.H.Hodrien at leeds.ac.uk>
To: "spacewalk-list at redhat.com" <spacewalk-list at redhat.com>
Subject: Re: [Spacewalk-list] Postgres spacewalk
Message-ID: <alpine.LRH.2.02.1111211041030.23151 at pfcpm187.yrrqf.np.hx>
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
On Mon, 21 Nov 2011, Jan Pazdziora wrote:
> On Mon, Nov 21, 2011 at 10:13:38AM +0000, John Hodrien wrote:
>>>
>>> Do you have your database freshly analyzed, by the way?
>>
>> No. ANALYSE; or is there more to it?
>
> Not sure about ANALYSE but ANALYZE should work. You can do
>
> ANALYZE VERBOSE
>
> to see the progress.
Done.
jh
More information about the Spacewalk-list
mailing list