Thread: Terrible Write Performance of a Stored Procedure

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

On Friday 26 June 2009, Brian Troutwine <goofyheadedpunk@gmail.com> wrote:
>  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)
>  );
>

Indexes are good things. Try them. Particularly on the isbn field.

--
Overshoot = http://www.theoildrum.com/files/evoltuion_timeline.JPG

On Fri, Jun 26, 2009 at 3:30 PM, Brian
Troutwine<goofyheadedpunk@gmail.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

> Indexes are good things. Try them. Particularly on the isbn field.

I'm not sure why amazon_items.isbn should be given an index.
item_details.isbn is used in a WHERE clause and is given an index
accordingly, but not amazon_items.isbn.

Brian



On Fri, Jun 26, 2009 at 12:40 PM, Alan Hodgson<ahodgson@simkin.ca> wrote:
> On Friday 26 June 2009, Brian Troutwine <goofyheadedpunk@gmail.com> wrote:
>>  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)
>>  );
>>
>
> Indexes are good things. Try them. Particularly on the isbn field.
>
> --
> Overshoot = http://www.theoildrum.com/files/evoltuion_timeline.JPG
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

> Turn commit delay and commit siblings off.

Why?

Brian

On Fri, Jun 26, 2009 at 1:06 PM, Scott Mead<scott.mead@enterprisedb.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@gmail.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@postgresql.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.mead@enterprisedb.com
> C: 607 765 1395
> www.enterprisedb.com
>

On Fri, 26 Jun 2009, Scott Mead wrote:

>     Having those settings enabled basically does the following:
>    " Do not complete the I/O for a commit until you have either commit_siblings commits also ready, or you have
waited.55 seconds." 
>
>   Basically, if you make 1 commit, you will sit there waiting until either 99 other commits take place, or ~ 1/2
secondgoes by. 

You're right that it should be removed, but this explanation is wrong.
The behavior as configured is actually "if there are >=100 other
transactions in progress, wait 0.1 second before committing after the
first one gets committed", in hopes that one of the other 100 might also
join along in the disk write.

Since in this case max_connections it set to 100, it's actually impossible
for the commit_delay/commit_siblings behavior to trigger give this
configuration.  That's one reason it should be removed.  The other is that
i general, if you don't exactly what you're doing, you shouldn't be
touching either parameters; they don't do what people expect them to and
it's extremely unlikely you'll encounter any of the rare use cases where
they might help.

I don't think any of the sync or write parameters have anything to do with
this problem though, it seems like a problem with the referential bits
taking too long to execute.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Jun 26, 9:30 pm, goofyheadedp...@gmail.com (Brian Troutwine) wrote:
> Hello, all.
>
>  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;
>

Hi, did the index on isbn field help?

Another note, that is more fine tuning actually, then the real cause
of the slow execution of your procedure. If you are expecting to
update more, then insert, then you probably should not wait for the
exception to be thrown as all the BEGIN EXCEPTION END blocks are more
expensive to execute, then simple calls. Have a look here:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
Also note that if you UPDATE first, and then try to INSERT only when
UPDATE could not find anything to update, you really HAVE to expect
INSERT to fail and then retry updating, as another, parallel
transaction, could be fast enough to INSERT a record after you tried
to update and before your transaction starts to insert.

With best regards,

-- Valentine Gogichashvili