Re: Would my postgresql 8.4.12 profit from doubling RAM?

Hello Scott and others,

On Sat, Sep 29, 2012 at 9:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sat, Sep 29, 2012 at 11:27 AM, Alexander Farber
> <alexander.farber@gmail.com> wrote:
>> I've finally doubled up RAM to 32 GB for my Quad core
>> CentOS 6.3 server and have changed postgresql.conf to
>>
>>    max_connections = 100
>>    shared_buffers = 4096MB
>>   work_mem = 16M
>>
>> http://serverfault.com/questions/433281/doubled-up-ram-to-32-gb-now-how-to-speed-up-a-lapp-server
>
> I'd suggest turning on persistent connections because you DO use
> pgbouncer.  It'll reduce connection time and give slightly better
> performance.  But from reading that page, I don't think you've given
> us (or yourself really) enough data to tell you how to improve
> performance.
>
> The first thing to do is some simple performance profiling in your php
> script.  Just add error_log() or whatever it's called in php, with
> some timing info in them to see where your time is being spent.  If
> it's mostly on the db side, we head there, if it's mostly in the php
> we look there.  At first just put in a couple statements throughout
> your script (include things like pid etc so you can trawl your logs
> for this later) to get an idea where in general you're spending your
> time.  Once we get a handle on where most of it is going we'll go from
> there.

actually that's what I tried yesterday,
right after the server was upgraded -

I've set in postgresql.conf

   max_connections = 600

(to match the 500 MaxClients in httpd.conf)
and then in /etc/php.ini

   pgsql.allow_persistent = On

and added the ..., array(PDO::ATTR_PERSISTENT => true);
to my PHP scripts -

and suddenly my scripts stopped fetching any
data from the database, were only returning empty values...

I have to retry this with d/b logs on...

About not giving enough information -
how much information do you want?
If I list all my databases + source code
of the scripts, I doubt anyone will read my mail.

I still hope that someone will mention
a cool way to make a picture of bottlenecks
of my PostgreSQL database - like select * from pg_smth
and then see what is it waiting for...

Thanks
Alex