Обсуждение: HELP? Postgres filling up disk
I posted over the weekend about this issue under the subject of "VACUUM" but the replies I got did not help. We have several large tables which seem to be taking 4 times as much disk space as they should?
Let me give a little background:
Every night, we do a pg_dump of our production DB and a pg_restore of this dumb into a standby DB. What I notice is the size of the standby DB is MORE THAN HALF the size of production.
For example, here is the oid2name dump of a table called users on PRODUCTION:
$ oid2name -d EBPRD1 -t users
Oid of table users from database "EBPRD1":
_______________________________
17260 = users
$ ls -l 17260*
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.1
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.2
-rw------- 1 postgres postgres 165445632 Dec 18 17:51 17260.3
Oid of table users from database "EBPRD1":
_______________________________
17260 = users
$ ls -l 17260*
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.1
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.2
-rw------- 1 postgres postgres 165445632 Dec 18 17:51 17260.3
but the same table on the standby DB yields this:
$ oid2name -d EBPRDS1 -t users
Oid of table users from database "EBPRDS1":
_______________________________
3828262123 = users
$ ls -l 3828262123
-rw------- 1 postgres postgres 1073741824 Dec 18 16:55 3828262123
Oid of table users from database "EBPRDS1":
_______________________________
3828262123 = users
$ ls -l 3828262123
-rw------- 1 postgres postgres 1073741824 Dec 18 16:55 3828262123
I tried a full VACUUM of the users table on production, but I didn't get any disk space back. I also looked thru all oids and there are no pg_toast files of significance. I am on postgres 7.2 How do I reclaim this disk space?
Any help would be GREATLY Appreciated!
thanks in advance
HT Levine <htlevine@ebates.com> writes: > For example, here is the oid2name dump of a table called users on > PRODUCTION: > $ oid2name -d EBPRD1 -t users > Oid of table users from database "EBPRD1": > _______________________________ > 17260 = users > $ ls -l 17260* > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260 > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.1 > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.2 > -rw------- 1 postgres postgres 165445632 Dec 18 17:51 17260.3 > but the same table on the standby DB yields this: > $ oid2name -d EBPRDS1 -t users > Oid of table users from database "EBPRDS1": > _______________________________ > 3828262123 = users > $ ls -l 3828262123 > -rw------- 1 postgres postgres 1073741824 Dec 18 16:55 3828262123 And? Your transcript does not prove that there's not 3828262123.1 and so forth ... given that 3828262123 is a full 1Gb, I'd be willing to bet lunch that 3828262123.1 exists too ... regards, tom lane
Sorry, I've spent much time examining this in detail, so if I neglected the * in the transcript I pasted you, it still doesn't matter. There is a .1 version (see my transcript below), BUT THAT'S IT . So my original argument is still unanswered: there are still extra files on production that aren't coming over in the standby : it's the .2 and .3 that I don't want in the production DB. I have this pattern acros several large tables and it's costing us disk space... $ oid2name -d EBPRDS1 -t users Oid of table users from database "EBPRDS1": _______________________________ 3828262123 = users $ ls -l 3828262123* -rw------- 1 postgres postgres 1073741824 Dec 18 23:08 3828262123 -rw------- 1 postgres postgres 558899200 Dec 18 23:16 3828262123.1 "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:15552.1040274982@sss.pgh.pa.us... > HT Levine <htlevine@ebates.com> writes: > > For example, here is the oid2name dump of a table called users on > > PRODUCTION: > > $ oid2name -d EBPRD1 -t users > > Oid of table users from database "EBPRD1": > > _______________________________ > > 17260 = users > > $ ls -l 17260* > > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260 > > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.1 > > -rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.2 > > -rw------- 1 postgres postgres 165445632 Dec 18 17:51 17260.3 > > > but the same table on the standby DB yields this: > > > $ oid2name -d EBPRDS1 -t users > > Oid of table users from database "EBPRDS1": > > _______________________________ > > 3828262123 = users > > $ ls -l 3828262123 > > -rw------- 1 postgres postgres 1073741824 Dec 18 16:55 3828262123 > > And? Your transcript does not prove that there's not 3828262123.1 and > so forth ... given that 3828262123 is a full 1Gb, I'd be willing to bet > lunch that 3828262123.1 exists too ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
"HT" <htlevine@ebates.com> writes: > $ ls -l 3828262123* > -rw------- 1 postgres postgres 1073741824 Dec 18 23:08 3828262123 > -rw------- 1 postgres postgres 558899200 Dec 18 23:16 3828262123.1 Okay, that looks more plausible. I think you need a VACUUM FULL to recover the wasted space in the master table, followed by more frequent plain VACUUMs to keep it from bloating. It's also almost certain that you need to increase the FSM parameters in postgresql.conf. See recent discussions in the archives about sizing FSM properly. regards, tom lane
will do! I'll let you know how it goes but it will be after xmas cause we can have no down-time right now. thanks! "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:7309.1040338410@sss.pgh.pa.us... > "HT" <htlevine@ebates.com> writes: > > $ ls -l 3828262123* > > -rw------- 1 postgres postgres 1073741824 Dec 18 23:08 3828262123 > > -rw------- 1 postgres postgres 558899200 Dec 18 23:16 3828262123.1 > > Okay, that looks more plausible. > > I think you need a VACUUM FULL to recover the wasted space in the master > table, followed by more frequent plain VACUUMs to keep it from bloating. > It's also almost certain that you need to increase the FSM parameters in > postgresql.conf. See recent discussions in the archives about sizing > FSM properly. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster