On 21-07-22 20:35, Justin Pryzby wrote:
On Thu, Jul 21, 2022 at 06:26:58PM -0400, Anthony Sotolongo wrote:
Hi all:
Here's a patch to add counters about planned/executed for parallelism to
pg_stat_statements, as a way to follow-up on if the queries are
planning/executing with parallelism, this can help to understand if you have
a good/bad configuration or if your hardware is enough
+1, I was missing something like this before, but it didn't occur to me to use
PSS:
First of all, thanks for review the the patch and for the comments
https://www.postgresql.org/message-id/20200310190142.gb29...@telsasoft.com
My hope is to answer to questions like these:
. is query (ever? usually?) using parallel paths?
. is query usefully using parallel paths?
. what queries are my max_parallel_workers(_per_process) being used for ?
. Are certain longrunning or frequently running queries which are using
parallel paths using all max_parallel_workers and precluding other queries
from using parallel query ? Or, are semi-short queries sometimes precluding
longrunning queries from using parallelism, when the long queries would
better benefit ?
This patch is storing the number of times the query was planned/executed using
parallelism, but not the number of workers. Would it make sense to instead
store the the *number* of workers launched/planned ? Otherwise, it might be
that a query is consistently planned to use a large number of workers, but then
runs with few. I'm referring to the fields shown in "explain/analyze". (Then,
the 2nd field should be renamed to "launched").
Workers Planned: 2
Workers Launched: 2
The main idea of the patch is to store the number of times the
statements were planned and executed in parallel, not the number of
workers used in the execution. Of course, what you mention can be
helpful, it will be given a review to see how it can be achieved
I don't think this is doing the right thing for prepared statements, like
PQprepare()/PQexecPrepared(), or SQL: PREPARE p AS SELECT; EXECUTE p;
Right now, the docs say that it shows the "number of times the statement was
planned to use parallelism", but the planning counter is incremented during
each execution. PSS already shows "calls" and "plans" separately. The
documentation doesn't mention prepared statements as a reason why they wouldn't
match, which seems like a deficiency.
We will check it and see how fix it
This currently doesn't count parallel workers used by utility statements, such
as CREATE INDEX and VACUUM (see max_parallel_maintenance_workers). If that's
not easy to do, mention that in the docs as a limitation.
We will update the documentation with information related to this comment
You should try to add some test to contrib/pg_stat_statements/sql, or add
parallelism test to an existing test. Note that the number of parallel workers
launched isn't stable, so you can't test that part..
You modified pgss_store() to take two booleans, but pass "NULL" instead of
"false". Curiously, of all the compilers in cirrusci, only MSVC complained ..
"planed" is actually spelled "planned", with two enns.
The patch has some leading/trailing whitespace (maybe shown by git log
depending on your configuration).
OK, we will fix it
Please add this patch to the next commitfest.
https://commitfest.postgresql.org/39/
