[GENERAL] Throttling Streamming Replication
Hi Guys, We are having a problem with our infrastructure provider because the network traffic between master and slave server is reaching more than 30k packages per second(SLA says 20k/second). Is there any way to throttle the streamming replication? I meant, any parameter that I set the max number of megabytes sent to standby server per second? I didn't have any luck looking at the postgresql streamming replication documentation. There is the wal_sender_delay, but I suppose that if I set more than 1 second, it could accumulate a bunch of wal files and send it at once. So, it wouldn't work. Thanks in advance, -- Rodrigo Pereira da Silva rodr...@paripassu.com.br http://www.paripassu.com.br Rua Coronel Luis Caldeira, nº 67, Bloco 1, Sala 3A 88034-110 • Florianópolis • SC Tel. (48) 3207-5755 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Throttling Streamming Replication
That's a good idea, but we are using windows copy command, not sure if it has something similar. Anyway, we just talked to infrastructure provider and they figured out it was a problem with their monitoring system, so we are not reaching 30k packages actually. Thanks a lot for the replies, Em 25/01/2013 12:18, Cliff de Carteret escreveu: Can you not change your method of streaming replication. What is your archive command, are you using rsync as you can control the bandwidth limit? We use: archive_command = 'test ! -f /opt/postgres/remote_pgsql/wal_archive/%f rsync -az %p /opt/postgres/remote_pgsql/wal_archive/%f' According to the link below you can add --bwlimit=1000 to the rsync arguments http://scratching.psybermonkey.net/2009/03/rsync-transfer-rate-limit-aka-it-down.html On 25 January 2013 12:59, Magnus Hagander mag...@hagander.net mailto:mag...@hagander.net wrote: On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da Silva rodr...@paripassu.com.br mailto:rodr...@paripassu.com.br wrote: Hi Guys, We are having a problem with our infrastructure provider because the network traffic between master and slave server is reaching more than 30k packages per second(SLA says 20k/second). Is there any way to throttle the streamming replication? I meant, any parameter that I set the max number of megabytes sent to standby server per second? I didn't have any luck looking at the postgresql streamming replication documentation. There is the wal_sender_delay, but I suppose that if I set more than 1 second, it could accumulate a bunch of wal files and send it at once. So, it wouldn't work. No, there is no such parameter. You might be able to send it through some proxy that slows it down, but there is no builtin support to do that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rodrigo Pereira da Silva rodr...@paripassu.com.br http://www.paripassu.com.br Rua Coronel Luis Caldeira, nº 67, Bloco 1, Sala 3A 88034-110 . Florianópolis . SC Tel. (48) 3207-5755
[GENERAL] Message: incomplete startup packet
Hi Guys, We are having a problem with our pgsql 9.1 on Linux(Debian). Suddently, the database stop working and the logs shows the statements below just before the problem. Any thoughts? 2012-11-08 02:46:44.216 CST 0 509b70fb.4570LOG: execute S_2: COMMIT 2012-11-08 02:47:12.529 CST 08P01 509b7190.4583LOG: incomplete startup packet 2012-11-08 02:48:12.534 CST 08P01 509b71cc.458bLOG: incomplete startup packet 2012-11-08 02:49:12.541 CST 08P01 509b7208.4593LOG: incomplete startup packet 2012-11-08 02:50:12.546 CST 08P01 509b7244.459aLOG: incomplete startup packet 2012-11-08 02:51:12.557 CST 08P01 509b7280.45a0LOG: incomplete startup packet 2012-11-08 02:52:12.562 CST 08P01 509b72bc.45a8LOG: incomplete startup packet 2012-11-08 02:53:12.569 CST 08P01 509b72f8.45b0LOG: incomplete startup packet 2012-11-08 02:54:12.575 CST 08P01 509b7334.45b6LOG: incomplete startup packet 2012-11-08 02:55:12.582 CST 08P01 509b7370.45c0LOG: incomplete startup packet 2012-11-08 02:56:12.592 CST 08P01 509b73ac.45c7LOG: incomplete startup packet 2012-11-08 02:57:12.599 CST 08P01 509b73e8.45d5LOG: incomplete startup packet 2012-11-08 02:58:12.604 CST 08P01 509b7424.45dcLOG: incomplete startup packet 2012-11-08 02:59:12.612 CST 08P01 509b7460.45eaLOG: incomplete startup packet 2012-11-08 03:00:12.617 CST 08P01 509b749c.45f2LOG: incomplete startup packet Thank you, Rodrigo Pereira da Silva. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Too much clients connected to the PostgreSQL Database
Hi Kevin,
I think you are right. We need to use the connection pool for
all clients, so we can limit the number of active database
connections.
We have some legacy applications that is hard to change, but
we will have to do it.
Thanks a lot,
Rodrigo
Em 10/30/2012 2:10 AM, Kevin Grittner escreveu:
rodr...@paripassu.com.br wrote:
150 maxActive setting, but we have some low usage application
getting direct db connection(out of the pool).
You will probably be better off with a connection pool somewhere
between 10 and 20, as long as it is transaction-based, the client
side has low latency to the database, and the transaction isn't
waiting for other things (like human beings or slow connections to
somewhere else) to get its work done.
The interesting is that the server suddently get a lot of
connections and reach the limit and then release them.
That is the pattern you will tend to see if you have too many
configured. The "thundering herd" syndrome. They charge in, drag
down performance while they over-stress the hardware, and then move
on.
I'm willing to change to Tomcat 7 connection pool. We are using
c3pO and apache ones, maybe that's a problem.
I have seen both the Tomcat pool and DBCP work well, although I think
the DBCP pool has more features. I don't have enough experience with
c3p0 to comment either way. I definitely would not bother to move
from Apache DBCP to the Tomcat pool. If I remember right I didn't
find much worth changing from defaults in the DBCP package, but it's
been a while. I would recommend either of those over an external
product like pgbouncer or pgpool; those are what you go to when you
don't have a quality connection pooler in your application software,
IMO.
The point is to configure whatever pool you use to limit the number
of active database transactions such that the active connections are
able to keep all available resources on the database server (cores,
spindles, network) busy, but no more. That should improve both
latency and throughput. There is no substitute for testing different
sizes with your actual load against your actual hardware; any advice
on size should be taken as the starting point for testing incremental
changes.
-Kevin
--
Rodrigo
Pereira da Silva
rodr...@paripassu.com.br
http://www.paripassu.com.br
Rua Coronel Luis Caldeira, nº 67, Bloco 1, Sala 3A
88034-110 • Florianópolis • SC
Tel. (48) 3207-5755
[GENERAL] Too much clients connected to the PostgreSQL Database
Hi Guys, We have about 10 web applications deployed in a Tomcat 7.0 accessing a Postgresql 9.1 database. We do use connection pooling. We have the max_connections parameter set to 200 connections, however, we are reaching the max connections sporadically(about 4 time/day). It's interesting, because we didn't have this problem with Postgresql 8.3. I don't know if that's can be caused by any postgresql internal task, like, auto-vaccum. Someone knows if there is anything new in PostgreSQL 9.1 that it's probably consuming connections? Is there any default approach to proceed with this kind of problem? I'm connected of increasing the max connections and do not resolve the problem or decrease performance. Thanks in advance, Rodrigo Pereira da Silva rodr...@paripassu.com.br http://www.paripassu.com.br Rua Coronel Luis Caldeira, n 67, Bloco 1, Sala 3A 88034-110 Florianpolis SC Tel. (48) 3207-5755
