<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>RE: [postgis-users] A PostGIS-Raster data proposal</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Craig,<BR>
<BR>
In my tests, the database was local, as was the file system. However, a remote database would not have produced a different result, as my test was timing access to TOASTed data from within a server-side function; in other words, all the access was in the postgres backend process. I did not time the performance of returning the data to the database client process.<BR>
<BR>
However, you make a good point. It may be that the time to return data over the network is much larger than the time for local data access. If so, the difference between seeking access from the DB and file may end up contributing a negligible difference to the total access time for remote data.<BR>
<BR>
I think I should time both local access and the time to return the data to a remote system. Thanks for the suggestion.<BR>
<BR>
Steve<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Craig Miller<BR>
Sent: Fri 10/27/2006 5:58 PM<BR>
To: 'PostGIS Users Discussion'<BR>
Subject: RE: [postgis-users] A PostGIS-Raster data proposal<BR>
<BR>
Steve,<BR>
<BR>
Thanks for reporting. Was the database local? Were the images loaded from<BR>
the filesystem local?<BR>
<BR>
I'd think a good "real world" example would be to have both the images and<BR>
the database remotely located somewhere with access being done via NFS or<BR>
similar. What I'm wondering is if at that point the network transfer time<BR>
will dwarf the file access times making file access times moot.<BR>
<BR>
Wish I had some time to play with it right now.<BR>
<BR>
--Craig<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net<BR>
[<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>
Marshall, Steve<BR>
Sent: Friday, October 27, 2006 2:32 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: RE: [postgis-users] A PostGIS-Raster data proposal<BR>
<BR>
Per Frank Warmerdam's suggestion, I've done a test of access performance<BR>
using internal postgresql toast functions vs. normal file seeking. <BR>
<BR>
The test involved seeking in a toasted bytea column containing approximately<BR>
20 MB of binary data. The TOAST column was set to EXTERNAL storage (i.e. in<BR>
separate TOAST table, but not compressed).<BR>
The test involved seeking through the data sequentially in chunks of 1000<BR>
bytes, and measuring the time to retrieve each chunk. The code to do this<BR>
was encapsulated in a postgresql server-side function and invoked through<BR>
SQL. I restarted the PostgreSQL server before the test to avoid having any<BR>
cacing of data in shared memory, which could artificially speed up the data<BR>
access.<BR>
<BR>
As a comparison, I also wrote a program that would do the equivalent data<BR>
access from a file. The file contained the same data as the bytea column,<BR>
and the access was replaced with fseek and fread calls.<BR>
<BR>
The results of the test were that toast seeking was about 10 times more<BR>
expensive than seeking in a local file. Each local file access averaged in<BR>
microseconds, while toast-seeks averaged 10's of microseconds. The worst<BR>
case file seeking was in milliseconds, while worst case toast-seeking was in<BR>
10's of milliseconds. The absolute values for toast-seeks don't seem too<BR>
bad to me, but it is a bit worrying that the values are an order of<BR>
magnitude worse than local file I/O.<BR>
<BR>
I did play around with some parameters in the DB test. Changing the chunk<BR>
size did not make a big difference, but it got a small boost by setting it<BR>
to the toast chunk size (1994 bytes). I did not vary the test to do seeking<BR>
around randomly instead of sequentially. This might give a boost to the DB<BR>
implementation due to caching; I'm not sure what this would do to file I/O.<BR>
<BR>
I also have not explored the performance of repeated access to the same data<BR>
segments. Here PostgreSQL data caching might help DB access relative to<BR>
file I/O.<BR>
<BR>
There are still more things to do here, but I thought I'd share some early<BR>
results. I'm happy to provide the code and SQL definitions for the test, if<BR>
anyone else is interested in it.<BR>
<BR>
Steve Marshall<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>