Upgraded to 8.2.3 --- still having performance issues
От | Carlos Moreno |
---|---|
Тема | Upgraded to 8.2.3 --- still having performance issues |
Дата | |
Msg-id | 45E5B7D2.5020306@mochima.com обсуждение исходный текст |
Ответы |
Re: Upgraded to 8.2.3 --- still having performance issues
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Upgraded to 8.2.3 --- still having performance issues (Dennis Bjorklund <db@zigo.dhs.org>) |
Список | pgsql-performance |
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 --
В списке pgsql-performance по дате отправления: