pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed[TIPS] Tuning PostgreSQL 9.2
25+ messages / 7 participants
[nested] [flat]
* [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 01:03 drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-18 01:03 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 17:52 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 3 replies; 25+ messages in thread
From: Scott Marlowe @ 2016-02-18 17:52 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 17:56 Scott Whitney <scott@journyx.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 25+ messages in thread
From: Scott Whitney @ 2016-02-18 17:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: pgsql-admin
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.
________________________________________
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/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 18:10 Gilberto Castillo <gilberto.castillo@etecsa.cu>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 25+ messages in thread
From: Gilberto Castillo @ 2016-02-18 18:10 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: drum.lucas@gmail.com <drum.lucas@gmail.com>; pgsql-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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 22:10 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-18 22:10 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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.
[image: Inline images 1]
Disk utilization during that period
[image: Inline images 2]
PostgreSQL buffer cache during that period:
[image: Inline images 3]
Postgres checkpoints:
[image: 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?
>
[image: 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
Attachments:
Screen Shot 2016-02-19 at 10.48.35 AM.png (image/png, 30.7K) download
view image
Screen Shot 2016-02-19 at 10.51.57 AM.png (image/png, 67.0K) download
view image
Screen Shot 2016-02-19 at 10.43.35 AM.png (image/png, 84.4K) download
view image
Screen Shot 2016-02-19 at 10.53.58 AM.png (image/png, 41.4K) download
view image
Screen Shot 2016-02-19 at 11.06.16 AM.png (image/png, 34.7K) download
view image
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 22:15 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-18 22:15 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 22:20 Payal Singh <payal@omniti.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Payal Singh @ 2016-02-18 22:20 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Scott Whitney <scott@journyx.com>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Following are some helpful links to help you tune your system better:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
http://evol-monkey.blogspot.com/2013/08/setting-sharedbuffers-hard-way.html
https://wiki.postgresql.org/wiki/Performance_Optimization
Plenty of other resources online.
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
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 22:24 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Payal Singh <payal@omniti.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-18 22:24 UTC (permalink / raw)
To: Payal Singh <payal@omniti.com>; +Cc: Scott Whitney <scott@journyx.com>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On 19 February 2016 at 11:20, Payal Singh <payal@omniti.com> wrote:
> Following are some helpful links to help you tune your system better:
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> http://evol-monkey.blogspot.com/2013/08/setting-sharedbuffers-hard-way.html
> https://wiki.postgresql.org/wiki/Performance_Optimization
>
> Plenty of other resources online.
>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-18 22:53 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-18 22:53 UTC (permalink / raw)
To: Payal Singh <payal@omniti.com>; +Cc: Scott Whitney <scott@journyx.com>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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:
>
>> Following are some helpful links to help you tune your system better:
>>
>> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>>
>> http://evol-monkey.blogspot.com/2013/08/setting-sharedbuffers-hard-way.html
>> https://wiki.postgresql.org/wiki/Performance_Optimization
>>
>> Plenty of other resources online.
>>
>
> 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.
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-19 00:47 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-02-19 00:47 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-19 01:48 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-19 01:48 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-19 03:32 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-02-19 03:32 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-21 18:34 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-02-21 18:34 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-22 21:23 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-22 21:23 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-22 21:29 Dorian Machado <dorian599@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
1 sibling, 0 replies; 25+ messages in thread
From: Dorian Machado @ 2016-02-22 21:29 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
Hello,
I found this tuning utility in the internet
http://pgtune.leopard.in.ua
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*
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-23 03:41 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-02-23 03:41 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-23 04:05 Joshua D. Drake <jd@commandprompt.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Joshua D. Drake @ 2016-02-23 04:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-02-23 04:32 Scott Marlowe <scott.marlowe@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-02-23 04:32 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: drum.lucas@gmail.com <drum.lucas@gmail.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-11 22:34 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-04-11 22:34 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-11 22:45 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-04-11 22:45 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
>
>>
> 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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-12 00:16 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-04-12 00:16 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-12 00:20 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-04-12 00:20 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
>
>
>
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-12 01:55 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2016-04-12 01:55 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-12 03:17 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: drum.lucas@gmail.com @ 2016-04-12 03:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [TIPS] Tuning PostgreSQL 9.2
@ 2016-04-13 13:52 Scott Marlowe <scott.marlowe@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 0 replies; 25+ messages in thread
From: Scott Marlowe @ 2016-04-13 13:52 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Payal Singh <payal@omniti.com>; Scott Whitney <scott@journyx.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2016-04-13 13:52 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-18 01:03 [TIPS] Tuning PostgreSQL 9.2 drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-18 17:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-18 17:56 ` Scott Whitney <scott@journyx.com>
2016-02-18 22:15 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-18 22:20 ` Payal Singh <payal@omniti.com>
2016-02-18 22:24 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-18 22:53 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-19 00:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-19 01:48 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-19 03:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-21 18:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-22 21:23 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-22 21:29 ` Dorian Machado <dorian599@gmail.com>
2016-02-23 03:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-23 04:05 ` Joshua D. Drake <jd@commandprompt.com>
2016-02-23 04:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-04-11 22:34 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-04-11 22:45 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-04-12 00:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-04-12 00:20 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-04-12 01:55 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-04-12 03:17 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-04-13 13:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2016-02-18 18:10 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2016-02-18 22:10 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
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