I'm not clear whether you still have a problem, or whether the
changes you mention solved your issues. I'll comment on potential
issues that leap out at me.
Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
> 3. we use xfs and our controller has BBU , we changed barriers=1
> to barriers=0 as i learnt that having barriers=1 on xfs and fsync
> as the sync method, the advantage of BBU is lost unless barriers
> is = 0 (correct me if my understanding is wrong)
We use noatime,nobarrier in /etc/fstab. I'm not sure where you're
setting that, but if you have a controller with BBU, you want to set
it to whichever disables write barriers.
> max_connections = 300
As I've previously mentioned, I would use a connection pool, in
which case this wouldn't need to be that high.
> work_mem = 4GB
That's pretty high. That much memory can be used by each active
connection, potentially for each of several parts of the active
query on each connection. You should probably set this much lower
in postgresql.conf and boost it if necessary for individual queries.
> effective_cache_size = 18GB
With 32GB RAM on the machine, I would probably set this higher --
somewhere in the 24GB to 30GB range, unless you have specific
reasons to believe otherwise. It's not that critical, though.
> add_missing_from = on
Why? There has been discussion of eliminating this option -- do you
have queries which rely on the non-standard syntax this enables?
> Also i would like to apologize that some of the discussions on
> this problem inadvertently became private between me & kevin.
Oops. I failed to notice that. Thanks for bringing it back to the
list. (It's definitely in your best interest to keep it in front of
all the other folks here, some of whom regularly catch things I miss
or get wrong.)
If you still do have slow queries, please follow up with details.
-Kevin
