Re: Requirements for a database server - Mailing list pgsql-admin
From | Robert.Farrugia@go.com.mt |
---|---|
Subject | Re: Requirements for a database server |
Date | |
Msg-id | OF2C1441C4.EFD4F752-ONC1256A8E.001D7B4C@go.com.mt Whole thread Raw |
In response to | Requirements for a database server (Robert.Farrugia@go.com.mt) |
List | 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 by date: