[GENERAL] Database Bloat
Hi, I am using PostgreSQL 9.1 and loading very large tables ( 13 million rows each ). The flat file size is only 25M. However, the equivalent database table is 548MB. This is without any indexes applied and auto vacuum turned on. I have read that the bloat can be around 5 times greater for tables than flat files so over 20 times seems quite excessive. Any ideas on how to go about decreasing this bloat or is this not unexpected for such large tables? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database Bloat
envdb=# \d astgtm2_n60e073; Table "public.astgtm2_n60e073" Column | Type | Modifiers +-+--- lat| real| lon| real| alt| integer | Indexes: "q3c_astgtm2_n60e073_idx" btree (q3c_ang2ipix(lon, lat)) CLUSTER On 8/20/2012 2:10 PM, John R Pierce wrote: On 08/20/12 10:53 AM, elliott wrote: Hi, I am using PostgreSQL 9.1 and loading very large tables ( 13 million rows each ). The flat file size is only 25M. However, the equivalent database table is 548MB. This is without any indexes applied and auto vacuum turned on. I have read that the bloat can be around 5 times greater for tables than flat files so over 20 times seems quite excessive. Any ideas on how to go about decreasing this bloat or is this not unexpected for such large tables? what do the fields of this table look like (output from \d tablename would be useful) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database Bloat
Yes, it is a tif file. Uncompressed it is around 85M. On 8/22/2012 1:20 PM, Jeff Janes wrote: On Mon, Aug 20, 2012 at 10:53 AM, elliott wrote: Hi, I am using PostgreSQL 9.1 and loading very large tables ( 13 million rows each ). The flat file size is only 25M. That is only 2 bytes per row. Is the size given for the flat file for a compressed file? Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum Launcher Process Exception
Hello, I have been running PostgreSQL for many months and just recently started getting this exception upon start up. Does anyone know what the issue might be? LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: autovacuum launcher process was terminated by exception 0xC006 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes LOG: could not open temporary statistics file "global/pgstat.tmp": No such file or directory LOG: all server processes terminated; reinitializing PANIC: could not open control file "global/pg_control": No such file or directory This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] The efficiency of the WAL log writer
Recently I became curious about the WAL log writer. I noticed that with
a default ACID complaint configuration that the the WAL log writer wakes
up every 200 msec (the default indicated by "wal_log_writer") and does
nothing. Here's a typical strace:
21:37:07.194809 select(0, NULL, NULL, NULL, {0, 31914}) = 0 (Timeout)
<0.032059>
21:37:07.227098 getppid() = 4979 <0.18>
21:37:07.227198 select(0, NULL, NULL, NULL, {0, 20}) = 0 (Timeout)
<0.200287>
21:37:07.427609 getppid() = 4979 <0.23>
and ltrace:
21:37:36.265849 getppid()= 4979 <0.000200>
21:37:36.266299 select(0, 0, 0, 0, 0x7fff2ffec9c0) = 0 <0.200442>
21:37:36.466979 getppid()= 4979 <0.000154>
21:37:36.467255 select(0, 0, 0, 0, 0x7fff2ffec9c0) = 0 <0.200442>
It's possible it's doing something useful, such as with shared memory,
that is not revealed by strace and ltrace, but my question is - For at
least default ACID complaint configurations where each write is made to
the WAL logs immediately what, if anything, is the WAL log writer
polling? And if the WAL log writer is polling for something could it
instead wait on a condition which would then be signaled?
I don't think the current behavior is particularly harmful, but maybe
PostgreSQL could be made to idle more quietly. I have not investigated
the other PostgreSQL processes as closely, but some of them appear to
behave in a similar manner.
--
----
| Steven Elliott | http://selliott.org | sellio...@austin.rr.com |
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] The efficiency of the WAL log writer
On Thu, 2011-02-17 at 10:26 -0500, Tom Lane wrote: > Steven Elliott writes: > > I don't think the current behavior is particularly harmful, but maybe > > PostgreSQL could be made to idle more quietly. > > Yeah, this is something that's on my personal to-do list. It's not > really an efficiency/performance issue, but in a machine that's > otherwise idle this behavior is bad for overall CPU power consumption. I see what you mean that it's more of a CPU power consumption issue than efficiency. That makes sense. This is a small issue that I've been meaning to ask about. Thanks for getting back to me. > The plan is to try to use the "latch" primitives that were recently > added to the code to eliminate sleep-and-check-for-something-to-do > loops. Didn't get done for 9.1 unfortunately. Sounds good. -- -------- | Steven Elliott | http://selliott.org | sellio...@austin.rr.com | -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] setting local variable
I have a function which looks like this:
...
declare _object bigint;
begin
set _object = insert into object (object_type,name) values
('type1','test object');
insert into ref (object,refname) values (_object,'test object reference');
end;
...
However I get a syntax error on the line 'set _object = insert '
I searched through the docs but don't know what I'm doing wrong.
Thanks!
Elliott
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
