Thread: Out of memory error
I had an Out of Memory error while running this query in psql over a
170 million rows table:
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
group by data, usuario
FC2 PG 7.4.6 1GB mem
Linux s1 2.6.9-1.11_FC2 #1 Sun Jan 2 15:49:30 EST 2005 i686 athlon
i386 GNU/Linux
logfile:
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
...
LOG: recycled transaction log file "000001D70000009A"
LOG: removing transaction log file "000001D700000097"
...
ERROR: canceling query due to user request
LOG: could not send data to client: Broken pipe
LOG: unexpected EOF on client connection
postgresql.conf (everything else default):
shared_buffers = 3000
sort_mem = 131072
vacuum_mem = 131072
max_fsm_pages = 300000
fsync = false
wal_buffers = 256
checkpoint_segments = 32
extra_float_digits = 2
lc_messages = 'C'
lc_monetary = 'pt_BR.UTF-8'
lc_numeric = 'pt_BR.UTF-8'
lc_time = 'pt_BR.UTF-8'
var/log/messages:
Feb 7 15:29:32 s1 su(pam_unix)[24675]: session closed for user root
Feb 7 16:30:22 s1 kernel: oom-killer: gfp_mask=0x1d2
Feb 7 16:30:22 s1 kernel: Mem-info:
Feb 7 16:30:22 s1 kernel: DMA per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 2, high 6, batch 1
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 2, batch 1
Feb 7 16:30:22 s1 kernel: Normal per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 32, high 96, batch 16
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 32, batch 16
Feb 7 16:30:22 s1 kernel: HighMem per-cpu:
Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 12, high 36, batch 6
Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 12, batch 6
Feb 7 16:30:22 s1 kernel:
Feb 7 16:30:22 s1 kernel: Free pages: 1280kB (120kB HighMem)
Feb 7 16:30:23 s1 kernel: Active:132041 inactive:116634 dirty:0
writeback:0 unstable:0 free:320 slab:2226 mapped:246816
pagetables:1456
Feb 7 16:30:23 s1 kernel: DMA free:16kB min:16kB low:32kB high:48kB
active:6616kB inactive:6068kB present:16384kB
Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:23 s1 kernel: Normal free:1144kB min:936kB low:1872kB
high:2808kB active:445348kB inactive:426140kB present:901120kB
Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:24 s1 kernel: HighMem free:120kB min:128kB low:256kB
high:384kB active:76200kB inactive:34328kB present:114624kB
Feb 7 16:30:24 s1 kernel: protections[]: 0 0 0
Feb 7 16:30:24 s1 kernel: DMA: 0*4kB 0*8kB 1*16kB 0*32kB 0*64kB
0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 16kB
Feb 7 16:30:24 s1 kernel: Normal: 32*4kB 9*8kB 3*16kB 2*32kB 1*64kB
0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 1144kB
Feb 7 16:30:24 s1 kernel: HighMem: 0*4kB 1*8kB 1*16kB 1*32kB 1*64kB
0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 120kB
Feb 7 16:30:25 s1 kernel: Swap cache: add 422343, delete 421792, find
12780/16340, race 0+0
Feb 7 16:30:25 s1 kernel: Free swap: 0kB
Feb 7 16:30:25 s1 kernel: 258032 pages of RAM
Feb 7 16:30:25 s1 kernel: 28656 pages of HIGHMEM
Feb 7 16:30:25 s1 kernel: 3138 reserved pages
Feb 7 16:30:26 s1 kernel: 14914 pages shared
Feb 7 16:30:26 s1 kernel: 551 pages swap cached
Feb 7 16:30:26 s1 kernel: Out of Memory: Killed process 30679 (psql).
Any advice on how to avoid it?
Regards, Clodoaldo Pinto
> Feb 7 16:30:25 s1 kernel: Free swap: 0kB > Feb 7 16:30:25 s1 kernel: 258032 pages of RAM > Feb 7 16:30:25 s1 kernel: 28656 pages of HIGHMEM > Feb 7 16:30:25 s1 kernel: 3138 reserved pages > Feb 7 16:30:26 s1 kernel: 14914 pages shared > Feb 7 16:30:26 s1 kernel: 551 pages swap cached > Feb 7 16:30:26 s1 kernel: Out of Memory: Killed process 30679 (psql). > > Any advice on how to avoid it? Use a cursor. > Regards, Clodoaldo Pinto > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
Attachment
On Mon, 07 Feb 2005 09:32:47 -0800, Joshua D. Drake
<jd@commandprompt.com> wrote:
>
> >
> > Any advice on how to avoid it?
>
> Use a cursor.
>
Same thing using a cursor:
declare
rdata record;
begin
truncate table usuarios2;
for rdata in
select distinct on (data) data
from usuarios
loop
insert into usuarios2
(
data,
usuario,
pontos,
wus
)
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
where data = rdata.data
group by data, usuario
;
end loop;
return;
end;
Clodoaldo Pinto wrote: > On Mon, 07 Feb 2005 09:32:47 -0800, Joshua D. Drake > <jd@commandprompt.com> wrote: > >>>Any advice on how to avoid it? >> >>Use a cursor. >> > > Same thing using a cursor: Well your first email didn't explain that you were doing the below :) The cursor will help you with large data pulls from a select. However you aren't doing a simple select. You are doing an insert into with a select as your data source. > > declare > rdata record; > begin > truncate table usuarios2; > for rdata in > select distinct on (data) data > from usuarios > loop > insert into usuarios2 > ( > data, > usuario, > pontos, > wus > ) > select > data, > usuario, > sum(pontos), > sum(wus) > from usuarios > where data = rdata.data > group by data, usuario > ; > end loop; > return; > end; Is this the entire function? Sincerely, Joshua D. Drake -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
Attachment
On Mon, 07 Feb 2005 13:51:46 -0800, Joshua D. Drake
<jd@commandprompt.com> wrote:
> Well your first email didn't explain that you were doing the below :)
In the first email I was not doing the insert. I was executing a psql script:
$ psql -e -f groupdup.psql ks2
This was the groupdup.psql script:
\pset format unaligned
\pset fieldsep '\t'
\pset tuples_only
\o '/KakaoStats/bak/groupdup.txt'
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
group by data, usuario
;
\o
\pset tuples_only
\pset format aligned
I was planning to insert the file with copy from.
> Is this the entire function?
Yes, it is the entire function.
I did: # /sbin/sysctl -w vm.overcommit_memory=2 following http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 And got the same error: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function "group_dup" line 9 at SQL statement The difference now is that the process was killed before overcommiting. Regards, Clodoaldo Pinto
On Tue, 8 Feb 2005 09:06:38 -0200, Clodoaldo Pinto <clodoaldo.pinto@gmail.com> wrote: > I did: > # /sbin/sysctl -w vm.overcommit_memory=2 > following > http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 > > And got the same error: > > ERROR: out of memory > DETAIL: Failed on request of size 44. > CONTEXT: PL/pgSQL function "group_dup" line 9 at SQL statement > > The difference now is that the process was killed before overcommiting. > > Regards, Clodoaldo Pinto > This is the log file content: TopMemoryContext: 32768 total in 3 blocks; 3720 free (1 chunks); 29048 used TopTransactionContext: 8192 total in 1 blocks; 2432 free (0 chunks); 5760 used SPI Exec: 8192 total in 1 blocks; 8064 free (0 chunks); 128 used ExecutorState: 8192 total in 1 blocks; 5352 free (1 chunks); 2840 used ExecutorState: 35643416 total in 14 blocks; 3999744 free (16 chunks); 31643672 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 AggContext: 8192 total in 1 blocks; 8128 free (1 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Proc: 8192 total in 1 blocks; 7736 free (0 chunks); 456 used SPI TupTable: 8192 total in 1 blocks; 7024 free (0 chunks); 1168 used SPI TupTable: 8192 total in 1 blocks; 3832 free (0 chunks); 4360 used DeferredTriggerXact: 1796202496 total in 224 blocks; 3752 free (10 chunks); 1796198744 used SPI Plan: 7168 total in 3 blocks; 32 free (0 chunks); 7136 used SPI Plan: 3072 total in 2 blocks; 1280 free (0 chunks); 1792 used SPI Plan: 1024 total in 1 blocks; 672 free (0 chunks); 352 used MessageContext: 8192 total in 1 blocks; 6696 free (1 chunks); 1496 used PortalMemory: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used PortalHeapMemory: 3072 total in 2 blocks; 1272 free (0 chunks); 1800 used ExecutorState: 8192 total in 1 blocks; 6440 free (1 chunks); 1752 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalHeapMemory: 1024 total in 1 blocks; 912 free (0 chunks); 112 used ExecutorState: 8192 total in 1 blocks; 7064 free (1 chunks); 1128 used ExprContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used CacheMemoryContext: 516096 total in 6 blocks; 130408 free (19 chunks); 385688 used ndx_usuarios_data: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_description_o_c_o_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_depender_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_reference_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_database_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_database_datname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_conversion_default_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_opclass_am_name_nsp_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used MdSmgr: 8192 total in 1 blocks; 6072 free (0 chunks); 2120 used DynaHash: 8192 total in 1 blocks; 6760 free (0 chunks); 1432 used DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used DynaHashTable: 24576 total in 2 blocks; 14280 free (6 chunks); 10296 used DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used DynaHashTable: 8192 total in 1 blocks; 1984 free (0 chunks); 6208 used DynaHashTable: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function "group_dup" line 9 at SQL statement
After much work i was able to do it:
The psql script was changed to:
\o '/KakaoStats/bak/groupdup1.txt'
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
where data <= 2056
group by data, usuario
;
\o
\o '/KakaoStats/bak/groupdup2.txt'
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
where data > 2056 and data <= 2400
group by data, usuario
;
\o
...snip...
\o '/KakaoStats/bak/groupdup8.txt'
select
data,
usuario,
sum(pontos),
sum(wus)
from usuarios
where data > 2600
group by data, usuario
;
\o
Then I had 8 files which i concateneted and then i tried in pgadmin3:
truncate table usuarios2;
vacuum usuarios2;
copy usuarios2 (
data,
usuario,
pontos,
wus
)
from '/KakaoStats/bak/groupdup.txt'
;
Again out of memory error.
Tried to break it the same way i did with the select:
truncate table usuarios2;
vacuum usuarios2;
copy usuarios2 (
data,
usuario,
pontos,
wus
)
from '/KakaoStats/bak/groupdup1.txt'
;
...snip...
copy usuarios2 (
data,
usuario,
pontos,
wus
)
from '/KakaoStats/bak/groupdup8.txt'
;
And again out of memory error. ???
Then I added begin and commit to each of the copy commands and it worked !!!
truncate table usuarios2;
vacuum usuarios2;
begin;
copy usuarios2 (
data,
usuario,
pontos,
wus
)
from '/KakaoStats/bak/groupdup1.txt'
;
commit;
...snip...
begin;
copy usuarios2 (
data,
usuario,
pontos,
wus
)
from '/KakaoStats/bak/groupdup8.txt'
;
commit;
This was with pgadmin3. Does pgadmin3 turns the whole script in one
only transaction?
My hardware: Asus a7v8x-mx motherboard, 80GB Maxtor HD, XP2600/333,
2x512MB memory at the most conservative settings.
Am Montag, den 07.02.2005, 20:05 -0200 schrieb Clodoaldo Pinto:
> On Mon, 07 Feb 2005 13:51:46 -0800, Joshua D. Drake
> <jd@commandprompt.com> wrote:
>
> > Well your first email didn't explain that you were doing the below :)
>
> In the first email I was not doing the insert. I was executing a psql script:
>
> $ psql -e -f groupdup.psql ks2
>
> This was the groupdup.psql script:
>
> \pset format unaligned
> \pset fieldsep '\t'
> \pset tuples_only
> \o '/KakaoStats/bak/groupdup.txt'
> select
> data,
> usuario,
> sum(pontos),
> sum(wus)
> from usuarios
> group by data, usuario
> ;
> \o
> \pset tuples_only
> \pset format aligned
>
> I was planning to insert the file with copy from.
Will this go into the same database?
If so, you should probably use:
CREATE TABLE targettable AS
select data,
usuario,
sum(pontos) as sum_pontos,
sum(wus) as sum_wus
from usuarios
group by data, usuario;
If you are going to insert this into another database,
consider doing the step above and then use
pg_dump -t targettable to get a nice dump you can easily
restore. You can also use copy to dump your own.
HTH
Tino
On Wed, 09 Feb 2005 13:18:44 +0100, Tino Wildenhain <tino@wildenhain.de> wrote: > > Will this go into the same database? Yes, this *went* into the same database. > If so, you should probably use: > > CREATE TABLE targettable AS > select data, > usuario, > sum(pontos) as sum_pontos, > sum(wus) as sum_wus > from usuarios > group by data, usuario; > Didn't try the create route. I have already performed the grouping. As the table is big, each step takes very long and i don't have more time to investigate further. Regards, Clodoaldo Pinto > > HTH > Tino > >
Am Mittwoch, den 09.02.2005, 10:39 -0200 schrieb Clodoaldo Pinto: > On Wed, 09 Feb 2005 13:18:44 +0100, Tino Wildenhain <tino@wildenhain.de> wrote: > > > > Will this go into the same database? > > Yes, this *went* into the same database. > > > If so, you should probably use: > > > > CREATE TABLE targettable AS > > select data, > > usuario, > > sum(pontos) as sum_pontos, > > sum(wus) as sum_wus > > from usuarios > > group by data, usuario; > > > > Didn't try the create route. I have already performed the grouping. As > the table is big, each step takes very long and i don't have more time > to investigate further. Well the create table ... select is the most obvious for this kind of task. Maybe it helps next time. There are also some nice howtos on "kind of" materialized views - which looks like you want to do here. Regards Tino