From: | Miguel Ramos <mail(at)miguel(dot)ramos(dot)name> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_restore out of memory |
Date: | 2016-07-15 07:36:58 |
Message-ID: | 1468568218.2533.8.camel@miguel.ramos.name |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu:
>
> A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu:
> >
> > I wrote:
> > >
> > > I'm still suspicious that this might be some sort of NOTICE-
> > > processing-
> > > related buffer bloat. Could you try loading the data with the
> > > server's
> > > log_min_messages level cranked down to NOTICE, so you can see
> > > from
> > > the
> > > postmaster log whether any NOTICEs are being issued to the
> > > pg_restore
> > > session?
> >
> > BTW, I experimented with that theory by creating a table with a
> > BEFORE
> > INSERT trigger function that emits a NOTICE, and then making
> > pg_restore
> > restore a lot of data into it. I could not see any memory growth
> > in
> > the pg_restore process. However, I was testing 9.1.22, not 9.1.8.
> > Also, some of the misbehaviors we've discovered along these lines
> > have
> > been timing-sensitive, meaning that the problem might or might not
> > reproduce for another person even with the same software version.
> > Are you running pg_restore locally on the same machine as the
> > server,
> > or across a network --- and if the latter, how fast is the network?
> >
> > regards, tom lane
> >
>
> I was running pg_restore locally.
> The disk containing the backup, however, is on NAS.
> The NAS is mounted on the server using SMB and the FreeBSD kernel
> implementation of smbfs (mount_smbfs -I ... /mnt).
> The kernel smbfs is notoriously outdated and sometimes we get
> timeouts.
>
> However, those timeouts happen randomly and this "out of memory"
> happens consistently.
> This time, the server was no longer under heavy load, the log lines
> are
> consecutive, there was no activity during the start of the COPY
> statement and the error.
>
> The network is 1Gbps with a single unmanaged 24-port switch.
> The server
> has two aggregated links to the switch.
>
>
> I ran pg_restore locally because the server is in another office,
> connected to mine through a VPN.
>
> Now I have arranjed for a PC to be there for me and my next test will
> be to do the restore using the latest pgadmin.
>
>
> Thanks,
>
> -- Miguel Ramos
I tried the restore using pgAdmin III 1.22.1.
This time from a Windows PC connected to the server through a 1Gbps switch.
Unfortunately the result was the same, and this was my best bet.
I see (transcribed by hand from screenshot):
...
pg_restore: processing data for table "inspection.positioned_scan"
out of memory
Process returned exit code 1.
I hadn't yet set log_min_messages to 'notice'. But as client_min_messages is at 'notice', aren't this displayed on a verbose pg_restore?
Maybe during the weekend I can have more verbose logging.
Now I'm repeating the backup (maybe the file is bad) and then I will repeat the restore with log_min_messages to 'notice'.
I suppose log_statement to 'all' is no longer necessary?
What else?
--
Miguel Ramos
From | Date | Subject | |
---|---|---|---|
Next Message | Miguel Ramos | 2016-07-15 07:37:50 | Re: pg_restore out of memory |
Previous Message | Derek Mahar | 2016-07-14 23:06:05 | Re: PostgreSQL image for rkt on CoreOS |