Re: vacuum problems

We increased shared memory in the linux kernel, which decreased the vacuumdb
time from 40 minutes to 14 minutes on a 450 mhz processor. We calculate that
on our dual 1ghz box with ghz ethernet san connection this will go down to
under 5 minutes. This is acceptable to us. Sorry about the unnecessary post.

On Wednesday 11 July 2001 09:16, Mark wrote:
> Quick rundown of our configuration:
> Red Hat 7.1 (no changes or extras added by us)
> Postgresql 7.1.2 and CVS HEAD from 07/10/2001
> 3.8 gb database size
>
> I included two pgsql versions because this happens on both.
>
> Here's the problem we're having:
>
> We run a vacuumdb from the server on the entire database. Some large
> tables are vacuumed very quickly, but the vacuum process hangs or takes
> more than a few hours on a specific table (we haven't let it finish
> before). The vacuum process works quickly on a table (loginhistory) with
> 2.8 million records, but is extremely slow on a table (inbox) with 1.1
> million records (the table with 1.1 million records is actually larger in
> kb size than the other table).
>
> We've tried to vacuum the inbox table seperately ('vacuum inbox' within
> psql), but this still takes hours (again we have never let it complete, we
> need to use the database for development as well).
>
> We noticed 2 things that are significant to this situatoin:
> The server logs the following:
>
>
> DEBUG: --Relation msginbox--
> DEBUG: Pages 129921: Changed 26735, reaped 85786, Empty 0, New 0; Tup
> 1129861: Vac 560327, Keep/VTL 0/0, Crash 0, UnUsed 51549, MinLen 100,
> MaxLen 2032; Re-using: Free/Avail. Space 359061488/359059332;
> EndEmpty/Avail. Pages 0/85785. CPU 11.18s/5.32u sec.
> DEBUG: Index msginbox_pkey: Pages 4749; Tuples 1129861: Deleted 76360.
> CPU 0.47s/6.70u sec.
> DEBUG: Index msginbox_fromto: Pages 5978; Tuples 1129861: Deleted 0.
> CPU 0.37s/6.15u sec.
> DEBUG: Index msginbox_search: Pages 4536; Tuples 1129861: Deleted 0.
> CPU 0.32s/6.30u sec.
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
>
> the last few lines (XLogWrite .....) repeat for ever and ever and ever.
> With 7.1.2 this never stops unless we run out of disk space or cancel the
> query. With CVS HEAD this still continues, but the log files don't consume
> all disk space, but we still have to cancel it or it might run forever.
>
> Perhaps we need to let it run until it completes, but we thought that we
> might be doing something wrong or have some data (we're converting data
> from MS SQL Server) that isn't friendly.
>
> The major issue we're facing with this is that any read or write access to
> the table being vacuumed times out (obviously because the table is still
> locked). We plan to use PostgreSQL in our production service, but we can't
> until we get this resolved.
>
> We're at a loss, not being familiar enough with PostgreSQL and it's source
> code. Can anyone please offer some advice or suggestions?
>
> Thanks,
>
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)