Re: Requirements for a database server

Поиск
Список
Период
Сортировка
От Robert.Farrugia@go.com.mt
Тема Re: Requirements for a database server
Дата
Msg-id OF2C1441C4.EFD4F752-ONC1256A8E.001D7B4C@go.com.mt
обсуждение исходный текст
Ответ на Requirements for a database server  (Robert.Farrugia@go.com.mt)
Список pgsql-admin
I'm using the latest version 7.1.2 (athough I started with 7 and then
migrated to 7.1).

Problem is that we have few main tables which continue to grow as against
having a large number of tables.  As I see it, the more data you have in a
table, the longer the query will take proportionally (even though you have
indexes).  A good solution I found out, is to use a divide and conquer
approach.  Normally I the queries depend on fixed periods of time (days or
months).  What I do is run a query for an hour, then the next hour and so
on for the period of time and collate the results together.  In this case I
normally use JDBC.  This is because queries for small periods of time are a
lot faster than for whole weeks or months ( I think it has to do with the
amount of data to go through).

Another thing I noticed is that if you do a query which uses only two
fields of index, this is not normally used (sequential search is then
used).  I do not know if this is a bug or it is how it works.  Could also
be that I do not do vacuums alot since it takes a lot of time.  As regards
to better vacuums from the ones of 6.5.x I concur.  With version 7.0,
vacuum used to crash a lot, but now it is more stable (keeping fingers
crossed, vacuum has never crashed with 7.1.2).  I don't know if there is a
way to vacuum analyze a table without doing the actual vacuum, which will
not lock the tables.

On another note, what system hardware do you use for storage of terabytes
of data ?  We were considering external harddisk racks (fibre channels) ?
What RAID is most useful in these cases, 0+1 or 5 ?  Does the OS make a
difference, for e.g. Linux as against Solaris in these cases ?

Thanks alot
Robert




                    "Ross J.
                    Reedstrom"           To:     Robert.Farrugia@go.com.mt
                    <reedstrm@ric        cc:     pgsql-admin@postgresql.org
                    e.edu>               Subject:     Re: [ADMIN] Requirements for a database server

                    18/07/2001
                    17:10






Robert -
You mention having a PostgreSQL database in production for around a year.
May I ask what version it is? I'm guessing you're still at 6.5.X,
in which case, I'd suggest an upgrade to 7.1.2, before doing anything
more drastic.  Not only are there bug fixes, many improving stability
of the system, there are also performance enhancements, particularly in
scalability. Without knowing more details of your schema and applications
(typical queries, table sizes, etc.) we can't be of much more help.

BTW, we've had  many reports of multi-gigabyte systems performing well,
and recently a report of a multi-TERA-byte system!

There have been lots of vacuum changes, as well: Bruce and Tom have made
VACUUM much friendlier to a live system since 6.5.x.

Ross

On Wed, Jul 18, 2001 at 07:52:37AM +0200, Robert.Farrugia@go.com.mt wrote:
> Hi,
>
> I have been using postgres for the last year now.  The database has grown
> from a mere few MBs to over 100GB data and expected to top up 300GB by
the
> end of the year.  Lately performance of queries, inserts, updates has
> continued to grow worse as the dataset has grown larger, even though most
> queries have indexes on them, while vacuuming the database has become a
> nightmare.  For this reason we were thinking of upgrading the system
> hardware.
>
> Is there anyone out there who has databases of this sort ?  Can he point
me
> out some basic requirements for these sort of databases (processors, ram,
> storage, etc) ?  Also would it be worthwile to migrate to another
database
> system which is more robust with this kind of amount of data (such as
> oracle) ?
>
> Thanks
> Robert





В списке pgsql-admin по дате отправления:

Предыдущее
От: Mike Finn
Дата:
Сообщение: Re: [GENERAL] A small question about Red Hat
Следующее
От: Robert.Farrugia@go.com.mt
Дата:
Сообщение: Re: Requirements for a database server