On Fri, Apr 8, 2011 at 4:00 AM, Gipsz Jakab <clausewit...@gmail.com> wrote:
> My question is the following: if this is a dedicated database server, with > maximum 30 users (but they are using ODBC with Microsoft Acces, and each of > them generating 4-6 connection at the same time), and other 200 people will > use this server through drupal, php, apache not in daily basis, but weekly, > what is the ideal memory configuration? > if it is a dedicated DB server, then give shared memory about 1/4 of the RAM, and perhaps a slightly larger maintenance work mem. depending on your workload you may want to increase the checkpoint segments (if write-mostly, then add more segments). Here is what I use on my FreeBSD 8, Pg 9 big-memory servers. these have 24GB or more of RAM and are attached to SSD external storage for the database: max_connections = 200 shared_buffers = 5120MB work_mem = 512MB maintenance_work_mem = 1024MB max_stack_depth = 8MB vacuum_cost_delay = 15checkpoint_segments = 64 checkpoint_timeout = 15min checkpoint_completion_target = 0.8 random_page_cost = 1.0 # RAM disk. set equal seq_page_cost effective_cache_size = 6400MB # shared_buffers + `sysctl -n vfs.hibufspace` / 8192 (BLKSZ) for individual complicated queries, you can increase the sort mem and work mem on a per-connection basis as needed. > > After the settings in the postgresql.conf our system is much faster, and no > more error messages in the postgres.log, but If I try to drop a table, or > add a new one, our system is stopping, until I kill the process, which is > dropping or adding a table. > Is something else using the table you want to drop and blocking the drop statement from taking the locks it needs? It should be fairly instant.
