Hi Justin,
Only one query is causing the issue, sharing the def of indexes. Please
have a look.
On Wed, 30 Mar 2022 at 01:09, Justin Pryzby <pry...@telsasoft.com> wrote:
> On Wed, Mar 30, 2022 at 12:52:05AM +0530, Rambabu g wrote:
> > > What indexes are defined on this table ?
> > > How large are they ?
> >
> > There are three indexes defined on the table, each one is around 20 to
> 25GB
> > and the indexes is create on
>
> Did you mean to say something else after "on" ?
>
> Show the definition of the indexes from psql \d
>
Index Definition :
postgres=# \d+ idx_empno
Index "l2.pd_activity_empi"
Column | Type | Key? | Definition | Storage | Stats
target
--------+-------------------------+------+------------+----------+--------------
empno | character varying(2000) | yes | empno | extended |
btree, for table "emp"
postgres=# \d+ id_dt
Index "dt"
Column | Type | Key? | Definition | Storage | Stats
target
--------+-----------------------------+------+------------+---------+--------------
dt | timestamp without time zone | yes | dt | plain |
btree, for table "emp"
postgres=# \d+ idx_tp
Index "idx_tp"
Column | Type | Key? | Definition | Storage | Stats
target
--------+-------------------------+------+------------+----------+--------------
tp | character varying(2000) | yes | tp | extended |
btree, for table "emp"
Query is been running for 30min.
> postgres=# explain select distinct empno from emp where sname='test'
> and tp='EMP NAME 1'
>
> Is this the only query that's performing poorly ?
> You should send explain (analyze,buffers) for the prolematic queries.
>
postgres=# select pid,(now()-query_start) as
age,wait_event_type,wait_event,query from pg_stat_activity where
state!='idle';
pid | age | wait_event_type | wait_event |
query
-------+-----------------+-----------------+---------------+-------------------------------------------------------------------------------------------------------------------
32154 | 00:09:56.131136 | IPC | ExecuteGather | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
847 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
848 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
849 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
850 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
851 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
852 | 00:09:56.131136 | IO | DataFileRead | explain
analyze select distinct empno from emp where sname='test' and tp='EMP
NAME 1'
645 | 00:00:00 | | | select
pid,(now()-query_start) as age,wait_event_type,wait_event,query from
pg_stat_activity where state!='idle'
postgres=# SELECT COUNT(nullif(isdirty,'f')) dirty, COUNT(1) as all,
COALESCE(c.relname, b.relfilenode::text) FROM pg_buffercache b LEFT JOIN
pg_class c ON b.relfilenode=pg_relation_filenode(c.oid) GROUP BY 3 ORDER BY
1 DESC,2 DESC LIMIT 9;
dirty | all | coalesce
-------+---------+---------------------------------
32 | 136 | fn_deployment
18 | 176 | fn_deployment_key
8 | 12 | event_logs_pkey
6 | 157 | event_logs
1 | 355 | pg_class
0 | 2890261 |
0 | 252734 | utput_status
0 | 378 | emp
0 | 299 | 1249
(9 rows)
-bash-4.2$ sar
Linux 3.10.0-1160.59.1.el7.x86_64 (ip-10-54-145-108.ec2.internal)
03/30/2022 _x86_64_ (24 CPU)
12:00:01 AM CPU %user %nice %system %iowait %steal
%idle
12:10:01 AM all 1.19 0.00 0.82 36.17 0.00
61.81
12:20:01 AM all 0.72 0.00 0.75 35.59 0.00
62.94
12:30:01 AM all 0.74 0.00 0.77 35.04 0.00
63.46
12:40:02 AM all 0.74 0.00 0.76 34.65 0.00
63.85
12:50:01 AM all 0.77 0.00 0.78 33.36 0.00
65.09
01:00:01 AM all 0.83 0.00 0.78 27.46 0.00
70.93
01:10:01 AM all 0.85 0.00 0.78 30.11 0.00
68.26
01:20:01 AM all 0.70 0.00 0.61 20.46 0.00
78.24
01:30:01 AM all 0.15 0.00 0.06 0.02 0.00
99.77
01:40:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
01:50:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
02:00:01 AM all 0.15 0.00 0.06 0.00 0.00
99.78
02:10:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
02:20:01 AM all 0.14 0.00 0.05 0.00 0.00
99.81
02:30:01 AM all 0.15 0.00 0.06 0.00 0.00
99.80
02:40:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
02:50:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
03:00:01 AM all 0.14 0.00 0.05 0.00 0.00
99.80
03:10:01 AM all 0.14 0.00 0.05 0.00 0.00
99.81
03:20:01 AM all 0.14 0.00 0.05 0.00 0.00
99.81
03:30:01 AM all 0.23 0.00 0.15 2.18 0.00
97.44
03:40:01 AM all 1.16 0.00 0.87 22.76 0.00
75.21
03:50:01 AM all 0.75 0.00 0.60 13.89 0.00
84.76
04:00:01 AM all 1.13 0.00 0.87 22.75 0.00
75.26
04:10:01 AM all 0.87 0.00 0.79 22.91 0.00
75.43
04:20:01 AM all 0.71 0.00 0.71 22.07 0.00
76.50
Average: all 0.50 0.00 0.41 13.81 0.00
85.28
-bash-4.2$ iostat
Linux 3.10.0-1160.59.1.el7.x86_64 (ip-.ec2.internal) 03/30/2022 _x86_64_ (24
CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.44 0.00 0.34 13.35 0.00 85.86
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
nvme1n1 1370.20 54514.54 4964.18 7297971937 664565000
nvme2n1 0.92 0.12 223.19 16085 29878260
nvme0n1 0.30 5.12 5.23 685029 699968
-bash-4.2$ iostat -d
Linux 3.10.0-1160.59.1.el7.x86_64 (ip-ec2.internal) 03/30/2022 _x86_64_ (24
CPU)
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
nvme1n1 1370.25 54518.06 4963.95 7298793425 664565248
nvme2n1 0.92 0.12 223.17 16085 29878260
nvme0n1 0.30 5.12 5.23 685029 699968
-bash-4.2$ free -g
total used free shared buff/cache
available
Mem: 92 1 0 2 90
87
Swap: 0 0 0
>
> > > > Hypervisor vendor: KVM
> > >
> > > Are KSM or THP enabled on the hypervisor ?
>
> > No, the Ec2 VM is delicate to postgres DB instances only.
>
> Oh, so this is an EC2 and you cannot change the hypervisor itself.
>
> > -bash-4.2$ tail /sys/kernel/mm/ksm/run
> /sys/kernel/mm/transparent_hugepage/khugepaged/defrag
> /sys/kernel/mm/transparent_hugepage/enabled
> /sys/kernel/mm/transparent_hugepage/defrag
> ...
> > ==> /sys/kernel/mm/transparent_hugepage/defrag <==
> > [always] madvise never
>
I doubt it will help, but you could try disabling these.
> It's a quick experiment anyway.
>
Disable THP
-bash-4.2$ tail /sys/kernel/mm/ksm/run
/sys/kernel/mm/transparent_hugepage/khugepaged/defrag
/sys/kernel/mm/transparent_hugepage/enabled
/sys/kernel/mm/transparent_hugepage/defrag
==> /sys/kernel/mm/ksm/run <==
0
==> /sys/kernel/mm/transparent_hugepage/khugepaged/defrag <==
1
==> /sys/kernel/mm/transparent_hugepage/enabled <==
always madvise [never]
==> /sys/kernel/mm/transparent_hugepage/defrag <==
always madvise [never]
Regards,
Rambabu.
