[Date Prev][Date Next] [Chronological] [Thread] [Top]

Re: proxy-cache

Pierangelo Masarati wrote: 
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 :).


the old code is from head from about a month ago, the new is from head as of yesterday (or so), so old is a bit relative (what isn't?).

I'll be patient ;-)

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







--
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