in reply to Architectural question...

You don't mention the database you are running on, but you might consider tuning your SQL too. You say you've 'hit a wall' so maybe you've already done all you can. However, if you haven't, some databases have automatic tuning tools and tuned SQL that can really improve performance. For example, there is a tuner in the TOAD package (not free) that you can run against Oracle.

You can also get help from SQL gurus. There are often tips for each database that dramatically improve performance by moving things around a bit.

If you've truly done all you can on the tuning front, then please disregard. :)

Replies are listed 'Best First'.
Re^2: Architectural question...
by devnul (Monk) on Jul 05, 2005 at 18:05 UTC
    Thank you for the timely comment.. :) The database is Postgres using the ltree extension. I've done all I can in the tuning side for now, I've fooled with various ways of doing the query, but its just a matter of the type of work to be done.

    - dEvNuL
[reply]

      But, as you said, you aren't doing the same kind of work in perl ... so it's likely that if you do the work differently in SQL it'll be faster. I'd put money on postgresql vs. perl for calculating a table of numbers from another set of numbers.

      Saying that it matters a _lot_ what version of postgresql you are using, so an upgrade to postgresql-8.0 could provide the speedup you need ... and an increase in other parts :).

      --
      James Antill
[reply]
      I am runing postgres 7.4. I don't see much on the Postgres site about performance improvements, except for the somewhat vague statement "This release has a more intelligent buffer replacement strategy, which will make better use of available shared buffers and improve performance.".

      The situation (on my development box) is one of *SEVERE* disk thrashing. Just doing a "select count(category),category from data" and I can hear the disk drive sounding like it is going to take off.

      While I agree that Postgres can summarize rows faster then I could ever hope to, it lacks the sophisticated cache configurations that are possible with other RDBMS (notably Sybase or Informix, which if I could use either of those here could probably make this problem a non-issue).

      That being said, I will give upgrading Postgres a shot, but I am not optimistic as it still relies on the operating system to cache files and thus there is no way to ensure certain tables have a higher caching priority.


      - dEvNuL
[reply]

In Section Seekers of Perl Wisdom