#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    110
    Rep Power
    54

    Smile PostgreSQL configuration on high-end machine


    Hi..

    I need som help setting up PostgreSQLs memory-configuration on my newest toy - a Dell PowerEdge with 2 cpus (4 cores) and 32 Gig of ram.

    The database contains +200 tables, and +5.000.000 rows in the biggest table. I wild guess is that perhaps 50.000 rows are changed / inserted / deleted per day. We practise a lot of joins and otherwise complex querries. Sometimes, we need to do "order by" and "group by" with sets of 200.000 rows. And all this must be done very fast, since it's used by a web-application.

    What do you consider to be the best settings for work_mem, shared_buffers, temp_buffers, maintenance_work_mem, etc etc.

    Anyone running on a similar and want to expose their settings in the postgresql.conf file?

    Thanx

    Robert Bengtsson
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    110
    Rep Power
    54
    Yes. Thank you. That was most interesting reading. Most greatful =)

    Anyone that wants to share their memsettings in postgresql.conf for high-end servers?


    Thanks in advance

    /robert
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    PostgreSQL has a specific "performance" mailing list, you might be interested in posting there also.
    We would also love to hear from you about your experience.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    These might be interesting as well:
    http://www.revsys.com/writings/postgresql-performance.html
    http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/

    The second one is also available as a PDF:
    http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance.pdf
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    What OS are you using?

    You will need to play with and balance a lot of strategies to get best performance, but I would say the starting place is your disk/filesystem setup:

    - RAID 0+1, if possible, rather than RAID5, and even more so, try to have a complete RAID system dedicated only to your data, with a separate disk or RAID array for the OS and other data.

    - Please tell me you are using SCSI and not SATA.

    - You would want to consider keeping a separate disk or array just for your WAL (write-ahead log), and begin toying with Tablespaces to separate your most heavily-used tables onto different drives/partitions/arrays.

    Then, spend some serious time reading this annotated guide to postgresql.conf. It's not just a question of putting every memory-related setting up to the max, because each impacts the other, and depends on your usage patterns.

    Generally, look around at www.powerpostgresql.com

    Comments on this post

    • pabloj agrees
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    rycamor, thoughts on this from Wikipedia regardin RAID 0+1
    With increasingly larger capacity disk drives (driven by serial ATA drives), the risk of drive failure is increasing. Additionally, bit error correction technologies have not kept up with rapidly rising drive capacities, resulting in higher risks of encountering media errors. In the case where a failed drive is not replaced in a RAID0+1 configuration, a single uncorrectable media error occurring on the mirrored hard drive would result in data loss.

    Given these increasing risks with RAID0+1, many business and mission critical enterprise environments are beginning to evaluate more fault tolerant RAID setups that add underlying disk parity.
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    True, but it does depend on the error of not immediately replacing a disk. I would only consider RAID 10 a starting point. As the article notes,
    RAID 10 is often the primary choice for high-load databases, because the lack of parity to calculate gives it faster write speeds.
    Right under the RAID 10 section we see RAID 30:
    Advantages

    RAID 30 can sustain one to four drive failures while maintaining data integrity if each failed disk is in a different RAID 3 array.

    Offers highest level of redundancy and performance

    Disadvantages

    Very costly to implement
    It all depends on which tradeoffs you're willing to make. At the simple (cheap) level, if performance isn't an absolute premium, you might want to stick with RAID5 on SATA drives. Just understand that your write-intensive performance will probably not be much faster than a single SATA drive. If you need performance but are willing to put the effort into constantly monitoring for disk failure, then RAID 10 isn't such a bad choice, especially if you keep your WAL on a separate RAID 5 array for point-in-time recovery.

    Also, remember this: SATA drives cannot be trusted to truly disable their write cache for fsync calls. Meaning, there is always the possibility of losing data without knowing it. Another reason I still stick with SCSI for important data.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    If you need performance but are willing to put the effort into constantly monitoring for disk failure ...
    Shouldn't the controller handle notifications?
    Also, remember this: SATA drives cannot be trusted to truly disable their write cache for fsync calls. Meaning, there is always the possibility of losing data without knowing it. Another reason I still stick with SCSI for important data.
    Where do SAS drives fit into this statement?
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.
  18. #10
  19. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally Posted by jharnois
    Shouldn't the controller handle notifications?
    Of course, but in the case you mentioned above with RAID 10, the reason someone would lose data would be either a) ignoring the controller notification, thus not replacing the failed drive, and THEN having a the associated mirror drive fail also, or b) two mirrored-pair drives go bad at the same time, which is unlikely, but I suppose possible. Of course, it is possible with any RAID configuration to somehow lose enough drives that there is data corruption. A good EMP going of in the vicinity might do that .

    Originally Posted by jharnois
    Where do SAS drives fit into this statement?
    I honestly don't know much about SAS drives at the moment. Most of my work these days is with FreeBSD/PostgreSQL on smaller network appliance systems, so I haven't stayed up with high-end trends. They certainly look interesting, but as with any new storage technology, I'd probably wait and see how stable it is in the long-term for mission-critical data. I have SCSI drives that have kept on chugging for years without a single problem. Every time I have tried to rely on ATA/IDE or some other technology, I have been sorry in the end.

    But I would *love* to try some SAS drives on a workstation, just to see how fast KDE starts up .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  20. #11
  21. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    I have SCSI drives that have kept on chugging for years without a single problem. Every time I have tried to rely on ATA/IDE or some other technology, I have been sorry in the end.
    Well, my understanding is that SAS drives are the SCSI equivalent of SATA drives, in simplist terms of course. That parts decided on anyway; we're getting SAS drives.

    I'm just trying to determine the best RAID level for my database server. I don't think they'll go for RAID 30. Would you recommend RAID 10 over RAID 0+1? I'm looking for a performance increase over RAID 5 while maintaining some level of redundancy; these servers must be available. Cost is low on the priority list.
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I suppose RAID 10 (1+0) is the safer choice, although I wonder if the "stripe of mirrors" rather than "mirror of stripes" approach makes it more processor-intensive. Anyway, it seems like the better bet for robustness, without the drawbacks of RAID 5.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    110
    Rep Power
    54
    Hi rycamor

    I use the following configuration (using SCSI):

    Virtual Disc 0: 2 discs with RAID 1 => 126 GB (and another disc as "hotspare") - Here we have OS and some databases that are not that big and performance demanding

    Virtual Disc 1: 6 discs with RAID 1+0 => 380 GB (and another disc as "hotspare") - Here we have the main databases (controlled with tablespaces)


    The database will handle something like 60-130 concurrent "sessions" (i.e. 1-4 querries per second).


    My postgresql.conf file has currently the following setup:

    max_connections = 400
    shared_buffers = 60000
    work_mem = 128024
    maintenance_work_mem = 128024
    max_fsm_pages = 40000
    max_fsm_relations = 2000

    But we are not finished tweeking theese yet.


    Correct me if I'm wrong, but since we do not utilize transactions, we dont have to worry about the WAL-log? Anyway, today the WAL-log is written to virtual disc 1, which problably is a bad idea.


    Best Regards
    Robert Bengtsson
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by coredev
    Correct me if I'm wrong, but since we do not utilize transactions, we dont have to worry about the WAL-log?
    As soon as you make updates to your data, you are using transactions. There is no way around that.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    110
    Rep Power
    54
    Oh..

    So.. I'd be better of moving the WAL to Virtual Disc 0?

    I do this by making the directory pg_xlog to a link that points to a directory on the Virtual Disc 0, right?

    Thanks in advance..

IMN logo majestic logo threadwatch logo seochat tools logo