growing disk usage problem: alternative solution?
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one regularly reindex and/or fully vacuum. The problem with either method is that they lock the tables and it is not practical for databases that one wants to keep it available for 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waiting update/delete/insert processes and this is not at all desirable.
Server response is still great even with growing disk usage. Any one has other ideas to check the growth of the disk usage.
Please do not cc the response to me. I will read it in the list.
Sincerely
Z-m Wu
wu_zhong_min@vrane.com wrote:
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one regularly reindex and/or fully vacuum. The problem with either method is that they lock the tables and it is not practical for databases that one wants to keep it available for 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waiting update/delete/insert processes and this is not at all desirable.
That is not true. If you have a proper vacuum strategy then you should
be able to maintain a specific level of growth that is directly related
to your dataset growth.
In short if you vacuum enough, you don't have to vacuum full.
Server response is still great even with growing disk usage. Any one has other ideas to check the growth of the disk usage.
Please do not cc the response to me. I will read it in the list.
Not how this list works, your options are:
1. You only receive the reply
2. You + the list receives the reply
Most users opt for 2.
Sincerely,
Joshua D. Drake
Sincerely
Z-m Wu
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
In response to wu_zhong_min@vrane.com:
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one regularly reindex and/or fully vacuum. The problem with either method is that they lock the tables and it is not practical for databases that one wants to keep it available for 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waiting update/delete/insert processes and this is not at all desirable.
Server response is still great even with growing disk usage. Any one has other ideas to check the growth of the disk usage.
VACUUM FULL and REINDEX are not required to maintain disk usage. Good old-
fashoned VACUUM will do this as long as your FSM settings are high enough.
There _will_ be _some_ wasted space, but (in my experience, at least) this
will hit a plateau and level off. Frequent VACUUM FULLs bloat your indexes
and require frequent REINDEXES, so should be avoided unless needed.
--
Bill Moran
http://www.potentialtech.com
On Jun 26, 2007, at 3:31 PM, Bill Moran wrote:
VACUUM FULL and REINDEX are not required to maintain disk usage.
Good old-
fashoned VACUUM will do this as long as your FSM settings are high
enough.
I find this true for the data but not necessarily for indexes. The
other week I reindexed a couple of O(100,000,000) row tables and
shaved about 20Gb of index bloat. Those tables are vacuumed
regularly, but we do a large data purge every few weeks. I think
that causes some issues. I'm running 8.1.
To mitigate the downtime, we make use of the fact that we have live
replica of the database on similarly capable hardware so we bring the
replica offline, reindex it, bring it back up, move the "master" to
it, then reindex the other server, and move the master back.
In article <DED7310D-6DE3-468C-8687-1304522D3AE0@khera.org>,
Vivek Khera <vivek@khera.org> wrote:
%
% On Jun 26, 2007, at 3:31 PM, Bill Moran wrote:
%
% > VACUUM FULL and REINDEX are not required to maintain disk usage.
% > Good old-
% > fashoned VACUUM will do this as long as your FSM settings are high
% > enough.
% >
%
% I find this true for the data but not necessarily for indexes. The
% other week I reindexed a couple of O(100,000,000) row tables and
% shaved about 20Gb of index bloat. Those tables are vacuumed
% regularly, but we do a large data purge every few weeks. I think
% that causes some issues. I'm running 8.1.
If you have an index on some monotonically increasing field (i.e., a
sequence or date), and you purge by deleting from the low end of this
index, then that space won't be reclaimed by vacuum. Vacuum full won't
help, either. You (only) need to rebuild the affected indices.
--
Patrick TJ McPhee
North York Canada
ptjm@interlog.com