Re: Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Geoghegan <peter(at)2ndquadrant(dot)com>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)
Date: 2012-03-28 23:14:43
Message-ID: 3482.1332976483@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Peter Geoghegan <peter(at)2ndquadrant(dot)com> writes:
> On 28 March 2012 15:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Is there any actual benefit in providing the
>> "pg_stat_statements.string_key" GUC? It looks to me more like something
>> that was thrown in because it was easy than because anybody would want
>> it. I'd just as soon leave it out and avoid the incremental API
>> complexity increase. (While on that subject, I see no documentation
>> updates in the patch...)

> Personally, I don't care for it, and I'm sure most users wouldn't
> either, but I thought that someone somewhere might be relying on the
> existing behaviour.

Hearing no squawks, I will remove it from the committed patch; one
less thing to document. Easy enough to put back later, if someone
makes a case for it.

>> Also, I'm not terribly happy with the "sticky entries" hack.

> I was troubled by that too, and had considered various ways of at
> least polishing the kludge. Maybe a better approach would be to start
> with a usage of 1e10 (or something rather high, anyway), but apply a
> much more aggressive multiplier than USAGE_DECREASE_FACTOR for sticky
> entries only? That way, in earlier calls of entry_dealloc() the sticky
> entries, easily identifiable as having 0 calls, are almost impossible
> to evict, but after a relatively small number of calls they soon
> become more readily evictable.

I did some simple experiments with the regression tests. Now, those
tests are by far a worst case for this sort of thing, since (a) they
probably generate many more unique queries than a typical production
application, and (b) they almost certainly provoke many more errors
and hence more dead sticky entries than a typical production app.
Nonetheless, the results look pretty bad. Using various values of
USAGE_NON_EXEC_STICK, the numbers of useful and dead entries in the hash
table after completing one round of regression tests was:

STICK live entries dead sticky entries

10.0 780 190
5.0 858 112
4.0 874 96
3.0 911 62
2.0 918 43

I did not bother measuring 1e10 ;-). It's clear that sticky entries
are forcing useful entries out of the table in this scenario.
I think wasting more than about 10% of the table in this way is not
acceptable.

I'm planning to commit the patch with a USAGE_NON_EXEC_STICK value
of 3.0, which is the largest value that stays below 10% wastage.
We can twiddle that logic later, so if you want to experiment with an
alternate decay rule, feel free.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2012-03-28 23:52:23 Re: Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)
Previous Message Kevin Grittner 2012-03-28 22:31:31 Re: BUG #6522: PostgreSQL does not start