annotated postgresql.conf

#**121**

Laurenz Albe ha scritto:
[CUT]

Quote:

no, relations are going to be very very less than one million

probably not more than one hundred (I have 35 tables on the db in
development), plus system tables. I set it, again, to an unreasonable
value to be sure it will never reached

Again, this will not cause a problem except waste of memory.
I think you can safely reduce it a little nevertheless...

will do in production, and will follow your advice to look results of
vacuum verbose

Quote:

If you know that you will have less than 100 connections, reduce
max_connections!
I'll leave it on defaults because the frontend will be ajax based. I am
thinking about to use Apache DBI but not sure (I use Perl and mod_perl,
but I never used Apache DBI before, and I dont know if it is valuable
for connection management, need to investigate on this)

Does that mean that the limit of database connections is the limit on web
server threads? Excuse my silly question, but I don't know much about
that. You should definitely find out what that limit is, because if it
is less than 100, you'll waste memory on the database server side, and if
it is more, some connections from the application server might be
refused.

it is all right, in default Apache configuration you have 150 maximum
concurrent clients (2 servers x 75 threads), so I should put the
Postgres max_connection to that limit. I found that Apache DBI module
isn't probably ready for mod_perl 2 and that mod_dbd is too complex to
manage... so for now I won't use connection pooling (I don't need it
because of low traffic, anyway)

Quote:

You will probably find the following setting in your postgresql.conf:

# autovacuum = on
Yes it is so. Commented too. So values shown on commented lines are
also the defaults?

Yes.
The commented lines you find in postgresql.conf indicate the default values
(unless you changed them of course).

In the server log file.
You set log_destination to "stderr", logging_collector to "on", and
log_directory should contain the directory where you want the log files
to be written.
mmm... it set it as following:

log_directory = '/var/log/postgres/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Looks fine.

do I need / can I change permissions/owner on the that directory? what
process and which user are responsible to write pg logs?

The one that calls itself "postgres: logger process" in the "ps" list.
The directory must be writable for the user who owns that process
(usually postgres).

Permissions should be "rwx" for postgres and none for anybody else.
You can be more lax if you are not afraid that the "enemy" can get
or delete valuable information about your PostgreSQL server.

The server wrote one time on that directory but fails to restart till I
keep logging turned on.

You can start the server in --single mode and set "silent=off" in
postgresql.conf, then it will tell you what the problem is.

Make sure that the postgres user can create files in that directory,
delete all files that are already there, then it should work.

problem resolved by leaving defaults, but will take note of advices for
future experiments

Quote:

I also read in the pg docs that syslog use is
not recommended, expecially no Linux (I was thinking to use syslog).

Really? I couldn't find that.

I think the only problem is that syslog needs extra steps to configure,
which may make it a good choice only for people who know how to configure
it.

I saw something (probably postgres) changed directory permissions with
sticky bit and changed group so:

drwxrwxr-t 2 root postgres 4096 2008-10-28 10:02 postgresql

That should work as long as user postgres is in group postgres and
the log file does not already exist and is owned by a different user.

indeed it wasn't working

Quote:

It is quite permissive though.

at the end I noticed that in this system the pg data home is under
/var/lib/postgresql/, so I leaved the relative default path and it
worked out, of course that directory is owned by postgres

#log_directory = 'pg_log'

-rw------- 1 postgres postgres 278 2008-10-28 10:41
postgresql-2008-10-28_104143.log

Sure, there's nothing wrong with that.

Except that - if there is excessive logging - the log files could fill up
your data directory and the server would stop working.

I have 800 Mbytes used over 140 Gbytes, I can sleep quiet for now

Quote:

ok, seems to have understood, so I'll have that memory (*work_mem)
allocated for each process-connection, and all processes will share the
shared_buffers memory amount too, right?

Yes. The shared memory is - erm - shared by all PostgreSQL processes.

The only inaccuracy is my fault, because I oversimplified.
The following sentence from the documentation:

Note that for a complex query, several sort or hash operations
might be running in parallel; each one will be allowed to use
as much memory as this value specifies before it starts
to put data into temporary files.

means that one client running a difficult query can use work_mem
several times. But probably not all clients at the same time, so I
guess it is a reasonable estimate.

ok, in my case I am still largely under the available RAM so it is ok
(indeed I can/should increase memory allocated by shared_buffers from 2
to 4 Gbytes, but don't want to exaggerate)

Quote:

Read the server log regularly, particularly in the beginning.
Frequently you will find performance tuning hints there, like a warning
that you should increase checkpoint_segments (if you have a lot of
changes on your database).
do I have to turn it to debug<n> level to have more details?

The default settings should be good.

thank you very much for all the hints and explanations, very appreciated.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

annotated postgresql.conf

comp.databases.postgresql comp.databases.postgresql

Re: annotated postgresql.conf - 10-30-2008 , 06:18 PM