pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPG 9.5 2 tables same DDL with diff size
5+ messages / 3 participants
[nested] [flat]
* PG 9.5 2 tables same DDL with diff size
@ 2018-01-09 22:54 ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: ghiureai @ 2018-01-09 22:54 UTC (permalink / raw)
To: pgsql-performance
HI List
I am trying to understand the following :
have 2 identical PG cluster on diff hosts, same postgresql.conf, same
db schema :
same tale DDL and row counts but different size ( 14GB diff ), I run
reindex and full vacuum analyze, but I can not decrease the size of
larger table(50GB) to match the size in second
PG cluster.
any tips what can make this 2 tables to have diff size except the host (
same OS and PG version 9.5.3)?
Thank you
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: PG 9.5 2 tables same DDL with diff size
@ 2018-01-10 15:14 Igor Neyman <ineyman@perceptron.com>
parent: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Igor Neyman @ 2018-01-10 15:14 UTC (permalink / raw)
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>; pgsql-performance
-----Original Message-----
From: ghiureai [mailto:isabella.ghiurea@nrc-cnrc.gc.ca]
Sent: Tuesday, January 09, 2018 5:54 PM
To: pgsql-performance@postgresql.org
Subject: PG 9.5 2 tables same DDL with diff size
HI List
I am trying to understand the following :
have 2 identical PG cluster on diff hosts, same postgresql.conf, same db schema :
same tale DDL and row counts but different size ( 14GB diff ), I run reindex and full vacuum analyze, but I can not decrease the size of larger table(50GB) to match the size in second
PG cluster.
any tips what can make this 2 tables to have diff size except the host ( same OS and PG version 9.5.3)?
Thank you
________________________________________________________________________________________________
Table is still bloated because of some long running transactions, which don't allow full vacuum to do its job?
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: PG 9.5 2 tables same DDL with diff size
@ 2018-01-10 15:48 Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 5+ messages in thread
From: Isabella Ghiurea @ 2018-01-10 15:48 UTC (permalink / raw)
To: pgsql-performance
I run full vacuum and reindex on largest table (50GB) while there was no
server activities so I assume no transaction was holding a lock on table
since the full vacuum was able to run, anything where I should consider
looking ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: PG 9.5 2 tables same DDL with diff size
@ 2018-01-10 16:10 Igor Neyman <ineyman@perceptron.com>
parent: Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Igor Neyman @ 2018-01-10 16:10 UTC (permalink / raw)
To: Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>; pgsql-performance
-----Original Message-----
From: Isabella Ghiurea [mailto:isabella.ghiurea@nrc-cnrc.gc.ca]
Sent: Wednesday, January 10, 2018 10:48 AM
To: pgsql-performance@postgresql.org
Subject: RE: PG 9.5 2 tables same DDL with diff size
Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.
I run full vacuum and reindex on largest table (50GB) while there was no server activities so I assume no transaction was holding a lock on table since the full vacuum was able to run, anything where I should consider looking ?
__________________________________________________________________________________________________________
Yes, in pg_stat_activity look for idle transactions that started long time ago.
To prevent vacuum from doing its job they don't need to lock the table, they could just prevent from cleaning "old" row versions.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 9.5 2 tables same DDL with diff size
@ 2018-01-10 18:49 ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 5+ messages in thread
From: ghiureai @ 2018-01-10 18:49 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; pgsql-performance
Thank you Igor, I was able to eliminate the 15GB bloating for a 35GB
table size , only after I restart the Pg server with one single
connections and run a full vacuum for table.
Isabella
On 10/01/18 11:10 AM, Igor Neyman wrote:
> -----Original Message-----
> From: Isabella Ghiurea [mailto:isabella.ghiurea@nrc-cnrc.gc.ca]
> Sent: Wednesday, January 10, 2018 10:48 AM
> To: pgsql-performance@postgresql.org
> Subject: RE: PG 9.5 2 tables same DDL with diff size
>
> Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.
>
>
> I run full vacuum and reindex on largest table (50GB) while there was no server activities so I assume no transaction was holding a lock on table since the full vacuum was able to run, anything where I should consider looking ?
>
>
> __________________________________________________________________________________________________________
>
> Yes, in pg_stat_activity look for idle transactions that started long time ago.
> To prevent vacuum from doing its job they don't need to lock the table, they could just prevent from cleaning "old" row versions.
>
> Regards,
> Igor Neyman
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-01-10 18:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 22:54 PG 9.5 2 tables same DDL with diff size ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
2018-01-10 15:14 ` Igor Neyman <ineyman@perceptron.com>
2018-01-10 15:48 ` Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
2018-01-10 16:10 ` Igor Neyman <ineyman@perceptron.com>
2018-01-10 18:49 ` ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox