As the subject says. A quite puzzling situation: we not only upgraded the
software, but also the hardware:
Old system:
PG 7.4.x on Red Hat 9 (yes, it's not a mistake!!!)
P4 HT 3GHz with 1GB of RAM and IDE hard disk (120GB, I believe)
New system:
PG 8.2.3 on Fedora Core 4
Athlon64 X2 4200+ with 2GB of RAM and SATA hard disk (250GB)
I would have expected a mind-blowing increase in responsiveness and
overall performance. However, that's not the case --- if I didn't know
better, I'd probably tend to say that it is indeed the opposite
(performance
seems to have deteriorated)
I wonder if some configuration parameters have somewhat different
meaning, or the considerations around them are different? Here's what
I have in postgresql.conf (the ones I believe are relevant) :
max_connections = 100
shared_buffers = 1024MB
#temp_buffers = 8MB
#max_prepared_transactions = 5
#work_mem = 1MB
#maintenance_work_mem = 16MB
#max_stack_depth = 2MB
max_fsm_pages = 204800
checkpoint_segments = 10
Here's my eternal confusion --- the kernel settings for shmmax and shmall:
I did the following in /ec/rc.local, before starting postgres:
echo -n "1342177280" > /proc/sys/kernel/shmmax
echo -n "83886080" > /proc/sys/kernel/shmall
I still haevn't found any docs that clarify this issue I know it's not
PG-specific,
but Linux kernel specific, or maybe even distro-specific??)
For shmall, I read "if in bytes, then ...., if in pages, then ....", and
I see
a reference to PAGE_SIZE (if memory serves --- no pun intended!);
How would I know if the spec has to be given in bytes or in pages?
And if in pages, how can I know the page size?? I put it like this to
maintain the ratio between the numbers that were by default. But I'm
still puzzled by this.
PostgreSQL does start (which it wouldn't if I put shmmax too low),
which suggests to me that the setting is ok ... Somehow, I'm extremely
uncomfortable with having to settle for a "seems like it's fine".
The system does very frequent insertions and updates --- the longest
table has, perhaps, some 20 million rows, and it's indexed (the primary
key is the combination of two integer fields). This longest table only
has inserts (and much less frequent selects), at a peak rate of maybe
one or a few insertions per second.
The commands top and ps seem to indicate that postgres is quite
comfortable in terms of CPU (CPU idle time rarely goes below 95%).
vmstat indicates activity, but it all looks quite smooth (si and so are
always 0 --- without exception).
However, I'm seeing the logs of my application, and right now the
app. is inserting records from last night around midnight (that's a
12 hours delay).
Any help/tips/guidance in troubleshooting this issue? It will be
much appreciated!
Thanks,
Carlos
--
