Thread: database 1.2G, pg_dump 73M?!
I have a postgres server for which du reports
1188072 /var/lib/postgresql/8.2/main
on Linux system.
The server has only one real database, which is for bacula. When I dump
the database, it's 73Mg.
This is immediately after I did a full vacuum and restarted the server.
Also,
bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC
limit 15;
relname | relpages
---------------------------------+----------
file_jpfid_idx | 27122
file_pathid_idx | 17969
file_jobid_idx | 17948
file_pkey | 14580
file_fp_idx | 12714
file | 11558
file_filenameid_idx | 9806
filename | 3958
filename_name_idx | 2510
filename_pkey | 1367
path | 966
path_name_idx | 950
path_pkey | 151
pg_attribute_relid_attnam_index | 46
pg_proc | 45
It seems very strange to me that there is such a difference in size
between the dump and the database: the data store is almost 15 time
larger than the dump.
Is this to be expected (e.g., from the indices taking up disk space)?
Is there anything I can do to reclaim some disk space?
Thanks.
Ross
Ross Boylan wrote:
> I have a postgres server for which du reports
> 1188072 /var/lib/postgresql/8.2/main
> on Linux system.
> The server has only one real database, which is for bacula. When I dump
> the database, it's 73Mg.
>
> This is immediately after I did a full vacuum and restarted the server.
>
> Also,
> bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC
> limit 15;
> relname | relpages
> ---------------------------------+----------
> file_jpfid_idx | 27122
> file_pathid_idx | 17969
> file_jobid_idx | 17948
> file_pkey | 14580
> file_fp_idx | 12714
> file | 11558
> file_filenameid_idx | 9806
> filename | 3958
> filename_name_idx | 2510
> filename_pkey | 1367
> path | 966
> path_name_idx | 950
> path_pkey | 151
> pg_attribute_relid_attnam_index | 46
> pg_proc | 45
>
> It seems very strange to me that there is such a difference in size
> between the dump and the database: the data store is almost 15 time
> larger than the dump.
>
> Is this to be expected (e.g., from the indices taking up disk space)?
> Is there anything I can do to reclaim some disk space
There are a few factors you need to take into account:
* Data storage in the database is packed into blocks and contains
header data. Since data needs to be put into blocks there is a
potential for waisting space. If you are unlucky it can become
nearly a single row in the worst case.
* You need to vacuum often, to ensure obsolete rows are removed and
space can be reused.
* Tables are not reduced in size and only grown. I thinks cluster
and vacuum full will reduce the size of your table.
* Indexes are not in the backup, they are derived from the table
data on a restore.
If you remove the indexes you are left with 150~200 MB of data (I
guessed).
Doing reindex will rebuild the index and get rid of all the bloat
it has been collected during use. _I recommend you try this_, as
your indexes on the file table look quite huge.
But the most important factor for you will be the following:
* Backups are compressed. Since you store filenames and paths these
will have a very high amount of regularity and therefore are very
good targets for compression. This can save a huge amount of data.
If you take a compression factor of 50%~70% you will reach your 70 MB.
Ow, server restarts will not help reduce your database size. In fact,
nothing at all should change, except lower performance until sufficient
cached data is back in the cache again.
Hope this helps...
- Joris
On Sun, 2008-03-30 at 20:27 +0200, Joris Dobbelsteen wrote:
> Ross Boylan wrote:
> > I have a postgres server for which du reports
> > 1188072 /var/lib/postgresql/8.2/main
> > on Linux system.
> > The server has only one real database, which is for bacula. When I dump
> > the database, it's 73Mg.
> >
> > This is immediately after I did a full vacuum and restarted the server.
> >
> > Also,
> > bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC
> > limit 15;
> > relname | relpages
> > ---------------------------------+----------
> > file_jpfid_idx | 27122
> > file_pathid_idx | 17969
> > file_jobid_idx | 17948
> > file_pkey | 14580
> > file_fp_idx | 12714
> > file | 11558
> > file_filenameid_idx | 9806
> > filename | 3958
> > filename_name_idx | 2510
> > filename_pkey | 1367
> > path | 966
> > path_name_idx | 950
> > path_pkey | 151
> > pg_attribute_relid_attnam_index | 46
> > pg_proc | 45
> >
> > It seems very strange to me that there is such a difference in size
> > between the dump and the database: the data store is almost 15 time
> > larger than the dump.
> >
> > Is this to be expected (e.g., from the indices taking up disk space)?
> > Is there anything I can do to reclaim some disk space
> There are a few factors you need to take into account:
>
> * Data storage in the database is packed into blocks and contains
> header data. Since data needs to be put into blocks there is a
> potential for waisting space. If you are unlucky it can become
> nearly a single row in the worst case.
> * You need to vacuum often, to ensure obsolete rows are removed and
> space can be reused.
> * Tables are not reduced in size and only grown. I thinks cluster
> and vacuum full will reduce the size of your table.
> * Indexes are not in the backup, they are derived from the table
> data on a restore.
> If you remove the indexes you are left with 150~200 MB of data (I
> guessed).
> Doing reindex will rebuild the index and get rid of all the bloat
> it has been collected during use. _I recommend you try this_, as
> your indexes on the file table look quite huge.
reindexing had a huge effect. After reindex the top tables (file,
filename and path) I now see
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 15;
relname | relpages
---------------------------------+----------
file | 11558
filename | 3958
filename_name_idx | 2383
file_jpfid_idx | 2145
file_fp_idx | 1787
file_jobid_idx | 1427
file_pathid_idx | 1427
file_pkey | 1427
file_filenameid_idx | 1427
filename_pkey | 1367
path | 966
path_name_idx | 871
path_pkey | 151
pg_attribute_relid_attnam_index | 46
pg_proc | 45
and du now reports 451M. That still seems a bit large, given the size
of the sql dump, but it's almost 2/3 lower than it was before.
Thanks so much!
I guess I need to figure out how to reindex automatically.
>
> But the most important factor for you will be the following:
>
> * Backups are compressed. Since you store filenames and paths these
> will have a very high amount of regularity and therefore are very
> good targets for compression. This can save a huge amount of data.
> If you take a compression factor of 50%~70% you will reach your 70 MB.
I don't see how this is relevant, since my dump file was plain text
(sql).
> Ow, server restarts will not help reduce your database size. In fact,
> nothing at all should change, except lower performance until sufficient
> cached data is back in the cache again.
>
> Hope this helps...
It was a huge help.
>
> - Joris
Ross Boylan wrote: > On Sun, 2008-03-30 at 20:27 +0200, Joris Dobbelsteen wrote: > >> Ross Boylan wrote: >> >>> I have a postgres server for which du reports >>> 1188072 /var/lib/postgresql/8.2/main >>> on Linux system. >>> The server has only one real database, which is for bacula. When I dump >>> the database, it's 73Mg. >>> >>> This is immediately after I did a full vacuum and restarted the server. >>> >>> Also, >>> bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC >>> limit 15; >>> relname | relpages >>> ---------------------------------+---------- >>> file_jpfid_idx | 27122 >>> file_pathid_idx | 17969 >>> file_jobid_idx | 17948 >>> file_pkey | 14580 >>> file_fp_idx | 12714 >>> file | 11558 >>> file_filenameid_idx | 9806 >>> filename | 3958 >>> filename_name_idx | 2510 >>> filename_pkey | 1367 >>> path | 966 >>> path_name_idx | 950 >>> path_pkey | 151 >>> pg_attribute_relid_attnam_index | 46 >>> pg_proc | 45 >>> >>> It seems very strange to me that there is such a difference in size >>> between the dump and the database: the data store is almost 15 time >>> larger than the dump. >>> >>> Is this to be expected (e.g., from the indices taking up disk space)? >>> Is there anything I can do to reclaim some disk space >>> >> There are a few factors you need to take into account: >> >> * Data storage in the database is packed into blocks and contains >> header data. Since data needs to be put into blocks there is a >> potential for waisting space. If you are unlucky it can become >> nearly a single row in the worst case. >> * You need to vacuum often, to ensure obsolete rows are removed and >> space can be reused. >> * Tables are not reduced in size and only grown. I thinks cluster >> and vacuum full will reduce the size of your table. >> * Indexes are not in the backup, they are derived from the table >> data on a restore. >> If you remove the indexes you are left with 150~200 MB of data (I >> guessed). >> Doing reindex will rebuild the index and get rid of all the bloat >> it has been collected during use. _I recommend you try this_, as >> your indexes on the file table look quite huge. >> > reindexing had a huge effect. After reindex the top tables (file, > filename and path) I now see > SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 15; > relname | relpages > ---------------------------------+---------- > file | 11558 > filename | 3958 > filename_name_idx | 2383 > file_jpfid_idx | 2145 > file_fp_idx | 1787 > file_jobid_idx | 1427 > file_pathid_idx | 1427 > file_pkey | 1427 > file_filenameid_idx | 1427 > filename_pkey | 1367 > path | 966 > path_name_idx | 871 > path_pkey | 151 > pg_attribute_relid_attnam_index | 46 > pg_proc | 45 > and du now reports 451M. That still seems a bit large, given the size > of the sql dump, but it's almost 2/3 lower than it was before. > > Thanks so much! > > I guess I need to figure out how to reindex automatically. > Take a threshold, e.g. look which indexes are towards the table size, or something. The bloat is mostly causes by continues updates to the indexes on every insert, update and delete command. The index needs to split pages that might be merged back some time later. Doing frequent vacuums might, or might not, prevent this. Even in theory you will see that algorithms allow trees to grow quite large up to a certain constant factor. This is in order to have a good limit on the amount of work that must be done on a operation on the index. >> But the most important factor for you will be the following: >> >> * Backups are compressed. Since you store filenames and paths these >> will have a very high amount of regularity and therefore are very >> good targets for compression. This can save a huge amount of data. >> If you take a compression factor of 50%~70% you will reach your 70 MB. >> > I don't see how this is relevant, since my dump file was plain text > (sql). > From the top contenders, about half are indexes, so you are stuck with ~200 MB of data in the tables. Postgresql has some wasted space due to placement of the tuples in a block and overhead for each block and row. I don't know those values, but they are in the range of 24 bytes per tuple, I believe. Secondly a block is 8 KB by default and tuples cannot be stored into multiple blocks (thats what toast to work around). All in all: Lookup tuple sizes, if they are small than the overhead from postgresql can be a big factor. If you are huge you loose on portions of unoccupied space in blocks. I believe pg_statistics will provide this information. Another factor is representation in the SQL dump might be more efficient than in the database, but this highly depends on your data set. For example, a int8 takes 8 bytes in a table, while it takes between 1 and ~20 in a SQL dump. How the plain SQL dump becomes this small I cannot explain without much much more details. >> Hope this helps... >> > > It was a huge help. > Glad it was, - Joris
Ross Boylan <RossBoylan@stanfordalumni.org> writes:
> reindexing had a huge effect.
So the indexes were indeed bloated. There are some known usage patterns
in which regular vacuum isn't very good at reclaiming space in b-tree
indexes. For example if you make daily entries in an index by date and
later remove all but the last-of-the-month entry --- this leaves a few
entries on every index page and we don't have code to collapse that,
short of reindexing.
However what seems more likely is that you're getting burnt by excessive
use of VACUUM FULL. V.F., far from shrinking indexes, tends to bloat
them. Recommended practice is to use plain VACUUM often enough that you
don't need VACUUM FULL.
regards, tom lane
On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote:
> From the top contenders, about half are indexes, so you are stuck
> with
> ~200 MB of data in the tables.
> Postgresql has some wasted space due to placement of the tuples in a
> block and overhead for each block and row. I don't know those values,
> but they are in the range of 24 bytes per tuple, I believe. Secondly
> a
> block is 8 KB by default and tuples cannot be stored into multiple
> blocks (thats what toast to work around).
>
> All in all: Lookup tuple sizes, if they are small than the overhead
> from
> postgresql can be a big factor. If you are huge you loose on portions
> of
> unoccupied space in blocks. I believe pg_statistics will provide this
> information.
There is a pg_statistic (no "s") table, but I don't know how to get
tuple size from it--the documentation refers to the source code to
figure out the codes. Backing up a step, I don't know what a tuple is
in Postgres, and don't see an entry for it in the index.
Is a tuple just a row? That's what the docs say for the following
report:
# select distinct relname, reltuples, relpages from pg_class where
relkind='r' and substring(relname, 1, 3) != 'pg_';
relname | reltuples | relpages
-------------------------+-----------+----------
basefiles | 0 | 0
cdimages | 0 | 0
client | 2 | 1
counters | 1 | 1
device | 0 | 0
file | 650659 | 11558
filename | 623012 | 3958
fileset | 22 | 1
job | 384 | 10
jobmedia | 596 | 7
location | 0 | 0
locationlog | 0 | 0
log | 0 | 0
media | 245 | 9
mediatype | 2 | 1
path | 67908 | 966
pool | 5 | 1
sql_features | 439 | 6
sql_implementation_info | 12 | 1
sql_languages | 4 | 1
sql_packages | 10 | 1
sql_parts | 9 | 1
sql_sizing | 23 | 1
sql_sizing_profiles | 0 | 0
status | 19 | 1
storage | 2 | 1
unsavedfiles | 0 | 0
version | 1 | 1
> Another factor is representation in the SQL dump might be more
> efficient
> than in the database, but this highly depends on your data set. For
> example, a int8 takes 8 bytes in a table, while it takes between 1
> and
> ~20 in a SQL dump.
>
> How the plain SQL dump becomes this small I cannot explain without
> much
> much more details.
On Tom's point, bacula regularly inserts entries into the tables and
then, days to months later, deletes them. As far as I know, the VACUUM
FULLs I just did were the first ever; I did do several of them because I
kept getting messages about needing more fsm_pages.
I am still trying to figure out if the database was getting any
automatic vacuuming at all. The Postgres documentation (the database is
8.2, though I'm moving to 8.3 soon) sounds as if it's on automatically,
but the Debian-specific documentation suggests I may need to do some
additional things to enable it.
Probably the fsm_pages being low also hurt disk useage, since the
message accompanying the vacuum said that's what happens if fsm_pages
is low. It was 20k; vacuum said I needed 56k, and I upped it to 80k.
I'm not sure if my recent cleaning has brought the needed fsm_pages
down.
I've only been doing partial backups for the last few months, so there's
probably less info in the tables than under normal conditions. I
suppose it's possible the space I gained was just a temporary win.
Ross
>-----Original Message----- >From: Ross Boylan [mailto:RossBoylan@stanfordalumni.org] >Sent: Sunday, 30 March 2008 23:43 >To: Joris Dobbelsteen >Cc: RossBoylan@stanfordalumni.org; pgsql-general@postgresql.org >Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?! > >On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote: >> From the top contenders, about half are indexes, so you are stuck >> with ~200 MB of data in the tables. >> Postgresql has some wasted space due to placement of the tuples in a >> block and overhead for each block and row. I don't know >those values, >> but they are in the range of 24 bytes per tuple, I believe. >Secondly a >> block is 8 KB by default and tuples cannot be stored into multiple >> blocks (thats what toast to work around). >> >> All in all: Lookup tuple sizes, if they are small than the overhead >> from postgresql can be a big factor. If you are huge you loose on >> portions of unoccupied space in blocks. I believe pg_statistics will >> provide this information. >There is a pg_statistic (no "s") table, but I don't know how >to get tuple size from it--the documentation refers to the >source code to figure out the codes. Backing up a step, I >don't know what a tuple is in Postgres, and don't see an entry >for it in the index. It was pg_stats. You get avg_width. It gives this per column. So probably you want SELECT tablename, SUM(avg_width) FROM pg_stats WHERE schemaname = 'public' GROUP BY tablename; >Is a tuple just a row? That's what the docs say for the following >report: Yes. Also where I typed "block" it will refer to "page". ># select distinct relname, reltuples, relpages from pg_class >where relkind='r' and substring(relname, 1, 3) != 'pg_'; > > relname | reltuples | relpages >-------------------------+-----------+---------- > basefiles | 0 | 0 > cdimages | 0 | 0 > client | 2 | 1 > counters | 1 | 1 > device | 0 | 0 > file | 650659 | 11558 > filename | 623012 | 3958 > fileset | 22 | 1 > job | 384 | 10 > jobmedia | 596 | 7 > location | 0 | 0 > locationlog | 0 | 0 > log | 0 | 0 > media | 245 | 9 > mediatype | 2 | 1 > path | 67908 | 966 > pool | 5 | 1 > sql_features | 439 | 6 > sql_implementation_info | 12 | 1 > sql_languages | 4 | 1 > sql_packages | 10 | 1 > sql_parts | 9 | 1 > sql_sizing | 23 | 1 > sql_sizing_profiles | 0 | 0 > status | 19 | 1 > storage | 2 | 1 > unsavedfiles | 0 | 0 > version | 1 | 1 Do relpages * 8096 / reltuples. Default installs have 8 KB pages/blocks by default. For file it should be ~144 bytes/tuple. For filename it should be ~51 bytes/tuple. Probably you will get some signficant differences here. >> Another factor is representation in the SQL dump might be more >> efficient than in the database, but this highly depends on your data >> set. For example, a int8 takes 8 bytes in a table, while it takes >> between 1 and ~20 in a SQL dump. >> >> How the plain SQL dump becomes this small I cannot explain without >> much much more details. > >On Tom's point, bacula regularly inserts entries into the >tables and then, days to months later, deletes them. As far >as I know, the VACUUM FULLs I just did were the first ever; I >did do several of them because I kept getting messages about >needing more fsm_pages. > >I am still trying to figure out if the database was getting >any automatic vacuuming at all. The Postgres documentation >(the database is 8.2, though I'm moving to 8.3 soon) sounds as >if it's on automatically, but the Debian-specific >documentation suggests I may need to do some additional things >to enable it. > >Probably the fsm_pages being low also hurt disk useage, since >the message accompanying the vacuum said that's what happens >if fsm_pages is low. It was 20k; vacuum said I needed 56k, >and I upped it to 80k. >I'm not sure if my recent cleaning has brought the needed >fsm_pages down. > >I've only been doing partial backups for the last few months, >so there's probably less info in the tables than under normal >conditions. I suppose it's possible the space I gained was >just a temporary win. Looks like some configuration changes are needed to tune your installation to better suite you needs. fsm_pages can be modified, but I don't know if any other parameters might need change for that. Documentation can help and probably a lot of people here can do and tell it blindfolded. - Joris
On Sun, 2008-03-30 at 22:59 +0100, Joris Dobbelsteen wrote: > >-----Original Message----- > >From: Ross Boylan [mailto:RossBoylan@stanfordalumni.org] > >Sent: Sunday, 30 March 2008 23:43 > >To: Joris Dobbelsteen > >Cc: RossBoylan@stanfordalumni.org; pgsql-general@postgresql.org > >Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?! > > > >On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote: > >> From the top contenders, about half are indexes, so you are stuck > >> with ~200 MB of data in the tables. > >> Postgresql has some wasted space due to placement of the tuples in a > >> block and overhead for each block and row. I don't know > >those values, > >> but they are in the range of 24 bytes per tuple, I believe. > >Secondly a > >> block is 8 KB by default and tuples cannot be stored into multiple > >> blocks (thats what toast to work around). > >> > >> All in all: Lookup tuple sizes, if they are small than the overhead > >> from postgresql can be a big factor. If you are huge you loose on > >> portions of unoccupied space in blocks. I believe pg_statistics will > >> provide this information. > >There is a pg_statistic (no "s") table, but I don't know how > >to get tuple size from it--the documentation refers to the > >source code to figure out the codes. Backing up a step, I > >don't know what a tuple is in Postgres, and don't see an entry > >for it in the index. > > It was pg_stats. > You get avg_width. It gives this per column. > > So probably you want > SELECT tablename, SUM(avg_width) > FROM pg_stats > WHERE schemaname = 'public' [Thanks; I didn't know about using schemaname to limit it to interesting tables] > GROUP BY tablename [RB added] ORDER BY tablename; tablename | sum -----------+----- client | 62 counters | 25 file | 109 filename | 18 fileset | 53 job | 149 jobmedia | 52 media | 226 mediatype | 16 path | 82 pool | 179 status | 29 storage | 23 version | 4 So, for example, if each tuple has 24 bytes of overhead, the overhead more than doubles the size of the file table (18 bytes), which has a big record count. Am I following correctly? Between the space taken up by indices and the other overhead, the size difference between the sql dump and the db disk useage is starting to seem more reasonable. The database uses SQL-ASCII encoding, so I'd expect the textual parts (filenames and paths) to take up the same space (just for the basic storage, not counting overhead/indices) in Postgres as on the dump, 1 byte/character. I'm not sure what "If you are huge you loose on portions of unoccupied space in blocks" means. > > >Is a tuple just a row? That's what the docs say for the following > >report: > > Yes. > Also where I typed "block" it will refer to "page". > > ># select distinct relname, reltuples, relpages from pg_class > >where relkind='r' and substring(relname, 1, 3) != 'pg_'; > > > > relname | reltuples | relpages > >-------------------------+-----------+---------- > > basefiles | 0 | 0 > > cdimages | 0 | 0 > > client | 2 | 1 > > counters | 1 | 1 > > device | 0 | 0 > > file | 650659 | 11558 > > filename | 623012 | 3958 > > fileset | 22 | 1 > > job | 384 | 10 > > jobmedia | 596 | 7 > > location | 0 | 0 > > locationlog | 0 | 0 > > log | 0 | 0 > > media | 245 | 9 > > mediatype | 2 | 1 > > path | 67908 | 966 > > pool | 5 | 1 > > sql_features | 439 | 6 > > sql_implementation_info | 12 | 1 > > sql_languages | 4 | 1 > > sql_packages | 10 | 1 > > sql_parts | 9 | 1 > > sql_sizing | 23 | 1 > > sql_sizing_profiles | 0 | 0 > > status | 19 | 1 > > storage | 2 | 1 > > unsavedfiles | 0 | 0 > > version | 1 | 1 > > Do relpages * 8096 / reltuples. > Default installs have 8 KB pages/blocks by default. > > For file it should be ~144 bytes/tuple. > For filename it should be ~51 bytes/tuple. Those tuple size estimates seem consistent with the tuple sizes reported earlier when the overhead is added in. I'm impressed! > > Probably you will get some signficant differences here. > > >> Another factor is representation in the SQL dump might be more > >> efficient than in the database, but this highly depends on your data > >> set. For example, a int8 takes 8 bytes in a table, while it takes > >> between 1 and ~20 in a SQL dump. > >> > >> How the plain SQL dump becomes this small I cannot explain without > >> much much more details. > > > >On Tom's point, bacula regularly inserts entries into the > >tables and then, days to months later, deletes them. As far > >as I know, the VACUUM FULLs I just did were the first ever; I > >did do several of them because I kept getting messages about > >needing more fsm_pages. > > > >I am still trying to figure out if the database was getting > >any automatic vacuuming at all. The Postgres documentation > >(the database is 8.2, though I'm moving to 8.3 soon) sounds as > >if it's on automatically, but the Debian-specific > >documentation suggests I may need to do some additional things > >to enable it. > > > >Probably the fsm_pages being low also hurt disk useage, since > >the message accompanying the vacuum said that's what happens > >if fsm_pages is low. It was 20k; vacuum said I needed 56k, > >and I upped it to 80k. > >I'm not sure if my recent cleaning has brought the needed > >fsm_pages down. > > > >I've only been doing partial backups for the last few months, > >so there's probably less info in the tables than under normal > >conditions. I suppose it's possible the space I gained was > >just a temporary win. > > Looks like some configuration changes are needed to tune your > installation to better suite you needs. fsm_pages can be modified, done > but I > don't know if any other parameters might need change for that. I didn't seem to bump into any system limits; I had already upped the overall shared memory limit a bit. > Documentation can help and probably a lot of people here can do and tell > it blindfolded. I'm not a DB admin; I only play one on my computer. I clearly need to figure out how to get regular vacuum, analyze, and reindexing going (if they aren't going already). > Thanks for all your help. Ross
>-----Original Message----- >From: Ross Boylan [mailto:RossBoylan@stanfordalumni.org] >Sent: Monday, 31 March 2008 0:23 >To: Joris Dobbelsteen >Cc: RossBoylan@stanfordalumni.org; pgsql-general@postgresql.org >Subject: RE: [GENERAL] database 1.2G, pg_dump 73M?! > > >On Sun, 2008-03-30 at 22:59 +0100, Joris Dobbelsteen wrote: >> >-----Original Message----- >> >From: Ross Boylan [mailto:RossBoylan@stanfordalumni.org] >> >Sent: Sunday, 30 March 2008 23:43 >> >To: Joris Dobbelsteen >> >Cc: RossBoylan@stanfordalumni.org; pgsql-general@postgresql.org >> >Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?! >> > >> >On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote: >> >> From the top contenders, about half are indexes, so you >are stuck >> >> with ~200 MB of data in the tables. >> >> Postgresql has some wasted space due to placement of the >tuples in >> >> a block and overhead for each block and row. I don't know >> >those values, >> >> but they are in the range of 24 bytes per tuple, I believe. >> >Secondly a >> >> block is 8 KB by default and tuples cannot be stored into >multiple >> >> blocks (thats what toast to work around). >> >> >> >> All in all: Lookup tuple sizes, if they are small than >the overhead >> >> from postgresql can be a big factor. If you are huge you loose on >> >> portions of unoccupied space in blocks. I believe pg_statistics >> >> will provide this information. >> >There is a pg_statistic (no "s") table, but I don't know >how to get >> >tuple size from it--the documentation refers to the source code to >> >figure out the codes. Backing up a step, I don't know what a tuple >> >is in Postgres, and don't see an entry for it in the index. >> >> It was pg_stats. >> You get avg_width. It gives this per column. >> >> So probably you want >> SELECT tablename, SUM(avg_width) >> FROM pg_stats >> WHERE schemaname = 'public' >[Thanks; I didn't know about using schemaname to limit it to >interesting tables] >> GROUP BY tablename [RB added] ORDER BY tablename; > tablename | sum >-----------+----- > client | 62 > counters | 25 > file | 109 > filename | 18 > fileset | 53 > job | 149 > jobmedia | 52 > media | 226 > mediatype | 16 > path | 82 > pool | 179 > status | 29 > storage | 23 > version | 4 > >So, for example, if each tuple has 24 bytes of overhead, the >overhead more than doubles the size of the file table (18 >bytes), which has a big record count. Am I following correctly? Yes. (Note not to pin down on the 24 bytes, it varies between versions. I think this is close enough however). >Between the space taken up by indices and the other overhead, >the size difference between the sql dump and the db disk >useage is starting to seem more reasonable. > >The database uses SQL-ASCII encoding, so I'd expect the >textual parts (filenames and paths) to take up the same space >(just for the basic storage, not counting overhead/indices) in >Postgres as on the dump, 1 byte/character. That's the idea. >I'm not sure what "If you are huge you loose on portions of >unoccupied space in blocks" means. A tuple has to be in exactly 1 page (or block). It cannot span multiple pages. Hence if your tuples happens to be a half page size or slightly larger, than only a single one will fit in a single page. So you waste half the page. You don't have to worry about this, as your tuples are significantly smaller than that. (As a reference only: if a tuple is larger than a blocksize some attributes will be evicted to the toast table or compressed. So that is to overcome the limitations of 8000 bytes per tuple.) [snip] >> >> ># select distinct relname, reltuples, relpages from pg_class where >> >relkind='r' and substring(relname, 1, 3) != 'pg_'; >> > >> > relname | reltuples | relpages >> >-------------------------+-----------+---------- >> > basefiles | 0 | 0 >> > cdimages | 0 | 0 >> > client | 2 | 1 >> > counters | 1 | 1 >> > device | 0 | 0 >> > file | 650659 | 11558 >> > filename | 623012 | 3958 >> > fileset | 22 | 1 >> > job | 384 | 10 >> > jobmedia | 596 | 7 >> > location | 0 | 0 >> > locationlog | 0 | 0 >> > log | 0 | 0 >> > media | 245 | 9 >> > mediatype | 2 | 1 >> > path | 67908 | 966 >> > pool | 5 | 1 >> > sql_features | 439 | 6 >> > sql_implementation_info | 12 | 1 >> > sql_languages | 4 | 1 >> > sql_packages | 10 | 1 >> > sql_parts | 9 | 1 >> > sql_sizing | 23 | 1 >> > sql_sizing_profiles | 0 | 0 >> > status | 19 | 1 >> > storage | 2 | 1 >> > unsavedfiles | 0 | 0 >> > version | 1 | 1 >> >> Do relpages * 8096 / reltuples. >> Default installs have 8 KB pages/blocks by default. >> >> For file it should be ~144 bytes/tuple. >> For filename it should be ~51 bytes/tuple. > >Those tuple size estimates seem consistent with the tuple >sizes reported earlier when the overhead is added in. I'm impressed! > [snip] - Joris
Ross Boylan wrote:
db=> explain analyze select email from users where email='abc@abc.com';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..652.40 rows=1 width=42) (actual time=3.467..3.467 rows=0 loops=1)
Filter: (email = 'abc@abc.com'::text)
Total runtime: 3.497 ms
now we add index on table 'users' column 'email':
db=> create index users_email_idx on users (email);
repeat the query:
db=> explain analyze select email from users where email='abc@abc.com';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using us_email on users (cost=0.00..8.05 rows=1 width=42) (actual time=0.113..0.113 rows=0 loops=1)
Index Cond: (email = 'abc@abc.com'::text)
Total runtime: 0.152 ms
(3 rows)
compare only 'Total runtime' for start: 0.152ms from second query comparing to 3.497ms from first query,guess we have some improvements ?
Always try to use 'explain analyze' commands to see if there's any difference in any change You do in Your database
2. do 'vacuum verbose analyze table' occasionally,depending on Your database usage,meanining if You're deleting/updating stuff like that frequently,You will have to do more often 'vaccum'. If You import all Your data and You basically do 'select',meaning reading data,do 'vacuum' on New Years Eve :)
3. You want to know the size of You tables,indexes ? You want to know about their size before and after 'vacuum' command ? Example:
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5496 kB
(1 row)
then we do the ordinary 'vacuum':
sms=> vacuum users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5520 kB
(1 row)
not much improvement ,ok we're going do the full 'vacuum':
db=> vacuum full users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
80 kB
(1 row)
How about indexes ? Example:
db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
192 kB
(1 row)
for indexes there're not 'vacuum',but 'reindex' command,like :
db=> REINDEX INDEX users_pkey ;
REINDEX
we look at the index size after 'reindex' command:
db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
16 kB
(1 row)
4. don't bother to 'analyze' anything :) if all those numbers and stuff doesn't mean anything to You,guess Your life would be easier than trying to apprehend all those 'analyzes' :) just do the regularly 'clean-up' on Your DB,and You should be fine.
Unfortunately,PostgreSQL is not a database meant for people looking for a 'black box' database solution,meaning plug-in-electricity-and-forget. But,if You belong to that group of people who are willing to understand better what their database solutions is up to and maximize the proportion 'what do I get'/'for how much money',You're welcome by all means :)
Sincerely
Dragan
1. optimize your sql queries and 'understand' index usage,don't index everything because 'somebody told You indexes are good to speed things up',for exampleI'm not a DB admin; I only play one on my computer. I clearly need to figure out how to get regular vacuum, analyze, and reindexing going (if they aren't going already).Thanks for all your help. Ross
db=> explain analyze select email from users where email='abc@abc.com';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..652.40 rows=1 width=42) (actual time=3.467..3.467 rows=0 loops=1)
Filter: (email = 'abc@abc.com'::text)
Total runtime: 3.497 ms
now we add index on table 'users' column 'email':
db=> create index users_email_idx on users (email);
repeat the query:
db=> explain analyze select email from users where email='abc@abc.com';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using us_email on users (cost=0.00..8.05 rows=1 width=42) (actual time=0.113..0.113 rows=0 loops=1)
Index Cond: (email = 'abc@abc.com'::text)
Total runtime: 0.152 ms
(3 rows)
compare only 'Total runtime' for start: 0.152ms from second query comparing to 3.497ms from first query,guess we have some improvements ?
Always try to use 'explain analyze' commands to see if there's any difference in any change You do in Your database
2. do 'vacuum verbose analyze table' occasionally,depending on Your database usage,meanining if You're deleting/updating stuff like that frequently,You will have to do more often 'vaccum'. If You import all Your data and You basically do 'select',meaning reading data,do 'vacuum' on New Years Eve :)
3. You want to know the size of You tables,indexes ? You want to know about their size before and after 'vacuum' command ? Example:
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5496 kB
(1 row)
then we do the ordinary 'vacuum':
sms=> vacuum users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5520 kB
(1 row)
not much improvement ,ok we're going do the full 'vacuum':
db=> vacuum full users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
80 kB
(1 row)
How about indexes ? Example:
db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
192 kB
(1 row)
for indexes there're not 'vacuum',but 'reindex' command,like :
db=> REINDEX INDEX users_pkey ;
REINDEX
we look at the index size after 'reindex' command:
db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
16 kB
(1 row)
4. don't bother to 'analyze' anything :) if all those numbers and stuff doesn't mean anything to You,guess Your life would be easier than trying to apprehend all those 'analyzes' :) just do the regularly 'clean-up' on Your DB,and You should be fine.
Unfortunately,PostgreSQL is not a database meant for people looking for a 'black box' database solution,meaning plug-in-electricity-and-forget. But,if You belong to that group of people who are willing to understand better what their database solutions is up to and maximize the proportion 'what do I get'/'for how much money',You're welcome by all means :)
Sincerely
Dragan
> I am still trying to figure out if the database was getting any > automatic vacuuming at all. The Postgres documentation (the database is > 8.2, though I'm moving to 8.3 soon) sounds as if it's on automatically, > but the Debian-specific documentation suggests I may need to do some > additional things to enable it. This is probably far too late but anyway. You do indeed have to enable autovacuum with 8.2, as it isn't enabled by default, at least with most distributions. 8.3 it's by default with most distributions. # show autovacuum; will tell you if it's on. Cheers Anton