pdns ddns Performance

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

pdns ddns Performance

Thomas Mieslinger
Hi,

maybe you remember my message regarding suboptimal ddns update
performance. A colleague helped me to identify the list_subzone_query as
the performance killer.

The original where clause is

  disabled=0 and (name='%s' OR name like '%s') and domain_id='%d'

with

%.<record-to-update>

as second argument.

When like is used with a search argument that starts with a wildcard the
index can not be used.

Because I know that my records to update have no subzones I short
circuit query with

SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records
WHERE disabled=0 and (name='%s' OR 'x'='%s') and domain_id='%d'

Cheers

Thomas
_______________________________________________
Pdns-users mailing list
[hidden email]
http://mailman.powerdns.com/mailman/listinfo/pdns-users
Reply | Threaded
Open this post in threaded view
|

Re: pdns ddns Performance

Ruben
Thanks for the update! It makes sense. I've logged this as ticket 3528, so somebody could have a look at that subzone query behaviour a bit better.


On Tue, Mar 08, 2016 at 09:40:57PM +0100, Thomas Mieslinger wrote:

> Hi,
>
> maybe you remember my message regarding suboptimal ddns update performance.
> A colleague helped me to identify the list_subzone_query as the performance
> killer.
>
> The original where clause is
>
>  disabled=0 and (name='%s' OR name like '%s') and domain_id='%d'
>
> with
>
> %.<record-to-update>
>
> as second argument.
>
> When like is used with a search argument that starts with a wildcard the
> index can not be used.
>
> Because I know that my records to update have no subzones I short circuit
> query with
>
> SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE
> disabled=0 and (name='%s' OR 'x'='%s') and domain_id='%d'
>
> Cheers
>
> Thomas
> _______________________________________________
> Pdns-users mailing list
> [hidden email]
> http://mailman.powerdns.com/mailman/listinfo/pdns-users
_______________________________________________
Pdns-users mailing list
[hidden email]
http://mailman.powerdns.com/mailman/listinfo/pdns-users