From: jaime soler <jaime.soler@gmail.com>
To: Gustav Karlsson <gustav.karlsson@bekk.no>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Frode Nerbråten <frode.nerbraten@bekk.no>
Subject: Re: Primary key index suddenly became very slow
Date: Tue, 16 Feb 2016 13:52:49 +0100
Message-ID: <1455627169.7311.61.camel@gmail.com> (raw)
In-Reply-To: <EC62E940-B42B-4AA3-BC39-8A09D2ECBE9C@bekk.no>
References: <1DF45FCC-BB65-44B7-B10E-5A8EED3C6C84@bekk.no>
<EC62E940-B42B-4AA3-BC39-8A09D2ECBE9C@bekk.no>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
El lun, 08-02-2016 a las 10:04 +0000, Gustav Karlsson escribió:
> Additional information:
>
> The problematic row has likely received many hot updates (100k+).
> Could this be a likely explanation for the high execution time?
>
>
Could you check if autovacuum is doing its job with this query:
select * from pg_stat_user_tables where relname='konto' , is it last_autovaccum and last_autoanalyze recent ?
if you don't reduce n_dead_tup in a short time after the bulk process of hot update, it will be a explanation, and also a "idle in transaction" connection could cause it.
This link: https://brandur.org/postgres-queues could help you.
>
>
> Regards,
>
> Gustav
>
>
>
>
>
>
>
>
>
> >
> > On Feb 8, 2016, at 10:45 AM, Gustav Karlsson <gustav.karlsson@bekk.no> wrote:
> >
> >
> >
> >
> > Hi,
> >
> >
> >
> > Question:
> >
> >
> >
> > What may cause a primary key index to suddenly become very slow? Index scan for single row taking 2-3 seconds. A manual vacuum resolved the problem.
> >
> >
> >
> >
> >
> > Background:
> >
> >
> >
> > We have a simple table ‘KONTO’ with about 600k rows.
> >
> >
> >
> >
> >
> >
> > Column | Type | Modifiers
> >
> > ------------------------------+-----------------------------+---------------
> >
> > id | bigint | not null
> >
> >
> > ...
> >
> >
> >
> >
> > Indexes:
> >
> > "konto_pk" PRIMARY KEY, btree (id)
> >
> >
> > ...
> >
> >
> >
> >
> >
> > Over the weekend we experienced that lookups using the primary key index (‘konto_pk’) became very slow, in the region 2-3s for fetching a single record:
> >
> >
> >
> >
> > QUERY PLAN
> >
> > Index Scan using konto_pk on konto (cost=0.42..6.44 rows=1 width=164) (actual time=0.052..2094.549 rows=1 loops=1)
> >
> > Index Cond: (id = 2121172829)
> >
> > Planning time: 0.376 ms
> >
> > Execution time: 2094.585 ms
> >
> >
> >
> >
> >
> >
> > After a manual Vacuum the execution time is OK:
> >
> >
> >
> >
> > QUERY PLAN
> >
> > Index Scan using konto_pk on konto (cost=0.42..6.44 rows=1 width=164) (actual time=0.037..2.876 rows=1 loops=1)
> >
> > Index Cond: (id = 2121172829)
> >
> > Planning time: 0.793 ms
> >
> > Execution time: 2.971 ms
> >
> >
> >
> >
> >
> >
> > So things are working OK again, but we would like to know what may cause such a degradation of the index scan, to avoid this happening again? (We are using Postgresql version 9.4.4)
> >
> >
> >
> >
> >
> >
> >
> > Regards,
> >
> > Gustav
> >
> >
> >
> >
>
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jaime.soler@gmail.com, gustav.karlsson@bekk.no, frode.nerbraten@bekk.no
Subject: Re: Primary key index suddenly became very slow
In-Reply-To: <1455627169.7311.61.camel@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox