database & table size

Поиск
Список
Период
Сортировка
Искать

database & table size

От:
"Anagha Joshi" <ajoshi@nulinkinc.com>
Дата:

Hello ,

Here is something I’ve found related to database & table size

My databse name is ’test’ and table name is ‘cdrrec’.

    1.      Following will give oids & database

test=# select datname, oid from pg_database;

   datname   |   oid

-------------+---------

 fcsconfigdb |   16562

 template1   |       1

 template0   |   16555

 fcsauditdb  |   16565

 fcsbsdb     |   16566

 fcsmibdb    |   32386

 fcstrapdb   |   52561

 test        | 1580177

(8 rows)

test=# select oid from pg_class where relname = 'cdrrec';

   oid

---------

 2078989

(1 row)

    2.      Here are actual disk sizes of tables & database

$ cd /export/home/uxapps/postgresql-7.2.4/data/base/

$ ls -l

total 32

drwx------   2 postgres postgres    1536 Jun  6 17:13 1

drwx------   2 postgres postgres    1536 Jun 24 15:02 1580177

drwx------   2 postgres postgres    1536 Jun  6 17:02 16555

drwx------   2 postgres postgres    2048 Jun  6 18:04 16562

drwx------   2 postgres postgres    1536 Jun 10 16:18 16565

drwx------   2 postgres postgres    2048 Jun  6 18:03 16566

drwx------   2 postgres postgres    1536 Jun  6 17:41 32386

drwx------   3 postgres postgres    1536 Jun 19 11:08 52561

    3.      the directory ‘1580177’ corresponds to ‘test’ database.

$ cd 1580177

    4.      the file ‘2078989’ corresponds to ‘cdrrec’ file (table)

$ ls -l 2078989

-rw-------   1 postgres postgres 2359296 Jun 24 17:18 2078989

This means table ’cdrrec’ takes approx. 2030.2626953125 KB i.e. approx. 2MB

Table is like this:

test=# \d cdrrec;

                                     Table "cdrrec"

 Column  |         Type          |                      Modifiers

---------+-----------------------+------------------------------------------------------

 seqno   | integer               | not null default nextval('"cdrrec_seqno_seq"'::text)         //4 bytes

 timerec | bigint                |                                                      //8 bytes                                              

 ack     | boolean               | default 'f'::bool                                    //1 byte

 pos     | boolean               | default 'f'::bool                                    //1 byte       

 ipaddr  | character varying(16) |                                                      // 4 + 16

 cdrs    | bit varying(524288)   |                                                      //64K

Primary key: cdrrec_pkey

*Sizes are as per documentation.

Therefore, 1 record is of 65570 bytes i.e. 64.033203125 KB.

And table contains 1000 records.

But results are noway closer to ‘2MB”.

Any idea?

Thanks,

Anagha

Re: [SQL] database & table size

От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:

On Wed, 25 Jun 2003, Anagha Joshi wrote:

> 	4.	the file '2078989' corresponds to 'cdrrec' file (table)
>
> $ ls -l 2078989
> -rw-------   1 postgres postgres 2359296 Jun 24 17:18 2078989
>
> This means table 'cdrrec' takes approx. 2030.2626953125 KB i.e. approx.
> 2MB
>
> Table is like this:
>
> test=# \d cdrrec;
>                                      Table "cdrrec"
>  Column  |         Type          |                      Modifiers
> ---------+-----------------------+--------------------------------------
> ----------------
>  seqno   | integer               | not null default
> nextval('"cdrrec_seqno_seq"'::text) 	//4 bytes
>  timerec | bigint                |
> //8 bytes
>  ack     | boolean               | default 'f'::bool
> //1 byte
>  pos     | boolean               | default 'f'::bool
> //1 byte
>  ipaddr  | character varying(16) |
> // 4 + 16
>  cdrs    | bit varying(524288)   |
> //64K
> Primary key: cdrrec_pkey
>
> *Sizes are as per documentation.
>
> Therefore, 1 record is of 65570 bytes i.e. 64.033203125 KB.
>
> And table contains 1000 records.
>
> But results are noway closer to '2MB".
>
> Any idea?

The two varyings won't necessarily take up the full size if the actual
data is smaller IIRC and large values of cdrs will be compressed and/or
stuck into a toast table.  You should look for the associated toast table
and factor its space in as well (I think reltoastrelid in the pg_class
row for cdrrec will give you the appropriate oid to look for).

FAQ