Обсуждение: [TIPS] Tuning PostgreSQL 9.2

Поиск
Список
Период
Сортировка

[TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:
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

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
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?


Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Whitney
Дата:
Echoing (other) Scott's comments, specifically, when he says "what does your IO subsystem looks like," what's the
hardware?RAID 10 is all well and good, but how many drives, what type are they, and how is your RAID-10 configured?
Specificmodel numbers would be useful. 

________________________________________
From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Scott Marlowe
<scott.marlowe@gmail.com>
Sent: Thursday, February 18, 2016 11:52 AM
To: drum.lucas@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] [TIPS] Tuning PostgreSQL 9.2

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


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888
f 512-834-8858

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at
http://go.journyx.com/emailPreference/e/4932/714/


Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2

От
"Gilberto Castillo"
Дата:
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



Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


On 19 February 2016 at 06:56, Scott Whitney <scott@journyx.com> wrote:
Echoing (other) Scott's comments, specifically, when he says "what does your IO subsystem looks like," what's the hardware? RAID 10 is all well and good, but how many drives, what type are they, and how is your RAID-10 configured? Specific model numbers would be useful.



 HI there! 

So... 

The disks are:

HP 3TB 6G SAS 7.2K 3.5inch HDD,, HDD RPM: 7200, GB Hard Drive: 3072 

HP Smart Array P840/4GB FBWC 12Gb/s SAS Raid Controller 

raid 10

LVM with ext4 filesystem

Re: [TIPS] Tuning PostgreSQL 9.2

От
Payal Singh
Дата:

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Thu, Feb 18, 2016 at 5:15 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:


On 19 February 2016 at 06:56, Scott Whitney <scott@journyx.com> wrote:
Echoing (other) Scott's comments, specifically, when he says "what does your IO subsystem looks like," what's the hardware? RAID 10 is all well and good, but how many drives, what type are they, and how is your RAID-10 configured? Specific model numbers would be useful.



 HI there! 

So... 

The disks are:

HP 3TB 6G SAS 7.2K 3.5inch HDD,, HDD RPM: 7200, GB Hard Drive: 3072 

HP Smart Array P840/4GB FBWC 12Gb/s SAS Raid Controller 

raid 10

LVM with ext4 filesystem


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


On 19 February 2016 at 11:20, Payal Singh <payal@omniti.com> wrote:

Thanks! BUT I'd love to hear from all of you.. sometimes talking each other is better than put some random confs in prod. I want to know what u guys have been through and, maybe, do some changes here as well.

Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:
I've sent an email to the list, showing the graphs (i think it is waiting for approval)

In the meantime...

I'm able to change to any of this below, if it will improve my performance:

Replace existing 4x3TB Disk with 1.5TB Silver SAN

Replace existing 4x3TB Disk with 4x800GB SSD Drives (Raid 5)

Replace existing 4x3TB Disk with 8x600GB 15K SAS (Raid 10)

Add 4x3TB to existing Raid 10 

On 19 February 2016 at 11:24, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:


On 19 February 2016 at 11:20, Payal Singh <payal@omniti.com> wrote:

Thanks! BUT I'd love to hear from all of you.. sometimes talking each other is better than put some random confs in prod. I want to know what u guys have been through and, maybe, do some changes here as well.

Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:
Hi there!
So the server is: HP DL380 G9 Dual Socket Octo Core Intel Xeon E5-2630v3 2.4GHz 128 GB RAM
It's a production server, so I can't "play" with it.

I'll post here some graphs about the SPIKE I had yesterday (2016-02-18) - Note that the spike isn't so big, but usually they are.
Inline images 1

Disk utilization during that period
Inline images 2

PostgreSQL buffer cache during that period:
Inline images 3

Postgres checkpoints:
Inline images 4




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.

The shared_buffers idea, was made by the previous DBA.
 

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?
Inline images 5 

What are max_connections, effective_io_concurrency, ramdom_page_cost,
wal_writer_delay, commit_delay, commit_siblings, checkpoint_segments,
temp_buffers, set to?


max_connections = 200

effective_io_concurrency = 0

wal_writer_delay = 200ms

commit_delay = 0

#####commit_siblings = 5

checkpoint_segments = 64

#####ramdom_page_cost (Couldn't find that parameter in postgresql.conf)

temp_buffers = 16MB

 

Turn on things like log_temp_files, log_checkpoints.
They are already on:
log_temp_files = 4096
log_checkpoints = on 

Also got a slow query and an explain analyze output?
Sometimes slow queries are INSERT/UPDATEs ... so no explain analyze for them 

Вложения

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
So how big is your data set?  Is it much bigger, about the same, or
much smaller than your shared_buffers? The problem with a giant
shared_buffers is that postgresql has a VERY big set of data to keep
track of when it comes time to write it out, evict stuff, etc etc.
generally speaking the kernel is better optimized to cache huge data
sets. For instance, I can get very good performance on a machine with
a multi-terabyte data set, running with 512GB RAM and only using 10GB
as shared_buffers, and letting the kernel use the rest as cache.
Making share_buffers bigger doesn't make it faster after a few
gigabytes, even with 24 cores and 10 SSDs in RAID-5 (we can go faster
with RAID 10 but we need the space). 51GB is a huge shared_buffer
setting. I'd want to see some benchmarks showing it was faster than 1
or 2GB. I'm betting it won't be.

Also are you using a pooler? I would take it as no. Note that your
connections go from 30 or so to over 140 during a spike. A computer,
based on number of concurrent iops it can handle etc, will have a
performance graph that climbs as you hit a certain peak number of
active connections. On a machine like yours I'd expect that peak to be
between 4 and 20. Restricting active connections to a number in that
range makes the machine faster in terms of throughput, and keeps it
from slowly tipping over as you go further and further past it's peak
number.

pgbouncer is super easy to setup and it can handle huge numbers of
idle connections (10,000 etc) while keeping the db running at its
fastest. My advice? Get a pooler in there.


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:
Thanks for the reply, Scott.


On 19 February 2016 at 13:47, Scott Marlowe <scott.marlowe@gmail.com> wrote:
So how big is your data set?  Is it much bigger, about the same, or
much smaller than your shared_buffers? The problem with a giant

The total DB size is 1,9 TB
 
shared_buffers is that postgresql has a VERY big set of data to keep
track of when it comes time to write it out, evict stuff, etc etc.
generally speaking the kernel is better optimized to cache huge data
sets. For instance, I can get very good performance on a machine with
a multi-terabyte data set, running with 512GB RAM and only using 10GB
as shared_buffers, and letting the kernel use the rest as cache.
Making share_buffers bigger doesn't make it faster after a few
gigabytes, even with 24 cores and 10 SSDs in RAID-5 (we can go faster
with RAID 10 but we need the space). 51GB is a huge shared_buffer
setting. I'd want to see some benchmarks showing it was faster than 1
or 2GB. I'm betting it won't be.

Also are you using a pooler? I would take it as no. Note that your
connections go from 30 or so to over 140 during a spike. A computer,
based on number of concurrent iops it can handle etc, will have a
performance graph that climbs as you hit a certain peak number of
active connections. On a machine like yours I'd expect that peak to be
between 4 and 20. Restricting active connections to a number in that
range makes the machine faster in terms of throughput, and keeps it
from slowly tipping over as you go further and further past it's peak
number.

pgbouncer is super easy to setup and it can handle huge numbers of
idle connections (10,000 etc) while keeping the db running at its
fastest. My advice? Get a pooler in there.

I'm not using a pooler.. But I'll have a look on it

Should I decrease my max_connections as well? 

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Thu, Feb 18, 2016 at 6:48 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
> Thanks for the reply, Scott.
>
>
> On 19 February 2016 at 13:47, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> So how big is your data set?  Is it much bigger, about the same, or
>> much smaller than your shared_buffers? The problem with a giant
>
>
> The total DB size is 1,9 TB

Yeah 50GB isn't gonna hold the whole thing so being that big is
probably counterproductive. I'd drop it to anywhere from 1 to 10GB,
and test work load on each size etc.

>> Also are you using a pooler? I would take it as no. Note that your
>> connections go from 30 or so to over 140 during a spike. A computer,
>> based on number of concurrent iops it can handle etc, will have a
>> performance graph that climbs as you hit a certain peak number of
>> active connections. On a machine like yours I'd expect that peak to be
>> between 4 and 20. Restricting active connections to a number in that
>> range makes the machine faster in terms of throughput, and keeps it
>> from slowly tipping over as you go further and further past it's peak
>> number.
>>
>> pgbouncer is super easy to setup and it can handle huge numbers of
>> idle connections (10,000 etc) while keeping the db running at its
>> fastest. My advice? Get a pooler in there.
>
>
> I'm not using a pooler.. But I'll have a look on it

Cool. pic a pool size (4 to 20 etc) that corresponds to the best
throughput (transactions per second etc).

> Should I decrease my max_connections as well?

Not before you put a connection pooler in place. Right now lowering it
will likely create as many problems as it solves, with failed to
connect error messages etc. After a pooler's in place it's nice to set
the max conns to something about 2x what you think you should see with
the connection pooler in place.

--
To understand recursion, one must first understand recursion.


Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
I also noticed you're running RHEL 6.x which runs on the truly ancient
(but pretty reliable) 2.6.32 kernel. The newer 3.11 and 3.13 kernels
are MUCH faster at IO and a lot smarter about caching and when to swap
etc. I've seen several big machines go from a few thousand tps to 15
to 20k tps just from going from 3.2 to 3.13. Keep us updated on
whether or not a pooler works for you.


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


On 22 February 2016 at 07:34, Scott Marlowe <scott.marlowe@gmail.com> wrote:
I also noticed you're running RHEL 6.x which runs on the truly ancient
(but pretty reliable) 2.6.32 kernel. The newer 3.11 and 3.13 kernels
are MUCH faster at IO and a lot smarter about caching and when to swap
etc. I've seen several big machines go from a few thousand tps to 15
to 20k tps just from going from 3.2 to 3.13. Keep us updated on
whether or not a pooler works for you.


I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6

Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think only in Centos 7)
Is so, can you please provide me any link that shows a IO improvement between two kernels? something that I can study  

Re: [TIPS] Tuning PostgreSQL 9.2

От
Dorian Machado
Дата:
Hello,

I found this tuning utility in the internet


Best Regards.

On Mon, Feb 22, 2016 at 6:23 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:


On 22 February 2016 at 07:34, Scott Marlowe <scott.marlowe@gmail.com> wrote:
I also noticed you're running RHEL 6.x which runs on the truly ancient
(but pretty reliable) 2.6.32 kernel. The newer 3.11 and 3.13 kernels
are MUCH faster at IO and a lot smarter about caching and when to swap
etc. I've seen several big machines go from a few thousand tps to 15
to 20k tps just from going from 3.2 to 3.13. Keep us updated on
whether or not a pooler works for you.


I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6

Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think only in Centos 7)
Is so, can you please provide me any link that shows a IO improvement between two kernels? something that I can study  




--
Dorian Machado

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Mon, Feb 22, 2016 at 2:23 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
>
>
> On 22 February 2016 at 07:34, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> I also noticed you're running RHEL 6.x which runs on the truly ancient
>> (but pretty reliable) 2.6.32 kernel. The newer 3.11 and 3.13 kernels
>> are MUCH faster at IO and a lot smarter about caching and when to swap
>> etc. I've seen several big machines go from a few thousand tps to 15
>> to 20k tps just from going from 3.2 to 3.13. Keep us updated on
>> whether or not a pooler works for you.
>
>
>
> I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6
>
> Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think
> only in Centos 7)
> Is so, can you please provide me any link that shows a IO improvement
> between two kernels? something that I can study

It has been many years since I took care of a Centos box, but in this
article by Josh Berkus about avoiding kernel 3.2 it is mentioned that
centos 7 can in fact run 3.10 kernel.

http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html


Re: [TIPS] Tuning PostgreSQL 9.2

От
"Joshua D. Drake"
Дата:
On 02/22/2016 07:41 PM, Scott Marlowe wrote:

>> I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6
>>
>> Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think
>> only in Centos 7)
>> Is so, can you please provide me any link that shows a IO improvement
>> between two kernels? something that I can study
>
> It has been many years since I took care of a Centos box, but in this
> article by Josh Berkus about avoiding kernel 3.2 it is mentioned that
> centos 7 can in fact run 3.10 kernel.
>
> http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

It isn't just 3.2. You should avoid 3.2-3.8. 2.6 is fine.

JD


>
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Mon, Feb 22, 2016 at 9:05 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On 02/22/2016 07:41 PM, Scott Marlowe wrote:
>
>>> I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6
>>>
>>> Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think
>>> only in Centos 7)
>>> Is so, can you please provide me any link that shows a IO improvement
>>> between two kernels? something that I can study
>>
>>
>> It has been many years since I took care of a Centos box, but in this
>> article by Josh Berkus about avoiding kernel 3.2 it is mentioned that
>> centos 7 can in fact run 3.10 kernel.
>>
>>
>> http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html
>
>
> It isn't just 3.2. You should avoid 3.2-3.8. 2.6 is fine.

I wouldn't say 2.6 is fine, but it is better than 3.2 through 3.8. But
it still has plenty of performance issues under load that 3.11 and up
addressed for me.

--
To understand recursion, one must first understand recursion.


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


On 23 February 2016 at 17:32, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Mon, Feb 22, 2016 at 9:05 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On 02/22/2016 07:41 PM, Scott Marlowe wrote:
>
>>> I'm running a CentOS 6.6 with Kernel 2.6.32-504.el6
>>>
>>> Is it possible to upgrade to the 3.13 version using Centos 6.6? (I think
>>> only in Centos 7)
>>> Is so, can you please provide me any link that shows a IO improvement
>>> between two kernels? something that I can study
>>
>>
>> It has been many years since I took care of a Centos box, but in this
>> article by Josh Berkus about avoiding kernel 3.2 it is mentioned that
>> centos 7 can in fact run 3.10 kernel.
>>
>>
>> http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html
>
>
> It isn't just 3.2. You should avoid 3.2-3.8. 2.6 is fine.

I wouldn't say 2.6 is fine, but it is better than 3.2 through 3.8. But
it still has plenty of performance issues under load that 3.11 and up
addressed for me.

Hi guys... how are you all doing?


Scott Marlowe, you sent an email about the pooler...

I've done some tests on my VM, but can't do tests on the servers, as they are already in production...
However... I'm willing to set-up the pgbouncer pool on my postgresql 9.2 server...

Just got some questions first.. can you please give me your opinion?

my scenario:
1 Load Balancer (nginx) --->
2 web servers (www01 and www02) --->
4 DB servers (master01, slave01 (read-only), slave02 (hot_standby), slave03 (hot_standby), slave04 (hot_standby)

I'd install the pgbouncer on the www01 server... What do you guys think about it? Having in mind my scenario? 

Cheers
Lucas




Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:



Hi guys... how are you all doing?


Scott Marlowe, you sent an email about the pooler...

I've done some tests on my VM, but can't do tests on the servers, as they are already in production...
However... I'm willing to set-up the pgbouncer pool on my postgresql 9.2 server...

Just got some questions first.. can you please give me your opinion?

my scenario:
1 Load Balancer (nginx) --->
2 web servers (www01 and www02) --->
4 DB servers (master01, slave01 (read-only), slave02 (hot_standby), slave03 (hot_standby), slave04 (hot_standby)

I'd install the pgbouncer on the www01 server... What do you guys think about it? Having in mind my scenario? 

Cheers
Lucas


I mean... can pgbouncer handle with my master and slave01? As I'm using slave01 as read-only server? Also.. I've got 2 www servers... Do I have to have pgbouncer in both of them?

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Mon, Apr 11, 2016 at 4:45 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
>
>>>
>>
>> Hi guys... how are you all doing?
>>
>>
>> Scott Marlowe, you sent an email about the pooler...
>>
>> I've done some tests on my VM, but can't do tests on the servers, as they
>> are already in production...
>> However... I'm willing to set-up the pgbouncer pool on my postgresql 9.2
>> server...
>>
>> Just got some questions first.. can you please give me your opinion?
>>
>> my scenario:
>>>
>>> 1 Load Balancer (nginx) --->
>>> 2 web servers (www01 and www02) --->
>>> 4 DB servers (master01, slave01 (read-only), slave02 (hot_standby),
>>> slave03 (hot_standby), slave04 (hot_standby)
>>
>>
>> I'd install the pgbouncer on the www01 server... What do you guys think
>> about it? Having in mind my scenario?
>>
>> Cheers
>> Lucas
>>
>
> I mean... can pgbouncer handle with my master and slave01? As I'm using
> slave01 as read-only server? Also.. I've got 2 www servers... Do I have to
> have pgbouncer in both of them?

pgbouncer is kinda happy living almost anywhere.

Putting it on separate vms means you can reconfigure when needed for
say another db or web server without having to edit anything but the
pgbouncer vms.

Putting it on the db servers means that if a db server goes down then
you need to reconfigure the app side to not look for them

Putting them on the app side means you have to configured according to
how many app servers you have etc.

It all really depends on your use cases. but putting it on the www
servers works fine and is how I've done it many times in the past.


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


pgbouncer is kinda happy living almost anywhere.

Putting it on separate vms means you can reconfigure when needed for
say another db or web server without having to edit anything but the
pgbouncer vms.

Putting it on the db servers means that if a db server goes down then
you need to reconfigure the app side to not look for them

Putting them on the app side means you have to configured according to
how many app servers you have etc.

It all really depends on your use cases. but putting it on the www
servers works fine and is how I've done it many times in the past.

Thanks for the reply...

But as I'm using two web servers, do I have to put pgbouncer on both of them?

Not sure how is going to work as I have two web servers

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Mon, Apr 11, 2016 at 6:20 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
>>
>>
>> pgbouncer is kinda happy living almost anywhere.
>>
>> Putting it on separate vms means you can reconfigure when needed for
>> say another db or web server without having to edit anything but the
>> pgbouncer vms.
>>
>> Putting it on the db servers means that if a db server goes down then
>> you need to reconfigure the app side to not look for them
>>
>> Putting them on the app side means you have to configured according to
>> how many app servers you have etc.
>>
>> It all really depends on your use cases. but putting it on the www
>> servers works fine and is how I've done it many times in the past.
>
>
> Thanks for the reply...
>
> But as I'm using two web servers, do I have to put pgbouncer on both of
> them?
>
> Not sure how is going to work as I have two web servers

Either way will work. The advantage to having one on each is that
connections are simpler to configure and if one goes fown you still
have pgbouncer running


Re: [TIPS] Tuning PostgreSQL 9.2

От
"drum.lucas@gmail.com"
Дата:


On 12 April 2016 at 13:55, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Mon, Apr 11, 2016 at 6:20 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
>>
>>
>> pgbouncer is kinda happy living almost anywhere.
>>
>> Putting it on separate vms means you can reconfigure when needed for
>> say another db or web server without having to edit anything but the
>> pgbouncer vms.
>>
>> Putting it on the db servers means that if a db server goes down then
>> you need to reconfigure the app side to not look for them
>>
>> Putting them on the app side means you have to configured according to
>> how many app servers you have etc.
>>
>> It all really depends on your use cases. but putting it on the www
>> servers works fine and is how I've done it many times in the past.
>
>
> Thanks for the reply...
>
> But as I'm using two web servers, do I have to put pgbouncer on both of
> them?
>
> Not sure how is going to work as I have two web servers

Either way will work. The advantage to having one on each is that
connections are simpler to configure and if one goes fown you still
have pgbouncer running

hmm ok..

So basically would be:

1 - Install the pgbouncer into the www server
2 - Do the tests to see if it works
3 - Change the APP connection parameters to start using pgbouncer (probably just the port)

Basically would be that, right?

Would my slave01 still be able to work as read-only?

Re: [TIPS] Tuning PostgreSQL 9.2

От
Scott Marlowe
Дата:
On Mon, Apr 11, 2016 at 9:17 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
>
>
> On 12 April 2016 at 13:55, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> On Mon, Apr 11, 2016 at 6:20 PM, drum.lucas@gmail.com
>> <drum.lucas@gmail.com> wrote:
>> >>
>> >>
>> >> pgbouncer is kinda happy living almost anywhere.
>> >>
>> >> Putting it on separate vms means you can reconfigure when needed for
>> >> say another db or web server without having to edit anything but the
>> >> pgbouncer vms.
>> >>
>> >> Putting it on the db servers means that if a db server goes down then
>> >> you need to reconfigure the app side to not look for them
>> >>
>> >> Putting them on the app side means you have to configured according to
>> >> how many app servers you have etc.
>> >>
>> >> It all really depends on your use cases. but putting it on the www
>> >> servers works fine and is how I've done it many times in the past.
>> >
>> >
>> > Thanks for the reply...
>> >
>> > But as I'm using two web servers, do I have to put pgbouncer on both of
>> > them?
>> >
>> > Not sure how is going to work as I have two web servers
>>
>> Either way will work. The advantage to having one on each is that
>> connections are simpler to configure and if one goes fown you still
>> have pgbouncer running
>
>
> hmm ok..
>
> So basically would be:
>
> 1 - Install the pgbouncer into the www server
> 2 - Do the tests to see if it works
> 3 - Change the APP connection parameters to start using pgbouncer (probably
> just the port)
>
> Basically would be that, right?
>
> Would my slave01 still be able to work as read-only?

Yes.