In-Reply-To: <105.1145567988@sss.pgh.pa.us>
References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com>
	<29439.1145564220@sss.pgh.pa.us>
	<47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com>
	<105.1145567988@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v749.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <53ACF10A-9240-420B-83AB-E4CBABD2AC56@clickspace.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Brendan Duddridge <brendan@clickspace.com>
Subject: Re: Recovery will take 10 hours 
Date: Thu, 20 Apr 2006 18:24:36 -0600
To: Tom Lane <tgl@sss.pgh.pa.us>

Hi Tom,

Well, we started the restore back up with the WAL archives copied to  
our local disk.

It's going at about the same pace as with the restore over NFS.

So I tried ktrace -p PID and it created a really big file. I had to  
do 'ktrace -p PID -c' to get it to stop.

The ktrace.out file is read using kdump, but there's a lot of binary  
data in there intermixed with some system calls.

For example:

15267 postgres RET   read 8192/0x2000
15267 postgres CALL  lseek(153,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  lseek(127,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  lseek(138,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  lseek(153,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  lseek(127,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  read(5,25225728,8192)
15267 postgres GIO   fd 5 read 8192 bytes
        "\M-P]\0\^A\0\0\0\^A\0\0\^A\M^H,\M-5`\0\0\0\^C\M-6r fill,  
polyester has a subtle sheen, machine wash\0\0\0Xreverses to\
          solid colour, polyester fill, polyester has a subtle sheen,  
machine wash\^_\^Y7\M-3\0\0\0\0\0\0\0\0\0\0\0\0\0\0oG\0\
         \b\0\^[)\^C \M^Or\M-#\^B\0\0\0\0\0A\M-&\M-]

... lots of data ....

         \M^K$\0\0\0\fcomplete\0\0\0HCustom-width Valanceless  
Aluminum Mini Blinds 37 1/4-44" w. x 48" l.\0\0\0\M-P1" aluminum\
          slats, valanceless headrail and matching bottom rail,  
hidden brackets, clear acrylic tilt wand, extra slats with rou\
         te holes in the back, can be cut down to minimum width of  
14", hardware. . .\0\0\^Aq1" aluminum slats, valanceless he\
         adrail and matching bottom rail, hidden brackets, clear  
acrylic tilt wand, extra slats with route holes in the back, \
         can be cut down to minimum width of 14", hardware and  
instructions included, wipe with a dam"
15267 postgres RET   read 8192/0x2000
15267 postgres CALL  lseek(138,0,2)
15267 postgres RET   lseek 0
15267 postgres CALL  lseek(158,317251584,0)
15267 postgres RET   lseek 0
15267 postgres CALL  write(158,35286464,8192)
15267 postgres GIO   fd 158 wrote 8192 bytes
        "\0\0\^A\M^H+\M^W@\M-,\0\0\0\^A\^A\M-D\^P\M-@\^_\M-p \^C?\M^X 
\M^@$?P\M^@$?\b\M^@$>\M-@\M^@$>x\M^@$>0\M^@$=\M-h\M^@$=\
         \240\M^@$#0\M^@$"X\M^@$=X\M^@$=\^P\M^@$<\M-H\M^@$<\M^@\M^@$<8 
\M^@$;\M-p\M^@$;\M-(\M^@$;`\M^@$;\^X\M^@$:\M-P\M^@$:\M^H\

etc...

I'm not sure that really tells me anything though other than the WAL  
archives don't actually archive SQL, but store only the database  
changes.


Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

On Apr 20, 2006, at 3:19 PM, Tom Lane wrote:

> Brendan Duddridge <brendan@clickspace.com> writes:
>> Do you mean do a kill -QUIT on the postgres process in order to
>> generate a stack trace?
>
> Not at all!  I'm talking about tracing the kernel calls it's making.
> Depending on your platform, the tool for this is called strace,
> ktrace, truss, or maybe even just trace.  With strace you'd do
> something like
>
> 	strace -p PID-of-process 2>outfile
> 	... wait 30 sec or so ...
> 	control-C
>
> Not sure about the APIs for the others but they're probably roughly
> similar ... read the man page ...
>
> 			regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>