Thread: postgresql performance tuning

postgresql performance tuning

From
Ameet Kini
Date:

Hello,

I have a question on postgres's performance tuning, in particular, the
vacuum and reindex commands. Currently I do a vacuum (without full) on all
of my tables.  However, its noted in the docs (e.g.
http://developer.postgresql.org/docs/postgres/routine-reindex.html)
and on the lists here that indexes may still bloat after a while and hence
reindex is necessary.  How often do people reindex their tables out
there? I guess I'd have to update my cron scripts to do reindexing too
along with vacuuming but most probably at a much lower frequency than
vacuum.

But these scripts do these maintenance tasks at a fixed time (every few
hours, days, weeks, etc.) What I would like is to do these tasks on a need
basis.  So for vacuuming, by "need" I mean every few updates or some such
metric that characterizes my workload. Similarly, "need" for the reindex
command might mean every few updates or degree of bloat, etc.

I came across the pg_autovacuum daemon, which seems to do exactly what I
need for vacuums. However, it'd be great if there was a similar automatic
reindex utility, like say, a pg_autoreindex daemon. Are there any plans
for this feature?  If not, then would cron scripts be the next best
choice?

Thanks,
Ameet

Re: postgresql performance tuning

From
Vivek Khera
Date:
On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote:

> need for vacuums. However, it'd be great if there was a similar
> automatic
> reindex utility, like say, a pg_autoreindex daemon. Are there any
> plans
> for this feature?  If not, then would cron scripts be the next best

what evidence do you have that you are suffering index bloat?  or are
you just looking for solutions to problems that don't exist as an
academic exercise? :-)


Re: postgresql performance tuning

From
Alan Stange
Date:
Vivek Khera wrote:
>
> On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote:
>
>> need for vacuums. However, it'd be great if there was a similar
>> automatic
>> reindex utility, like say, a pg_autoreindex daemon. Are there any plans
>> for this feature?  If not, then would cron scripts be the next best
>
> what evidence do you have that you are suffering index bloat?  or are
> you just looking for solutions to problems that don't exist as an
> academic exercise? :-)

The files for the two indices on a single table used 7.8GB of space
before a reindex, and 4.4GB after.   The table had been reindexed over
the weekend and a vacuum was completed on the table about 2 hours ago.

The two indices are now 3.4GB smaller.   I don't think this counts as
bloat, because of our use case.  Even so, we reindex our whole database
every weekend.

-- Alan


Re: postgresql performance tuning

From
Tom Lane
Date:
Alan Stange <stange@rentec.com> writes:
> Vivek Khera wrote:
>> what evidence do you have that you are suffering index bloat?

> The files for the two indices on a single table used 7.8GB of space
> before a reindex, and 4.4GB after.

That's not bloat ... that's pretty nearly in line with the normal
expectation for a btree index, which is about 2/3rds fill factor.
If the compacted index were 10X smaller then I'd agree that you have
a bloat problem.

Periodic reindexing on this scale is not doing a lot for you except
thrashing your disks --- you're just giving space back to the OS that
will shortly be sucked up again by the same index.

            regards, tom lane

Re: postgresql performance tuning

From
Alan Stange
Date:
Tom Lane wrote:
> Alan Stange <stange@rentec.com> writes:
>
>> Vivek Khera wrote:
>>
>>> what evidence do you have that you are suffering index bloat?
>>>
>
>
>> The files for the two indices on a single table used 7.8GB of space
>> before a reindex, and 4.4GB after.
>>
>
> That's not bloat ... that's pretty nearly in line with the normal
> expectation for a btree index, which is about 2/3rds fill factor.
> If the compacted index were 10X smaller then I'd agree that you have
> a bloat problem.
>
I wrote "I don't think this counts as bloat...".  I still don't.

-- Alan

Re: postgresql performance tuning

From
Ameet Kini
Date:

> what evidence do you have that you are suffering index bloat?  or are
> you just looking for solutions to problems that don't exist as an
> academic exercise? :-)

Well, firstly, its not an academic exercise - Its very much of a real
problem that needs a real solution :)

I'm running postgresql v8.0 and my problem is that running vacuum on my
indices are blazing fast (upto 10x faster) AFTER running reindex. For a
table with only 1 index, the time to do a vacuum (without full) went down
from 45 minutes to under 3 minutes.  Maybe thats not bloat but thats
surely surprising.  And this was after running vacuum periodically.

Ameet

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Ameet

Re: postgresql performance tuning

From
Michael Stone
Date:
On Tue, Dec 06, 2005 at 04:03:22PM -0600, Ameet Kini wrote:
>I'm running postgresql v8.0 and my problem is that running vacuum on my
>indices are blazing fast (upto 10x faster) AFTER running reindex. For a
>table with only 1 index, the time to do a vacuum (without full) went down
>from 45 minutes to under 3 minutes.

I've also noticed a fairly large increase in vacuum speed after a
reindex. (To the point where the reindex + vacuum was faster than just a
vacuum.)

Mike Stone

Re: postgresql performance tuning

From
Vivek Khera
Date:
On Dec 6, 2005, at 5:03 PM, Ameet Kini wrote:

> table with only 1 index, the time to do a vacuum (without full)
> went down
> from 45 minutes to under 3 minutes.  Maybe thats not bloat but thats
> surely surprising.  And this was after running vacuum periodically.

I'll bet either your FSM settings are too low and/or you don't vacuum
often enough for your data churn rate.

Without more data, it is hard to solve the right problem.


slow COMMITs

From
Evgeny Gridasov
Date:
Hi everybody!

My system is 2xXEON 3 GHz, 4GB RAM, RAID-10 (4 SCSI HDDs), running Postgres 8.1.0, taken from CVS REL8_1_STABLE,
compiledwith gcc-3.4 with options "-march=nocona -O2 -mfpmath=sse -msse3". Hyperthreading is disabled. 

There are about 300,000 - 500,000 transactions per day. Database size is about 14 Gigabytes.

The problem is that all queries run pretty good except transaction COMMITs.
Sometimes it takes about 300-500 ms to commit a transaction and it is unacceptebly slow for my application.
I had this problem before, on 8.0.x and 7.4.x, but since 8.1 upgrade all queries began to work very fast except commit.

BTW, I ran my own performance test, a multithreaded typical application user emulator, on 7.4 and 8.1. 8.1 performance
was8x times faster than 7.4, on the same machine and with the same config file settings. 

Some settings from my postgresql.conf:

shared_buffers = 32768
temp_buffers = 32768
work_mem = 12228
bg_writer_delay = 400
wal_buffers = 128
commit_delay = 30000
checkpoint_segments = 8
effective_cache_size = 262144 # postgres is the one and the only application on this machine
default_statistics_target = 250

all statistic collection enabled
autovacuum runs every 120 seconds. vacuum is run after 2000 updates, analyze is run after 1000 updates.

I've run vmstat to monitor hard disk activity. It was 50-500 Kb/sec for reading and 200-1500 Kb/sec for writing. There
aresome peak hdd reads and writes (10-20Mb/s) but commit time does not always depend upon them. 

What parameters should I tune?


--
Evgeny Gridasov
Software Engineer
I-Free, Russia