Обсуждение: 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 problemwith either method is that they lock the tables and it is not practical for databases that one wants to keep it availablefor 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waiting update/delete/insertprocesses 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. Theproblem with either method is that they lock the tables and it is not practical for databases that one wants to keep itavailable for 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waitingupdate/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? > > http://www.postgresql.org/docs/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. Theproblem with either method is that they lock the tables and it is not practical for databases that one wants to keep itavailable for 24x7x365 basis. For very large tables, while the tables are being reindexed, there will be many waitingupdate/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.
Re: growing disk usage problem: alternative solution?
От
ptjm@interlog.com (Patrick TJ McPhee)
Дата:
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