Postgresql tuning ideas

I'll start on the ideas on a weekly or longer basis (tracking via my issues rss), as I'm not too knowledgeable on psql atm. Should be fun to see performance improvements. :)

First step, connect via socket.

http://drupal.org/node/26836

pg_hba.conf could look like this:

# Database administrative login by UNIX sockets
local all postgres ident

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local databasename databaseuser trust

2nd step, do not use anything other than C collation, C type, to enable the use of all available indexes:

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
drupaldatabase | drupaldbuser | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres
: postgres=CTc/postgres

2nd step in this case would mean a patch to convert collation and Ctype from other locales to C.

If installing 8.4 psql and drupal 6.20 from scratch as root

pg_dropcluster --stop 8.4 main

pg_createcluster --locale=C --encoding=UTF-8 --start 8.4 main

A command could be available after typing su postgres [then type] psql (must use semicolon to finish statements in psql terminal), but I'm not sure of that, since I used the command from the command prompt.

Aside from using indexes, for faster query lookups, another category is autovacuum management, that's usually done by postgresql.conf

I set mine up as follows, until I get more info on tuning it with my resource environment.
autovacuum = on
autovacuum_max_workers = 4 # max number of autovacuum subprocesses
autovacuum_naptime = 15min # time between autovacuum runs

This takes care of getting rid of temporary data used for locks.

Getting stats from postgresql will allow benchmarking.

Set default_statistics_target = 100 (postgresql.conf) - Jim Nasby, Greg Smith, Robert Treat, Christopher Browne, et. al.

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

That page has some suggestions that are actionable, but not for all configuration variables, as it will depend on total ram and traffic. Benchmarks would help out here.

On a VPS, I'm getting tps around 500 after tuning; at initial tuning, tps was around 40 to 230:

/usr/lib/postgresql/8.4/bin/pgbench -c 20 -t 200 pgbouncerbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 4 [ my database size, and new drupal database size around this scale ]
query mode: simple
number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 561.092604 (including connections establishing)
tps = 571.121961 (excluding connections establishing)

This isn't anywhere close to a dedicated machine with more ram. I'm at 512mb system ram. synchronous_commit = off gave the biggest boost in the environment.

http://pgfoundry.org/projects/pgmemcache/ is an option for modules such as dbtuner.

Used pgbouncer, but I'm looking into keeping the database connection persistent with apache2.