Thread: VACUUM FULL freezes
I have a database that is on a RAID5 machine that is almost out of memory (277GB of 330GB used). I have deleted some data and run the VACUUM FULL command, but after a few hours gave me the error message "Server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed." I know that the server did not fail, so i assume that this timed out. The server is running version 7.4 (i know it's an old version). The database has several tables, but 99% of the data is located in a single table. My fear is that there's not enough system memory left to perform the full vacuum since it's all contained in a single table. Any ideas? -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, May 22, 2013 at 8:49 AM, RDNikeAir <ryan.dupuis@excelitas.com> wrote: > I have a database that is on a RAID5 machine that is almost out of memory > (277GB of 330GB used). I have deleted some data and run the VACUUM FULL > command, but after a few hours gave me the error message "Server closed the > connection unexpectedly. This probably means the server terminated > abnormally before or while processing the request. The connection to the > server was lost. Attempting reset: Failed." I know that the server did not > fail, so i assume that this timed out. The server is running version 7.4 (i > know it's an old version). > > The database has several tables, but 99% of the data is located in a single > table. My fear is that there's not enough system memory left to perform the > full vacuum since it's all contained in a single table. > > Any ideas? Anything in the postgres logs about it? I'm guessing you've got some corruption somewhere that's causing the backend to crash during a vacuum full. Whether or not the backend crashed or the connection timed out, there should be something about it in the postgres logs.
Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last message i have in messages.* is from a few days ago. So there are no recent entries that i can look at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477p5756484.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
RDNikeAir wrote: > Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last > message i have in messages.* is from a few days ago. So there are no recent > entries that i can look at. Well, it is clear then that the first thing you need to do is fix your logging. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 05/22/2013 08:49 AM, RDNikeAir wrote:
> I have a database that is on a RAID5 machine that is almost out of memory
> (277GB of 330GB used). I have deleted some data and run the VACUUM FULL
> command, but after a few hours gave me the error message "Server closed the
> connection unexpectedly. This probably means the server terminated
> abnormally before or while processing the request. The connection to the
> server was lost. Attempting reset: Failed." I know that the server did not
> fail, so i assume that this timed out. The server is running version 7.4 (i
> know it's an old version).
>
> The database has several tables, but 99% of the data is located in a single
> table. My fear is that there's not enough system memory left to perform the
> full vacuum since it's all contained in a single table.
>
> Any ideas?
Perhaps your best option is a full dump and restore. That will
certainly de-bloat everything as well. And if you do that, you could
consider upgrading as well.
Cheers!
- Chris