Re: Terrible Write Performance of a Stored Procedure

On Fri, Jun 26, 2009 at 4:36 PM, Brian Troutwine
<goofyheadedpunk(at)gmail(dot)com>wrote:

> > Turn commit delay and commit siblings off.
>
> Why?

Sorry about the short and sweet, was driving:

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 second goes by. This is really
designed to alleviate the i/o involved with the commit process, and since
you've turned fsync off anyway (which means when I commit, don't write to
disk, just to memory), you're waiting around for 99 of your best buddies to
come along for 1/2 second for basically... nothing.

I will note btw, that fsync=off is really only recommended when you aren't
concerned about your data in the event of disk / power / general node
failure. With fsync=off, your journal (REDO / xlog / WAL whatever you want
to call it) is not consistent with the latest changes to your database,
risking data loss in the event of failure.

Test it out, let me know how it goes.

--SCott

>
>
> 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
> >
>