Обсуждение: disk usage spike
I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed form, it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).
What would cause such a spike in disk usage? And why would it stay at such a high level for 2 days?
This is postgresql 8.1.4 on a slightly modified RHEL4 system (2.6.17 kernel).
At the time the spike occurred (about 2pm), I may have been running some simple queries against various tables to determine usage statistics (eg, how many rows in table a, sum of column b in table c, etc (maximum of 100,000 rows in the tables, <10 columns), no nasty joins or anything). Also, I may have extracted a couple of large objects in full (not more than 200MB).
Otherwise, the database processes a few (< 20) small (< 1k data) inserts/updates/deletes per minute. This would have been the only traffic to the database after friday afternoon when I went home.
Autovacuum is turned on and the system had finished importing a large amount of data (50GB) earlier (4am) that morning. Is it possible the spike was caused by autovacuum re-indexing the pg_largeobject table?
Thanks,
Geoff.
What would cause such a spike in disk usage? And why would it stay at such a high level for 2 days?
This is postgresql 8.1.4 on a slightly modified RHEL4 system (2.6.17 kernel).
At the time the spike occurred (about 2pm), I may have been running some simple queries against various tables to determine usage statistics (eg, how many rows in table a, sum of column b in table c, etc (maximum of 100,000 rows in the tables, <10 columns), no nasty joins or anything). Also, I may have extracted a couple of large objects in full (not more than 200MB).
Otherwise, the database processes a few (< 20) small (< 1k data) inserts/updates/deletes per minute. This would have been the only traffic to the database after friday afternoon when I went home.
Autovacuum is turned on and the system had finished importing a large amount of data (50GB) earlier (4am) that morning. Is it possible the spike was caused by autovacuum re-indexing the pg_largeobject table?
Thanks,
Geoff.
Geoff Parker <geoffparkernews@yahoo.ca> writes:
> I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed
form,it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5
minutes),stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).
It's impossible to say much with only that amount of information. If we
knew what part of the database had bloated (table? index? WAL log?) we
could perhaps offer some wisdom. Next time, use "du" and and related
tools to see where the space went.
regards, tom lane
Unfortunately I didn't notice the problem until I saw the logs today, and by then it was back tor normal.
Is it safe to assume this is abnormal behaviour?
Geoff.
Is it safe to assume this is abnormal behaviour?
Geoff.
----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Geoff Parker <geoffparkernews@yahoo.ca>
Cc: pgsql admin <pgsql-admin@postgresql.org>
Sent: Tuesday, August 8, 2006 12:12:44 PM
Subject: Re: [ADMIN] disk usage spike
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Geoff Parker <geoffparkernews@yahoo.ca>
Cc: pgsql admin <pgsql-admin@postgresql.org>
Sent: Tuesday, August 8, 2006 12:12:44 PM
Subject: Re: [ADMIN] disk usage spike
Geoff Parker <geoffparkernews@yahoo.ca> writes:
> I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed form, it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).
It's impossible to say much with only that amount of information. If we
knew what part of the database had bloated (table? index? WAL log?) we
could perhaps offer some wisdom. Next time, use "du" and and related
tools to see where the space went.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
> I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed form, it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).
It's impossible to say much with only that amount of information. If we
knew what part of the database had bloated (table? index? WAL log?) we
could perhaps offer some wisdom. Next time, use "du" and and related
tools to see where the space went.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
On Aug 7, 2006, at 10:36 PM, Geoff Parker wrote: > Unfortunately I didn't notice the problem until I saw the logs > today, and by then it was back tor normal. > > Is it safe to assume this is abnormal behaviour? Probably, unless you had a transaction running for 2 days that was using space in pgsql_tmp. Another possibility would be if you killed a backend that had stuff in pgsql_tmp and it didn't clean that up for some reason, but I don't think it should be possible for that to happen. There's also the remote possibility that a table bloated due to a lot of rows being updated, and that table later ended up with a bunch of empty pages at the end of the table, which vacuum then removed. > ----- Original Message ---- > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Geoff Parker <geoffparkernews@yahoo.ca> > Cc: pgsql admin <pgsql-admin@postgresql.org> > Sent: Tuesday, August 8, 2006 12:12:44 PM > Subject: Re: [ADMIN] disk usage spike > > Geoff Parker <geoffparkernews@yahoo.ca> writes: > > I have a database with about 155GB of binary data in large > objects and 15GB of data in other tables. In compressed form, it > consumes about 60GB on disk. On friday, the amount of disk usage > increased to 240GB (over a period of 5 minutes), stayed at 240GB > for 2 days, and then returned to 60GB (again over a period of about > 5 minutes). > > It's impossible to say much with only that amount of information. > If we > knew what part of the database had bloated (table? index? WAL log?) we > could perhaps offer some wisdom. Next time, use "du" and and related > tools to see where the space went. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461