Re: weird long time query
I think I should also report it as a bug since logically, it couldn't exist. On Wed, Dec 18, 2019 at 1:04 AM Tom Lane wrote: > Kaijiang Chen writes: > > I'm using postgres 9.4.17 on centos 7. > > I check the running queries with the following SQL: > > SELECT > > procpid, > > start, > > now() - start AS lap, > > current_query > > FROM > > (SELECT > > backendid, > > pg_stat_get_backend_pid(S.backendid) AS procpid, > > pg_stat_get_backend_activity_start(S.backendid) AS start, > > pg_stat_get_backend_activity(S.backendid) AS current_query > > FROM > > (SELECT pg_stat_get_backend_idset() AS backendid) AS S > > ) AS S > > WHERE > > current_query <> '' > > ORDER BY > > lap DESC; > > Don't know where you got this query from, but it's wrong for any PG > version more recent than (I think) 9.1. We don't use "" as an > indicator of idle sessions anymore; rather, those can be identified > by having state = 'idle'. What's in the query column for such a session > is its last query. > > > Then, I found a SQL that has run for some days (and still running): > > procpid | 32638 > > start | 2019-11-25 16:29:29.529318+08 > > lap | 21 days 18:24:54.707369 > > current_query | DEALLOCATE pdo_stmt_0388 > > It's not running. That was the last query it ran, back in November :-( > You could zap the session with pg_terminate_backend(), but > pg_cancel_backend() is not going to have any effect because there's > no active query. > > regards, tom lane >
Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction)
On Tue, Dec 17, 2019 at 08:03:41PM +, Piotr Włodarczyk wrote:
> Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1.
>
> During that we have a problem:
>
> command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port 50432
> --username postgres --schema-only --quote-all-identifiers --binary-upgrade
> --format=custom --file="pg_upgrade_dump_281535902.custom" 'dbname=sprint'
> >> "pg_upgrade_dump_281535902.log" 2>&1
> pg_dump: error: query failed: ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
> pg_dump: error: query was: LOCK TABLE
> "some_schemaa"."table_part_8000_2018q3" IN ACCESS SHARE MODE
>
> On current instance we have about one thousand of partitions, partitioned in
> two levels: first by id_product, and second level by quarter of the year, as
> you can see on above log.
>
> How have we to calculate shared memory, and (eventually
> max_locks_per_transaction) to be fit to the limits during upgrade?
Great question. Clearly, if you can run that (or similar) pg_dump command,
then you can pg_upgrade. I think you could also do pg_upgrade --check,
The query looks like
FROM pg_class c...
WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
..and then does:
if (tblinfo[i].dobj.dump &&
(tblinfo[i].relkind == RELKIND_RELATION ||
tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"LOCK TABLE %s IN
ACCESS SHARE MODE",
fmtQualifiedDumpable([i]));
ExecuteSqlStatement(fout, query->data);
}
..then filters by -N/-n/-t/-T (which doesn't apply to pg_upgrade):
selectDumpableTable([i], fout);
So it looks like COUNT(1) FROM pg_class WHERE relkind IN ('r','p') should do it.
But actually, during pg_upgrade, since nothing else is running, you actually
have max_connections*max_locks_per_transaction total locks.
Said differently, I think you could set max_locks_per_transaction to:
SELECT (SELECT COUNT(1) FROM pg_class WHERE relkind IN
('r','p'))/current_setting('max_connections')::int;
..probably with a fudge factor of +10 for any system process (and due to
integer truncation).
Someone might say that pg_upgrade or pg_dump could check for that specifically..
Justin
shared memory size during upgrade pgsql with partitions
Hello, Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1. During that we have a problem: command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="pg_upgrade_dump_281535902.custom" 'dbname=sprint' >> "pg_upgrade_dump_281535902.log" 2>&1 pg_dump: error: query failed: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. pg_dump: error: query was: LOCK TABLE "some_schemaa"."table_part_8000_2018q3" IN ACCESS SHARE MODE On current instance we have about one thousand of partitions, partitioned in two levels: first by id_product, and second level by quarter of the year, as you can see on above log. How have we to calculate shared memory, and (eventually max_locks_per_transaction) to be fit to the limits during upgrade? smime.p7s Description: S/MIME cryptographic signature
Re: weird long time query
Kaijiang Chen writes: > I'm using postgres 9.4.17 on centos 7. > I check the running queries with the following SQL: > SELECT > procpid, > start, > now() - start AS lap, > current_query > FROM > (SELECT > backendid, > pg_stat_get_backend_pid(S.backendid) AS procpid, > pg_stat_get_backend_activity_start(S.backendid) AS start, > pg_stat_get_backend_activity(S.backendid) AS current_query > FROM > (SELECT pg_stat_get_backend_idset() AS backendid) AS S > ) AS S > WHERE > current_query <> '' > ORDER BY > lap DESC; Don't know where you got this query from, but it's wrong for any PG version more recent than (I think) 9.1. We don't use "" as an indicator of idle sessions anymore; rather, those can be identified by having state = 'idle'. What's in the query column for such a session is its last query. > Then, I found a SQL that has run for some days (and still running): > procpid | 32638 > start | 2019-11-25 16:29:29.529318+08 > lap | 21 days 18:24:54.707369 > current_query | DEALLOCATE pdo_stmt_0388 It's not running. That was the last query it ran, back in November :-( You could zap the session with pg_terminate_backend(), but pg_cancel_backend() is not going to have any effect because there's no active query. regards, tom lane
Re: Consecutive Query Executions with Increasing Execution Time
On Tue, Dec 17, 2019 at 8:08 AM Laurenz Albe wrote: > On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wrote: > > Peter Geoghegan writes: > > > Why do the first and the twentieth executions of the query have almost > > > identical "buffers shared/read" numbers? That seems odd. > > > > It's repeat execution of the same query, so that doesn't seem odd to me. > > Really? Shouldn't the blocks be in shared buffers after a couple > of executions? > If it is doing a seq scan (I don't know if it is) they intentionally use a small ring buffer to, so they evict their own recently used blocks, rather than evicting other people's blocks. So these blocks won't build up in shared_buffers very rapidly just on the basis of repeated seq scans. Cheers, Jeff
Re: Consecutive Query Executions with Increasing Execution Time
On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wrote: > Peter Geoghegan writes: > > Why do the first and the twentieth executions of the query have almost > > identical "buffers shared/read" numbers? That seems odd. > > It's repeat execution of the same query, so that doesn't seem odd to me. Really? Shouldn't the blocks be in shared buffers after a couple of executions? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: weird long time query
út 17. 12. 2019 v 11:45 odesílatel Kaijiang Chen napsal: > I'm using postgres 9.4.17 on centos 7. > I check the running queries with the following SQL: > SELECT > procpid, > start, > now() - start AS lap, > current_query > FROM > (SELECT > backendid, > pg_stat_get_backend_pid(S.backendid) AS procpid, > pg_stat_get_backend_activity_start(S.backendid) AS start, > pg_stat_get_backend_activity(S.backendid) AS current_query > FROM > (SELECT pg_stat_get_backend_idset() AS backendid) AS S > ) AS S > WHERE > current_query <> '' > ORDER BY > lap DESC; > I think so this query is weird - probably this query was finished you should to use constraint WHERE state <> 'idle'; Regards Pavel > Then, I found a SQL that has run for some days (and still running): > procpid | 32638 > start | 2019-11-25 16:29:29.529318+08 > lap | 21 days 18:24:54.707369 > current_query | DEALLOCATE pdo_stmt_0388 > > I tried to kill it with: SELECT pg_cancel_backend(32638) but it takes no > effects. > > What's this query and what shall I do for it? > > Best Wishes > Kaijiang > >
weird long time query
I'm using postgres 9.4.17 on centos 7. I check the running queries with the following SQL: SELECT procpid, start, now() - start AS lap, current_query FROM (SELECT backendid, pg_stat_get_backend_pid(S.backendid) AS procpid, pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query <> '' ORDER BY lap DESC; Then, I found a SQL that has run for some days (and still running): procpid | 32638 start | 2019-11-25 16:29:29.529318+08 lap | 21 days 18:24:54.707369 current_query | DEALLOCATE pdo_stmt_0388 I tried to kill it with: SELECT pg_cancel_backend(32638) but it takes no effects. What's this query and what shall I do for it? Best Wishes Kaijiang
