Skip site navigation (1) Skip section navigation (2)

Disk space taken

From: "Slavisa Garic" <sgaric(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Disk space taken
Date: 2006-07-18 10:22:35
Message-ID: bcb558900607180322w58ece824qee465191f13dff96@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi all,

I've run into an interesting situation (interesting for me at least :-)). I
have a table that takes up about a 1GB of disk space but has no entries in
it. I asume this is old stuff data that hasn't been cleaned up properly but
I have no idea how to get rid of it.

I'll quickly show few things I've done to get the information I have:

------------------------------------------------------------------------------------------------------------------------------------
1.
  -bash-3.00$ psql -c "select oid, datname from pg_database where
datname='wmpmpc' "
    oid  | datname
  -------+---------
   17829 | wmpmpc
  (1 row)

2.
  -bash-3.00$ du -k --max-depth=1 $PGDATA/base/ | grep 17829
  2087600 /opt/postgres/data/base/17829
  -bash-3.00$

3.
  -bash-3.00$ ls -la $PGDATA/base/17829/ | grep 18069
  -rw-------  1 postgres postgres 1073741824 Jul 18 10:52 18069
  -rw-------  1 postgres postgres  341098496 Jul 18 10:53 18069.1
  -bash-3.00$

4.
  -bash-3.00$ export PGDATABASE=wmpmpc
  -bash-3.00$ psql -c "select oid, relname from pg_class where oid = 18069"
    oid  |    relname
  -------+---------------
   18069 | gridrun
  (1 row)
  -bash-3.00$

5.
  -bash-3.00$ time psql -c "select count(*) from gridrun"
   count
  -------
       0
  (1 row)

  real    0m24.798s
  user    0m0.003s
  sys     0m0.004s
  -bash-3.00$

6.
###############################################################
-bash-3.00$ psql -c "\d gridrun"
                                    Table "public.gridrun"
   Column    |            Type             |
Modifiers
-------------+-----------------------------+--------------------------------------------------------
 agent_id    | integer                     | not null default
nextval('gridrun_id'::regclass)
 service_id  | integer                     | not null
 compute_id  | integer                     | not null
 executable  | character varying(255)      | not null
 arguments   | character varying(255)      | not null
 exe_type    | character(1)                | not null
 control     | character varying(8)        | not null default
'start'::character varying
 status      | character varying(8)        | not null default
'ready'::character varying
 actuator_id | integer                     |
 job_ident   | character varying(255)      |
 error_info  | text                        | not null default ''::text
 more_info   | text                        | not null default ''::text
 active_time | timestamp without time zone |
 lastcheck   | timestamp without time zone |
Indexes:
    "gridrun_pkey" primary key, btree (agent_id)
    "ngr_idx" btree (compute_id)
    "ngr_idx1" btree (compute_id, status)
Check constraints:
    "gridrun_status_check" CHECK (status::text = 'ready'::text OR
status::text = 'pending'::text OR status::text = 'active'::text OR
status::text = 'queued'::text OR status::text = 'failed'::text OR
status::text = 'done'::text OR status::text = 'stopping'::text OR
status::text = 'jsuspend'::text)
    "gridrun_control_check" CHECK (control::text = 'nostart'::text OR
control::text = 'start'::text OR control::text = 'stop'::text)
    "gridrun_exe_type_check" CHECK (exe_type = 'A'::bpchar OR exe_type =
'P'::bpchar OR exe_type = 'R'::bpchar)
Foreign-key constraints:
    "gridrun_actuator_id_fkey" FOREIGN KEY (actuator_id) REFERENCES
gridactuator(actuator_id) ON DELETE SET NULL
    "gridrun_compute_id_fkey" FOREIGN KEY (compute_id) REFERENCES
computeresource(compute_id)
    "gridrun_service_id_fkey" FOREIGN KEY (service_id) REFERENCES
gridservice(service_id)

###############################################################

----------------------------------------------------------------------------------------------------


So as you see no data but over a gig taken in disk space. I've ran this
after I run vacuumdb --analyze. Also I ran vacuumdb --analyze regularly
(every 10 minutes) when the database is actively used.

Any help would be really appreciate as this table has been causing me some
grief for some time.
Cheers,
Slavisa

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-07-18 12:34:05
Subject: Re: Disk space taken
Previous:From: Christoph Della ValleDate: 2006-07-18 07:06:20
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group