Reducing memory usage of insert into select operations?
Redhat 4
postgresql 8.3.3
Memory: 12GB
While doing a couple of operations of the type
insert into <table> select from <some other table>
The OS triggered the out of memory killer (oom-killer).
After some research and trial/error I found it was the inserts.
I see one of the inserts is using up 12GB!
How can I reduce the usage?
Postgresql.conf settings.
shared_buffers = 3GB
temp_buffers = 64MB # min 800kB
work_mem = 256MB # min 64kB
maintenance_work_mem = 1GB
Reducing work_mem would help?
The table I am selecting from has a few hundred million rows.
The table I am inserting into has partitions. I am benchmarking breaking up
a large table into smaller partitions.
On Thu, Jul 17, 2008 at 7:21 PM, Francisco Reyes <lists@stringsutils.com> wrote:
Redhat 4
postgresql 8.3.3
Memory: 12GBWhile doing a couple of operations of the type
insert into <table> select from <some other table>The OS triggered the out of memory killer (oom-killer).
Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way
too big for a 32-bit setup.
-Doug
Douglas McNaught writes:
Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way
too big for a 32-bit setup.
64-bit.
The machine has 12GB of RAM so shared-buffers is about 1/3.
Dedicated DB server.
On Thu, Jul 17, 2008 at 9:27 PM, Francisco Reyes <lists@stringsutils.com> wrote:
Douglas McNaught writes:
Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way
too big for a 32-bit setup.64-bit.
The machine has 12GB of RAM so shared-buffers is about 1/3.
Dedicated DB server.
Ahh, good. Just wanted to answer the obvious question first. Some
people set shared_buffers really high on 32-bit systems and then are
surprised when it doesn't work well.
It does seem that reducing work_mem might help you, but others on this
list are much more expert than I in diagnosing this sort of problem.
It would probably be helpful for you to post the EXPLAIN output from
your query, so they can see which part of the plan causes the large
memory usage.
-Doug
Douglas McNaught writes:
It does seem that reducing work_mem might help you, but others on this
I reduced it from 256MB to 64MB. It seems it is helping.
At 256MB the usage per DB connection instance was upwards of 12GB. At 64MB
so far is around 7GB. I just reduced it further to 32MB and see how that
works.
It would probably be helpful for you to post the EXPLAIN output from
Hash Join (cost=712213.57..27293913.33 rows=234402352 width=24)
Hash Cond: (coh.id = ids.id)
-> Hash Join (cost=551387.26..18799378.16 rows=234402352 width=22)
Hash Cond: (coh.user_id = ca.user_id)
-> Seq Scan on customer_original_historical coh
(cost=0.00..6702501.40 rows=234402352 width=47)
Filter: (yearmo > '200703'::bpchar)
-> Hash (cost=268355.67..268355.67 rows=14637567 width=32)
-> Seq Scan on cards ca
(cost=0.00..268355.67 rows=14637567 width=32)
-> Hash (cost=77883.25..77883.25 rows=5055525 width=6)
-> Seq Scan on customer_ids ids
(cost=0.00..77883.25 rows=5055525 width=6)
Francisco Reyes wrote:
The OS triggered the out of memory killer (oom-killer).
The table I am selecting from has a few hundred million rows.
The table I am inserting into has partitions. I am benchmarking breaking
up a large table into smaller partitions.
Is the partition split done with triggers or rules?
--
Richard Huxton
Archonet Ltd
On Fri, Jul 18, 2008 at 12:18 AM, Francisco Reyes
<lists@stringsutils.com> wrote:
Douglas McNaught writes:
It does seem that reducing work_mem might help you, but others on this
I reduced it from 256MB to 64MB. It seems it is helping.
You should also look at your memory overcommit settings (in
/proc/sys/vm). You can set things up so that Postgres gets a malloc()
failure (which it is generally prepared to cope with cleanly) when the
system runs out of RAM, rather than having the OOM killer go off and
hit it with SIGKILL. Overcommit is useful in some contexts (Java apps
tend to map a lot more memory than they actually use) but for a
dedicated database server you really don't ever want to have the OOM
killer triggered.
-Doug
On 9:53 am 07/18/08 "Douglas McNaught" <doug@mcnaught.org> wrote:
dedicated database server you really don't ever want to have the OOM
killer triggered.
Found that yesterday (vm.overcommit_memory=2).
Agree that this is better than OOM. I still ran out of memory last night
and postgres just failed on the malloc(), which as you mentioned is better.
Reduced work_mem to 8MB and trying again.
On 8:13 am 07/18/08 Richard Huxton <dev@archonet.com> wrote:
Is the partition split done with triggers or rules?
I have a single trigger+function combo that dynamically computes which
partition the data has to go to.
Francisco Reyes wrote:
On 8:13 am 07/18/08 Richard Huxton <dev@archonet.com> wrote:
Is the partition split done with triggers or rules?
I have a single trigger+function combo that dynamically computes which
partition the data has to go to.
I'm wondering whether it's memory usage either for the trigger itself,
or for the function (pl/pgsql?). If you're doing something like:
INSERT INTO partitioned_table SELECT * FROM big_table
then that's not only taking place within a single transaction, but
within a single statement.
Without being a hacker, I'd say it's entirely plausible that PG might
clean up triggers at the end of a statement meaning you would need
memory for 200million+ triggers.
Alternatively, it could be a memory-leak somewhere in the pl/pgsql or
trigger code. Wouldn't have to be much to affect this particular case.
What happens if you do the insert/select in stages but all in one
transaction? Do you see PG's memory requirement stay constant or grow in
steps. That will show whether the memory is growing over the duration of
a statement or a transaction.
BEGIN;
INSERT ... SELECT ... WHERE id BETWEEN 0 AND 999999
INSERT ... SELECT ... WHERE id BETWEEN 1000000 AND 1999999
...
COMMIT;
--
Richard Huxton
Archonet Ltd
On 11:25 am 07/18/08 Richard Huxton <dev@archonet.com> wrote:
I'm wondering whether it's memory usage either for the trigger
itself, or for the function (pl/pgsql?).
Good point.
If you're doing something
like: INSERT INTO partitioned_table SELECT * FROM big_table
then that's not only taking place within a single transaction, but
within a single statement.
Correct.
I have kept decreasing work_mem and that does not seem to help.
Without being a hacker, I'd say it's entirely plausible that PG might
clean up triggers at the end of a statement meaning you would need
memory for 200million+ triggers.
Sure hope that is not the case.
Alternatively, it could be a memory-leak somewhere in the pl/pgsql or
trigger code. Wouldn't have to be much to affect this particular case.
Will post an strace.
What happens if you do the insert/select in stages but all in one
transaction?
Will test.
The data is about a year worth of data. I will try to do one month at a
time, within a single transaction.
A single month finishes fine.
Do you see PG's memory requirement stay constant or grow
in steps. That will show whether the memory is growing over the
duration of a statement or a transaction.
Right now for the single statement/transaction (the one big process) it is
growing slowly over time. It may be a leak. It seems to start growing
somewhere between the 1st and 2nd hower. It seems to always be failing
around 4 hours.
I wrote a little process that shows the amount of free memory every 15
minutes..
I will post strace for the big process and then will try breaking the
process down by month, but within a single transaction and report that
later when I get some results.
On 11:25 am 07/18/08 Richard Huxton <dev@archonet.com> wrote:
Strace of the single/large process.
Again, all the query is doing is
insert into <file> select <subquery>
The strace is pretty much a repetition of the lines below.
semop(557057, 0x7fbfffdfb0, 1) = 0
lseek(100, 0, SEEK_END) = 671719424
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(508, 0, SEEK_END) = 55697408
write(508, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, "\0\0\0&\0\224\21\0\225o\10\0\30\331c\0c\225%w(\0\0\0\0\0\0\0\0\0\5\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1a0, 1) = 0
lseek(100, 0, SEEK_END) = 671727616
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1) = 0
semop(557057, 0x7fbfffd1a0, 1) = 0
semop(557057, 0x7fbfffd1c0, 1) = 0
read(381, "w\317\21\0<]9\0\177\246eA(\0\0\0\0\0\0\0\0\0\5\0\2\0\30\0.\v\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1a0, 1) = 0
lseek(512, 0, SEEK_END) = 48144384
write(512, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1) = 0
lseek(100, 0, SEEK_END) = 671735808
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(517, 0, SEEK_END) = 89309184
write(517, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1) = 0
semop(557057, 0x7fbfffddd0, 1) = 0
lseek(100, 0, SEEK_END) = 671744000
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, ">\212\225\202(\0\0\0\0\0\0\0\0\0\5\0\2\0\30\0000\v\0\0\1\0\23\2\0\0\0\t"..., 8192) = 8192
lseek(510, 0, SEEK_END) = 29351936
write(510, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(100, 0, SEEK_END) = 671752192
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffddf0, 1) = 0
read(381, "\0\0\0\0\0\0\5\0\2\0\30\0001\v\0\0\0\0\23\2\0\0\0\30\0\4\20\0\302\326\0\0"..., 8192) = 8192
lseek(513, 0, SEEK_END) = 19316736
write(513, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(100, 0, SEEK_END) = 671760384
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, "\2\0\30\0004\v\0\0\0\0\23\2\0\0\0\16\0\254\r\0\t+\35\0\301\217@\0\304\346U\241"..., 8192) = 8192
lseek(100, 0, SEEK_END) = 671768576
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(518, 0, SEEK_END) = 55025664
write(518, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1) = 0
semop(557057, 0x7fbfffd1c0, 1) = 0
semop(557057, 0x7fbfffd1c0, 1) = 0
lseek(100, 0, SEEK_END) = 671776768
On Fri, Jul 18, 2008 at 10:40:02AM -0400, Francisco Reyes wrote:
Found that yesterday (vm.overcommit_memory=2).
Agree that this is better than OOM. I still ran out of memory last night
and postgres just failed on the malloc(), which as you mentioned is better.Reduced work_mem to 8MB and trying again.
Perhaps you can try reducing the shared_buffers, to see if that helps
more? 8MB is quite small for workmem. More shared_buffers is not
necessarily better.
Also, how much swap are you running? overcommit disabled while not
having any swap setup is a great way to ensure you run out of memory
quickly.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
Francisco Reyes wrote:
On 11:25 am 07/18/08 Richard Huxton <dev@archonet.com> wrote:
Strace of the single/large process.
Again, all the query is doing is
insert into <file> select <subquery>The strace is pretty much a repetition of the lines below.
Do you have long-running transactions? (For example transactions that
have been idle for a long time).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On 12:03 pm 07/18/08 Martijn van Oosterhout <kleptog@svana.org> wrote:
Perhaps you can try reducing the shared_buffers, to see if that helps
more?
Will try.
8MB is quite small for workmem. More shared_buffers is not
necessarily better.
Ok, but from everything I had read shared_buffers of 1/4 seemed like a
starting point. Will try reducing it to 2GB.
Also, how much swap are you running?
Started out with 12GB (same as memory) and last night I added 24GB more.
I had 2 instances of inserts going so each exausted about 18GB of ram!
On 12:23 pm 07/18/08 Alvaro Herrera <alvherre@commandprompt.com> wrote:
Do you have long-running transactions? (For example transactions that
have been idle for a long time).
No.
The two inserts I was running were the only processes. I even did a restart
to make sure there was absolutely nothing else running and to make sure all
my postgresql.conf settings were in.
Given that memory grows over time I am beggining to wonder if it is some
type of memory leak.
Just installed the postgresql debug rpm, but not sure if did anything..
strace doesn't look any different..
read(81, "2\1\0\0\260~!\16\1\0\0\0\370\1\0\2\0 \4 \0\0\0\0\300\237r\0\200\237r\0"..., 8192) = 8192
write(191, "Q=J\313\253]1\0\0\0\1\0007\33\4\0\2\0\2\t\30\0\3\302\204\0;a1OjG"..., 8192) = 8192
write(160, "XQxbqQEx+yo=H\333o\2371\0\0\0\1\0.\33C\0\2\0\2\t\30\0"...,
8192) = 8192
read(81, "2\1\0\0\320(\301\17\1\0\0\0\370\1\0\2\0 \4 \0\0\0\0\300\237r\0\200\237r\0"..., 8192) = 8192
"Francisco Reyes" <lists@stringsutils.com> writes:
Given that memory grows over time I am beggining to wonder if it is some
type of memory leak.
Are there any AFTER triggers (including foreign key constraints) on the
table being inserted into? If so the list of pending trigger events
might be your problem.
If you can get Postgres to report an actual out-of-memory error (as
opposed to crashing from OOM kill) then it should dump a memory usage
map into the postmaster log. Looking at that would be informative.
regards, tom lane
On 1:00 pm 07/18/08 Tom Lane <tgl@sss.pgh.pa.us> wrote:
Are there any AFTER triggers (including foreign key constraints)
I have two foreign key constraints.
the table being inserted into? If so the list of pending trigger
events might be your problem.
I guess I can try disablign the foreign key, but that would be less than
ideal for production. This is an analytics environment so all operations
are in bulk.
If you can get Postgres to report an actual out-of-memory error (as
opposed to crashing from OOM kill)
Disabled oom with vm.overcommit_memory=2.
then it should dump a memory usage
map into the postmaster log. Looking at that would be informative.
Got it.
----------------------
AfterTriggerEvents: 10553909248 total in 1268 blocks; 20432 free (6
chunks); 10553888816 used
ExecutorState: 122880 total in 4 blocks; 68040 free (8 chunks); 54840
used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
MessageContext: 131072 total in 5 blocks; 50712 free (291 chunks); 80360
used
smgr relation table: 24576 total in 2 blocks; 3584 free (4 chunks); 20992
used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 768 free (0 chunks); 256 used
ExecutorState: 98784 total in 8 blocks; 24064 free (22 chunks); 74720
used
ExprContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
HashTableContext: 8192 total in 1 blocks; 8064 free (1 chunks); 128
used
HashBatchContext: 532676656 total in 74 blocks; 1863936 free (5
chunks); 530812720 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 415227952 total in 59 blocks; 6589744 free (5
chunks); 408638208 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
Relcache by OID: 24576 total in 2 blocks; 8672 free (3 chunks); 15904 used
CacheMemoryContext: 2390256 total in 22 blocks; 751904 free (2 chunks);
1638352 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 7168 total in 3 blocks; 3120 free (0 chunks); 4048 used
CachedPlanSource: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
SPI Plan: 1024 total in 1 blocks; 784 free (0 chunks); 240 used
CachedPlan: 3072 total in 2 blocks; 792 free (0 chunks); 2280 used
CachedPlanSource: 7168 total in 3 blocks; 3600 free (0 chunks); 3568 used
SPI Plan: 1024 total in 1 blocks; 800 free (0 chunks); 224 used
pg_cast_source_target_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_language_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_toast_2619_index: 2048 total in 1 blocks; 608 free (0 chunks); 1440
used
pg_amop_opr_fam_index: 2048 total in 1 blocks; 608 free (0 chunks);
1440 used
tcf_mnfoids_partid: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
tcf_mnfoids_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
cards_cardnum_key: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
cards_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
tcf_original_trans_partid_cardnum: 2048 total in 1 blocks; 656 free (0
chunks); 1392 used
tcf_original_trans_yearmo: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
pg_constraint_contypid_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
pg_constraint_conname_nsp_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 392 free (0
chunks); 1656 used
pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
pg_proc_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_shdepend_reference_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_namespace_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_statistic_relid_att_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_inherits_relid_seqno_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_constraint_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_attribute_relid_attnam_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_attrdef_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
pg_shdepend_depender_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
pg_type_typname_nsp_index: 2048 total in 1 blocks; 608 free (0 chunks);
1440 used
pg_type_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_depend_depender_index: 2048 total in 1 blocks; 584 free (0 chunks);
1464 used
pg_depend_reference_index: 2048 total in 1 blocks; 584 free (0 chunks);
1464 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_description_o_c_o_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
pg_constraint_conrelid_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_class_relname_nsp_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_namespace_nspname_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
pg_authid_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
MdSmgr: 24576 total in 2 blocks; 15872 free (0 chunks); 8704 used
LOCALLOCK hash: 57344 total in 3 blocks; 36384 free (11 chunks); 20960 used
Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
ERROR: out of memory
DETAIL: Failed on request of size 40.
"Francisco Reyes" <lists@stringsutils.com> writes:
On 1:00 pm 07/18/08 Tom Lane <tgl@sss.pgh.pa.us> wrote:
If you can get Postgres to report an actual out-of-memory error (as
opposed to crashing from OOM kill)
then it should dump a memory usage
map into the postmaster log. Looking at that would be informative.
Got it.
AfterTriggerEvents: 10553909248 total in 1268 blocks; 20432 free (6
chunks); 10553888816 used
Well, that's definitely your problem ...
HashBatchContext: 532676656 total in 74 blocks; 1863936 free (5
chunks); 530812720 used
HashBatchContext: 415227952 total in 59 blocks; 6589744 free (5
chunks); 408638208 used
although these numbers seem way outta line too. What did you say you
had work_mem set to?
regards, tom lane
On 3:55 pm 07/18/08 Tom Lane <tgl@sss.pgh.pa.us> wrote:
AfterTriggerEvents: 10553909248 total in 1268 blocks; 20432 free (6
chunks); 10553888816 usedWell, that's definitely your problem ...
So I need to remove the foreign constraints?
HashBatchContext: 415227952 total in 59 blocks; 6589744
free (5 chunks); 408638208 usedalthough these numbers seem way outta line too. What did you say you
had work_mem set to?
Initially on the first crash it was 256MB. I believe at the time of the
crash I got the dump for it was down to 64MB or 8MB. I kept trying lower
values. Also tried reducing shared_buffers as someone suggested.
I will bump my shared_buffers back to 3GB and work_mem back to 64MB.