Re: out of memory - no sort
| От | Don |
|---|---|
| Тема | Re: out of memory - no sort |
| Дата | |
| Msg-id | 4E5E5892.50900@noaa.gov обсуждение исходный текст |
| Ответ на | Re: out of memory - no sort (Pavel Stehule <pavel.stehule@gmail.com>) |
| Ответы |
Re: out of memory - no sort
(Scott Ribe <scott_ribe@elevated-dev.com>)
Re: out of memory - no sort (Pavel Stehule <pavel.stehule@gmail.com>) |
| Список | pgsql-general |
Pavel... Thanks for the reply... This still did not solve the issue. It seems odd that a simple select command in psql accessing 32MB of records should cause a problem. I have tables much larger than this and may want to access them the same way. I have 24 GB RAM on the sever and 32GB RAM on the client machine. Both machines are 64bit. Thanks Don On 8/30/2011 10:25 AM, Pavel Stehule wrote: > Hello > > if table is large, then client can raise this exception too > > try to set FETCH_COUNT to 1000 > > http://www.postgresql.org/docs/8.4/interactive/app-psql.html > > Regards > > Pavel Stehule > > 2011/8/30 Don<Donald.Laurine@noaa.gov>: >> I am trying a simple access of a table and get an out of memory error. How >> do I avoid this issue. It seems I have some configuration set wrong. >> >> Our system has 24GB of memory and is dedicated to the postgres database. >> >> Back ground information >> >> aquarec=> explain analyze verbose select * from ens_memb; >> QUERY >> PLAN >> -------------------------------------------------------------------------------------------------------------------------- >> Seq Scan on ens_memb (cost=0.00..719893.12 rows=32216212 width=62) (actual >> time=4.954..37513.377 rows=32216154 loops=1) >> Output: id, shefpc, bwmon, ewmon, pb, tb, cdate, vdate, source, tyr, val >> Total runtime: 39588.386 ms >> >> >> #------------------------------------------------------------------------------ >> # RESOURCE USAGE (except WAL) >> #------------------------------------------------------------------------------ >> >> # - Memory - >> >> shared_buffers = 6144MB # min 128kB >> # (change requires restart) >> #temp_buffers = 8MB # min 800kB >> max_prepared_transactions = 5 # zero disables the feature >> # (change requires restart) >> # Note: Increasing max_prepared_transactions costs ~600 bytes of shared >> memory >> # per transaction slot, plus lock space (see max_locks_per_transaction). >> # It is not advisable to set max_prepared_transactions nonzero unless you >> # actively intend to use prepared transactions. >> work_mem = 48MB # min 64kB >> maintenance_work_mem = 256MB # min 1MB >> #max_stack_depth = 2MB # min 100kB >> >> # - Kernel Resource Usage - >> >> #max_files_per_process = 1000 # min 25 >> # (change requires restart) >> #shared_preload_libraries = '' # (change requires restart) >> >> # - Cost-Based Vacuum Delay - >> >> #vacuum_cost_delay = 0ms # 0-100 milliseconds >> #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 >> >> # - Background Writer - >> >> #bgwriter_delay = 200ms # 10-10000ms between rounds >> #bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round >> #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers >> scanned/round >> >> # - Asynchronous Behavior - >> >> #effective_io_concurrency = 1 # 1-1000. 0 disables prefetching >> >> >> #------------------------------------------------------------------------------ >> # WRITE AHEAD LOG >> #------------------------------------------------------------------------------ >> >> # - Settings - >> >> #fsync = on # turns forced synchronization on or >> off >> #synchronous_commit = on # immediate fsync at commit >> #wal_sync_method = fsync # the default is the first option >> # supported by the operating system: >> # open_datasync >> # fdatasync >> # fsync >> # fsync_writethrough >> # open_sync >> #full_page_writes = on # recover from partial page writes >> wal_buffers = 8MB # min 32kB >> # (change requires restart) >> #wal_writer_delay = 200ms # 1-10000 milliseconds >> >> #commit_delay = 0 # range 0-100000, in microseconds >> #commit_siblings = 5 # range 1-1000 >> >> # - Checkpoints - >> >> checkpoint_segments = 32 # in logfile segments, min 1, 16MB >> each >> #checkpoint_timeout = 5min # range 30s-1h >> #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - >> 1.0 >> #checkpoint_warning = 30s # 0 disables >> >> # - Archiving - >> >> #archive_mode = off # allows archiving to be done >> # (change requires restart) >> #archive_command = '' # command to use to archive a logfile >> segment >> #archive_timeout = 0 # force a logfile segment switch after this >> # number of seconds; 0 disables >> >> #------------------------------------------------------------------------------ >> # QUERY TUNING >> #------------------------------------------------------------------------------ >> >> # - Planner Method Configuration - >> >> #enable_bitmapscan = on >> #enable_hashagg = on >> #enable_hashjoin = on >> #enable_indexscan = on >> #enable_mergejoin = on >> #enable_nestloop = on >> #enable_seqscan = on >> #enable_sort = on >> #enable_tidscan = on >> >> # - Planner Cost Constants - >> >> #seq_page_cost = 1.0 # measured on an arbitrary scale >> random_page_cost = 3.0 # same scale as above >> #cpu_tuple_cost = 0.01 # same scale as above >> #cpu_index_tuple_cost = 0.005 # same scale as above >> #cpu_operator_cost = 0.0025 # same scale as above >> effective_cache_size = 12288MB >> >> # - Genetic Query Optimizer - >> >> #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 >> >> # - Other Planner Options - >> >> default_statistics_target = 100 # range 1-10000 >> #constraint_exclusion = partition # on, off, or partition >> #cursor_tuple_fraction = 0.1 # range 0.0-1.0 >> #from_collapse_limit = 8 >> #join_collapse_limit = 8 # 1 disables collapsing of explicit >> # JOIN clauses >>
В списке pgsql-general по дате отправления: