Re: REINDEX takes half a day (and still not complete!)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Apr 17, 2011 at 9:44 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> On Sun, Apr 17, 2011 at 9:30 AM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote:
>> Sorry, rejuvenating a thread that was basically unanswered.
>>
>> I closed the database for any kinds of access to focus on maintenance
>> operations, killed all earlier processes so that my maintenance is the
>> only stuff going on.
>>
>> REINDEX is still taking 3 hours -- and it is still not finished!
>>
>> Similarly, if I cancel the REINDEX and issue a VACUUM ANALYZE VERBOSE,
>> this too seems to just hang there on my big table.
>>
>> I changed the maintenance_work_men to 2GB for this operation. It's
>> highly worrisome -- the above slow times are with 2GB of my server
>> dedicated to Postgresql!!!!
>>
>> Surely this is not tenable for enterprise environments? I am on a
>> 64bit RedHat server with dual CPU Intel Woodcrest or whatever that was
>> called. Postgres is 8.2.9.
>>
>> How do DB folks do this with small maintenance windows? This is for a
>> very high traffic website so it's beginning to get embarrassing.
>>
>> Would appreciate any thoughts or pointers.
>
> Upgrade to something more modern than 8.2.x.  Autovacuum was still
> very much in its infancy back then.  9.0 or higher is a good choice.
> What do iostat -xd 10 and vmstat 10 and top say about these processes
> when they're running.  "It's taking a really long time and seems like
> it's hanging" tells us nothing useful.  Your OS has tools to let you
> figure out what's bottlenecking your operations, so get familiar with
> them and let us know what they tell you.  These are all suggestions I
> made before which you have now classified as "not answering your
> questions" so I'm getting a little tired of helping you when you don't
> seem interested in helping yourself.
>
> What are your vacuum and autovacuum costing values set to?  Can you
> make vacuum and / or autovacuum more aggresive?

Also a few more questions, what are you using for storage?  How many
drives, RAID controller if any, RAID configuration etc.?

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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux