Re: out of memory - no sort

Hello

2011/8/31 Don <Donald(dot)Laurine(at)noaa(dot)gov>:
> 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.
>

so there are two possibilities

a) broken datafiles
b) PostgreSQL's bug

Pavel

> 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(dot)Laurine(at)noaa(dot)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
>>>
>
>