From: Gilberto Castillo <gilberto.castillo@etecsa.cu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: drum.lucas@gmail.com <drum.lucas@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2
Date: Thu, 18 Feb 2016 13:10:01 -0500 (GMT+5)
Message-ID: <47625.192.168.207.54.1455819001.squirrel@webmail.etecsa.cu> (raw)
In-Reply-To: <CAOR=d=067_jGrkSQvxjx5YpJOaRLi+zhOX2EbdvKzUw75R3Xfw@mail.gmail.com>
References: <CAE_gQfXcw=qOTSWiTsuJR3Z3ujM2kPfGTEjgDQcmgODnH4B1Zw@mail.gmail.com>
<CAOR=d=067_jGrkSQvxjx5YpJOaRLi+zhOX2EbdvKzUw75R3Xfw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hello
Use pgtuning in you server.
> On Wed, Feb 17, 2016 at 6:03 PM, drum.lucas@gmail.com
> <drum.lucas@gmail.com> wrote:
>> Hi all,
>>
>> Trying to improve the performance, it would be great hear from you some
>> tips
>> to it...
>>
>> My current scenario is:
>> 128 GB Ram - Raid 10 - PostgreSQL 9.2 in a Centos 6.6 64 Bits
>>
>> How could I measure a nice parameter to it?
>> Can you guys tell your experience and how did you get nice results with
>> your
>> changes?
>>
>> But biggest problem nowadays are slow queries and I/O (In some spikes I
>> get
>> 100% I/O usage)
>>
>> Thank you!
>>
>> Current confs:
>> 1 - Newrelic
>> 2 - pg_stat_statements = on
>> 3 - log_min_duration_statement = 1000
>> 4 - log_statement = 'ddl'
>> 5 - Munin
>> 6 - Vaccum:
>>>
>>> vacuum_cost_delay = 20ms
>>> vacuum_cost_page_hit = 1
>>> vacuum_cost_page_miss = 10
>>> vacuum_cost_page_dirty = 20
>>> vacuum_cost_limit = 100
>>> autovacuum = on
>>> log_autovacuum_min_duration = 30000
>>> autovacuum_max_workers = 2
>>> autovacuum_naptime = 1min
>>> autovacuum_vacuum_threshold = 500
>>> autovacuum_analyze_threshold = 500
>>> autovacuum_vacuum_scale_factor = 0.1
>>> autovacuum_analyze_scale_factor = 0.1
>>> autovacuum_freeze_max_age = 200000000
>>> autovacuum_vacuum_cost_delay = -1
>>> autovacuum_vacuum_cost_limit = -1
>>> vacuum_freeze_min_age = 50000000
>>> vacuum_freeze_table_age = 150000000
>>
>>
>> shared_buffers = 51605MB
>> work_mem = 32MB
>> maintenance_work_mem = 128 MB
>> effective_cache_size = 96760MB
>
> There are a lot of things you can do to improve performance, but we
> don't know your usage patterns or underlying IO subsystem. What does
> your IO sybsystem look like? How fast can you get something like
> pgbench to go on this machine?
>
> Might I ask where the idea for shared_buffers being 51GB came from?
> Generally speaking shared_buffers don't work well that big, except in
> some very specific circumstances maybe.
>
> So when you say IO is 100% utilized, is that being used by sorts, the
> background writer, reads?
>
> How many active and idle connections do you typically have on this
> machine? If you have a lot of connections have you considered pooling?
>
> What are max_connections, effective_io_concurrency, ramdom_page_cost,
> wal_writer_delay, commit_delay, commit_siblings, checkpoint_segments,
> temp_buffers, set to?
>
> Turn on things like log_temp_files, log_checkpoints.
>
> Also got a slow query and an explain analyze output?
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gilberto.castillo@etecsa.cu, scott.marlowe@gmail.com, drum.lucas@gmail.com
Subject: Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2
In-Reply-To: <47625.192.168.207.54.1455819001.squirrel@webmail.etecsa.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox