Re: Terrible Write Performance of a Stored Procedure

On Fri, Jun 26, 2009 at 3:30 PM, 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;

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