hey folks
(please CC, I'm not subscribed).
I am trying to merge duplicates of two fields in a rather large table
(139M rows, about 7GB of data).
creating helper table, that would hold it.
my feeling is, postgresql shouldn't go beyond memory bounds I am
setting it in conf, never had any problems like it before.
Table:
staty=> \d+ stats_busy
Table "public.stats_busy"
Column | Type | Modifiers
| Description
----------------+--------------------------------+--------------------
+-------------
size_to | bigint | not null default 0 |
size_from | bigint | not null default 0 |
mac | integer | not null |
data_timestamp | timestamp(0) without time zone | not null |
data | integer | not null |
Indexes:
"d" btree (data)
"dm" btree (data, mac)
Foreign-key constraints:
"m" FOREIGN KEY (mac) REFERENCES macs(id) MATCH FULL
Has OIDs: no
conf file:
/var/lib/postgresql/8.3/main# grep -v -E "^#|^[[:space:]]+#|^$"
postgresql.conf
max_connections = 100 # (change requires restart)
shared_buffers = 32MB # min 128kB or max_connections*16kB
temp_buffers = 32MB # min 800kB
work_mem = 64MB # min 64kB
maintenance_work_mem = 32MB # min 1MB
max_fsm_pages = 4013760 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_page_hit = 1 # 0-10000 credits
vacuum_cost_page_miss = 10 # 0-10000 credits
vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 1-10000 credits
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
geqo = on
geqo_threshold = 12
geqo_effort = 5 # range 1-10
geqo_pool_size = 0 # selects default based on effort
geqo_generations = 0 # selects default based on effort
geqo_selection_bias = 2.0 # range 1.5-2.0
default_statistics_target = 200 # range 1-1000
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_line_prefix = '[%t %h %d %u %p]: ' # special values:
track_activities = on
track_counts = on
update_process_title = on
autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_naptime = 60min # time between autovacuum runs
autovacuum_vacuum_threshold = 500000 # min number of row updates before
autovacuum_analyze_threshold = 250000 # min number of row updates before
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
datestyle = 'iso, mdy'
lc_messages = 'en_US' # locale for system error message
lc_monetary = 'en_US' # locale for monetary formatting
lc_numeric = 'en_US' # locale for number formatting
lc_time = 'en_US' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
~# ulimit -a; file /usr/lib/postgresql/8.3/bin/postgres; free; grep -i
-E 'work_mem|shared_buffers|max_connections' /etc/postgresql/8.3/main/
postgresql.conf
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 16375
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 16375
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
/usr/lib/postgresql/8.3/bin/postgres: ELF 32-bit LSB executable, Intel
80386, version 1 (SYSV), for GNU/Linux 2.6.8, dynamically linked (uses
shared libs), stripped
total used free shared buffers
cached
Mem: 2071396 223888 1847508 0 6860
158284
-/+ buffers/cache: 58744 2012652
Swap: 1951888 104716 1847172
max_connections = 50
# note: increasing max_connections costs ~400 bytes of shared memory per
# might also need to raise shared_buffers to support more connections.
shared_buffers = 16000 # min 16 or max_connections*2, 8KB each
work_mem = 128140 # min 64, size in KB
maintenance_work_mem = 65535 # min 1024, size in KB
# max_locks_per_transaction * (max_connections +
max_prepared_transactions)
TopMemoryContext: 49416 total in 6 blocks; 6016 free (5 chunks); 43400
used
Type information cache: 8192 total in 1 blocks; 1800 free (0
chunks); 6392 used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
TopTransactionContext: 8192 total in 1 blocks; 5560 free (0
chunks); 2632 used
MessageContext: 24576 total in 2 blocks; 12936 free (3 chunks);
11640 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
smgr relation table: 8192 total in 1 blocks; 2808 free (0 chunks);
5384 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks);
128 used
ExecutorState: 24576 total in 2 blocks; 14520 free (15 chunks);
10056 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 1981800448 total in 250 blocks; 3768 free (117
chunks); 1981796680 used
TupleHashTable: 1158668312 total in 150 blocks; 465512 free
(580 chunks); 1158202800 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
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816
used
CacheMemoryContext: 667472 total in 20 blocks; 217568 free (8
chunks); 449904 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
dm: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
d: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free
(0 chunks); 872 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free
(0 chunks); 936 used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
MdSmgr: 8192 total in 1 blocks; 6472 free (1 chunks); 1720 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280
used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: ERROR: out of
memory
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: DETAIL: Failed
on request of size 20.
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: STATEMENT:
create table dups as select count(*) as c, data, mac from stats_busy
group by data,mac having count(*) >1;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
