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
