On Fri, Jun 26, 2009 at 3:30 PM, Brian Troutwine<goofyheadedpunk@xxxxxxxxx> wrote: > Hello, all. > > I'm finding that write performance of a certain stored procedure is > abysmal. I need to be able to sustain approximately 20 calls to this > procedure per second, but am finding that, on the average, each call > takes 2 seconds in itself, in addition to pegging a single processor > at 100% for the duration of the call. Additionally, while the stored > procedure calls are being made a single worker does a full-table scan > once every half-hours. > > Being a software developer more than a DBA I hope those on this list > will be kind enough to help me troubleshoot and correct this issue. I > do not know what information would be exactly pertinent, but I have > included table definitions, configurations and the function in > question below. I am using PostgreSQL 8.3 on a Linux Intel Core Duo > system with 2GB of RAM and am running Postgres on XFS. Here are the > relevant settings of my postgresql.conf: > > max_connections = 25 > shared_buffers = 512MB > max_fsm_pages = 153600 > fsync = off > synchronous_commit = off > wal_writer_delay = 10000ms > commit_delay = 100000 > commit_siblings = 100 > checkpoint_segments = 64 > checkpoint_completion_target = 0.9 > effective_cache_size = 1024MB > track_activities = on > track_counts = on > update_process_title = on > autovacuum = on > log_autovacuum_min_duration = 1000 > autovacuum_vacuum_threshold = 50 > autovacuum_analyze_threshold = 50 > > Here is the relevant table definition: > > DROP TABLE IF EXISTS amazon_items CASCADE; > CREATE TABLE amazon_items ( > asin char(10) PRIMARY KEY, > locale varchar(10) NOT NULL DEFAULT 'US', > currency_code char(3) DEFAULT 'USD', > isbn char(13), > sales_rank integer, > offers text, > offer_pages integer DEFAULT 10, > offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > UNIQUE (asin, locale) > ); > > The stored procedure in question, plus supporting procedures: > > CREATE OR REPLACE FUNCTION item_data_insert( > iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, > iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, > iweight INTEGER, > ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, > ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) > RETURNS VOID AS > $$ > DECLARE > y integer[]; > BEGIN > y[1] := iwidth; > y[2] := ilength; > y[3] := iheight; > y[4] := iweight; > BEGIN > INSERT INTO item_details > (isbn, title, author, binding, list_price, dimensions) > VALUES > (iisbn, ititle, iauthor, ibinding, ilist_price, y); > EXCEPTION WHEN unique_violation THEN > UPDATE item_details SET > title = ititle, > author = iauthor, > binding = ibinding, > list_price = ilist_price, > dimensions = y > WHERE isbn = iisbn; > END; > BEGIN > INSERT INTO amazon_items > (asin, sales_rank, offers, offer_pages, isbn) > VALUES > (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); > EXCEPTION WHEN unique_violation THEN > IF isales_rank IS NOT NULL THEN > UPDATE amazon_items SET > sales_rank = isales_rank > WHERE asin = iasin; > END IF; > IF ioffers IS NOT NULL THEN > UPDATE amazon_items SET > offers = crunch(ioffers), > offers_last_updated = CURRENT_TIMESTAMP, > offer_pages = ioffer_pages > WHERE asin = iasin; > END IF; > END; > END; > $$ > LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION crunch(text) > RETURNS text AS > $$ > BEGIN > RETURN encode(text2bytea($1), 'base64'); > END; > $$ > LANGUAGE 'plpgsql' IMMUTABLE STRICT; > > CREATE OR REPLACE FUNCTION text2bytea(text) > RETURNS bytea AS > $$ > BEGIN > RETURN $1; > END; > $$ > LANGUAGE 'plpgsql' IMMUTABLE STRICT; some general tips: *) use indexes to optimize where and join conditions. for example, update yadda set yadda where foo = bar, make sure that there is an index on foo. As alan noted this is almost definitely your problem. *) prefer '_' to 'i' to prefix arguments (more readable and less chance for error). *) use varchar, not char (always). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
