Thread

1. Complex database for testing, U.S. Census Tiger/UA

   Mark Woodward <pgsql@mohawksoft.com> — 2003-04-08T12:52:22Z

   The U.S. Census provides a database of street polygons and other data 
   about landmarks, elevation, etc. This was discussed in a separate thread.
   
   The main URL is here:
   http://www.census.gov/geo/www/tiger/index.html
   
   My loader was written for the 2000 version, the 2002 version has some 
   difference, but it should be easy enough to ad the fields.
   
   On my site, in the downloads section, at the bottom is the tigerua 
   loader. It is very raw, just hacked together to load the data. It may 
   take a little work to function with 2002 files, I have not looked at 
   that yet.
   
   My site:
   http://www.mohawksoft.com
   
   
   

2. Re: Complex database for testing, U.S. Census Tiger/UA

   Jan Wieck <janwieck@yahoo.com> — 2003-04-08T14:32:52Z

   mlw wrote:
   > 
   > The U.S. Census provides a database of street polygons and other data
   > about landmarks, elevation, etc. This was discussed in a separate thread.
   > 
   > The main URL is here:
   > http://www.census.gov/geo/www/tiger/index.html
   
   While yes, the tiger database (or better it's content) is interesting, I
   don't think that it can be counted as a "complex database". Just that
   something is big doesn't mean that.
   
   > 
   > My loader was written for the 2000 version, the 2002 version has some
   > difference, but it should be easy enough to ad the fields.
   
   OT:
   
   Just out of curiosity, do you plan more on this? I was playing around
   with the 2000 version a while back, but the Garmin GPS units
   unfortunately use a proprietary map format, so one cannot generate his
   own detail maps for download. The waypoint and route data protocol is
   well known though.
   
   
   Jan
   
   -- 
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #================================================== JanWieck@Yahoo.com #
   
   
   

3. Re: Complex database for testing, U.S. Census Tiger/UA

   Mark Woodward <pgsql@mohawksoft.com> — 2003-04-08T15:12:11Z

   > mlw wrote:
   >> 
   >> The U.S. Census provides a database of street polygons and other data
   >> about landmarks, elevation, etc. This was discussed in a separate
   >> thread.
   >> 
   >> The main URL is here:
   >> http://www.census.gov/geo/www/tiger/index.html
   > 
   > While yes, the tiger database (or better it's content) is interesting,
   > I don't think that it can be counted as a "complex database". Just that
   > something is big doesn't mean that.
   
   I guess you are right, but there are a lot of related tables. I wouldn't
   call it simple, though. It can get huge, however.
   > 
   >> 
   >> My loader was written for the 2000 version, the 2002 version has some
   >> difference, but it should be easy enough to ad the fields.
   > 
   > OT:
   > 
   > Just out of curiosity, do you plan more on this? I was playing around
   > with the 2000 version a while back, but the Garmin GPS units
   > unfortunately use a proprietary map format, so one cannot generate his
   > own detail maps for download. The waypoint and route data protocol is
   > well known though.
   
   I'm not sure what a Garmin GPS unit is, but the TigerUA DB uses longitude
   and latitude. Any reasonable geographical system must somehow map to lat/long.
   
   Actually, I am going to download the latest version and get it installed on
   a system. There is a project I plan to work on in the near future, after all
   the other crap I gotta do, that will make use of the data.
   
   
   

4. Re: Complex database for testing, U.S. Census Tiger/UA

   Your Name <cbbrowne@cbbrowne.com> — 2003-04-08T15:24:06Z

   Jan Wieck wrote:
   > mlw wrote:
   > > 
   > > The U.S. Census provides a database of street polygons and other data
   > > about landmarks, elevation, etc. This was discussed in a separate thread.
   > > 
   > > The main URL is here:
   > > http://www.census.gov/geo/www/tiger/index.html
   > 
   > While yes, the tiger database (or better it's content) is interesting, I
   > don't think that it can be counted as a "complex database". Just that
   > something is big doesn't mean that.
   
   Just so.
   
   There are doubtless interesting cases that may be tested by virtue of
   having a data set that is large, and perhaps "deeply interlinked."
   
   But that only covers cases that have to do with "largeness."  It doesn't
   help ensure that PostgreSQL plays well when it gets hit by nested sets
   of updates where the challenges involve ensuring the system performs OK
   and does not deadlock when hit by complex sets of transactions.
   
   So that an "interesting" database might involve not only a database, but
   also a set of transactions that hit multiple tables that are to update
   that database.  In effect, something like the "readers/writers" that get
   used to test locking semantics.
   
   This is something that would not be able to solely consist of a set of
   tables; it would have to include streams of updates.  Something like one
   of the TPC benchmarks...
   --
   output = reverse("moc.enworbbc@" "enworbbc")
   http://www3.sympatico.ca/cbbrowne/rdbms.html
   "If I  could find  a way to  get [Saddam  Hussein] out of  there, even
   putting a  contract out on him,  if the CIA  still did that sort  of a
   thing, assuming it ever did, I would be for it."  -- Richard M. Nixon
   
   
   

5. Re: Complex database for testing, U.S. Census Tiger/UA

   Dustin Sallings <dustin@spy.net> — 2003-04-08T16:35:10Z

   Around 11:24 on Apr 8, 2003, cbbrowne@cbbrowne.com said:
   
   	I think it was my first application I wrote in python which parsed
   the zip files containing these data and shoved it into a postgres system.
   I had multiple clients on four or five computers running nonstop for about
   two weeks to get it all populated.
   
   	By the time I was done, and got my first index created, I began to
   run out of disk space.  I think I only had about 70GB to work with on the
   RAID array.
   
   # Jan Wieck wrote:
   # > mlw wrote:
   # > >
   # > > The U.S. Census provides a database of street polygons and other data
   # > > about landmarks, elevation, etc. This was discussed in a separate thread.
   # > >
   # > > The main URL is here:
   # > > http://www.census.gov/geo/www/tiger/index.html
   # >
   # > While yes, the tiger database (or better it's content) is interesting, I
   # > don't think that it can be counted as a "complex database". Just that
   # > something is big doesn't mean that.
   #
   # Just so.
   #
   # There are doubtless interesting cases that may be tested by virtue of
   # having a data set that is large, and perhaps "deeply interlinked."
   #
   # But that only covers cases that have to do with "largeness."  It doesn't
   # help ensure that PostgreSQL plays well when it gets hit by nested sets
   # of updates where the challenges involve ensuring the system performs OK
   # and does not deadlock when hit by complex sets of transactions.
   #
   # So that an "interesting" database might involve not only a database, but
   # also a set of transactions that hit multiple tables that are to update
   # that database.  In effect, something like the "readers/writers" that get
   # used to test locking semantics.
   #
   # This is something that would not be able to solely consist of a set of
   # tables; it would have to include streams of updates.  Something like one
   # of the TPC benchmarks...
   # --
   # output = reverse("moc.enworbbc@" "enworbbc")
   # http://www3.sympatico.ca/cbbrowne/rdbms.html
   # "If I  could find  a way to  get [Saddam  Hussein] out of  there, even
   # putting a  contract out on him,  if the CIA  still did that sort  of a
   # thing, assuming it ever did, I would be for it."  -- Richard M. Nixon
   #
   #
   # ---------------------------(end of broadcast)---------------------------
   # TIP 3: if posting/reading through Usenet, please send an appropriate
   # subscribe-nomail command to majordomo@postgresql.org so that your
   # message can get through to the mailing list cleanly
   #
   #
   
   --
   SPY                      My girlfriend asked me which one I like better.
   pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
   |    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
   L_______________________ I hope the answer won't upset her. ____________
   
   
   

6. Re: Complex database for testing, U.S. Census Tiger/UA

   Your Name <cbbrowne@cbbrowne.com> — 2003-04-08T18:58:42Z

   Dustin Sallings wrote:
   > 	I think it was my first application I wrote in python which parsed
   > the zip files containing these data and shoved it into a postgres system.
   > I had multiple clients on four or five computers running nonstop for about
   > two weeks to get it all populated.
   > 
   > 	By the time I was done, and got my first index created, I began to
   > run out of disk space.  I think I only had about 70GB to work with on the
   > RAID array.
   
   But this does not establish that this data represents a meaningful
   "transactional" load.
   
   Based on the sources, which presumably involve unique data, the
   "transactions" are all touching independent sets of data, and are likely
   to be totally uninteresting from the perspective of seeing how the
   system works under /TRANSACTION/ load.
   
   TRANSACTION loading will involve doing updates that actually have some
   opportunity to trample on one another.  Multiple transactions
   concurrently updating a single balance table.  Multiple transactions
   concurrently trying to attach links to a table entry.  That sort of
   thing.
   
   I remember a while back when MSFT did a "enterprise scalability day,"
   where they were trumpeting SQL Server performance on "hundreds of
   millions of transactions."  At the time, I was at Sabre, who actually do
   tens of millions of transactions per day, for passenger reservations
   across lotso airlines.  Microsoft was making loud noises to the effect
   that NT Server was wonderful for "enterprise transaction" work; the guys
   at work just laughed, because the kind of performance they got involved
   considerable amounts of 370 assembler to tune vital bits of the
   systems.
   
   What happened in the "scalability tests" was that Microsoft did much the
   same thing you did; they had hordes of transactions going through that
   were well, basically independent of one another.  They could "scale"
   things up trivially by adding extra boxes.  Need to handle 10x the
   transactions?  Well, since they don't actually modify any shared
   resources, you just need to put in 10x as many servers.
   
   And that's essentially what happens any time TPC-? benchmarks reach the
   point of irrelevance; that happens every time someone figures out some
   "hack" that is able to successfully partition the work load.  At that
   point, they merely need to add a bit of extra hardware, and increasing
   performance is as easy as adding extra processor boards.  The real world
   doesn't scale so easily...
   --
   (concatenate 'string "cbbrowne" "@acm.org")
   http://cbbrowne.com/info/emacs.html
   Send  messages calling for fonts  not  available to the  recipient(s).
   This can (in the case of Zmail) totally disable the user's machine and
   mail system for up to a whole day in some circumstances.
   -- from the Symbolics Guidelines for Sending Mail