Thread: 9.5alpha1 vs 9.4
Hi, today I have update my test system to 9.5alpha1. Most of the operations are ok, except delete. I get ~1000 times slower! chimera=# SELECT (total_time / 1000 )::numeric(10,2) as total_secs, (total_time/calls)::numeric(10,2) as average_time_ms, calls, query FROM pg_stat_statements where userid = 16384 ORDER BY 1 DESC LIMIT 10; total_secs | average_time_ms | calls | query ------------+-----------------+-------+------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------- 255.88 | 566.11 | 452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND inlink = ? 0.13 | 0.13 | 1006 | insert into t_dirs (iparent, iname, ipnfsid) (select $1 as iparent, $2 as iname,$3 as ipnfsid where not exists (select ? from t_dirs where iparent=$4 and iname=$5)) 0.11 | 0.02 | 6265 | SELECT isize,inlink,itype,imode,iuid,igid,iatime,ictime,imtime,icrtime,igenerationFROM t_inodes WHERE ipnfsid=$1 0.03 | 0.03 | 1002 | INSERT INTO t_inodes VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) 0.02 | 0.02 | 1002 | UPDATE t_inodes SET inlink=inlink +$1,imtime=$2,ictime=$3,igeneration=igeneration+?WHERE ipnfsid=$4 0.02 | 0.03 | 905 | UPDATE t_inodes SET inlink=inlink -$1,imtime=$2,ictime=$3,igeneration=igeneration+?WHERE ipnfsid=$4 0.02 | 0.01 | 2000 | SELECT ilocation,ipriority,ictime,iatime FROM t_locationinfo WHERE itype=$1 ANDipnfsid=$2 AND istate=? ORDER BY ipriority DESC 0.01 | 0.01 | 906 | SELECT ipnfsid FROM t_dirs WHERE iname=$1 AND iparent=$2 0.01 | 0.01 | 453 | DELETE FROM t_dirs WHERE iname=$1 AND iparent=$2 chimera=# \d t_inodes Table "public.t_inodes" Column | Type | Modifiers -------------+--------------------------+------------------------ ipnfsid | character varying(36) | not null itype | integer | not null imode | integer | not null inlink | integer | not null iuid | integer | not null igid | integer | not null isize | bigint | not null iio | integer | not null ictime | timestamp with time zone | not null iatime | timestamp with time zone | not null imtime | timestamp with time zone | not null icrtime | timestamp with time zone | not null default now() igeneration | bigint | not null default 0 Indexes: "t_inodes_pkey" PRIMARY KEY, btree (ipnfsid) Referenced by: TABLE "t_access_latency" CONSTRAINT "t_access_latency_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid)ON DELETE CASCADE TABLE "t_acl" CONSTRAINT "t_acl_fkey" FOREIGN KEY (rs_id) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_dirs" CONSTRAINT "t_dirs_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) TABLE "t_inodes_checksum" CONSTRAINT "t_inodes_checksum_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid)ON DELETE CASCADE TABLE "t_inodes_data" CONSTRAINT "t_inodes_data_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETECASCADE TABLE "t_level_1" CONSTRAINT "t_level_1_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_2" CONSTRAINT "t_level_2_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_3" CONSTRAINT "t_level_3_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_4" CONSTRAINT "t_level_4_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_5" CONSTRAINT "t_level_5_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_6" CONSTRAINT "t_level_6_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_level_7" CONSTRAINT "t_level_7_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE TABLE "t_locationinfo" CONSTRAINT "t_locationinfo_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ONDELETE CASCADE TABLE "t_retention_policy" CONSTRAINT "t_retention_policy_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid)ON DELETE CASCADE TABLE "t_storageinfo" CONSTRAINT "t_storageinfo_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETECASCADE TABLE "t_tags" CONSTRAINT "t_tags_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) Triggers: tgr_locationinfo_trash BEFORE DELETE ON t_inodes FOR EACH ROW EXECUTE PROCEDURE f_locationinfo2trash() Any ideas? Tigran.
And this is with 9.4 in the same hardware ( restored from backup) 0.35 | 0.35 | 1002 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND inlink = ? 0.16 | 0.16 | 1006 | insert into t_dirs (iparent, iname, ipnfsid) (select $1 as iparent, $2 as iname,$3 as ipnfsid where not exists (select ? from t_dirs where iparent=$4 and iname=$5)) 0.15 | 0.02 | 8026 | SELECT isize,inlink,itype,imode,iuid,igid,iatime,ictime,imtime,icrtime,igenerationFROM t_inodes WHERE ipnfsid=$1 0.06 | 0.06 | 1002 | INSERT INTO t_inodes VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) 0.04 | 0.02 | 2004 | UPDATE t_inodes SET inlink=inlink -$1,imtime=$2,ictime=$3,igeneration=igeneration+?WHERE ipnfsid=$4 0.03 | 0.01 | 2000 | SELECT ilocation,ipriority,ictime,iatime FROM t_locationinfo WHERE itype=$1 ANDipnfsid=$2 AND istate=? ORDER BY ipriori ty DESC 0.02 | 0.02 | 1002 | UPDATE t_inodes SET inlink=inlink +$1,imtime=$2,ictime=$3,igeneration=igeneration+?WHERE ipnfsid=$4 0.02 | 0.01 | 2006 | SELECT ipnfsid FROM t_dirs WHERE iname=$1 AND iparent=$2 0.01 | 0.01 | 1006 | DELETE FROM t_dirs WHERE iname=$1 AND iparent=$2 0.00 | 0.00 | 2004 | COMMI Tigran. ----- Original Message ----- > From: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de> > To: "pgsql-performance" <pgsql-performance@postgresql.org> > Sent: Sunday, July 5, 2015 1:10:51 PM > Subject: [PERFORM] 9.5alpha1 vs 9.4 > Hi, > > today I have update my test system to 9.5alpha1. > Most of the operations are ok, except delete. > I get ~1000 times slower! > > > chimera=# SELECT > (total_time / 1000 )::numeric(10,2) as total_secs, > (total_time/calls)::numeric(10,2) as average_time_ms, calls, > query > FROM pg_stat_statements where userid = 16384 > ORDER BY 1 DESC > LIMIT 10; > total_secs | average_time_ms | calls | > query > > ------------+-----------------+-------+------------------------------------------------------------------------------------------------------------------------------------------ > ------------------------------- > 255.88 | 566.11 | 452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND > inlink = ? > 0.13 | 0.13 | 1006 | insert into t_dirs (iparent, iname, ipnfsid) > (select $1 as iparent, $2 as iname, $3 as ipnfsid where not exists (select ? > from t_dirs where iparent=$4 and iname=$5)) > 0.11 | 0.02 | 6265 | SELECT > isize,inlink,itype,imode,iuid,igid,iatime,ictime,imtime,icrtime,igeneration > FROM t_inodes WHERE ipnfsid=$1 > 0.03 | 0.03 | 1002 | INSERT INTO t_inodes > VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) > 0.02 | 0.02 | 1002 | UPDATE t_inodes SET inlink=inlink > +$1,imtime=$2,ictime=$3,igeneration=igeneration+? WHERE ipnfsid=$4 > 0.02 | 0.03 | 905 | UPDATE t_inodes SET inlink=inlink > -$1,imtime=$2,ictime=$3,igeneration=igeneration+? WHERE ipnfsid=$4 > 0.02 | 0.01 | 2000 | SELECT ilocation,ipriority,ictime,iatime FROM > t_locationinfo WHERE itype=$1 AND ipnfsid=$2 AND istate=? ORDER BY ipriority > DESC > 0.01 | 0.01 | 906 | SELECT ipnfsid FROM t_dirs WHERE iname=$1 AND > iparent=$2 > 0.01 | 0.01 | 453 | DELETE FROM t_dirs WHERE iname=$1 AND > iparent=$2 > > > > > chimera=# \d t_inodes > Table "public.t_inodes" > Column | Type | Modifiers > -------------+--------------------------+------------------------ > ipnfsid | character varying(36) | not null > itype | integer | not null > imode | integer | not null > inlink | integer | not null > iuid | integer | not null > igid | integer | not null > isize | bigint | not null > iio | integer | not null > ictime | timestamp with time zone | not null > iatime | timestamp with time zone | not null > imtime | timestamp with time zone | not null > icrtime | timestamp with time zone | not null default now() > igeneration | bigint | not null default 0 > Indexes: > "t_inodes_pkey" PRIMARY KEY, btree (ipnfsid) > Referenced by: > TABLE "t_access_latency" CONSTRAINT "t_access_latency_ipnfsid_fkey" FOREIGN KEY > (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_acl" CONSTRAINT "t_acl_fkey" FOREIGN KEY (rs_id) REFERENCES > t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_dirs" CONSTRAINT "t_dirs_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES > t_inodes(ipnfsid) > TABLE "t_inodes_checksum" CONSTRAINT "t_inodes_checksum_ipnfsid_fkey" FOREIGN > KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_inodes_data" CONSTRAINT "t_inodes_data_ipnfsid_fkey" FOREIGN KEY > (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_1" CONSTRAINT "t_level_1_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_2" CONSTRAINT "t_level_2_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_3" CONSTRAINT "t_level_3_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_4" CONSTRAINT "t_level_4_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_5" CONSTRAINT "t_level_5_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_6" CONSTRAINT "t_level_6_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_level_7" CONSTRAINT "t_level_7_ipnfsid_fkey" FOREIGN KEY (ipnfsid) > REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_locationinfo" CONSTRAINT "t_locationinfo_ipnfsid_fkey" FOREIGN KEY > (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_retention_policy" CONSTRAINT "t_retention_policy_ipnfsid_fkey" FOREIGN > KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_storageinfo" CONSTRAINT "t_storageinfo_ipnfsid_fkey" FOREIGN KEY > (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE > TABLE "t_tags" CONSTRAINT "t_tags_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES > t_inodes(ipnfsid) > Triggers: > tgr_locationinfo_trash BEFORE DELETE ON t_inodes FOR EACH ROW EXECUTE PROCEDURE > f_locationinfo2trash() > > > > Any ideas? > > Tigran. > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
Hi, On 2015-07-05 13:10:51 +0200, Mkrtchyan, Tigran wrote: > today I have update my test system to 9.5alpha1. > Most of the operations are ok, except delete. > I get ~1000 times slower! > 255.88 | 566.11 | 452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND inlink = ? That certainly should not be the case. Could you show the query plan for this statement in both versions? Any chance that there's a parameter type mismatch for $1? Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes: > On 2015-07-05 13:10:51 +0200, Mkrtchyan, Tigran wrote: >> today I have update my test system to 9.5alpha1. >> Most of the operations are ok, except delete. >> I get ~1000 times slower! >> 255.88 | 566.11 | 452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND inlink = ? > That certainly should not be the case. Could you show the query plan for > this statement in both versions? EXPLAIN ANALYZE, please. I'm wondering about a missing index on some foreign-key-involved column. That would show up as excessive time in the relevant trigger ... regards, tom lane
Thanks for the hin. My bad. The backup db and 9.5 had a different type on one of the foreign-key constrains char(36) vs varchar(36). The schema was screwed couple of days ago, byt performance numbers I checked only after migration to 9.5. Sorry for the noise. Tigran. ----- Original Message ----- > From: "Tom Lane" <tgl@sss.pgh.pa.us> > To: "Andres Freund" <andres@anarazel.de> > Cc: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de>, "pgsql-performance" <pgsql-performance@postgresql.org> > Sent: Sunday, July 5, 2015 4:33:25 PM > Subject: Re: [PERFORM] 9.5alpha1 vs 9.4 > Andres Freund <andres@anarazel.de> writes: >> On 2015-07-05 13:10:51 +0200, Mkrtchyan, Tigran wrote: >>> today I have update my test system to 9.5alpha1. >>> Most of the operations are ok, except delete. >>> I get ~1000 times slower! > >>> 255.88 | 566.11 | 452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND >>> inlink = ? > >> That certainly should not be the case. Could you show the query plan for >> this statement in both versions? > > EXPLAIN ANALYZE, please. I'm wondering about a missing index on some > foreign-key-involved column. That would show up as excessive time in > the relevant trigger ... > > regards, tom lane
On 07/05/2015 10:16 AM, Mkrtchyan, Tigran wrote: > Thanks for the hin. My bad. The backup db and 9.5 had a different type on > one of the foreign-key constrains char(36) vs varchar(36). > > The schema was screwed couple of days ago, byt performance numbers I checked only > after migration to 9.5. Thank you for testing! Can you re-run your tests with the fixed schema? How does it look? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Jul 6, 2015 18:45, Josh Berkus <josh@agliodbs.com> wrote: > > On 07/05/2015 10:16 AM, Mkrtchyan, Tigran wrote: > > Thanks for the hin. My bad. The backup db and 9.5 had a different type on > > one of the foreign-key constrains char(36) vs varchar(36). > > > > The schema was screwed couple of days ago, byt performance numbers I checked only > > after migration to 9.5. > > Thank you for testing! > > Can you re-run your tests with the fixed schema? How does it look? With fixed schema performance equal to 9.4. I have updated my code to use ON CONFLICT statement. ~5% better compared withINSERT WHERE NOT EXIST. Really cool! Thanks. Tigran. > > -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.com > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance