Search Postgresql Archives

out of memory problem in CVS

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



hi,
we are using postgresql to analyze our web log, we got a 6M table,
and while doing the query:

SELECT url,sum(ct) as ctperkw from ctrraw group by url order by ctperkw
desc limit 1000;

the table structure is:

CREATE TABLE ctrRAW
(
cdate date,
ip inet,
kw varchar(128),
prd varchar(6),
pos int,
url varchar(512),
ct int
);

create INDEX ctr_ip_index on ctrraw(ip);

our boxes ia a freebsd 4.1 with 2G emmory, but our postgresql user got
a 512M data sigment ulimit.

and the query run out of memory, the log file attached.

the postgresql was check out from CVS around June 25.
we are going to test in newly checkout CVS tip, but want to report here
first.

regards laser
TopMemoryContext: 24576 total in 3 blocks; 7360 free (5 chunks); 17216 used
Operator class cache: 8192 total in 1 blocks; 4916 free (0 chunks); 3276 used
Type information cache: 8192 total in 1 blocks; 1844 free (0 chunks); 6348 used
TopTransactionContext: 8192 total in 1 blocks; 7840 free (0 chunks); 352 used
MessageContext: 24576 total in 2 blocks; 14812 free (4 chunks); 9764 used
smgr relation table: 8192 total in 1 blocks; 2844 free (0 chunks); 5348 used
Portal hash: 8192 total in 1 blocks; 1844 free (0 chunks); 6348 used
PortalMemory: 8192 total in 1 blocks; 8036 free (0 chunks); 156 used
PortalHeapMemory: 1024 total in 1 blocks; 868 free (0 chunks); 156 used
ExecutorState: 24576 total in 2 blocks; 14740 free (14 chunks); 9836 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 467656704 total in 66 blocks; 3708 free (59 chunks); 467652996 used
TupleHashTable: 67133468 total in 18 blocks; 6616496 free (40 chunks); 60516972 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 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
Relcache by OID: 8192 total in 1 blocks; 3868 free (0 chunks); 4324 used
CacheMemoryContext: 516096 total in 6 blocks; 219572 free (0 chunks); 296524 used
ctr_url_idx: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
ctr_ip_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_index_indrelid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_type_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_shadow_usename_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_proc_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
pg_operator_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_opclass_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_namespace_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_language_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_language_name_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_group_sysid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_group_name_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_conversion_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_conversion_default_index: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_class_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
pg_cast_source_target_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
MdSmgr: 8192 total in 1 blocks; 7112 free (0 chunks); 1080 used
LockTable (locallock hash): 8192 total in 1 blocks; 3892 free (0 chunks); 4300 used
Timezones: 323228 total in 2 blocks; 5952 free (0 chunks); 317276 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
ERROR:  out of memory
DETAIL:  Failed on request of size 144.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux