pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedConfiguring a Large RAM PostgreSQL Server
5+ messages / 4 participants
[nested] [flat]
* Configuring a Large RAM PostgreSQL Server
@ 2007-11-29 18:28 Alex Hochberger <alex@dsgi.us>
0 siblings, 2 replies; 5+ messages in thread
From: Alex Hochberger @ 2007-11-29 18:28 UTC (permalink / raw)
To: pgsql-performance
Does anyone have any white papers or basic guides for a large RAM
server?
We are consolidating two databases to enable better data-mining that
currently run on a 4 GB and 2 GB machine. The data issues on the 4
GB machine are numerous, things like "create index" fail and update
queries fail from out of memory issues. Re-factoring the data is
helping, but isn't finishing the job.
The new machine will have 48 GB of RAM, so figuring out starting
points for the Shared Buffers and Work_mem/Maintenance_work_mem is
going to be a crap shoot, since the defaults still seem to be based
upon 256MB of RAM or less.
Usage:
Most of the time, the database is being hit with a handle of
poorly written and unoptimized queries from a Ruby on Rails app that
is being refactored as a simple Ruby-DBI app since we need to support
our legacy code but don't need the Rails environment, just a lot of
SQL writes. Some stored procedures should streamline this. However,
each transaction will do about 5 or 6 writes.
Common Usage: we have a reporting tool that is also being
refactored, but does a lot of aggregate queries. None of these take
more than 500 ms after indexing on the 2 GB database, so assuming
that more RAM should help and eliminate the problems.
Problem Usage: we have a 20GB table with 120m rows that we are
splitting into some sub-tables. Generally, we do large data pulls
from here, 1 million - 4 million records at a time, stored in a new
table for export. These queries are problematic because we are
unable to index the database for the queries that we run because we
get out of memory errors. Most of my cleanup has restored to FOR-IN
loops via pl-pgsql to manage the data one row at a time. This is
problematic because many of these scripts are taking 4-5 days to run.
Other usage: we will import between 10k and 10m rows at one time
out of CSVs into the big database table. I got my gig here because
this was all failing and the data was becoming worthless. These
imports involve a lot of writes.
Our simultaneous queries are small, and currently run
acceptably. It's the big imports, data-mining pulls, and system
manipulation were we routinely wait days on the query that we are
looking to speed up.
Thanks,
Alex
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Configuring a Large RAM PostgreSQL Server
@ 2007-11-29 19:15 Richard Huxton <dev@archonet.com>
parent: Alex Hochberger <alex@dsgi.us>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2007-11-29 19:15 UTC (permalink / raw)
To: Alex Hochberger <alex@dsgi.us>; +Cc: pgsql-performance
Alex Hochberger wrote:
> Does anyone have any white papers or basic guides for a large RAM server?
>
> We are consolidating two databases to enable better data-mining that
> currently run on a 4 GB and 2 GB machine. The data issues on the 4 GB
> machine are numerous, things like "create index" fail and update queries
> fail from out of memory issues.
> Problem Usage: we have a 20GB table with 120m rows that we are
> splitting into some sub-tables. Generally, we do large data pulls from
> here, 1 million - 4 million records at a time, stored in a new table for
> export. These queries are problematic because we are unable to index
> the database for the queries that we run because we get out of memory
> errors.
Would it not make sense to find out why you are getting these errors first?
It's not normal to get "out of memory" when rebuilding an index.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Configuring a Large RAM PostgreSQL Server
@ 2007-11-29 19:21 Alex Hochberger <alex@dsgi.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alex Hochberger @ 2007-11-29 19:21 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
It's not on rebuilding the index, it's on CREATE INDEX.
I attribute it to wrong setting, Ubuntu bizarre-ness, and general
problems.
We need new hardware, the servers are running on aging
infrastructure, and we decided to get a new system that will last us
the next 3-4 years all at once.
But many large queries are getting Out of Memory errors.
Alex
On Nov 29, 2007, at 2:15 PM, Richard Huxton wrote:
> Alex Hochberger wrote:
>> Does anyone have any white papers or basic guides for a large RAM
>> server?
>> We are consolidating two databases to enable better data-mining
>> that currently run on a 4 GB and 2 GB machine. The data issues on
>> the 4 GB machine are numerous, things like "create index" fail and
>> update queries fail from out of memory issues.
>
>> Problem Usage: we have a 20GB table with 120m rows that we are
>> splitting into some sub-tables. Generally, we do large data pulls
>> from here, 1 million - 4 million records at a time, stored in a
>> new table for export. These queries are problematic because we
>> are unable to index the database for the queries that we run
>> because we get out of memory errors.
>
> Would it not make sense to find out why you are getting these
> errors first?
>
> It's not normal to get "out of memory" when rebuilding an index.
>
> --
> Richard Huxton
> Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Configuring a Large RAM PostgreSQL Server
@ 2007-11-29 19:29 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Alex Hochberger <alex@dsgi.us>
0 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-11-29 19:29 UTC (permalink / raw)
To: Alex Hochberger <alex@dsgi.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
> On Nov 29, 2007, at 2:15 PM, Richard Huxton wrote:
>> Alex Hochberger wrote:
>>> Problem Usage: we have a 20GB table with 120m rows that we are
>>> splitting into some sub-tables. Generally, we do large data pulls from
>>> here, 1 million - 4 million records at a time, stored in a new table for
>>> export. These queries are problematic because we are unable to index the
>>> database for the queries that we run because we get out of memory errors.
>>
>> Would it not make sense to find out why you are getting these errors
>> first?
Alex Hochberger wrote:
> It's not on rebuilding the index, it's on CREATE INDEX.
>
> I attribute it to wrong setting, Ubuntu bizarre-ness, and general problems.
Please do not top-post. I reformatted your message for clarity.
Richard is still correct: it is not normal to get out-of-memory errors
during index building, regardless of age of servers and Linux distro.
Perhaps you just have a maintenance_work_mem setting that's too large
for your server.
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Configuring a Large RAM PostgreSQL Server
@ 2007-11-30 05:50 Josh Berkus <josh@agliodbs.com>
parent: Alex Hochberger <alex@dsgi.us>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2007-11-30 05:50 UTC (permalink / raw)
To: pgsql-performance; +Cc: Alex Hochberger <alex@dsgi.us>
Alex,
> The new machine will have 48 GB of RAM, so figuring out starting
> points for the Shared Buffers and Work_mem/Maintenance_work_mem is
> going to be a crap shoot, since the defaults still seem to be based
> upon 256MB of RAM or less.
Why a crap shoot?
Set shared_buffers to 12GB. Set work_mem to 20GB / # of concurrent active
connections (check your logs). Set Maint_mem to 2GB (I don't think we can
actually use more). Then tune from there.
Also, use 8.2 or later, and you'd better compile 64-bit.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-11-30 05:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-29 18:28 Configuring a Large RAM PostgreSQL Server Alex Hochberger <alex@dsgi.us>
2007-11-29 19:15 ` Richard Huxton <dev@archonet.com>
2007-11-29 19:21 ` Alex Hochberger <alex@dsgi.us>
2007-11-29 19:29 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-30 05:50 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox