Re: [PERFORM] 9.5alpha1 vs 9.4
Andres Freund and...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 9.5alpha1 vs 9.4
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,igeneration FROM 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 AND ipnfsid=$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.mkrtch...@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
Re: [PERFORM] 9.5alpha1 vs 9.4
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[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
Re: [PERFORM] pgbouncer issue
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Guys can anyone please explain or point me to a link where i can understand this output for pgbouncer. What does each column of this table mean? pgbouncer=# show mem; (Please do not post to more than one mailing list at a time). It does appear to be undocumented. Your best bet is to ask on the pgbouncer mailing list: http://lists.pgfoundry.org/mailman/listinfo/pgbouncer-general You may want to raise this as an official doc issue as well: https://github.com/pgbouncer/pgbouncer/issues That said, if it's undocumented and nobody else has complained, it's probably not too important as far as day to day pgbouncer use. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201507051040 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlWZQwsACgkQvJuQZxSWSsjLBwCfao3lKsN5IZvwKISTkb9FabBO /6kAoLHOSKplGOM+K0L5JkxL5ZX+bygH =R7lS -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 9.5alpha1 vs 9.4
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 t...@sss.pgh.pa.us To: Andres Freund and...@anarazel.de Cc: Mkrtchyan, Tigran tigran.mkrtch...@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 and...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
