Re: Terrible Write Performance of a Stored Procedure

> Turn commit delay and commit siblings off.

Why?

Brian

On Fri, Jun 26, 2009 at 1:06 PM, Scott Mead<scott(dot)mead(at)enterprisedb(dot)com> wrote:
> -- sorry for the top-post and short response.
>
> Turn commit delay and commit siblings off.
>
> --Scott
>
> On 6/26/09, Brian Troutwine <goofyheadedpunk(at)gmail(dot)com> 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;
>>
>> Thanks,
>> Brian
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> --
> Sent from my mobile device
>
> --
> Scott Mead
> Sr. Systems Engineer
> EnterpriseDB
>
> scott(dot)mead(at)enterprisedb(dot)com
> C: 607 765 1395
> www.enterprisedb.com
>