On Mon, 2006-04-10 at 02:16, Martijn van Oosterhout wrote:
> The appears to be two seperate cases here though, one is to just farm
> out the read request to another process (basically aio), the other is
> to do actual processing there. The latter is obviously for more useful
> but requires a fair bit more infrastructure.
>
I ran some tests to see where time is spent during SeqScans. I did the
following.
tester=# vacuum analyze verbose test;
INFO: vacuuming "public.test"
INFO: "test": found 0 removable, 727960 nonremovable row
versions in 5353 pagesDETAIL: 0 dead row versions cannot be
removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.18s/0.27u sec elapsed 0.91 sec.
INFO: analyzing "public.test"
INFO: "test": scanned 3000 of 5353 pages, containing 407952
live rows and 0 dead rows; 3000 rows in sample, 727922 estimated
total rows
VACUUM
tester=# select version();
version
-------------------------------------------------------------------------------
PostgreSQL 8.2devel on sparc-sun-solaris2.11, compiled by GCC
gcc (GCC) 3.3.2
(1 row)
tester=# select count(random) from test;
count
--------
727960
(1 row)
With the follow ing dtrace results...
# ./probediff2.d 514607
dtrace: script './probediff2.d' matched 10 probes
CPU ID FUNCTION:NAME
0 46811 ExecEndSeqScan:return scan time 20406
^C
smgrread
641566800
Virtualized - smgrread
439798800
smgread - Call Count
5353
HeapTupleSatisfiesSnapshot
6735471000
Virtualized - HeapTupleSatisfiesSnapshot
3516556800
HeapTupleSatisfiesSnapshot - Call Count
727960
Virtualized - ReadBuffer
558230600
ReadBuffer
864931000
Virtualized - ExecutePlan
7331181400
Virtualized - ExecSeqScan
7331349600
ExecutePlan
20405943000
ExecSeqScan
20406161000
The virtualized times are supposed to be actual time spent on the CPU
with
the time spent in the probe factored out. It seems here that half the
time
in SeqScan is spent time validating the tuples as opposed to 1/10th
doing IO.
I'm not sure that just farming out read IO is going to be all that
helpful
in this situation. That's why I think it's a good idea to create a
slave
process that prefetchs pages and transfers valid ItemPointers to the
master.
There may not be much to be gained on simple SeqScans, however, in
complex
queries that include a SeqScan, you may gain alot by offloading this
work
onto a slave thread. A table with TOAST'ed attributes comes to mind.
The
slave thread could be working away on the rest of the table while the
master
is PG_DETOAST_DATUM'ing the attributes for transmission back to the
client
or additional processing. Am I missing something in this analysis?
I've attached my dtrace script.
Myron Scott
#!/usr/sbin/dtrace -s
pid$1::ExecInitSeqScan:entry
{
ts = timestamp;
vts = vtimestamp;
timeon = 1;
}
pid$1::ExecEndSeqScan:return
/ts/
{
printf("scan time %d",(timestamp - ts) /1000000) ;
@val["ExecSeqScan"] = sum(timestamp - ts);
@val["Virtualized - ExecSeqScan"] = sum(vtimestamp - vts);
ts = 0;
vts = 0;
timeon = 0;
}
pid$1::HeapTupleSatisfiesSnapshot:entry
/timeon/
{
validity = timestamp;
vvalidity = vtimestamp;
}
pid$1::HeapTupleSatisfiesSnapshot:return
/validity/
{
@val["HeapTupleSatisfiesSnapshot"] = sum(timestamp - validity);
@val["Virtualized - HeapTupleSatisfiesSnapshot"] = sum(vtimestamp -
vvalidity);
@val["HeapTupleSatisfiesSnapshot - Call Count"] = sum(1);
validity = 0;
vvalidity = 0;
}
pid$1::smgrread:entry
/timeon/
{
rt= timestamp;
vrt= vtimestamp;
}
pid$1::smgrread:return
/rt/
{
@val["smgrread"] = sum(timestamp - rt);
@val["Virtualized - smgrread"] = sum(vtimestamp - vrt);
@val["smgread - Call Count"] = sum(1);
rt = 0;
vrt = 0;
}
pid$1::ReadBuffer:entry
/timeon/
{
brt= timestamp;
vbrt= vtimestamp;
}
pid$1::ReadBuffer:return
/brt/
{
@val["ReadBuffer"] = sum(timestamp - brt);
@val["Virtualized - ReadBuffer"] = sum(vtimestamp - vbrt);
brt = 0;
vbrt = 0;
}
pid$1::ExecutePlan:entry
{
pt= timestamp;
vpt= vtimestamp;
}
pid$1::ExecutePlan:return
/pt/
{
@val["ExecutePlan"] = sum(timestamp - pt);
@val["Virtualized - ExecutePlan"] = sum(vtimestamp - vpt);
pt = 0;
vpt = 0;
}
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
