Обсуждение: database size
Hi, I created a table with two columns of type int, and loaded about 300 K records in it. So, the total size of the table is approx. that of 600 K integers, roughly 2.4 MB. But, the file corresponding to the table in pgsql/data/base directory has a size of 19 MB. I was wondering if I have done something wrong in the installation or usage, or is it the normal behavior ? Also, I was trying to execute the query: select item as item, count(*) as cnt into table C_temp from temp group by item; Here, temp is the name of the table which contains the data and item is an integer attribute. While doing the sort for the group by, the size of one of the temporary pg_psort relation grows to about 314 MB. The size of the temp table is as mentioned above. If someone tried similar queries, could you please tell me if this is normal. The above query did not finish even after 2 hours. I am executing it on a Sun Sparc 5 running Sun OS 5.5. Thanks --shiby
Shiby Thomas wrote: > > Hi, > > I created a table with two columns of type int, and loaded about 300 K records > in it. So, the total size of the table is approx. that of 600 K integers, > roughly 2.4 MB. > But, the file corresponding to the table in pgsql/data/base directory > has a size of 19 MB. I was wondering if I have done something wrong in > the installation or usage, or is it the normal behavior ? This is OK. First thing - int is not 2 bytes long, it's 4 bytes long. Use int2 if you want so. Second - you have to add up other per-record stuff like oids and other internal attributes. > Also, I was trying to execute the query: > select item as item, count(*) as cnt into table C_temp > from temp group by item; > > Here, temp is the name of the table which contains the data and item is an > integer attribute. While doing the sort for the group by, the size of one of > the temporary pg_psort relation grows to about 314 MB. The size of the temp > table is as mentioned above. It ain't good. Seems like the psort is very hungry. Mike -- WWW: http://www.lodz.pdi.net/~mimo tel: Int. Acc. Code + 48 42 148340 add: Michal Mosiewicz * Bugaj 66 m.54 * 95-200 Pabianice * POLAND
On Tue, 6 Jan 1998, Shiby Thomas wrote:
> Hi,
>
> I created a table with two columns of type int, and loaded about 300 K records
> in it. So, the total size of the table is approx. that of 600 K integers,
> roughly 2.4 MB.
> But, the file corresponding to the table in pgsql/data/base directory
> has a size of 19 MB. I was wondering if I have done something wrong in
> the installation or usage, or is it the normal behavior ?
>
> Also, I was trying to execute the query:
> select item as item, count(*) as cnt into table C_temp
> from temp group by item;
>
> Here, temp is the name of the table which contains the data and item is an
> integer attribute. While doing the sort for the group by, the size of one of
> the temporary pg_psort relation grows to about 314 MB. The size of the temp
> table is as mentioned above. If someone tried similar queries, could you
> please tell me if this is normal.
> The above query did not finish even after 2 hours. I am executing it on a
> Sun Sparc 5 running Sun OS 5.5.
What version of PostgreSQL are you running? *raised eyebrow*
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
At 06:09 PM 1/6/98 -0500, Shiby Thomas wrote:
>Hi,
>
>I created a table with two columns of type int, and loaded about 300 K records
>in it. So, the total size of the table is approx. that of 600 K integers,
>roughly 2.4 MB.
>But, the file corresponding to the table in pgsql/data/base directory
>has a size of 19 MB. I was wondering if I have done something wrong in
>the installation or usage, or is it the normal behavior ?
>
Just wondering.. did you happen to do an INSERT into the database,
then delete some rows.. say 19megs worth, then re-add... From what I've
seen msql db's will always be at least the size of the largest you've ever
had the database before. It will over time, overrite existing deleted
records, but it keeps the data still in there, just sets a delete flag.
If you really need to cut the size down, I've had to delete the database
completely, then create another table from scratch. Not sure if there
is a 'purge' type function available, but you have to be careful that
nobody is accessing the db at that time, since it's very sensitive at
that time.
-Tony
>Also, I was trying to execute the query:
>select item as item, count(*) as cnt into table C_temp
>from temp group by item;
>
>Here, temp is the name of the table which contains the data and item is an
>integer attribute. While doing the sort for the group by, the size of one of
>the temporary pg_psort relation grows to about 314 MB. The size of the temp
>table is as mentioned above. If someone tried similar queries, could you
>please tell me if this is normal.
>The above query did not finish even after 2 hours. I am executing it on a
>Sun Sparc 5 running Sun OS 5.5.
>
>Thanks
>--shiby
>
>
>
>
----------------------------------------------------------------------
<bold>Tony Rios Compass Communications,
Inc.
</bold> 2001 Sixth Ave, Suite
3205
Seattle, WA 98121
<bold>VOICE</bold>: (206) 777-9999 <bold>FAX</bold>:
(206) 777-3077
----------------------------------------------------------------------
On Tue, 6 Jan 1998, Tony Rios wrote:
> At 06:09 PM 1/6/98 -0500, Shiby Thomas wrote:
>
> >Hi,
>
> >
>
> >I created a table with two columns of type int, and loaded about 300 K records
>
> >in it. So, the total size of the table is approx. that of 600 K integers,
>
> >roughly 2.4 MB.
>
> >But, the file corresponding to the table in pgsql/data/base directory
>
> >has a size of 19 MB. I was wondering if I have done something wrong in
>
> >the installation or usage, or is it the normal behavior ?
>
> >
>
>
> Just wondering.. did you happen to do an INSERT into the database,
>
> then delete some rows.. say 19megs worth, then re-add... From what I've
>
> seen msql db's will always be at least the size of the largest you've ever
>
> had the database before. It will over time, overrite existing deleted
>
> records, but it keeps the data still in there, just sets a delete flag.
>
>
> If you really need to cut the size down, I've had to delete the database
>
> completely, then create another table from scratch. Not sure if there
>
> is a 'purge' type function available, but you have to be careful that
>
> nobody is accessing the db at that time, since it's very sensitive at
>
> that time.
vacuum will clean out the deleted records and truncate the table...has
been so since v6.1, I believe...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
=> What version of PostgreSQL are you running? *raised eyebrow* 6.2.1. I haven't yet applied the patches(put in the PostgreSQL web page) though. --shiby
> Just wondering.. did you happen to do an INSERT into the database, > > then delete some rows.. say 19megs worth, then re-add... From what I've > > seen msql db's will always be at least the size of the largest you've ever > > had the database before. It will over time, overrite existing deleted > > records, but it keeps the data still in there, just sets a delete flag. > > > If you really need to cut the size down, I've had to delete the database > > completely, then create another table from scratch. Not sure if there > > is a 'purge' type function available, but you have to be careful that > > nobody is accessing the db at that time, since it's very sensitive at > > that time. > Thanks to Vadim, vacuum shrinks the size to the exact amount needed to store the data. Also, the table is locked while vacuuming, so no one can accidentally access it. -- Bruce Momjian maillist@candle.pha.pa.us