Thread: hyper slow after upgrade to 8.1.4
I have just upgraded from 7.3.4 to 8.1.4 and now *all* db access calls are extremely slow. I didn’t need to preserve any old data so at this point all my tables are empty. Just connecting to a db takes several seconds.
When I was accidentally linking my app with the 7.3.4 libs but running the 8.1.4 postmaster everything was fine.
I know I’m not giving much to go on but I’m stumped. Can anyone suggest how I might track down the cause of this problem?
Medora Schauer
On Wednesday 12 July 2006 13:41, Medora Schauer wrote:
> I have just upgraded from 7.3.4 to 8.1.4 and now *all* db access calls
> are extremely slow. I didn't need to preserve any old data so at this
> point all my tables are empty. Just connecting to a db takes several
> seconds.
>
>
>
> When I was accidentally linking my app with the 7.3.4 libs but running
> the 8.1.4 postmaster everything was fine.
>
>
>
> I know I'm not giving much to go on but I'm stumped. Can anyone suggest
> how I might track down the cause of this problem?
analyze?
Sincerely,
Joshua D. Drake
>
>
>
> Medora Schauer
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Wed, Jul 12, 2006 at 15:41:14 -0500, Medora Schauer <mschauer@fairfield.com> wrote: > I have just upgraded from 7.3.4 to 8.1.4 and now *all* db access calls > are extremely slow. I didn't need to preserve any old data so at this > point all my tables are empty. Just connecting to a db takes several > seconds. > > I know I'm not giving much to go on but I'm stumped. Can anyone suggest > how I might track down the cause of this problem? That connections are slow makes me think DNS is worth looking at. It might be that reverse lookups are timing out.
> From: Bruno Wolff III [mailto:bruno@wolff.to] > Sent: Wednesday, July 12, 2006 8:54 PM > To: Medora Schauer > Cc: postgresql > Subject: Re: hyper slow after upgrade to 8.1.4 > > On Wed, Jul 12, 2006 at 15:41:14 -0500, > Medora Schauer <mschauer@fairfield.com> wrote: > > I have just upgraded from 7.3.4 to 8.1.4 and now *all* db access calls > > are extremely slow. I didn't need to preserve any old data so at this > > point all my tables are empty. Just connecting to a db takes several > > seconds. > > > > I know I'm not giving much to go on but I'm stumped. Can anyone suggest > > how I might track down the cause of this problem? > > That connections are slow makes me think DNS is worth looking at. It might > be that reverse lookups are timing out. It does seem to be network related. Using the 8.1.4 psql on a machine other than the db server connecting to a database takes ~11 secs. Using it on the db server the connection is virtually instantaneous. Using the 7.3.4 psql (still the 8.1.4 postmaster) the connection is fast regardless of what machine I am on. The pg.log contains the following at the top: LOG: could not create IPv6 socket: Address family not supported by protocol I am using a 2.4.25 linux kernel. For reasons I can't get into I have no choice but to use this kernel. The config utility for the kernel shows support for the IPv6 protocol as "experimental" and is not included in my current build. I can try building a kernel that includes IPv6 but the "experimental" caveat is scary. Can it be that the connection delay is because first an IPv6 socket is trying to be established and when that fails an IPv4 socket is created? If so, is there any way to make 8.1.4 use only IPv4 sockets? BTW - The slowness seems to be only during db connection. Once the connection is established queries seem to execute in a timely manner. Medora
On Thu, Jul 13, 2006 at 08:22:46AM -0500, Medora Schauer wrote: > Can it be that the connection delay is because first an IPv6 socket is > trying to be established and when that fails an IPv4 socket is created? A sniffer like tcpdump or ethereal might reveal why connecting is so slow. The problem might be with DNS queries for AAAA (IPv6) records prior to queries for A (IPv4) records; see this thread from almost a year ago: http://archives.postgresql.org/pgsql-general/2005-08/msg00216.php -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Jul 13, 2006 at 08:22:46AM -0500, Medora Schauer wrote:
>> Can it be that the connection delay is because first an IPv6 socket is
>> trying to be established and when that fails an IPv4 socket is created?
> A sniffer like tcpdump or ethereal might reveal why connecting is
> so slow.
I'd try strace'ing the client process first --- whatever is slow might
not be exposed as TCP traffic. It does sound though that the problem
is related to userland expecting IPv6 support that the kernel doesn't
actually have.
regards, tom lane
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, July 13, 2006 11:12 AM > > Michael Fuhr <mike@fuhr.org> writes: > > On Thu, Jul 13, 2006 at 08:22:46AM -0500, Medora Schauer wrote: > >> Can it be that the connection delay is because first an IPv6 socket is > >> trying to be established and when that fails an IPv4 socket is created? > > > A sniffer like tcpdump or ethereal might reveal why connecting is > > so slow. > > I'd try strace'ing the client process first --- whatever is slow might > not be exposed as TCP traffic. It does sound though that the problem > is related to userland expecting IPv6 support that the kernel doesn't > actually have. > > regards, tom lane Good idea Tom. Strace showed communications with a machine that didn't make sense. Turns out someone had configured DNS on the "slow" machine but the DNS server wasn't running. When I use the IP address of the PG server rather than the name with psql, the connection is made quickly. Thanks for all the help everyone, Medora Schauer