date:20191103

Re: Refactor parse analysis of EXECUTE command

2019-11-03 Thread Peter Eisentraut


On 2019-11-02 16:00, Tom Lane wrote:

Peter Eisentraut  writes:

This patch moves the parse analysis component of ExecuteQuery() and
EvaluateParams() into a new transformExecuteStmt() that is called from
transformStmt().


Uhmm ... no actual patch attached?


Oops, here it is.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 7c5bc30a02ec34646c8e49af1499fe4113bc9e5e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Thu, 31 Oct 2019 09:54:07 +0100
Subject: [PATCH] Refactor parse analysis of EXECUTE command

Move the parse analysis component of ExecuteQuery() and
EvaluateParams() into a new transformExecuteStmt() that is called from
transformStmt().  This makes EXECUTE behave more like other utility
commands.  It also allows error messages to have position information,
and it allows using external parameters in the arguments of the
EXECUTE command.
---
 src/backend/commands/createas.c   |  2 +-
 src/backend/commands/prepare.c| 72 ++
 src/backend/parser/analyze.c  | 89 +++
 src/backend/tcop/utility.c|  2 +-
 src/include/commands/prepare.h|  2 +-
 src/test/regress/expected/prepare.out | 25 
 src/test/regress/sql/prepare.sql  | 21 +++
 7 files changed, 143 insertions(+), 70 deletions(-)

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index b7d220699f..e4244f84e2 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -271,7 +271,7 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char 
*queryString,
ExecuteStmt *estmt = castNode(ExecuteStmt, query->utilityStmt);
 
Assert(!is_matview);/* excluded by syntax */
-   ExecuteQuery(estmt, into, queryString, params, dest, 
completionTag);
+   ExecuteQuery(estmt, into, params, dest, completionTag);
 
/* get object address that intorel_startup saved for us */
address = ((DR_intorel *) dest)->reladdr;
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 7e0a041fab..0aba6a7b00 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -47,7 +47,7 @@ static HTAB *prepared_queries = NULL;
 
 static void InitQueryHashTable(void);
 static ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *params,
-   const 
char *queryString, EState *estate);
+   EState 
*estate);
 static Datum build_regtype_array(Oid *param_types, int num_params);
 
 /*
@@ -189,16 +189,10 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString,
  * indicated by passing a non-null intoClause.  The DestReceiver is already
  * set up correctly for CREATE TABLE AS, but we still have to make a few
  * other adjustments here.
- *
- * Note: this is one of very few places in the code that needs to deal with
- * two query strings at once.  The passed-in queryString is that of the
- * EXECUTE, which we might need for error reporting while processing the
- * parameter expressions.  The query_string that we copy from the plan
- * source is that of the original PREPARE.
  */
 void
 ExecuteQuery(ExecuteStmt *stmt, IntoClause *intoClause,
-const char *queryString, ParamListInfo params,
+ParamListInfo params,
 DestReceiver *dest, char *completionTag)
 {
PreparedStatement *entry;
@@ -229,8 +223,7 @@ ExecuteQuery(ExecuteStmt *stmt, IntoClause *intoClause,
 */
estate = CreateExecutorState();
estate->es_param_list_info = params;
-   paramLI = EvaluateParams(entry, stmt->params,
-queryString, 
estate);
+   paramLI = EvaluateParams(entry, stmt->params, estate);
}
 
/* Create a new portal to run the query in */
@@ -316,7 +309,6 @@ ExecuteQuery(ExecuteStmt *stmt, IntoClause *intoClause,
  *
  * pstmt: statement we are getting parameters for.
  * params: list of given parameter expressions (raw parser output!)
- * queryString: source text for error messages.
  * estate: executor state to use.
  *
  * Returns a filled-in ParamListInfo -- this can later be passed to
@@ -324,72 +316,19 @@ ExecuteQuery(ExecuteStmt *stmt, IntoClause *intoClause,
  * during query execution.
  */
 static ParamListInfo
-EvaluateParams(PreparedStatement *pstmt, List *params,
-  const char *queryString, EState *estate)
+EvaluateParams(PreparedStatement *pstmt, List *params, EState *estate)
 {
Oid*param_types = pstmt->plansource->param_types;
int num_params = pstmt->plansource->num_params;
-   int

Re: cost based vacuum (parallel)

2019-11-03 Thread Komяpa

>
>
> This is somewhat similar to a memory usage problem with a
> parallel query where each worker is allowed to use up to work_mem of
> memory.  We can say that the users using parallel operation can expect
> more system resources to be used as they want to get the operation
> done faster, so we are fine with this.  However, I am not sure if that
> is the right thing, so we should try to come up with some solution for
> it and if the solution is too complex, then probably we can think of
> documenting such behavior.
>

In cloud environments (Amazon + gp2) there's a budget on input/output
operations. If you cross it for long time, everything starts looking like
you work with a floppy disk.

For the ease of configuration, I would need a "max_vacuum_disk_iops" that
would limit number of input-output operations by all of the vacuums in the
system. If I set it to less than value of budget refill, I can be sure than
that no vacuum runs too fast to impact any sibling query.

There's also value in non-throttled VACUUM for smaller tables. On gp2 such
things will be consumed out of surge budget, and its size is known to
sysadmin. Let's call it "max_vacuum_disk_surge_iops" - if a relation has
less blocks than this value and it's a blocking in any way situation
(antiwraparound, interactive console, ...) - go on and run without
throttling.

For how to balance the cost: if we know a number of vacuum processes that
were running in the previous second, we can just divide a slot for this
iteration by that previous number.

To correct for overshots, we can subtract the previous second's overshot
from next one's. That would also allow to account for surge budget usage
and let it refill, pausing all autovacuum after a manual one for some time.

Precision of accounting limiting count of operations more than once a
second isn't beneficial for this use case.

Please don't forget that processing one page can become several iops (read,
write, wal).

Does this make sense? :)

Re: [HACKERS] Block level parallel vacuum

2019-11-03 Thread Masahiko Sawada

On Mon, 4 Nov 2019 at 14:02, Amit Kapila  wrote:
>
> On Fri, Nov 1, 2019 at 2:21 PM Masahiko Sawada  wrote:
> >
> > I think that two approaches make parallel vacuum worker wait in
> > different way: in approach(a) the vacuum delay works as if vacuum is
> > performed by single process, on the other hand in approach(b) the
> > vacuum delay work for each workers independently.
> >
> > Suppose that the total number of blocks to vacuum is 10,000 blocks,
> > the cost per blocks is 10, the cost limit is 200 and sleep time is 5
> > ms. In single process vacuum the total sleep time is 2,500ms (=
> > (10,000 * 10 / 200) * 5). The approach (a) is the same, 2,500ms.
> > Because all parallel vacuum workers use the shared balance value and a
> > worker sleeps once the balance value exceeds the limit. In
> > approach(b), since the cost limit is divided evenly the value of each
> > workers is 40 (e.g. when 5 parallel degree). And suppose each workers
> > processes blocks  evenly,  the total sleep time of all workers is
> > 12,500ms (=(2,000 * 10 / 40) * 5 * 5). I think that's why we can
> > compute the sleep time of approach(b) by dividing the total value by
> > the number of parallel workers.
> >
> > IOW the approach(b) makes parallel vacuum delay much more than normal
> > vacuum and parallel vacuum with approach(a) even with the same
> > settings. Which behaviors do we expect?
> >
>
> Yeah, this is an important thing to decide.  I don't think that the
> conclusion you are drawing is correct because it that is true then the
> same applies to the current autovacuum work division where we divide
> the cost_limit among workers but the cost_delay is same (see
> autovac_balance_cost).  Basically, if we consider the delay time of
> each worker independently, then it would appear that a parallel vacuum
> delay with approach (b) is more, but that is true only if the workers
> run serially which is not true.
>
> > I thought the vacuum delay for
> > parallel vacuum should work as if it's a single process vacuum as we
> > did for memory usage. I might be missing something. If we prefer
> > approach(b) I should change the patch so that the leader process
> > divides the cost limit evenly.
> >
>
> I am also not completely sure which approach is better but I slightly
> lean towards approach (b).

Can we get the same sleep time as approach (b) if we divide the cost
limit by the number of workers and have the shared cost balance (i.e.
approach (a) with dividing the cost limit)? Currently the approach (b)
seems better but I'm concerned that it might unnecessarily delay
vacuum if some indexes are very small or bulk-deletions of indexes
does almost nothing such as brin.

>
>   I think we need input from some other
> people as well.  I will start a separate thread to discuss this and
> see if that helps to get the input from others.

+1

--
Masahiko Sawada  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pglz performance

2019-11-03 Thread Andrey Borodin

Hi Tels!
Thanks for your interest in fast decompression.

> 3 нояб. 2019 г., в 12:24, Tels  написал(а):
> 
> I wonder if you agree and what would happen if you try this variant on your 
> corpus tests.

I've tried some different optimization for literals. For example loop 
unrolling[0] and literals bulk-copying.
This approaches were brining some performance improvement. But with noise. 
Statistically they were somewhere better, somewhere worse, net win, but that 
"net win" depends on what we consider important data and important platform.

Proposed patch makes clearly decompression faster on any dataset, and platform.
I believe improving pglz further is viable, but optimizations like common data 
prefix seems more promising to me.
Also, I think we actually need real codecs like lz4, zstd and brotli instead of 
our own invented wheel.

If you have some spare time - Pull Requests to test_pglz are welcome, lets 
benchmark more micro optimizations, it brings a lot of fun :)


--
Andrey Borodin
Open source RDBMS development team leader
Yandex.Cloud

[0] https://github.com/x4m/test_pglz/blob/master/pg_lzcompress_hacked.c#L166

For parallel vacuum [1], we were discussing what is the best way to
divide the cost among parallel workers but we didn't get many inputs
apart from people who are very actively involved in patch development.
I feel that we need some more inputs before we finalize anything, so
starting a new thread.

The initial version of the patch has a very rudimentary way of doing
it which means each parallel vacuum worker operates independently
w.r.t vacuum delay and cost. This will lead to more I/O in the system
than the user has intended to do. Assume that the overall I/O allowed
for vacuum operation is X after which it will sleep for some time,
reset the balance and continue. In the patch, each worker will be
allowed to perform X before which it can sleep and also there is no
coordination for the same with master backend which would have done
some I/O for the heap. So, in the worst-case scenario, there can be n
times more I/O where n is the number of workers doing the parallel
operation. This is somewhat similar to a memory usage problem with a
parallel query where each worker is allowed to use up to work_mem of
memory. We can say that the users using parallel operation can expect
more system resources to be used as they want to get the operation
done faster, so we are fine with this. However, I am not sure if that
is the right thing, so we should try to come up with some solution for
it and if the solution is too complex, then probably we can think of
documenting such behavior.

The two approaches to solve this problem being discussed in that
thread [1] are as follows:
(a) Allow the parallel workers and master backend to have a shared
view of vacuum cost related parameters (mainly VacuumCostBalance) and
allow each worker to update it and then based on that decide whether
it needs to sleep. Sawada-San has done the POC for this approach.
See v32-0004-PoC-shared-vacuum-cost-balance in email [2]. One
drawback of this approach could be that we allow the worker to sleep
even though the I/O has been performed by some other worker.

(b) The other idea could be that we split the I/O among workers
something similar to what we do for auto vacuum workers (see
autovac_balance_cost). The basic idea would be that before launching
workers, we need to compute the remaining I/O (heap operation would
have used something) after which we need to sleep and split it equally
across workers. Here, we are primarily thinking of dividing
VacuumCostBalance and VacuumCostLimit parameters. Once the workers
are finished, they need to let master backend know how much I/O they
have consumed and then master backend can add it to it's current I/O
consumed. I think we also need to rebalance the cost of remaining
workers once some of the worker's exit. Dilip has prepared a POC
patch for this, see 0002-POC-divide-vacuum-cost-limit in email [3].

I think approach-2 is better in throttling the system as it doesn't
have the drawback of the first approach, but it might be a bit tricky
to implement.

As of now, the POC for both the approaches has been developed and we
see similar results for both approaches, but we have tested simpler
cases where each worker has similar amount of I/O to perform.

Thoughts?

[1] - https://commitfest.postgresql.org/25/1774/
[2] -
https://www.postgresql.org/message-id/CAD21AoAqT17QwKJ_sWOqRxNvg66wMw1oZZzf9Rt-E-zD%2BXOh_Q%40mail.gmail.com
[3] -
https://www.postgresql.org/message-id/CAFiTN-thU-z8f04jO7xGMu5yUUpTpsBTvBrFW6EhRf-jGvEz%3Dg%40mail.gmail.com

43 matches

Mail list logo