pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHuge table pg_shdepend
7+ messages / 5 participants
[nested] [flat]
* Huge table pg_shdepend
@ 2019-01-21 10:18 Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 10:33 ` Re: Huge table pg_shdepend Zuhair Khayyat <zuhair@lucidya.com>
2019-01-21 11:19 ` Re: Huge table pg_shdepend Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-01-21 11:42 ` Re: Huge table pg_shdepend Ron <ronljohnsonjr@gmail.com>
2019-01-21 11:50 ` Re: Huge table pg_shdepend Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Horst Düster @ 2019-01-21 10:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
I'm struggling with a huge table pg_shdepend. My database has a size of
640MB exclude the size of the system tables. But the real size of the db
is more than 2GB. The reason is a huge table pg_shdepend with more than
32000000 records. PgAdmin3 shows a table size of
1885MB and an index size of 1690MB. This looks strange for me, because
this size is a multiple amount of the db size itself. I believe, that
there are a lot of rubbish in that table, but I'm not sure.
I had run a vacuum full process, but this didn't help. Could anyone give
me an advice how to clean the table pg_shdepend or reduce the table size?
Any hit would be appreciated.
Regards
Horst
--
Dr. Horst Düster
Managing Director
Sourcepole AG - Linux & Open Source Solutions
Weberstrasse 5, 8004 Zürich, Switzerland
Tel: +41 (0)44 440 77 11
Direkt: +41 (0)44 515 67 70
horst.duester@sourcepole.ch http://www.sourcepole.ch
qgiscloud.com - Karten einfach vom Desktop ins Netz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
@ 2019-01-21 10:33 ` Zuhair Khayyat <zuhair@lucidya.com>
2019-01-21 11:53 ` Re: Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
3 siblings, 1 reply; 7+ messages in thread
From: Zuhair Khayyat @ 2019-01-21 10:33 UTC (permalink / raw)
To: Horst Düster <horst.duester@sourcepole.ch>; +Cc: pgsql-admin@lists.postgresql.org
Hi Horst,
Try pg_repack
http://reorg.github.io/pg_repack/
On Mon, 21 Jan 2019 at 1:18 PM Horst Düster <horst.duester@sourcepole.ch>
wrote:
> Hi
>
> I'm struggling with a huge table pg_shdepend. My database has a size of
> 640MB exclude the size of the system tables. But the real size of the db
> is more than 2GB. The reason is a huge table pg_shdepend with more than
> 32000000 records. PgAdmin3 shows a table size of
> 1885MB and an index size of 1690MB. This looks strange for me, because
> this size is a multiple amount of the db size itself. I believe, that
> there are a lot of rubbish in that table, but I'm not sure.
>
> I had run a vacuum full process, but this didn't help. Could anyone give
> me an advice how to clean the table pg_shdepend or reduce the table size?
>
> Any hit would be appreciated.
>
> Regards
> Horst
>
> --
> Dr. Horst Düster
> Managing Director
> Sourcepole AG - Linux & Open Source Solutions
> Weberstrasse 5, 8004 Zürich, Switzerland
> <https://maps.google.com/?q=Weberstrasse+5,+8004+Z%C3%BCrich,+Switzerland&entry=gmail&source=...;
> Tel: +41 (0)44 440 77 11
> Direkt: +41 (0)44 515 67 70
> horst.duester@sourcepole.ch http://www.sourcepole.ch
>
> qgiscloud.com - Karten einfach vom Desktop ins Netz
>
>
>
>
>
> --
<https://www.twitter.com/lucidya;
<https://www.linkedin.com/company/lucidya/;
<https://www.facebook.com/lucidya;
Zuhair Khayyat
Chief Technology Officer
Lucidya
+966503418330
lucidya.com
Please consider the environment before printing this email
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information and
is intended only for the individual named. If you are not the named
addressee you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system. If you
are not the intended recipient you are notified that disclosing,
copying, distributing or taking any action in reliance on the contents
of this information is strictly prohibited.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 10:33 ` Re: Huge table pg_shdepend Zuhair Khayyat <zuhair@lucidya.com>
@ 2019-01-21 11:53 ` Horst Düster <horst.duester@sourcepole.ch>
0 siblings, 0 replies; 7+ messages in thread
From: Horst Düster @ 2019-01-21 11:53 UTC (permalink / raw)
To: Zuhair Khayyat <zuhair@lucidya.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi zuhair
Thank's for your quick response. To use pg_repack it is not an option
unfortunately, because it's not possible to install additional software
at the db server. Are there other options to do with the postgresql
onboard tools?
Regards
Horst
Am 21.01.19 um 11:33 schrieb Zuhair Khayyat:
> Hi Horst,
>
> Try pg_repack
> http://reorg.github.io/pg_repack/
>
> On Mon, 21 Jan 2019 at 1:18 PM Horst Düster
> <horst.duester@sourcepole.ch <mailto:horst.duester@sourcepole.ch>> wrote:
>
> Hi
>
> I'm struggling with a huge table pg_shdepend. My database has a
> size of
> 640MB exclude the size of the system tables. But the real size of
> the db
> is more than 2GB. The reason is a huge table pg_shdepend with more
> than
> 32000000 records. PgAdmin3 shows a table size of
> 1885MB and an index size of 1690MB. This looks strange for me, because
> this size is a multiple amount of the db size itself. I believe, that
> there are a lot of rubbish in that table, but I'm not sure.
>
> I had run a vacuum full process, but this didn't help. Could
> anyone give
> me an advice how to clean the table pg_shdepend or reduce the
> table size?
>
> Any hit would be appreciated.
>
> Regards
> Horst
>
> --
> Dr. Horst Düster
> Managing Director
> Sourcepole AG - Linux & Open Source Solutions
> Weberstrasse 5, 8004 Zürich, Switzerland
> <https://maps.google.com/?q=Weberstrasse+5,+8004+Z%C3%BCrich,+Switzerland&entry=gmail&source=...;
> Tel: +41 (0)44 440 77 11
> Direkt: +41 (0)44 515 67 70
> horst.duester@sourcepole.ch <mailto:horst.duester@sourcepole.ch>
> http://www.sourcepole.ch
>
> qgiscloud.com <http://qgiscloud.com; - Karten einfach vom Desktop
> ins Netz
>
>
>
>
>
> --
> <https://www.twitter.com/lucidya><https://www.linkedin.com/company/lucidya/><https://www....;
> Zuhair Khayyat Chief Technology Officer Lucidya
> +966503418330 <tel:+966503418330> lucidya.com <http://lucidya.com;
>
> Please consider the environment before printing this email
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager. This message contains confidential information and
> is intended only for the individual named. If you are not the named
> addressee you should not disseminate, distribute or copy this e-mail.
> Please notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system. If you
> are not the intended recipient you are notified that disclosing,
> copying, distributing or taking any action in reliance on the contents
> of this information is strictly prohibited.
>
--
Dr. Horst Düster
Managing Director
Sourcepole AG - Linux & Open Source Solutions
Weberstrasse 5, 8004 Zürich, Switzerland
Tel: +41 (0)44 440 77 11
Direkt: +41 (0)44 515 67 70
horst.duester@sourcepole.ch http://www.sourcepole.ch
qgiscloud.com - Karten einfach vom Desktop ins Netz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
@ 2019-01-21 11:19 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-01-21 13:50 ` Re: Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
3 siblings, 1 reply; 7+ messages in thread
From: Andrew Gierth @ 2019-01-21 11:19 UTC (permalink / raw)
To: Horst Düster <horst.duester@sourcepole.ch>; +Cc: pgsql-admin@lists.postgresql.org
>>>>> "Horst" == Horst Düster <horst.duester@sourcepole.ch> writes:
Horst> Hi
Horst> I'm struggling with a huge table pg_shdepend. My database has a
Horst> size of 640MB exclude the size of the system tables. But the
Horst> real size of the db is more than 2GB. The reason is a huge table
Horst> pg_shdepend with more than 32000000 records.
pg_shdepend has a row for every object and every ACL entry that records
which role (other than the initial db superuser) owns the object or is
referenced from the ACL entry.
Do you have an unusually large number of tables (or other database
objects)?
Note that there's a single pg_shdepend table shared between all
databases in the cluster; it's not part of any single database but
rather is a "global" table. So what matters is the total number of
objects over all databases, not the size of any single db.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 11:19 ` Re: Huge table pg_shdepend Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2019-01-21 13:50 ` Horst Düster <horst.duester@sourcepole.ch>
0 siblings, 0 replies; 7+ messages in thread
From: Horst Düster @ 2019-01-21 13:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 21.01.19 um 12:19 schrieb Andrew Gierth:
> Note that there's a single pg_shdepend table shared between all
> databases in the cluster; it's not part of any single database but
> rather is a "global" table. So what matters is the total number of
> objects over all databases, not the size of any single db.
OK that declares the size of the table. We have more than 50'000
databases in one cluster.
Regards
Horst
--
Dr. Horst Düster
Managing Director
Sourcepole AG - Linux & Open Source Solutions
Weberstrasse 5, 8004 Zürich, Switzerland
Tel: +41 (0)44 440 77 11
Direkt: +41 (0)44 515 67 70
horst.duester@sourcepole.ch http://www.sourcepole.ch
qgiscloud.com - Karten einfach vom Desktop ins Netz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
@ 2019-01-21 11:42 ` Ron <ronljohnsonjr@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Ron @ 2019-01-21 11:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/21/19 4:18 AM, Horst Düster wrote:
> Hi
>
> I'm struggling with a huge table pg_shdepend. My database has a size of
> 640MB exclude the size of the system tables. But the real size of the db
> is more than 2GB. The reason is a huge table pg_shdepend with more than
> 32000000 records. PgAdmin3 shows a table size of
> 1885MB and an index size of 1690MB. This looks strange for me, because
> this size is a multiple amount of the db size itself. I believe, that
> there are a lot of rubbish in that table, but I'm not sure.
>
> I had run a vacuum full process, but this didn't help. Could anyone give
> me an advice how to clean the table pg_shdepend or reduce the table size?
Silly question: did you vacuum the postgres database, or your user database(s)?
Also, you should do the "vacuumdb --freeze --dbname=postgres" when nothing
else is attached to any databases, so that nothing else can be holding old
records in that table.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Huge table pg_shdepend
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
@ 2019-01-21 11:50 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Shreeyansh Dba @ 2019-01-21 11:50 UTC (permalink / raw)
To: Horst Düster <horst.duester@sourcepole.ch>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
It may be, when you perform vacuum full, some of the processes connected to
the same or another database that could be holding on to the pg_shdepend
table?
For reclaim the index size perform the reindex.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Mon, Jan 21, 2019 at 3:48 PM Horst Düster <horst.duester@sourcepole.ch>
wrote:
> Hi
>
> I'm struggling with a huge table pg_shdepend. My database has a size of
> 640MB exclude the size of the system tables. But the real size of the db
> is more than 2GB. The reason is a huge table pg_shdepend with more than
> 32000000 records. PgAdmin3 shows a table size of
> 1885MB and an index size of 1690MB. This looks strange for me, because
> this size is a multiple amount of the db size itself. I believe, that
> there are a lot of rubbish in that table, but I'm not sure.
>
> I had run a vacuum full process, but this didn't help. Could anyone give
> me an advice how to clean the table pg_shdepend or reduce the table size?
>
> Any hit would be appreciated.
>
> Regards
> Horst
>
> --
> Dr. Horst Düster
> Managing Director
> Sourcepole AG - Linux & Open Source Solutions
> Weberstrasse 5, 8004 Zürich, Switzerland
> Tel: +41 (0)44 440 77 11
> Direkt: +41 (0)44 515 67 70
> horst.duester@sourcepole.ch http://www.sourcepole.ch
>
> qgiscloud.com - Karten einfach vom Desktop ins Netz
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-01-21 13:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-21 10:18 Huge table pg_shdepend Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 10:33 ` Zuhair Khayyat <zuhair@lucidya.com>
2019-01-21 11:53 ` Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 11:19 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-01-21 13:50 ` Horst Düster <horst.duester@sourcepole.ch>
2019-01-21 11:42 ` Ron <ronljohnsonjr@gmail.com>
2019-01-21 11:50 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
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