Re: Postgresql performance degrading... how to diagnose the root cause

[Julien Cigar]

On 03/29/2013 15:20, Franck Routier wrote:
> Hi,
>

Hello,

> I have a postgresql database (8.4) running in production whose
> performance is degrading.
> There is no single query that underperforms, all queries do.
> Another interesting point is that a generic performance test
> (https://launchpad.net/tpc-b) gives mediocre peformance when run on
> the database, BUT the same test on a newly created database, on the
> same pg cluster, on the same tablespace, does perform good.
>
> So the problem seems to be limited to this database, even on newly
> created tables...
>
> What should I check to find the culprit of this degrading performance ?
>

Difficult to answer with so few details, but I would start by logging
slow queries, and run an explain analyze on them (or use auto_explain).
Check if you're CPU bound or I/O bound (top, iostats, vmstat, systat,
gstat..), check your configuration (shared_buffers,
effective_cache_size, work_mem, checkpoint_segments, cpu_tuple_cost, ...)

> Franck
>


--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.



--
Sent via pgsql-performance mailing list (pgsql-pe...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Guillaume Cottenceau]

Franck Routier <franck.routier 'at' axege.com> writes:

> Hi,

>
> I have a postgresql database (8.4) running in production whose
> performance is degrading.
> There is no single query that underperforms, all queries do.
> Another interesting point is that a generic performance test
> (https://launchpad.net/tpc-b) gives mediocre peformance when run on
> the database, BUT the same test on a newly created database, on the
> same pg cluster, on the same tablespace, does perform good.
>
> So the problem seems to be limited to this database, even on newly
> created tables...
>
> What should I check to find the culprit of this degrading performance ?

I don't know that tcp-b does but it looks like bloat, provided
your comparison with the newly created database is using the same
amount of data in database. You may want to use this loose bloat
estimate:

http://wiki.postgresql.org/wiki/Show_database_bloat

and then use any preferred unbloat mechanism (vacuum full,
cluster, possibly also reindex), and in the long term better
configure some parameters (activate autovacuum if not already the
case, lower autovacuum_vacuum_cost_delay and raise
autovacuum_vacuum_cost_limit, raise max_fsm_* on your 8.4 or
upgrade to 9.x).

--
Guillaume Cottenceau

[Jeff Janes]

On Fri, Mar 29, 2013 at 7:20 AM, Franck Routier <franck....@axege.com> wrote:

Hi,

I have a postgresql database (8.4) running in production whose performance is degrading.

There have been substantial improvements in performance monitoring in newer versions, so using 8.4 limits your options.

 

There is no single query that underperforms, all queries do.
Another interesting point is that a generic performance test (https://launchpad.net/tpc-b) gives mediocre peformance when run on the database, BUT the same test on a newly created database, on the same pg cluster, on the same tablespace, does perform good.

Is the server still running its production workload while you do these test, or are you running it on a clone or during off-peak hours?  If the former, then if you do your test in a clone which has no load other than the benchmark, do you still see the same thing.

Also, have you tried running pgbench, which also has a tpc-b-ish workload?  People on this list will probably be more familiar with that than with the one you offer.  What was the size of the test set (and your RAM) and the number of concurrent connections it tests?

 

Cheers,

Jeff

[Guillaume Cottenceau]

Franck Routier <franck.routier 'at' axege.com> writes:

>> http://wiki.postgresql.org/wiki/Show_database_bloat
> How do I interpret the output of this query ? Is 1.1 bloat level on a
> table alarming, or quite ok ?

I am not very used to this, but I'd start by comparing the top
result in your established DB against the top result in your
fresh DB. What does it say? The wiki page says it is a loose
estimate, however, unusually larger tbloat and/or wastedbytes
might be an indication.

Of course, if you can afford it, a good old VACUUM FULL ANALYZE
VERBOSE would tell you how many pages were reclaimed while
rewriting the table. Otherwise, VACUUM VERBOSE on both the
established DB and a backup/restore on a fresh DB also provide a
helpful comparison of how many pages are used for suspected
tables.

[Cédric Villemain]

> > I don't know that tcp-b does

>

> tpcb.jar is a java implementation of the http://www.tpc.org/tpcb/

> benchmark. It is not particularly representative of my workload, but

> gives a synthetic, db-agnostic, view of the system performance.

> We use it to have quick view to compare differents servers (different

> OS, different RDBMS, etc...).

 

For information, pgbench is a sort of limited TPC-B benchmark.

 

> That said, the test wil create tables, load them with data, and perform

> some transactions on them.

> The point that makes me wonder what happens, is that the test run on my

> main database is slow, while the same test run on a database on its own

> is quick.

 

Do you mean when you run it against already existing data vs its own TPC-B DB?

 

> This is the same postgresql cluster (same postgresql.conf), same

> tablespace (so same disks), same hardware obviously.

>

> Regarding the server activity, it seems quite flat : iostat shows that

> disks are not working much (less than 5%), top shows only one active

> core, and load average is well under 1...

>

> > http://wiki.postgresql.org/wiki/Show_database_bloat

>

> How do I interpret the output of this query ? Is 1.1 bloat level on a

> table alarming, or quite ok ?

 

quite ok. The threshold for maintenance task is around 20%.

I wonder about your system catalogs (pg_type, pg_attribute, ...)

 

You can use low level tool provided by PostgreSQL to help figure what's going wrong.

pg_buffercache, pg_stattuple come first to explore your cached data and the block content.

 

Or some weird database configuration ? (parameters in PostgreSQL can be set per DB, per role, etc...)

--

Cédric Villemain +33 (0)6 20 30 22 52

http://2ndQuadrant.fr/

PostgreSQL: Support 24x7 - Développement, Expertise et Formation

[Jeff Janes]

On Fri, Mar 29, 2013 at 8:31 AM, Franck Routier <franck....@axege.com> wrote:

Hi,

I don't know that tcp-b does

tpcb.jar is a java implementation of the http://www.tpc.org/tpcb/ benchmark. It is not particularly representative of my workload, but gives a synthetic, db-agnostic, view of the system performance.

We use it to have quick view to compare differents servers (different OS, different RDBMS, etc...).

I took a quick look at that implementation, and I can't make heads nor tails of it.  It is just a lit of .java files.  There is no documentation, README, instructions, or example usage.  Am I missing something?  How do I run it, and tell it what scale to use and what database to connect to?

 

That said, the test wil create tables, load them with data, and perform some transactions on them.
The point that makes me wonder what happens, is that the test run on my main database is slow, while the same test run on a database on its own is quick.

This is the same postgresql cluster (same postgresql.conf), same tablespace (so same disks), same hardware obviously.

Regarding the server activity, it seems quite flat : iostat shows that disks are not working much (less than 5%),

Which column of the iostat output is that coming from?

 

top shows only one active core, and load average is well under 1...

Can you show the first few rows of the top output?

Cheers,

Jeff