Thread: postgresql performance tuning
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
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? :-)
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
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
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
> 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
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
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.
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