On 9/4/07, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> wrote: > A client is moving their postgresql db to a brand new Windows 2003 x64 > server with 2 quad cores and 32GB of RAM. It is a dedicated server to run > 8.2.4. And what does the drive subsystem look like? All that horsepower isn't going to help if all your data is sitting on an inferior drive subsystem. > The server typically will have less than 10 users. The primary use of this > server is to host a database that is continuously being updated by data > consolidation and matching software software that hits the server very hard. > There are typically eight such processes running at any one time. The > software extensively exploits postgresql native fuzzy string for data > matching. The SQL is dynamically generated by the software and consists of > large, complex joins. (the structure of the joins change as the software > adapts its matching strategies). > > I would like to favour the needs of the data matching software, and the > server is almost exclusivly dedicated to PostgreSQL. > > I have made some tentative modifications to the default postgres.config file > (see below), but I don't think I've scratched the surface of what this new > system is capable of. Can I ask - given my client's needs and this new, > powerful server and the fact that the server typically has a small number of > extremely busy processes, what numbers they would change, and what the > recommendations would be? > > Thanks! > > Carlo > > max_connections = 100 > shared_buffers = 100000 > work_mem = 1000000 Even with only 10 users, 1 gig work_mem is extremely high. (without a unit, work_mem is set in k on 8.2.x) 10000 would be much more reasonable. OTOH, shared_buffers, at 100000 is only setting it to 100 meg. that's pretty small on a machine with 32 gig. Also, I recommend setting values more readable, like 500MB in postgresql.conf. Much easier to read than 100000... > effective_cache_size = 375000 This seems low by an order of magnitude or two. But the most important thing is what you've left out. What kind of I/O does this machine have. It's really important for something that sounds like an OLAP server. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
