Thread: Performance Tuning
I wanted to check on my settings in my postgresql.conf file for a Dual Xeon 2.4 GHZ server with 6 GB of Ram.
I have max_connections set to 512, with shared buffers set to 1024. If I set this any higher, postgres will not start. But, it seems that this setting is not enough. Though the server runs fine, certain queries for reports are taking anywhere from 40 to 55 seconds, and the CPU is only topping out at 25%. Is there a way to make this better? My thinking is that if more CPU was utilized then the query would run faster.
Thank you!
"Darryl W. DeLao Jr." <ddelao@gmail.com> writes:
> I have max_connections set to 512, with shared buffers set to 1024. If I
> set this any higher, postgres will not start. But, it seems that this
> setting is not enough. Though the server runs fine, certain queries for
> reports are taking anywhere from 40 to 55 seconds, and the CPU is only
> topping out at 25%. Is there a way to make this better?
You really really want shared_buffers higher --- 10000 or so would be
reasonable. (Which PG version are you running? If 8.1 you might want
higher than that.)
Fix the kernel's SHMMAX setting to let you do this.
After that, you probably want to read the archives of the
pgsql-performance list a bit. You likely have a standard query-tuning
problem, but you've not said enough to let anyone help you.
regards, tom lane
Running ver 7.3.10 in RHEL 3.0 ES. If I change shared buffers, dont i have to change max connections as well?
On 2/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Darryl W. DeLao Jr." <ddelao@gmail.com> writes:
> I have max_connections set to 512, with shared buffers set to 1024. If I
> set this any higher, postgres will not start. But, it seems that this
> setting is not enough. Though the server runs fine, certain queries for
> reports are taking anywhere from 40 to 55 seconds, and the CPU is only
> topping out at 25%. Is there a way to make this better?
You really really want shared_buffers higher --- 10000 or so would be
reasonable. (Which PG version are you running? If 8.1 you might want
higher than that.)
Fix the kernel's SHMMAX setting to let you do this.
After that, you probably want to read the archives of the
pgsql-performance list a bit. You likely have a standard query-tuning
problem, but you've not said enough to let anyone help you.
regards, tom lane
On Fri, 2006-02-17 at 14:01, Darryl W. DeLao Jr. wrote: > Running ver 7.3.10 in RHEL 3.0 ES. If I change shared buffers, dont i > have to change max connections as well? You should update to a newer version of PostgreSQL if performance is important to you. 7.3 was ok, but 8.1 is light years ahead of it. No, the shared_buffers has to be at least twice as big as the max connections. It can be more than twice as big, that's just a minimum. Read this: http://www.varlena.com/GeneralBits/Tidbits/perf.html
The world rejoiced as ddelao@gmail.com ("Darryl W. DeLao Jr.") wrote:
> Running ver 7.3.10 in RHEL 3.0 ES. If I change shared buffers, dont i have to change max connections as well?
If you have enough connections, then that seems unnecessary.
The *opposite* would be true; if you change max connections, you might
need to change the size of the shared buffer...
At any rate, if you actually want substantive improvements in
performance, I would *highly* advise moving to a version of PostgreSQL
that is *way* newer, like version 8.1. There are *enormous* numbers
of improvements that affect performance between 7.3 and 8.1.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/lsf.html
"In the case of CAPP, an EAL4 evaluation tells you everything you need
to know. It tells you that Microsoft spent millions of dollars
producing documentation that shows that Windows 2000 meets an
inadequate set of requirements, and that you can have reasonably
strong confidence that this is the case." -- Jonathan S. Shapiro