Thread: Big insert/delete memory problems
Hi all, I have a kinda weird postgres sql question for everyone. When
running the following SQL commands:
delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;
insert into xrefmembergroup (membergroupid, memberid) select 2 as
membergroupid, member.id as memberid from member where ((extract(year
from age(birthdate))) >= '17' ); select 1 as true;
...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
After it finishes these commands, the memory usage does not go back
down, so after awhile, memory usage becomes a problem. I would
estimate that the xrefmembergroup table has about 20,000-30,000 rows in
it, and I'm deleting/inserting about 5000 rows at a time with these
commands, which run VERY fast.
Here's the table definition:
Column | Type |
Modifiers
---------------+--------------------------+------------------------------------------------------------
id | integer | not null default
nextval('"xrefmembergroup_id_seq"'::text)
membergroupid | integer | not null default 0
memberid | integer | not null default 0
timestamp | timestamp with time zone | default
"timestamp"('now'::text)
Indexes: xrefmembergroup_pkey primary key btree (id),
membergroupid_xrefmembergroup_key btree (membergroupid),
memberid_xrefmembergroup_key btree (memberid)
Is this an excessive delete/insert? Am I breaking 'good form' here?
Thanks for any help!
/kurt
Kurt Overberg <kurt@hotdogrecords.com> writes:
> delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;
> insert into xrefmembergroup (membergroupid, memberid) select 2 as
> membergroupid, member.id as memberid from member where ((extract(year
> from age(birthdate))) >= '17' ); select 1 as true;
> ...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
Postgres version?
Sounds like a memory leak, but there's not enough info here to debug it.
Which of these queries exactly is leaking memory? If it's the
INSERT/SELECT (as I suspect), what's the schema of the 'member' table?
regards, tom lane
Darn it, I knew I forgot something. I'm running on redhat 7.3, with
postgresql version 7.3.2, accessing it through tomcat 4.1.18. It
APPEARS that its the insert that bumps the memory. Here's the schema
for the member table:
Column | Type |
Modifiers
------------------------+--------------------------+---------------------------------------------------
id | integer | not null default
nextval('"member_id_seq"'::text)
username | character varying(16) | not null
password | character varying(16) | not null
email | character varying(255) | not null
firstname | character varying(32) | not null
lastname | character varying(32) | not null
address1 | character varying(100) | default ''
address2 | character varying(100) | default ''
address3 | character varying(100) | default ''
city | character varying(50) | default ''
state | character varying(10) | default ''
zipcode | character varying(10) | default ''
telephone | character varying(20) | default ''
fax | character varying(20) | default ''
imname | character varying(50) | default ''
implatform | character varying(20) | default ''
gender | character(1) |
birthdate | date |
notifycampaigns | boolean | default false
notifytasks | boolean | default false
notifypoints | boolean | default false
notifyrewards | boolean | default false
contactemail | boolean | default false
contactim | boolean | default false
contactmail | boolean | default false
contacttelephone | boolean | default false
contactmembers | boolean | default false
receivecampaignresults | boolean | default false
receivenewsletter | boolean | default false
educationlevel | character varying(30) | default ''
studentorworking | character varying(10) | default ''
companyname | character varying(50) | default ''
title | character varying(50) | default ''
studentlevel | character varying(30) |
implatformother | character varying(20) | default ''
signedup | timestamp with time zone | default
"timestamp"('now'::text)
flags | character(20) | default
'00000000000000000000'
last_login | timestamp with time zone | default
"timestamp"('now'::text)
rating | double precision | default 0.5
Thanks Tom. I at my wit's end here.
/kurt
Tom Lane wrote:
> Kurt Overberg <kurt@hotdogrecords.com> writes:
>
>>delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;
>
>
>>insert into xrefmembergroup (membergroupid, memberid) select 2 as
>>membergroupid, member.id as memberid from member where ((extract(year
>>from age(birthdate))) >= '17' ); select 1 as true;
>
>
>>...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
>
>
> Postgres version?
>
> Sounds like a memory leak, but there's not enough info here to debug it.
> Which of these queries exactly is leaking memory? If it's the
> INSERT/SELECT (as I suspect), what's the schema of the 'member' table?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
Kurt Overberg <kurt@hotdogrecords.com> writes:
> Darn it, I knew I forgot something. I'm running on redhat 7.3, with
> postgresql version 7.3.2, accessing it through tomcat 4.1.18. It
> APPEARS that its the insert that bumps the memory. Here's the schema
> for the member table:
I created the two tables, loaded some dummy data into member, and
inserted/selected like mad ... no sign of memory bloat. So there's
something else involved here. Any plpgsql functions being used?
Triggers or foreign keys?
regards, tom lane
Hmmmm....are you running on RedHat 7.3? I'm not using any plpgsql, triggers or foreignkeys. Very simple DB. I'm using the pre-compiled RPM version of 7.3.2 on the production machine. I have a similar environment setup running under Debian (woody), but that's a version that I compiled from the source, and I can't really seem to duplicate this behavior. Things are kinda moving around a bit, because I'm switching from Struts DB pooling to the Postgresql driver JDBC2 pooling mechanism as well (this is a live production server, and I gotta get this thing fixed so I can get some sleep and stop having to babysit the site), so I'm throwing everything I can think of at this. So here's the main question: During a large select/insert like this, the memory use of the pgsql backend goes up, right? Is it supposed to go down afterwards? I just need one fact to hang on to here to help me work this out. I think my plan at this point is to compile 7.3.2 on redhat with a fresh install and see how that behaves. Thanks again, Tom. I appreciate your help. /kurt Tom Lane wrote: > Kurt Overberg <kurt@hotdogrecords.com> writes: > >>Darn it, I knew I forgot something. I'm running on redhat 7.3, with >>postgresql version 7.3.2, accessing it through tomcat 4.1.18. It >>APPEARS that its the insert that bumps the memory. Here's the schema >>for the member table: > > > I created the two tables, loaded some dummy data into member, and > inserted/selected like mad ... no sign of memory bloat. So there's > something else involved here. Any plpgsql functions being used? > Triggers or foreign keys? > > regards, tom lane > >
Kurt Overberg <kurt@hotdogrecords.com> writes:
> main question:
> During a large select/insert like this, the memory use of the pgsql
> backend goes up, right?
No, it's not supposed to go up in the first place. When I run what
I think is the same case, I don't see the backend's resident-set-size
going above a couple meg. You seem to be suffering an internal memory
leak, which is not a very surprising kind of bug --- but I don't
understand why I can't duplicate it.
Let's see ... what locale and encoding are you using? Maybe it depends
on that.
regards, tom lane
Okay- here's exactly what I'm seeing: I'm getting these figures from
top. I see the query come in, and it goes to a postgres that's running
at 19Mb. After the request, that postgres process (backend?) is at
39252 (37658 of that is shared). My Locale is set to 'C', and my
client_encoding is the default (database encoding?). Hope this helps.
Let me know if you want me to try anything else. I'm currently
installing the compiled version, I'll let you know how it goes.
/kurt
(here's my postgresql.conf file, in case you'd want it)
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false
max_connections = 64
#superuser_reserved_connections = 2
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
shared_buffers = 30400 # min max_connections*2 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
sort_mem = 16384 # 32168 # min 64, size in KB
vacuum_mem = 16384 # 32168 # min 1024, size in KB
#
# Write-ahead log (WAL)
#
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#
#fsync = true
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or
open_datasync
#wal_debug = 0 # range 0-16
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
#default_statistics_target = 10 # range 1-1000
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Message display
#
server_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log,
fatal,
# panic
client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#silent_mode = false
log_connections = true
#log_pid = false
#log_statement = false
#log_duration = false
log_timestamp = true
#log_min_error_statement = error # Values in order of increasing severity:
# debug5, debug4, debug3, debug2,
debug1,
# info, notice, warning, error,
panic(off)
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#explain_pretty_print = true
# requires USE_ASSERT_CHECKING
#debug_assertions = true
#
# Syslog
#
syslog = 0 # range 0-2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
#
# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_statement_stats = false
# requires BTREE_BUILD_STATS
#show_btree_build_stats = false
#
# Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false
#
# Lock Tracing
#
#trace_notify = false
# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#client_encoding = sql_ascii # actually, defaults to database encoding
#authentication_timeout = 60 # 1-600, in seconds
#deadlock_timeout = 1000 # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
#db_user_namespace = false
#
# Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
Tom Lane wrote:
> Kurt Overberg <kurt@hotdogrecords.com> writes:
>
>>main question:
>
>
>>During a large select/insert like this, the memory use of the pgsql
>>backend goes up, right?
>
>
> No, it's not supposed to go up in the first place. When I run what
> I think is the same case, I don't see the backend's resident-set-size
> going above a couple meg. You seem to be suffering an internal memory
> leak, which is not a very surprising kind of bug --- but I don't
> understand why I can't duplicate it.
>
> Let's see ... what locale and encoding are you using? Maybe it depends
> on that.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
Tom, After installing the compiled version, things are looking better. The processes are still growing, but not nearly as fast as before I think this problem may have something to do with my connection from Tomcat. I'm using the JDBC2 drivers from postgresql.org, and when I reset my tomcat, memory usage on those backends goes way down (back to normal). So it seems like something in the JDBC driver is keeping those backends large. I'm using the PooledDataSource object for accessing this database. Don't know if that helps or not. I'll try to post to the JDBC list and see what I can find. Thanks again! /kurt Tom Lane wrote: > Kurt Overberg <kurt@hotdogrecords.com> writes: > >>main question: > > >>During a large select/insert like this, the memory use of the pgsql >>backend goes up, right? > > > No, it's not supposed to go up in the first place. When I run what > I think is the same case, I don't see the backend's resident-set-size > going above a couple meg. You seem to be suffering an internal memory > leak, which is not a very surprising kind of bug --- but I don't > understand why I can't duplicate it. > > Let's see ... what locale and encoding are you using? Maybe it depends > on that. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
Just to follow up on this thread, I found the problem(s). JDBC2
connection pooling AND that pre-compiled version on RedHat. After
switching to my compiled version, I was still seeing memory leaking from
the postgresql processes. BUT- it wasn't as bad as it was with the
precompiled version. It was still leaking too much for me to be
comfortable with though, so I stopped doing connection pooling. Its a
bit slower, especially with DB connection intensive operations, but now
postgresql takes up less memory than my apache processes! WOOOO HOOOOO!
So the problem appears to be that Jdbc2 connection pooling makes
postgresql leak, at least on RedHat 7.3. Thanks Tom!
/kurt
Kurt Overberg wrote:
> Hi all, I have a kinda weird postgres sql question for everyone. When
> running the following SQL commands:
>
>
> delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;
>
> insert into xrefmembergroup (membergroupid, memberid) select 2 as
> membergroupid, member.id as memberid from member where ((extract(year
> from age(birthdate))) >= '17' ); select 1 as true;
>
> ...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
> After it finishes these commands, the memory usage does not go back
> down, so after awhile, memory usage becomes a problem. I would
> estimate that the xrefmembergroup table has about 20,000-30,000 rows in
> it, and I'm deleting/inserting about 5000 rows at a time with these
> commands, which run VERY fast.
>
> Here's the table definition:
>
> Column | Type | Modifiers
> ---------------+--------------------------+------------------------------------------------------------
>
> id | integer | not null default
> nextval('"xrefmembergroup_id_seq"'::text)
> membergroupid | integer | not null default 0
> memberid | integer | not null default 0
> timestamp | timestamp with time zone | default
> "timestamp"('now'::text)
> Indexes: xrefmembergroup_pkey primary key btree (id),
> membergroupid_xrefmembergroup_key btree (membergroupid),
> memberid_xrefmembergroup_key btree (memberid)
>
>
> Is this an excessive delete/insert? Am I breaking 'good form' here?
> Thanks for any help!
>
> /kurt
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
Kurt Overberg <kurt@hotdogrecords.com> writes:
> So the problem appears to be that Jdbc2 connection pooling makes
> postgresql leak, at least on RedHat 7.3.
There's something fishy about that conclusion. How could the JDBC code
cause a server-side memory leak?
regards, tom lane