Re: PostgreSQL + FreeBSD memory configuration, and an issue

[Gipsz Jakab <clausewitz45@xxxxxxxxx>]

Thanks Vick, I'll try it tonight. I will give 1024 shared_buffers and maintenance_work_mem, and 102 MB of work_mem.

A question: I didn't use (it's marked with #) the effective_planner (or any other planner method or config option). Is it ok, when I turn it on with that parameter: 1036MB?

DROP/ADD TABLE stuck: I realized, that the locks number is so high, what about these settings:

deadlock_timeout = 1s

max_locks_per_transaction = 64 

is it ok? or is it too high?

Thanks,

Carl

2011/4/8 Vick Khera <vivek@xxxxxxxxx>

On Fri, Apr 8, 2011 at 4:00 AM, Gipsz Jakab <clausewitz45@xxxxxxxxx> 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.