On Thu, 22 Apr 2004, Pallav Kalva wrote: > Hi > > We are in the process of building a new machine for our production > database. Below you will see some of the harware specs for the machine. > I need some help with setting these parameters (shared buffers, > effective cache, sort mem) in the pg_conf file. Also can anyone explain > the difference between shared buffers and effective cache , how these > are allocated in the main memory (the docs are not clear on this). > > Here are the Hardware details: > Operating System: Red Hat 9 > Database Ver: Postgres 7.4 > CPU'S : 4 > RAM : 4 gig > Datafile layout : RAID 1+0 > Transaction log : on different RAID1 Array > RAID Stripe Size: 8k
Read this first: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Basically shared buffers are the "play area" for the database backends to toss data in the air and munge it together. The effective cache size reflects the approximate amount of space your operating system is using to buffer Postgresql data. On a dedicated database machine this is about the same as the size of the kernel buffer shown in top. On a mixed machine, you'll have to see how much of what data is getting buffered to get a guesstimate of how much kernel cache is being used for pgsql and how much for other processes. Then divide that number in bytes by 8192, the default block size. On a machine with 1.2 gigs of kernel cache, that'd be about 150,000 blocks. Buffer sizes from 1000 to 10000 blocks are common. Block sizes from 10000 to 50000 can somtimes increase performance, but those sizes only really make sense for machines with lots of ram, and very large datasets being operated on. ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
