Re: cpu bound postgresql setup.

Dear List,

1. It was found that too many stray queries were getting generated
from rouge users and bots
we controlled using some manual methods.

2. We have made application changes and some significant changes have been done.

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)

4. We had implemented partitioning using exclusion constraints ,
parent relnship
was removed from quite a lot of old partition tables.

our postgresql.conf

--------------------------------------
# cat postgresql.conf | grep -v "^\s*#" | grep -v "^\s*$"

listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 300 # (change requires restart)
shared_buffers = 10GB # min 128kB
work_mem = 4GB # min 64kB
fsync = on # turns forced synchronization on or off
synchronous_commit = on # immediate fsync at commit
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each
archive_mode = on # allows archiving to be done
archive_command = '/opt/scripts/archive_wal.sh %p %f '
archive_timeout = 600 # force a logfile segment switch after this
effective_cache_size = 18GB
constraint_exclusion = on # on, off, or partition
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/var/log/postgresql' # directory where log
files are written,
log_filename = 'postgresql.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_error_verbosity = verbose # terse, default, or verbose messages
log_min_duration_statement = 5000 # -1 is disabled, 0 logs all statements
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system
error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
add_missing_from = on
custom_variable_classes = 'general' # list of custom
variable class names
general.report_level = ''
general.disable_audittrail2 = ''
general.employee=''

Also i would like to apologize that some of the discussions on this problem
inadvertently became private between me & kevin.

On Thu, Jun 24, 2010 at 12:10 AM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> It was nice to go through the interesting posting guidelines. i shall
> be analyzing the slow queries more objectively tomorrow during the
> peak hours. I really hope it sould be possible to track down the
> problem.
>
> On 6/23/10, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com> wrote:
>>
>>> did you suggest at some point that number of backend per core
>>> should be preferebly 3 ?
>>
>> I've found the number of *active* backends is optimal around (2 *
>> cores) + spindles. You said you had eight cores and eight or ten
>> spindles, so I figure a connection pool limited to somewhere around
>> 24 active connections is ideal. (Depending on how you set up your
>> pool, you may need a higher total number of connections to keep 24
>> active.)
>>
>> -Kevin
>>
>
> --
> Sent from Gmail for mobile | mobile.google.com
>