Re: Speed Question - Mailing list pgsql-performance

Thanks for the help.  We've been using MySQL for the last 4 years, so
PgSQL is a whole new world for me.  Lots to learn

Actually the "real world" test we are performing is an exact
duplication of our intended use.  Without divulging too many company
secrets, we create a 32 key profile of an object.  We then have to be
able to search the database to  find "similar" objects.  In reality, we
will probably have 20MM to 30MM rows in our table.  I need to very
quickly find the matching records on a "test" object.

If you're really curious as to more details, let me know (I don't want
to bore the group with our specifics)

Since this machine is solely a database server, I want to utilize a ton
of RAM to help things along.  Probably at lease 1.5 Gigs worth.  I
guess my next step is to try and figure out what all the various memory
settings are and where to set them.

Thanks,

-N


On Saturday, December 21, 2002, at 07:21  AM, Manfred Koizar wrote:

> On Fri, 20 Dec 2002 19:10:49 -0500, Noah Silverman
> <noah@allresearch.com> wrote:
>> Now we are creating more of a real world example: 10MM rows with 32
>> columns of integers.  I'm loading up the data now, and will create a
>> multi-column index(on all 32) after the data is loaded.
>
> If a table with a 32 column key and no dependent attributes is a real
> world example, I'd like to see your use case ;-)
>
> An index on c1, c2, ..., cn will only help, if your search criteria
> contain (strict) conditions on the leading index columns, e.g.
>     WHERE c1 = ... AND c2 = ... AND c3 BETWEEN ... AND ...
>
> It won't help for
>     WHERE c22 = ...
>
>> From everyone's responses I understand that we really need to tune
>> [...]
>> 2x AMD 2100MP CPU
>> 2 GB RAM
>> Data - 350GB on a raid5 card
>
> It all depends on your application, but looking at SHARED_BUFFERS,
> EFFECTIVE_CACHE_SIZE, SORT_MEM, MAX_FSM_RELATIONS, and MAX_FSM_PAGES
> might be a good start.  Later you might want to use CPU_*_COST,
> RANDOM_PAGE_COST, and various WAL settings to fine tune your system.
>
>> Note: We will probably NEVER use transactions,
>
> Oh yes, you will.  You have no other choice.  If you don't enclose
> (several) statements between BEGIN and COMMIT, every statement is
> automatically wrapped into its own transaction.
>
> It helps performance and consistency, if *you* control transactions.
>
> Servus
>  Manfred
>
>