Обсуждение: Error message, which memory param should I increase?
I have a very large query which needs to be very large, and it gives me this error sometimes: "Error","7175","06/23/03","23:52:52",,"ODBC Error Code = 08S01 (Communication link failure)<P> Error while executing the query; Out of memory while reading tuples. Previously I had done: 1) in /etc/sysctl.conf kernel.shmall = 134217728 kernel.shmmax = 134217728 2) in postgresql.conf: shared_buffers 10000 The machine has 2 processors, and 1gb of real memory, linux kernel 2.2.14-5.0smp Postgres 7.2.4, many gigabytes free disk space. Can anyone tell me what I should do to further try to prevent this out of memory situation? Additional info, here is the memory segment of my postgresql.conf: # # Shared Memory Size # #shared_buffers = 64 # 2*max_connections, min 16 shared_buffers = 10000 # 2*max_connections, min 16 #max_fsm_relations = 100 # min 10, fsm is free space map #max_fsm_pages = 10000 # min 1000, fsm is free space map #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8 # min 4 # # Non-shared Memory Sizes # #sort_mem = 512 # min 32 #vacuum_mem = 8192 # min 1024 # # Write-ahead log (WAL) # #wal_files = 0 # range 0-64 #wal_sync_method = fsync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 #commit_delay = 0 # range 0-100000 #commit_siblings = 5 # range 1-1000 #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 #checkpoint_timeout = 300 # in seconds, range 30-3600 #fsync = true Thanks Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085
It's not the server running out of memory, it's your ODBC client. You have to change your application to use a cursor in order to deal with huge result sets. Jan terry@ashtonwoodshomes.com wrote: > I have a very large query which needs to be very large, and it gives me this > error sometimes: > "Error","7175","06/23/03","23:52:52",,"ODBC Error Code = 08S01 > (Communication link failure)<P> Error while executing the query; Out of > memory while reading tuples. > > > Previously I had done: > 1) in /etc/sysctl.conf > kernel.shmall = 134217728 > kernel.shmmax = 134217728 > > 2) in postgresql.conf: > shared_buffers 10000 > > The machine has 2 processors, and 1gb of real memory, linux kernel > 2.2.14-5.0smp Postgres 7.2.4, many gigabytes free disk space. Can anyone > tell me what I should do to further try to prevent this out of memory > situation? > > Additional info, here is the memory segment of my postgresql.conf: > # > # Shared Memory Size > # > #shared_buffers = 64 # 2*max_connections, min 16 > shared_buffers = 10000 # 2*max_connections, min 16 > #max_fsm_relations = 100 # min 10, fsm is free space map > #max_fsm_pages = 10000 # min 1000, fsm is free space map > #max_locks_per_transaction = 64 # min 10 > #wal_buffers = 8 # min 4 > > # > # Non-shared Memory Sizes > # > #sort_mem = 512 # min 32 > #vacuum_mem = 8192 # min 1024 > > > # > # Write-ahead log (WAL) > # > #wal_files = 0 # range 0-64 > #wal_sync_method = fsync # the default varies across platforms: > # # fsync, fdatasync, open_sync, or open_datasync > #wal_debug = 0 # range 0-16 > #commit_delay = 0 # range 0-100000 > #commit_siblings = 5 # range 1-1000 > #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 > #checkpoint_timeout = 300 # in seconds, range 30-3600 > #fsync = true > > Thanks > > > Terry Fielder > Manager Software Development and Deployment > Great Gulf Homes / Ashton Woods Homes > terry@greatgulfhomes.com > Fax: (416) 441-9085 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
<terry@ashtonwoodshomes.com> writes: > I have a very large query which needs to be very large, and it gives me this > error sometimes: > "Error","7175","06/23/03","23:52:52",,"ODBC Error Code = 08S01 > (Communication link failure)<P> Error while executing the query; Out of > memory while reading tuples. I'd say your problem is on the client side, and that you're wasting your time fooling with server-side configuration. Consider converting the query to use a cursor, so that you can fetch a reasonably small number of rows at a time. regards, tom lane