Обсуждение: 9.5alpha1 vs 9.4

Поиск
Список
Период
Сортировка

9.5alpha1 vs 9.4

От
"Mkrtchyan, Tigran"
Дата:
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.

Re: 9.5alpha1 vs 9.4

От
"Mkrtchyan, 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

Re: 9.5alpha1 vs 9.4

От
Andres Freund
Дата:
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

Re: 9.5alpha1 vs 9.4

От
Tom Lane
Дата:
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

Re: 9.5alpha1 vs 9.4

От
"Mkrtchyan, Tigran"
Дата:
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

Re: 9.5alpha1 vs 9.4

От
Josh Berkus
Дата:
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

Re: 9.5alpha1 vs 9.4

От
"Mkrtchyan, Tigran"
Дата:
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

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.