[Sqlgrey-users] Performance: strict versus loose expiration time

[Michael S. <Mic...@lr...>]

Hi Dan and Lionel,

the last days I was thinking about the performance impact of implementing
a strict versus a loose expiration time. At the moment, Sqlgrey uses a
mixed implementation, the is_in_* subroutines use a strict implementation,
whereas the count subroutines use a loose implementation.

strict means, every select has a condition in the where-clause which
results in returning only active entries from a table.

loose means, there is no such condition in the where-clause. Expiration is
handled only by deleting expired entries from a table via a cleanup
subroutine.

The patches I sent Dan, implement a strict expiration time for all
subroutines.

However, implementing loose expiration time instead of strict, could bring
some performance gains.

If a select does not have the expiration condition in the where-clause, it
will be characterwise identical for the same triplet (or part of it). This
allows to use prepare_cached instead of prepare, which gives us the first
performance gain. In the next step this select statement will hit the
query cache as long as the table involved hasn't changed. This could give
us a second gain especially for the tables from_awl or domain_awl which do
not change often.

In the loose implementation the maximum expiration time would be
db_cleandelay seconds longer than the strict one. Taking the default this
means it is just half an hour longer, which is at least not noticeable for
the AWLs.

The only problem I see at the moment is a race condition between cleanup
and is_in/update, where the update could try to update a deleted entry.
Therefore update must take this into account similar to the put_in
subroutines which take into account the possibility of the insertion of an
entry by another Sqlgrey daemon.

What is your opinion?

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================