From: Rural Hunter <ruralhunter@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Julio Leyva <jcleyva@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: plsql gets "out of memory"
Date: Mon, 29 Aug 2011 23:45:39 +0800
Message-ID: <4E5BB423.6010303@gmail.com> (raw)
In-Reply-To: <4E5B67910200002500040805@gw.wicourts.gov>
References: <4E5B8FF7.4040007@gmail.com>
<BAY154-W12A115156AED066911581DA1140@phx.gbl>
<4E5B9604.5070602@gmail.com>
<4E5B518002000025000407E5@gw.wicourts.gov>
<4E5BA3C0.7080909@gmail.com>
<4E5B67910200002500040805@gw.wicourts.gov>
Hi Kevin,
Thank you very much for the quick and detailed answers/suggestions. I
will check and try them.
δΊ 2011/8/29 23:18, Kevin Grittner ει:
> Good (but don't forget to change that once the bulk load is done). You
> should probably also turn off full_page_writes and synchronous_commit.
> I've seen benchmarks which show that this helps, even with the other
> settings you mention.
I forgot to mention synchronous_commit is already off and I will test
with full_page_writes off.
>> Increase checkpoint_segments
>
> There can be paradoxical results with that. For reasons yet to be
> determined, bulk conversion (unlike most workloads) sometimes runs
> faster with a small setting like the default. You have to test to
> see how it works in your environment.
yes, will check that.
> Yeah, you definitely want to build those only after the data for a
> table is loaded. I also recommend a VACUUM FREEZE ANALYZE on the
> database unless most of these rows will be deleted or updated before
> you run a billion database transactions. Otherwise you will get a
> painful "anti-wraparound" autovacuum on everything, probably at a time
> of heavy usage.
hmm....I will try to turn autovacuum off though I didn't see any
resource intension caused by it.
>> I have about 5G memory free on the server and have these memory
>> settings:
>> shared_buffers = 1GB
>> work_mem = 8MB
>> maintenance_work_mem = 1GB
>> effective_cache_size = 4GB
>
> Reasonable for that amount of RAM, but that's seems like
> underpowered hardware for the size of the database. If the
> production conversion is going to be run against different hardware,
> these tests may not be giving you very good numbers for what to
> expect.
I have mysql run on the server. The memory above is just for the import
for pgsql. I will shutdown mysql and give the memory(32G totally) to
pgsql when I doing the performance tests.
> Network latency? Avoid encrypted connections for the load, and do
> whatever you can to minimize latency, like attaching both machines to
> the same switch. You can't improve performance much if you're working
> on the things which are only using a small part of the time.
> Identifying the source of your delays is the most important thing at
> this point.
No, I do the import locally on the db server so the network letency can
be excluded.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ruralhunter@gmail.com, Kevin.Grittner@wicourts.gov, jcleyva@hotmail.com
Subject: Re: plsql gets "out of memory"
In-Reply-To: <4E5BB423.6010303@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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