Lionel wrote: > Hello, > > I have to choose a dedicated server to host a big 8.3 database. > The global size of the database (indexes included) will grow by 40 Go every > year (40 millions of lines/year) > Real data (indexes excluded) will be around 5-7 Go/year. > I need to store 4 years of activity. > Very few simultaneous users (~4). > 100000 rows added every day via csv imports. > The application will be a reporting application. > Main statements: aggregation of 10000 to 10millions of line. > Vast majority will hit 300000 lines (90% of the connected users), few will > hit more than 10 millions (10% of the connected users, there may never be 2 > simultaneous users of this category). > 20sec - 30sec for such a statement is acceptable. > > It's quite easy to choose CPU (xeon quad core 2.66, maybe dual xeon), RAM > (8-12Go) but I still hesitate for hard disks. If the number of users is low or your queries are complex, a faster CPU with fewer cores will serve you better because PostgreSQL cannot split a single query across multiple CPUs/cores. It will also speed up your CSV imports (be sure to do them as a single transaction or with COPY). If you can bear the heat (and the electricity bill) you can get 3.4 GHz Xeons. Be sure to use a 64-bit OS and lots of memory (16 GB+). > Option 5) > RAID10 SATA2 4x250 Go Good enough. > Any other better option that I could ask for ? Yes, 8x250 :) You need as much drives as possible - not for capacity or reliability but for speed. Use RAID 5 or RAID 6 only if the database isn't going to be updated often (for example, if you add records to the database only several times a day, it's ok). > What would be the best choice in case of an external USB drive : using it > for indexes or x_log ? Skip any USB-connected drives for production environments. It's not worth it. If new data isn't going to be added to the database continuously, you don't need a separate x_log. Better use the drive as a (hot, if possible) spare for RAID in case one of the RAID drives malfunctions.
Attachment:
signature.asc
Description: OpenPGP digital signature
