Skip to Content.
Sympa Menu

pcplantdb - Re: [pcplantdb] PIW Relationships Modelling

pcplantdb@lists.ibiblio.org

Subject: pcplantdb

List archive

Chronological Thread  
  • From: Sean Maley <semaley@yahoo.com>
  • To: john@eco-living.net, pcplantdb <pcplantdb@lists.ibiblio.org>
  • Subject: Re: [pcplantdb] PIW Relationships Modelling
  • Date: Fri, 22 Jul 2005 10:39:58 -0700 (PDT)



--- John Schinnerer <john@eco-living.net> wrote:
> Moi,
>
> Haven't read the rest of this thread but if there
> are concerns in terms of search overhead and how
> long it will take in real time to return search
> results, I have no concerns over 'inefficient' but
> otherwise attractive implementations/algorithms at
> this point or for the considerable future.

Sounds familiar to me. If plant data was the only
entity concept the system handled, then this is
generally correct. I think this discussion went down
the plant relationship path instead. Plants have lots
of needs and produce many things. For this to be
analyzed, the five other kingdoms come into the
picture with even more inorganic elements and
environmental factors. The cartesian product between
a million of them and the million they could be
associated may not be what happens, but I bet you will
have more than you thought. Even still a good index
will still get you through this trillion records
efficiently in any case, but the discussion even
further focused on defining the underlying data in
hierarchical tables which are fully normalized. As
you move from 100 test records to the expected:

1> select count(*) from itis_taxonomic_units
2> go
itis_taxonomic_units
--------------------
376885

just from these kingdoms:

1> select * from itis_kingdoms
2> go
kingdom_id kingdom_name update_date
---------- ------------ -------------------
1 Monera Mar 26 1996 12:00AM
2 Protozoa Jun 4 2004 12:00AM
3 Plantae Mar 26 1996 12:00AM
4 Fungi Mar 26 1996 12:00AM
5 Animalia Mar 26 1996 12:00AM
6 Chromista Jun 4 2004 12:00AM

select count(*) from usda_food_desc
usda_food_desc
--------------
6840

select count(*) from usda_nutrient_data
usda_nutrient_data
------------------
455905

> We should consider scalability of
> implementation/algorithms in terms of data growth
> (both amount and type or nature of data) for sure.

But then you need to decide if you are in the business
of a living things repository or a living things
relationships repository.

> Our level of traffic\use is not IMO going make
> slightly or even significantly 'inefficient'
> searching an issue, even in several years or
> maybe even a decade from now (hardware gets faster
> too... ;-).

If the repository doesn't heavily record
relationships, which differing groups compete to
maintain authority (what about the N versions of the
otherwise same relationship?). It is otherwise true
that not many will be trying to contribute data as it
stands today. There might be more interest as
permaculture is realized as a local food solution.

> A few seconds' difference in real time is not
> significant. Fifteen or thirty seconds is, from an
> end-user usability standpoint.

I see people get upset over a few seconds, but that is
just the industry I am in. In the real world, any
query taking more than a second better have a reason
and any query over ten should be banned. Consider
that a resource on the computer is pegged when this
occurs, usually a CPU. This is fine when you have
enough CPU to handle the load, but inevitably a quiet
reason users don't use the system anymore as the query
time creeps up.

> Does anyone really think we will be dealing with
> more than a few *fractions* of a second at the
> server-engine itself, based on these different
> implementation options?

Yes, just not for looking for a specific organism. It
is the analysis of what organisms thrive together in a
community that matters.

> Seems unlikely to me.
> If you want to convince me otherwise, some
structured
> testing will have to be done to measure actual
> search-transaction times and show orders of
> magnitude of difference early on.

We can start with a schema to try first. Otherwise,
your computer or mine? I only have one CPU, so if you
can get access to a multi-CPU system, I do this sort
of benchmarking in my day job. I'll be happy to
contribute some scripting; sorry no Python, I'll stick
to Perl or even a bash script is fine. What profiling
tools are available for MySQL? I'm coming in from
Sybase land, so I'm used to analyzing between volume
groups and CPU usage depending on database size or
simply just the optimizer plan and looking for a
missed index.

If all you want is to look up a list of plants, then
forget about performance over the next several years.
Even the nearly ten thousand plants in the eden DB
could pose a threat, but not if you just want a bunch
of added comments to search through.


-Sean.

I'll throw in some queries from my baseball database
for good effect next time.



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs





Archive powered by MHonArc 2.6.24.

Top of Page