Обсуждение: Disk space taken
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
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
Thanks Richard,
Running vacuumdb --full freed the rest of the disk space. Regarding indexes from what you said I understand that if I want to free the space used in the past by indexes and that isn't freed yet (but it should) I should run reindexdb (or reindex in SQL). I did that and another 300MB got freed so thank you for both hints,
Cheers,
Slavisa
Running vacuumdb --full freed the rest of the disk space. Regarding indexes from what you said I understand that if I want to free the space used in the past by indexes and that isn't freed yet (but it should) I should run reindexdb (or reindex in SQL). I did that and another 300MB got freed so thank you for both hints,
Cheers,
Slavisa
On 18/07/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> 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:
I believe that only vacuum full returns disk spaces. Also, if there are any indexs they could be
using disk space also.
http://www.postgresql.org/docs/8.1/interactive/sql-reindex.html
http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html
Regards,
Richard Broersma Jr.
> Running vacuumdb --full freed the rest of the disk space. Regarding indexes > from what you said I understand that if I want to free the space used in the > past by indexes and that isn't freed yet (but it should) I should run > reindexdb (or reindex in SQL). I did that and another 300MB got freed so > thank you for both hints, Here is another link you might find useful. http://linux.inet.hr/optimize_postgresql_database_size.html I found it on this thread. It is an interesting read. http://archives.postgresql.org/pgsql-general/2006-06/msg00632.php Regards, Richard Broersma Jr.
On Tuesday 18 July 2006 03:22, "Slavisa Garic" <sgaric@gmail.com> wrote: > > 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. truncate gridrun; or vacuum full analyze ridrun; reindex table gridrun; Either will reclaim the space. -- Alan
> 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: I believe that only vacuum full returns disk spaces. Also, if there are any indexs they could be using disk space also. http://www.postgresql.org/docs/8.1/interactive/sql-reindex.html http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html Regards, Richard Broersma Jr.