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