[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
Re: SQL backend SELECT problem (1=1 causes horrible performance)
> SELECT DISTINCT ldap_entries.id,
> inetorgperson.id,'inetOrgPerson' AS objectClass,
> ldap_entries.dn AS dn
> FROM ldap_entries,inetorgperson
> WHERE inetorgperson.id=ldap_entries.keyval
> AND ldap_entries.oc_map_id=1
> AND upper(ldap_entries.dn) LIKE '%OU=CONTACTS,DC=EVALESCO,DC=COM'
> AND (1=1 AND ((upper(inetorgperson.mail) LIKE 'FOO@%')
> OR (upper(inetorgperson.displayName) LIKE '%FOO@%')
> OR 1=1 OR 1=1)
> );
>
> The problem here, being the "OR 1=1" entries. This causes the *full*
> inetorgperson table to be returned to OpenLDAP on every query, instead
> of only the entries matching the searched-for string.
>
> This, in turn, causes a lot of OpenLDAP CPU usage, a lot of tiny queries
> to the PostgreSQL backend, and the end result is that it takes about
> 10-15 seconds for a query to return, while the actual select above
> takes less than 100ms to execute on the PostgreSQL backend.
>
> As I understand it, it is the following code in search.c (in the LDAP
> SQL backend code) around line 700 that inserts the "1=1" clauses:
> -------------------------
> case LDAP_FILTER_EXT:
> ad = f->f_mra->ma_desc;
> if ( f->f_mr_dnattrs ) {
> /*
> * if dn attrs filtering is requested, better return
> * success and let test_filter() deal with candidate
> * selection; otherwise we'd need to set conditions
> * on the contents of the DN, e.g. "SELECT ... FROM
> * ldap_entries AS attributeName WHERE attributeName.dn
> * like '%attributeName=value%'"
> */
> backsql_strfcat_x( &bsi->bsi_flt_where,
> bsi->bsi_op->o_tmpmemctx,
> "l",
> (ber_len_t)STRLENOF( "1=1" ), "1=1" );
> bsi->bsi_status = LDAP_SUCCESS;
> rc = 1;
> goto done;
> }
> break;
> -------------------------
>
> Is this correct? Or are the "OR 1=1" clauses generated elsewhere?
If you're using OpenLDAP 2.3 that's correct. Previous versions always
used '1=1' to indicate TRUE.
The bottom line for a search whose filter contains extended match with the
"dn" field set consists in searching the entire database. It would be
interesting to see what request gets to slapd. Can you provide logs of
the server at "stats" level?
> In any case, is there a way to configure the SQL backend to *not* insert
> those clauses?
No.
> I was wondering if there was a way to remove the search filters (by
> configuring slapd - because I would rather not have to patch
> Thunderbird).
Well, you could hack back-meta to ignore the "dn" bit of extended matches;
or, you could write an overlay that strips it from search filters.
> Is there anything I can do with my LDAP metadata in the database to help
> the SQL LDAP backend to avoid those?
Nothing comes to mind.
p.
Ing. Pierangelo Masarati
Responsabile Open Solution
OpenLDAP Core Team
SysNet s.n.c.
Via Dossi, 8 - 27100 Pavia - ITALIA
http://www.sys-net.it
------------------------------------------
Office: +39.02.23998309
Mobile: +39.333.4963172
Email: pierangelo.masarati@sys-net.it
------------------------------------------