On Tue, Oct 6, 2015 at 3:33 AM, FattahRozzaq <ssoorruu@gmail.com> wrote:
> @Merlin Moncure, I got the calculation using pg_tune. And I modified
> the shared_buffers=24GB and the effective_cache_size=64GB
>
> @Igor Neyman,
> Yes, I had performance problem which sometimes the response time took
> 11ms, with the exactly same query it took 100ms, and the response time
> seems randomly fluctuating even with the exact same query.
>
> Any idea on how I should configure postgres to effectively utilize the
> hardware and reduce the response time to be quicker?
> *(RAM=128GB, CPU=24cores, RAID-1+0:SSD)
OK I'm gonna copy and paste some stuff from previous messages since
top-posting kinda messed up the formatting.
First, this line:
>> max_connections = 9999
When you are experiencing this problem, how many connections are
there? There's a bell shaped curve for performance, and the peak is
WAY less than 9999. The IPC / shared memory performance etc will drop
off very quickly after a few dozen or at most a hundred or so
connections. If your application layer needs to keep more than a
couple dozen connections open, then it's a REAL good idea to throw a
connection pooler between the app and the db. I recommend pgbouncer as
it's very easy to setup.
BUT more important than that, it appears you're looking for a "go
faster" knob, and there may or may not be one for what you're doing.
I'd recommend profiling your db server under load to see what's going
on. What does iostat, iotop, top, etc show you when this is happening?
Are you running out of IO? Memory, CPU? What does "explain analyze
slowquerygoeshere" tell you?
I would recommend you consider reducing shared_buffers unless you have
some concrete proof that 24GB is helping. Big shared_buffers have
maintenance costs that affect write speeds, and slow writing can make
everything kind of back up behind it. Typically something under 1GB
is fine. PostgreSQL relies on the OS to cache most read data. So
trying to crank up shared_buffers to do the same job is often either
counter-productive or of no real gain.