Обсуждение: How to free disk space
Hi:
My database is growing fast taking too much disk space. How can I free disk space without performing a VACCUM FULL? It locks the database for several hours, and that is not a solution.
I guess a backup-restore would do the work but, isn't there a better way to do this without shutting down postgres?
Thanks in advandce.
My database is growing fast taking too much disk space. How can I free disk space without performing a VACCUM FULL? It locks the database for several hours, and that is not a solution.
I guess a backup-restore would do the work but, isn't there a better way to do this without shutting down postgres?
Thanks in advandce.
Greetings!
The database we install at our customers as part of our product includes an event_history table. For some reason lost in the mists of time, the most important field in that table, the description, is a varchar field specified to be only 64 characters long. This leads me to a more fundamental question: why specify the length of a varchar field at all? Is there a big difference between the amount of disk space taken up by "abc" stored in a varchar(64) field and stored in a varchar field? How much space does an unspecified-length varchar field take up? Are there other reasons to use varchar(64) instead of varchar?
Thank you very much!
RobR
After run VACUUM, you must run REINDEXDB to decrease indexes. You can pg_resetxlog too, but you need restart server to do that. 2008/10/21 Ruben Blanco <rubenblan@gmail.com>: > Hi: > > My database is growing fast taking too much disk space. How can I free disk > space without performing a VACCUM FULL? It locks the database for several > hours, and that is not a solution. > > I guess a backup-restore would do the work but, isn't there a better way to > do this without shutting down postgres? > > Thanks in advandce. > > -- Emanuel Calvo Franco Syscope Postgresql DBA BaPUG Member
Well, I would guess that whoever designed the DB structure was used to non-Postgres databases. First see http://www.postgresql.org/docs/8.3/static/datatype-character.html for the tip in Para. 7 on that page. Most Data Bases DO require much more effort (i.e. don't run as fast) if you use unlimited size data fields but Postgres is different. If either an old version of Postgres was the target for the original design or the designer had not read the above item then the result is varchar(nn) being used where for a pure Postgres system a TEXT (or varchar) field is a better design. ----- Original Message ----- From: "Rob Richardson" <Rob.Richardson@rad-con.com> To: pgsql-general@postgresql.org Sent: Tuesday, October 21, 2008 8:07:31 AM GMT -05:00 US/Canada Eastern Subject: [GENERAL] Varchar vs varchar(64) Greetings! The database we install at our customers as part of our product includes an event_history table. For some reason lost inthe mists of time, the most important field in that table, the description, is a varchar field specified to be only 64characters long. This leads me to a more fundamental question: why specify the length of a varchar field at all? Is therea big difference between the amount of disk space taken up by "abc" stored in a varchar(64) field and stored in a varcharfield? How much space does an unspecified-length varchar field take up? Are there other reasons to use varchar(64)instead of varchar? Thank you very much! RobR -- This email, and any files transmitted with it, is 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 advise postmaster@mdtsoft.com <mailto:postmaster@mdtsoft.com>. New MDT Software Headquarters (As of July 1, 2008): 3480 Preston Ridge Road Suite 450 Alpharetta, GA 30005 Philip W. Dalrymple III <pwd@mdtsoft.com> MDT Software - The Change Management Company +1 678 297 1001 Fax +1 678 297 1003
On Tue, 2008-10-21 at 11:59 +0100, Ruben Blanco wrote: > My database is growing fast taking too much disk space. Are you running regular vacuum? -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Вложения
On 21/10/2008 11:59, Ruben Blanco wrote: > My database is growing fast taking too much disk space. How can I free > disk space without performing a VACCUM FULL? It locks the database for > several hours, and that is not a solution. You shouldn't need to do VACUUM FULL - plain VACUUM should do the job. Have a trawl through the archives, as this topic comes up regularly. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Rob Richardson wrote: > The database we install at our customers as part of our > product includes an event_history table. For some reason > lost in the mists of time, the most important field in that > table, the description, is a varchar field specified to be > only 64 characters long. This leads me to a more fundamental > question: why specify the length of a varchar field at all? > Is there a big difference between the amount of disk space > taken up by "abc" stored in a varchar(64) field and stored in > a varchar field? How much space does an unspecified-length > varchar field take up? Are there other reasons to use > varchar(64) instead of varchar? You can't have "varchar" without a length in parentheses, as far as I know. But you can use "text" which is essentially the same thing. I can think of two reasons to use varchar(n) instead of text: - you deliberately want to limit the amount of characters. - you want to index the column (indexes have a maximum row size). It is not a performance issue, however, and there is no wasted space either. Yours, Laurenz Albe
On Tuesday 21 October 2008 09:00:30 postgres Emanuel CALVO FRANCO wrote: > After run VACUUM, you must run REINDEXDB to decrease indexes. > This is probably overkill, as you won't need to do this for a lot of tables in your database, and the locking issues are probably unhelpful. > You can pg_resetxlog too, but you need restart server to do that. > No No No!!! You should never ever ever run pg_resetxlog on a production machine!! I'm not sure where you got this idea, but it is a bad one to be sure! > 2008/10/21 Ruben Blanco <rubenblan@gmail.com>: > > Hi: > > > > My database is growing fast taking too much disk space. How can I free > > disk space without performing a VACCUM FULL? It locks the database for > > several hours, and that is not a solution. > > > > I guess a backup-restore would do the work but, isn't there a better way > > to do this without shutting down postgres? > > > > Thanks in advandce. > > -- > Emanuel Calvo Franco > Syscope Postgresql DBA > BaPUG Member -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > Rob Richardson wrote: >> Are there other reasons to use >> varchar(64) instead of varchar? > You can't have "varchar" without a length in parentheses, > as far as I know. That's what the spec says and that's what some other implementations require, but not Postgres. We treat varchar without a length as pretty much just an alias for text. regards, tom lane
On Tue, Oct 21, 2008 at 10:46 AM, Robert Treat <xzilla@users.sourceforge.net> wrote: > On Tuesday 21 October 2008 09:00:30 postgres Emanuel CALVO FRANCO wrote: >> After run VACUUM, you must run REINDEXDB to decrease indexes. >> > > This is probably overkill, as you won't need to do this for a lot of tables in > your database, and the locking issues are probably unhelpful. Note, however, that if you have scheduled downtime and your db server is fast enough, you can schedule a vacuum full / reindexdb during that time. We had a rogue query that made a few of our largest tables very very bloated (like 95% bloat) and we had some downtime to fix it and it was way easier to just do the whole db with vacuum full verbose, then reindex. I also had a chacne to set fill factors on some of the tables that I'd wanted new fill factors on.
what's the version you're running ? you shouldn't run into that problem too often with 8.3
just like guys said here, regular vacuum, and reindex once in a while.
On Wed, Oct 22, 2008 at 2:46 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > what's the version you're running ? you shouldn't run into that problem too > often with 8.3 I'm running 8.3.3 soon to be 8.3.4 or .5 if there's a bug fix due out. There's only so much the autovacuum daemon can do when a rogue query runs an update on a whole table several times in a row. The rogue query has been eliminated from our app, but the damage was already done. -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis