Обсуждение: Size on Disk

Hello.

I am working on a Knoppix distribution of my program.
I do have it working, but the size of the database on
disk is becoming a factor. (I copy it to ramdisk
before starting postmaster).

How can I change (minimize) the size of the db on
disk? Are there any parameters I can set? Maybe limit
the size of logs/journals?

Greg (dostatnig@yahoo.com)

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

you do not mention which version of postgresql or which files are problematic,
but just guessing:
- log/checkpoint files: http://www.varlena.com/GeneralBits/Tidbits/perf.html
- index files: see "reindex" command
- table files: see FSM settings and vacuum more, or vacuum full
  (contrib/pg_autovacuum is helpful here)

SELECT CASE WHEN ((SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND
(class3.oid= pg_index.indrelid))) IS NOT NULL) THEN (SELECT class3.relname FROM pg_class class3, pg_index WHERE
((class1.oid= pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) WHEN (class1.relname IS NOT NULL) THEN
class1.relnameELSE NULL::name END AS "table", CASE WHEN (class1.relkind = 'r'::"char") THEN NULL::name ELSE
class1.relnameEND AS "index", (class1.relpages * 8) AS "size (KBytes)" FROM pg_class class1 WHERE ((class1.relkind =
'r'::"char")OR (class1.relkind = 'i'::"char")) ORDER BY CASE WHEN ((SELECT class3.relname FROM pg_class class3,
pg_indexWHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) IS NOT NULL) THEN (SELECT
class3.relnameFROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid =
pg_index.indrelid)))WHEN (class1.relname IS NOT NULL) THEN class1.relname ELSE NULL::name END, CASE WHEN
(class1.relkind= 'r'::"char") THEN NULL::name ELSE class1.relname END, (class1.relpages * 8); 

might help telling you how big things are. i have problems sometimes where
the sizes for indexes are not correct. you can also run "vacuum verbose full"
and see if it is maybe dead tuples sticking around.

On Tue, 25 Nov 2003, Grzegorz Dostatni wrote:
> I am working on a Knoppix distribution of my program.
> I do have it working, but the size of the database on
> disk is becoming a factor. (I copy it to ramdisk
> before starting postmaster).
>
> How can I change (minimize) the size of the db on
> disk? Are there any parameters I can set? Maybe limit
> the size of logs/journals?

Thank you for your very quick response Chester.

I guess I should clarify something. Knoppix is a
version of linux that runs entirely in memory. I need
as smallest footprint as possible.

Currently the datase is roughly 80 Megs. About half of
the size is stored in pg_xlog directory. I managed to
figure out that those files are transaction log files?
How can I delete them safely? (after creatation the
database will pretty much be read-only - any changes
will be in memory only). Are there any other tricks I
can use to get the size down? Please keep in mind that
I work with a fairly new database - I'm always
recreating it from a dump so only the initial inserts
are in there, no changes or deletions.

Vacuuming helps a bit, but nowhere near as much as I
would hope.

Greg
Greg

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

Grzegorz Dostatni <dostatnig@yahoo.com> writes:
> Currently the datase is roughly 80 Megs. About half of
> the size is stored in pg_xlog directory. I managed to
> figure out that those files are transaction log files?
> How can I delete them safely?

You can NOT.  Don't even think about going there.

What you can do, if you intend only low-update-volume usage,
is reduce checkpoint_segments to reduce the number of WAL files
the system wants to keep around.

            regards, tom lane

Hello

>SELECT CASE WHEN ((SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND
(class3.oid= pg_index.indrelid))) IS NOT NULL) THEN (SELECT class3.relname FROM pg_class class3, pg_index WHERE
((class1.oid= pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) WHEN (class1.relname IS NOT NULL) THEN
class1.relnameELSE NULL::name END AS "table", CASE WHEN (class1.relkind = 'r'::"char") THEN NULL::name ELSE
class1.relnameEND AS "index", (class1.relpages * 8) AS "size (KBytes)" FROM pg_class class1 WHERE ((class1.relkind =
'r'::"char")OR (class1.relkind = 'i'::"char")) ORDER BY CASE WHEN ((SELECT class3.relname FROM pg_class class3,
pg_indexWHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) IS NOT NULL) THEN (SELECT
class3.relnameFROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid =
pg_index.indrelid)))WHEN (class1.relname IS NOT NULL) THEN class1.relname ELSE NULL::name END, CASE WHEN
(class1.relkind= 'r'::"char") THEN NULL::name ELSE class1.relname END, (class1.relpages * 8); 
>
>
I was verry interested in your querry but I did not understood it.
Therefore I rewrote it. Now it is a little simpler to read and does (in
my opinion) the same?

--
-- Amount of space per object used after vacuum
--
VACUUM;
SELECT c1.relname AS "tablename", c2.relname AS "indexname",
       c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
  AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tablename, indexname DESC, size_kb;

Caution: This Sktipt does NOT exactly the same... but the results should
be the same

Regrards
Oli

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

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

Hello,
   Try SQlite. It is small DB with SQL 92 standard - almost
implemented. If you need something fast and small it is
just for you.
   BTW: it is embadded system, so concurency... is not in the
focus ;)

--
Regards
   Michal Zaborowski (TeXXaS)
http://sqlite4delphi.sourceforge.net/