[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
Re: proxy-cache
There must be a misunderstanding; unfortunately now I'm not in the
position to check the code right now, I'll look at it later tonight. What
you mark as __old__ in my intention is the __new__ query; I explicitly
added that equality because after reworking the ldap_entry_objclasses
usage in candidate selection, MySQL started failing because it didn't like
the implicit relation between tables, while PostgreSQL worked like a
charm. I know there are many reasons not to use MySQL with back-sql, but
it is used by a number of persons, so I wanted to have things working with
all the RDBMSes I was able to test. If you say that I reversed something
that was working, then I apologize (and I'll try to fix it as soon as I'm
back to my laptop :).
p.
> Pierangelo Masarati wrote:
>
>> There were no changes between 2.2.15 and 2.2.17; there are lots of
>> changes
>> between 2.2.17 and HEAD; at a first glance there's not much that should
>> impact performances. There's a small issue, I had to change the way
>> schema mapping is looked up; at some point it was done by comparing
>> pointers, but that made tests not portable because attribute ordering in
>> the results was machine or even run-time dependent; so I made it default
>> to strcmp based. I haven't noticed significant performance impact, but
>> I
>> never used very large databases. You can revert to the old behavior by
>> recompiling back-sql (actually, all you need to recompile is
>> schema-map.c)
>> with -DBACKSQL_USE_PTR_CMP (see the comment at the beginning of
>> schema-map.c).
>>
>> This may (and will) cause sql-test* fail because the results are not
>> ordered as in the reference.
>>
>> Anybody please holler if you find a better way to sort results in a
>> better
>> way...
>>
>> p.
>
> Hi,
>
> I've done a bit of digging and it seems the difference is in the way the
> candidate list is constructed. The new query is much much slower than
> the new one (by a large amount), I guess that the (maybe overly
> redundant) phrase 'ldap_entries.id=ldap_entry_objclasses.entry_id and'
> helps the query optimizer in postgres a lot....
>
> If it doesn't make any differnce to you, I guess I liked the performance
> of the old query a lot better.
>
> (new version)
> Constructed query: SELECT DISTINCT
> ldap_entries.id,posixshadowaccount.id,text('account') AS
> objectClass,ldap_entries.dn AS dn FROM
> ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE
> posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=?
> AND upper(ldap_entries.dn) LIKE ? AND
> ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3
> AND (ldap_entry_objclasses.oc_name='posixAccount')
> id: '3'
> (sub)dn: "%OU=TERAS,O=SARA,C=NL"
>
>
> (old version)
> Constructed query: SELECT DISTINCT
> ldap_entries.id,posixshadowaccount.id,text('account') AS
> objectClass,ldap_entries.dn AS dn FROM
> ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE
> posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=?
> AND upper(ldap_entries.dn) LIKE ? AND
> ldap_entries.id=ldap_entry_objclasses.entry_id and
> ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3
> AND (ldap_entry_objclasses.oc_name='posixAccount')
> id: '3'
> (sub)dn: "%OU=TERAS,O=SARA,C=NL"
>
> explain output for both queries:
>
> db2=> explain
> db2-> SELECT DISTINCT
> ldap_entries.id,posixshadowaccount.id,text('account') AS
> objectClass,ldap_entries.dn AS dn FROM
> ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE
> posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=3
> AND upper(ldap_entries.dn) LIKE '%OU=TERAS,O=SARA,C=NL' AND
> ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3
> AND (ldap_entry_objclasses.oc_name='posixAccount')
> db2-> ;
> QUERY
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=182.39..184.30 rows=153 width=40)
> -> Sort (cost=182.39..182.77 rows=153 width=40)
> Sort Key: ldap_entries.id, m.id, 'account'::text,
> ldap_entries.dn
> -> Nested Loop (cost=115.35..176.84 rows=153 width=40)
> -> Index Scan using ldap_entry_objclasses_name_key on
> ldap_entry_objclasses (cost=0.00..58.43 rows=17 width=0)
> Index Cond: (oc_name = 'posixAccount'::text)
> -> Materialize (cost=115.35..115.44 rows=9 width=40)
> -> Nested Loop (cost=0.00..115.35 rows=9 width=40)
> -> Nested Loop (cost=0.00..61.30 rows=9
> width=52)
> -> Index Scan using
> ldap_entries_oc_keyval_key on ldap_entries (cost=0.00..32.56 rows=1
> width=40)
> Index Cond: (oc_map_id = 3)
> Filter: (upper(dn) ~~
> '%OU=TERAS,O=SARA,C=NL'::text)
> -> Index Scan using
> user_machine_login_key on user_machine_login m (cost=0.00..28.64 rows=8
> width=16)
> Index Cond: (m.id =
> "outer".keyval)
> -> Index Scan using uid_uniek on
> user_global g (cost=0.00..5.99 rows=1 width=12)
> Index Cond: ("outer".userid = g.userid)
> (16 rows)
>
> db2=> explain
> db2-> SELECT DISTINCT
> ldap_entries.id,posixshadowaccount.id,text('account') AS
> objectClass,ldap_entries.dn AS dn FROM
> ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE
> posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=3
> AND upper(ldap_entries.dn) LIKE '%OU=TERAS,O=SARA,C=NL' AND
> ldap_entries.id=ldap_entry_objclasses.entry_id and
> ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3
> AND (ldap_entry_objclasses.oc_name='posixAccount');
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=173.97..174.09 rows=9 width=40)
> -> Sort (cost=173.97..174.00 rows=9 width=40)
> Sort Key: ldap_entries.id, m.id, 'account'::text,
> ldap_entries.dn
> -> Nested Loop (cost=0.00..173.83 rows=9 width=40)
> -> Nested Loop (cost=0.00..119.79 rows=9 width=52)
> -> Nested Loop (cost=0.00..91.05 rows=1 width=40)
> -> Index Scan using
> ldap_entries_oc_keyval_key on ldap_entries (cost=0.00..32.56 rows=1
> width=40)
> Index Cond: (oc_map_id = 3)
> Filter: (upper(dn) ~~
> '%OU=TERAS,O=SARA,C=NL'::text)
> -> Index Scan using ldap_entry_id_key on
> ldap_entry_objclasses (cost=0.00..58.47 rows=1 width=4)
> Index Cond: ("outer".id =
> ldap_entry_objclasses.entry_id)
> Filter: (oc_name = 'posixAccount'::text)
> -> Index Scan using user_machine_login_key on
> user_machine_login m (cost=0.00..28.64 rows=8 width=16)
> Index Cond: (m.id = "outer".keyval)
> -> Index Scan using uid_uniek on user_global g
> (cost=0.00..5.99 rows=1 width=12)
> Index Cond: ("outer".userid = g.userid)
> (16 rows)
>
>
> --
> Met vriendelijke groeten,
>
> Remco Post
>
> SARA - Reken- en Netwerkdiensten http://www.sara.nl
> High Performance Computing Tel. +31 20 592 3000 Fax. +31 20 668 3167
>
> "I really didn't foresee the Internet. But then, neither did the
> computer industry. Not that that tells us very much of course - the
> computer industry didn't even foresee that the century was going to
> end." -- Douglas Adams
>
>
--
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it
SysNet - via Dossi,8 27100 Pavia Tel: +390382573859 Fax: +390382476497