Pushing PostgreSQL to the Limit (urgent!)

Anyone use postgres on hardware with more than 4gb ram? Can you use
more memory than that with freebsd?

David Blood
Matraex, Inc

--- Paulo Henrique Baptista de Oliveira
<baptista@linuxsolutions.com.br> wrote:

I will put it to insert 30 M (millions) registers by month (or 1
Million by day) in a year is about 400 Millions registers.
Can pgsql support this? In What Machine?

I have a similar application. I am storing astronomical data
from a set of automated cameras. The data just floods in
forever. I can see a billion rows in the future.
I find that I _can_ keep up using only modest hardware IF I use
"COPY" and not "INSERT" to input the data. "COPY" is much, much
faster. Also indexes help with SELECT speed not really hurt
COPY/INSERT speed so you need to ballance. 1 Million per
24 hour period is not to much for a "standard" PC BUT if you
also want to do many SELECTS or JOINS at the same time then
you will need more so the hardware depends on what _else_ you
are doing.

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Yahoo! Autos - Get free new car price quotes
http://autos.yahoo.com

--- Paulo Henrique Baptista de Oliveira
<baptista@linuxsolutions.com.br> wrote:

I will put it to insert 30 M (millions) registers by month (or 1
Million by day) in a year is about 400 Millions registers.
Can pgsql support this? In What Machine?

Yes. A reasonably powerful PC with at least two nice fast IDE drives
should do the trick. I recommend you buy such a machine, set up
postgres, and start experimenting. It will probably take a couple
of weeks of work to figure out how to make your application run
efficiently.

On Mon, 15 Jul 2002, Chris Albertson wrote:

I have a similar application. I am storing astronomical data
from a set of automated cameras. The data just floods in
forever. I can see a billion rows in the future.
I find that I _can_ keep up using only modest hardware IF I use
"COPY" and not "INSERT" to input the data. "COPY" is much, much
faster. Also indexes help with SELECT speed not really hurt
COPY/INSERT speed so you need to ballance.

Right. You may find it worthwhile to drop the indexes, import, and rebuild
instead of import with the indexes in place, if you're not doing queries
at the same time. Or maybe partial indexes could help....

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Mon, Jul 15, 2002 at 01:09:59PM -0600, David Blood wrote:

Anyone use postgres on hardware with more than 4gb ram? Can you use
more memory than that with freebsd?

We use PostgreSQL on 16-gig Sun machines. But they're 64 bit
processors, and therefore have no trouble seeing more than 4 Gig.

Any 32-bit x86-based system is going to have some kind of evil hack
lurking around the 4 Gig mark. It's just an inherent limitation of a
32-bit address space.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

--- Curt Sampson <cjs@cynic.net> wrote:

On Mon, 15 Jul 2002, Chris Albertson wrote:

I have a similar application. I am storing astronomical data
from a set of automated cameras. The data just floods in
forever. I can see a billion rows in the future.
I find that I _can_ keep up using only modest hardware IF I use
"COPY" and not "INSERT" to input the data. "COPY" is much, much
faster. Also indexes help with SELECT speed not really hurt
COPY/INSERT speed so you need to ballance.

Right. You may find it worthwhile to drop the indexes, import, and
rebuild
instead of import with the indexes in place, if you're not doing
queries
at the same time. Or maybe partial indexes could help....

I took that route on one application. Not sure if it was a smart move.
The trouble is that yes, the import goes faster but then you have to
re-build the dropped index. Can take a long time. So the database
is not usable untill the time to import PLUS the time to index.
Leaving the index in place means both are done at once.

Maybe the best thing would be to use a multi-CPU machine and do the
inport using several processes. In the Intel world dual CPU
systems are cost effective. But I agree experiment. but you _must_
us full size tables. I made the mistake once of prototyping using
small cacheable tables. It did not scale to larger 1E7 size tables.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974
http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Yahoo! Autos - Get free new car price quotes
http://autos.yahoo.com

On Tue, 16 Jul 2002, Andrew Sullivan wrote:

Any 32-bit x86-based system is going to have some kind of evil hack
lurking around the 4 Gig mark. It's just an inherent limitation of a
32-bit address space.

Well, it's going to be well under 4 gig for Unix; 2 gig or 3 gig is more
typical, since part of every process' address space is dedicated to
kernel use.

However, when it comes to postgres you're not likely to see any evil
hacks unless you want to dedicated more than a couple of gig to
postgres' shared memory buffers. The OS's cached disk blocks need not
all be mapped at the same time, and each process has a separate address
space, so the total memory used by cached disk blocks and all of the
processes in the system can be well over 4 GB without any problems at all.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC