Hi,
I created an index on a 11devel base while sampling pg_stat_activity with a little tool. Tool catches a line if state = active. Collected rows are aggregated and sorted by activity percentage.
Test environment :
select version();
version
----------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11devel (Debian 11~~devel~20180227.2330-1~420.git51057fe.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-5) 7.3.0, 64-bit
(1 ligne)
Temps : 0,762 ms
create table t1(c1 bigint, c2 double precision, c3 text);
CREATE TABLE
insert into t1 select generate_series(1,100000000,1), random(), md5(random()::text) ;
INSERT 0 100000000
With a select (select max(c1) from t1 group by c2;) I have this kind of output :
./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"
traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11
INFORMATION, no connection parameters provided, connecting to traqueur database ...
INFORMATION, connected to traqueur database
INFORMATION, PostgreSQL version : 110000
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
busy_pc | distinct_exe | pid | backend_type | query | wait_event_type | wait_event
---------+--------------+-------+-----------------+-------------------------------------+-----------------+--------------
76 | 1 / 151 | 10065 | parallel worker | select max(c1) from t1 group by c2; | IO | DataFileRead
73 | 1 / 146 | 8262 | client backend | select max(c1) from t1 group by c2; | IO | DataFileRead
72 | 1 / 144 | 10066 | parallel worker | select max(c1) from t1 group by c2; | IO | DataFileRead
26 | 1 / 53 | 10066 | parallel worker | select max(c1) from t1 group by c2; | |
26 | 1 / 51 | 8262 | client backend | select max(c1) from t1 group by c2; | |
24 | 1 / 47 | 10065 | parallel worker | select max(c1) from t1 group by c2; | |
2 | 1 / 3 | 10066 | parallel worker | select max(c1) from t1 group by c2; | IO | BufFileWrite
2 | 1 / 3 | 8262 | client backend | select max(c1) from t1 group by c2; | IO | BufFileWrite
1 | 1 / 2 | 10065 | parallel worker | select max(c1) from t1 group by c2; | IO | BufFileWrite
With an index creation (create index t1_i1 on t1(c1, c2);) I have this kind of output :
./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"
traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11
INFORMATION, no connection parameters provided, connecting to traqueur database ...
INFORMATION, connected to traqueur database
INFORMATION, PostgreSQL version : 110000
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
busy_pc | distinct_exe | pid | backend_type | query | wait_event_type | wait_event
---------+--------------+------+----------------+-----------------------------------+-----------------+--------------
68 | 1 / 136 | 8262 | client backend | create index t1_i1 on t1(c1, c2); | IO | DataFileRead
26 | 1 / 53 | 8262 | client backend | create index t1_i1 on t1(c1, c2); | |
6 | 1 / 11 | 8262 | client backend | create index t1_i1 on t1(c1, c2); | IO | BufFileWrite
(3 rows)
No parallel worker. At least one parallel worker was active though, I could see its work with a direct query on pg_stat_activity or a ps -ef :
...
postgres 8262 8230 7 08:54 ? 00:22:46 postgres: 11/main: postgres postgres [local] CREATE INDEX
...
postgres 9833 8230 23 14:17 ? 00:00:33 postgres: 11/main: parallel worker for PID 8262
...
Tool only catches activity of the client backend cause column state of pg_stat_activity is null for the parallel workers in this case. I added an option to do a "(state = 'active' or wait_event_is not null)" It's not 100% accurate though : I miss the activity
of the parallel workers which is not waiting and it’s more difficult to know who helps whom since query is also null.
I can imagine various workarounds but 11 is in devel and maybe columns active & query of pg_stat_activity will be filled for the parallel workers even for an index creation ?
Best regards
Phil
