From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: Fabien COELHO <coelho@cri.ensmp.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Jaime Soler <jaime.soler@gmail.com>
Subject: Re: pgbench and timestamps (bounced)
Date: Tue, 24 Nov 2020 15:11:53 +0300
Message-ID: <6c6af173-85b9-f0b3-4764-b0df415d2736@postgrespro.ru> (raw)
In-Reply-To: <alpine.DEB.2.22.394.2009111501410.3995562@pseudo>
References: <alpine.DEB.2.22.394.2006291740420.805678@pseudo>
<1720048.1599525071@sss.pgh.pa.us>
<alpine.DEB.2.22.394.2009111501410.3995562@pseudo>
On 11.09.2020 16:59, Fabien COELHO wrote:
>
> Hello Tom,
>
>>> It requires a mutex around the commands, I tried to do some windows
>>> implementation which may or may not work.
>>
>> Ugh, I'd really rather not do that. Even disregarding the effects
>> of a mutex, though, my initial idea for fixing this has a big problem:
>> if we postpone PREPARE of the query until first execution, then it's
>> happening during timed execution of the benchmark scenario and thus
>> distorting the timing figures. (Maybe if we'd always done it like
>> that, it'd be okay, but I'm quite against changing the behavior now
>> that it's stood for a long time.)
>
> Hmmm.
>
> Prepare is done *once* per client, ISTM that the impact on any
> statistically significant benchmark is nul in practice, or it would
> mean that the benchmark settings are too low.
>
> Second, the mutex is only used when absolutely necessary, only for the
> substitution part of the query (replacing :stuff by ?), because
> scripts are shared between threads. This is just once, in an unlikely
> case occuring at the beginning.
>
>> However, perhaps there's more than one way to fix this. Once we've
>> scanned all of the script and seen all the \set commands, we know
>> (in principle) the set of all variable names that are in use.
>> So maybe we could fix this by
>>
>> (1) During the initial scan of the script, make variable-table
>> entries for every \set argument, with the values shown as undefined
>> for the moment. Do not try to parse SQL commands in this scan,
>> just collect them.
>
> The issue with this approach is
>
> SELECT 1 AS one \gset pref_
>
> which will generate a "pref_one" variable, and these names cannot be
> guessed without SQL parsing and possibly execution. That is why the
> preparation is delayed to when the variables are actually known.
>
>> (2) Make another scan in which we identify variable references
>> in the SQL commands and issue PREPAREs (if enabled).
>
>> (3) Perform the timed run.
>>
>> This avoids any impact of this bug fix on the semantics or timing
>> of the benchmark proper. I'm not sure offhand whether this
>> approach makes any difference for the concerns you had about
>> identifying/suppressing variable references inside quotes.
>
> I do not think this plan is workable, because of the \gset issue.
>
> I do not see that the conditional mutex and delayed PREPARE would have
> any significant (measurable) impact on an actual (reasonable)
> benchmark run.
>
> A workable solution would be that each client actually execute each
> script once before starting the actual benchmark. It would still need
> a mutex and also a sync barrier (which I'm proposing in some other
> thread). However this may raise some other issues because then some
> operations would be trigger out of the benchmarking run, which may or
> may not be desirable.
>
> So I'm not to keen to go that way, and I think the proposed solution
> is reasonable from a benchmarking point of view as the impact is
> minimal, although not zero.
>
CFM reminder.
Hi, this entry is "Waiting on Author" and the thread was inactive for a
while. I see this discussion still has some open questions. Are you
going to continue working on it, or should I mark it as "returned with
feedback" until a better time?
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lubennikova@postgrespro.ru, coelho@cri.ensmp.fr, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org, dgrowleyml@gmail.com, jaime.soler@gmail.com
Subject: Re: pgbench and timestamps (bounced)
In-Reply-To: <6c6af173-85b9-f0b3-4764-b0df415d2736@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox