I'm trying to optimize a resume search engine that is using Tsearch2
indexes. It's running on a dual-opteron 165 system with 4GB of ram
and a raid1 3Gb/sec SATA array. Each text entry is about 2-3K of
text, and there are about 23,000 rows in the search table, with a goal
of reaching about 100,000 rows eventually.
I'm running Ubuntu 6.06 amd64 server edition. The raid array is a
software-based linux array with LVM on top of it and the file system
for the database mount point is XFS. The only optimization I've done
so far is to put the following in /etc/sysctl.conf:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
And in postgresql.conf I set the following parameters:
shared_buffers = 131072
work_mem = 65536
max_stack_depth = 4096
max_fsm_pages = 40000
max_fsm_relations = 2000
These probably aren't ideal but I was hoping they would perform a
little better than the defaults. I got the following results from a
pgbench script I picked up off the web:
CHECKPOINT
===== sync ======
10 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 632.146016 (including connections establishing)
tps = 710.474526 (excluding connections establishing)
Once again I don't know if these results are good or not for my hardware.
I have a couple of questions:
- Does anyone have some good advice for optimizing postgres for
tsearch2 queries?
- I noticed that there are six different postmaster daemons running.
Only one of them is taking up a lot of RAM (1076m virtual and 584m
resident). The second one is using 181m resident while the others are
less than 20m each. Is it normal to have multiple postmaster
processes? Even the biggest process doesn't seem to be using near as
much RAM as I have on this machine. Is that bad? What percentage of
my physical memory should I expect postgres to use for itself? How
can I encourage it to cache more query results in memory?
Thanks in advance for your time.
Carl Youngblood