Обсуждение: size of indexes and tables (more than 1GB)
I read and have seen that when a table has more than 1GB it is divided in several files with the names of inode,inode.1,inode.2,inode.3, etc. I have a table of 1.3 GB (9.618.118 rows,13 fields) it is divided in that way as i see on /PGDATA/base but each file has the same size i mean table inode (1.3GB), inode.1(1.3GB),inode.2(1.3GB) so is this not a waste of space?, are those file sizes reusable by postgresql?. The size of the table is 3 times bigger than, for instance Visual Fox Pro dbf's? since is there fisically three times. I am a little confuse on this. I apreciatte anybody could explain this to me Thanks in advance.
On Thu, Aug 25, 2005 at 08:52:35 -0400, jose fuenmayor <jafn82@gmail.com> wrote: > > The size of the table is 3 times bigger than, for instance Visual Fox > Pro dbf's? since is there fisically three times. Have you been vacuuming properly? It is possible you have a lot of dead tuples in the database. If that seems to be the case and you need more help dealing with this, please include the version of Postgres you are running, since that will affect what suggestions people will give you.
jafn82@gmail.com (jose fuenmayor) writes: > I read and have seen that when a table has more than 1GB it is divided > in several files with the names of inode,inode.1,inode.2,inode.3, etc. > > I have a table of 1.3 GB (9.618.118 rows,13 fields) it is divided in > that way as i see on /PGDATA/base but each file has the same size i > mean > table inode (1.3GB), inode.1(1.3GB),inode.2(1.3GB) so is this not a > waste of space?, are those file sizes reusable by postgresql?. > > The size of the table is 3 times bigger than, for instance Visual Fox > Pro dbf's? since is there fisically three times. Having "file", "file.1", "file.2", and such is routine; that is the normal handling of tables that grow beyond 1GB in size. If there is actually 3GB of data to store in the table, then there is nothing to be 'fixed' about this. There is no duplication of data; each of those files contains distinct sets of tuples. First question... Are you vacuuming the table frequently to reclaim dead space? If that table is heavily updated (e.g. - via DELETE/UPDATE; mere INSERTs do NOT represent "updates" in this context), then maybe there's a lot of dead space, and running VACUUM would cut down on the size. If you're running VACUUM often enough, then there's nothing wrong, and nothing to be done. You're simply observing how PostgreSQL handles large tables. -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://cbbrowne.com/info/multiplexor.html "Funny, the only thing that makes me go Keanu about Microsoft is the fact that they are constantly behind the times and yet claim to be innovating." -- Steve Lamb <morpheus@despair.rpglink.com>
Hi Chris, > If you're running VACUUM often enough, then there's nothing wrong, and > nothing to be done. You're simply observing how PostgreSQL handles > large tables. Wrong. I have a big table - running VACUUM the first time needs as long as I run it after the VACUUM has finished. There are other problems with VACUUM, fixed in 8.1. In 8.1. you have a server internal AUTOVACUUM - setting this correct might be the solution. My table has about 40GB of data with about 120 million tuples. Correct max_fsm settings, etc... I created test datases with about 10-20 million tuples - and VACUUM runs fast, but not when you do many changes and your tables are mooooore bigger. Chris Browne wrote: > jafn82@gmail.com (jose fuenmayor) writes: > >>I read and have seen that when a table has more than 1GB it is divided >>in several files with the names of inode,inode.1,inode.2,inode.3, etc. >> >>I have a table of 1.3 GB (9.618.118 rows,13 fields) it is divided in >>that way as i see on /PGDATA/base but each file has the same size i >>mean >>table inode (1.3GB), inode.1(1.3GB),inode.2(1.3GB) so is this not a >>waste of space?, are those file sizes reusable by postgresql?. >> >>The size of the table is 3 times bigger than, for instance Visual Fox >>Pro dbf's? since is there fisically three times. > > > Having "file", "file.1", "file.2", and such is routine; that is the > normal handling of tables that grow beyond 1GB in size. If there is > actually 3GB of data to store in the table, then there is nothing to > be 'fixed' about this. There is no duplication of data; each of those > files contains distinct sets of tuples. > > First question... > > Are you vacuuming the table frequently to reclaim dead space? > > If that table is heavily updated (e.g. - via DELETE/UPDATE; mere > INSERTs do NOT represent "updates" in this context), then maybe > there's a lot of dead space, and running VACUUM would cut down on the > size. > > If you're running VACUUM often enough, then there's nothing wrong, and > nothing to be done. You're simply observing how PostgreSQL handles > large tables.
i am using postgreSql 8.0.3 i've try vacuum "vacuum full" the hole database but it takes too long, seems it will never be finished, i've tried "vacuum <table_name>" and the table remains nearly the same as isee i my PGDATA directory. is this normal? . is there something i can do ?
On 8/25/05, Bruno Wolff III <bruno@wolff.to> wrote:
On Thu, Aug 25, 2005 at 08:52:35 -0400,
jose fuenmayor <jafn82@gmail.com> wrote:
>
> The size of the table is 3 times bigger than, for instance Visual Fox
> Pro dbf's? since is there fisically three times.
Have you been vacuuming properly? It is possible you have a lot of dead
tuples in the database. If that seems to be the case and you need more
help dealing with this, please include the version of Postgres you are
running, since that will affect what suggestions people will give you.
On Tue, Aug 30, 2005 at 08:38:03 -0400, jose fuenmayor <jafn82@gmail.com> wrote: > i am using postgreSql 8.0.3 i've try vacuum "vacuum full" the hole database > but it takes too long, seems it will never be finished, i've tried "vacuum > <table_name>" and the table remains nearly the same as isee i my PGDATA > directory. is this normal? . is there something i can do ? Have you tried VACUUM FULL on just that table? It may be that your FSM setting isn't high enough to remove all of the dead tuples with a normal vacuum. How long did you wait when you tried to vacuum full the whole database? Another option is to use the CLUSTER command on the big table. If that table is mostly dead tuples, cluster may run faster than vacuum full.
i waited like 11 hours and it doesn't finish, where is the FSM setting postgreSQL.conf?
On 8/30/05, Bruno Wolff III < bruno@wolff.to> wrote:
On Tue, Aug 30, 2005 at 08:38:03 -0400,
jose fuenmayor < jafn82@gmail.com> wrote:
> i am using postgreSql 8.0.3 i've try vacuum "vacuum full" the hole database
> but it takes too long, seems it will never be finished, i've tried "vacuum
> <table_name>" and the table remains nearly the same as isee i my PGDATA
> directory. is this normal? . is there something i can do ?
Have you tried VACUUM FULL on just that table?
It may be that your FSM setting isn't high enough to remove all of the
dead tuples with a normal vacuum.
How long did you wait when you tried to vacuum full the whole database?
Another option is to use the CLUSTER command on the big table. If that
table is mostly dead tuples, cluster may run faster than vacuum full.
On Tue, Aug 30, 2005 at 09:26:16 -0400, jose fuenmayor <jafn82@gmail.com> wrote: > i waited like 11 hours and it doesn't finish, where is the FSM setting > postgreSQL.conf? http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-RESOURCE