Re: PostgreSQL replication lag - Suggestions and questions - Mailing list pgsql-general
| From | Vijaykumar Jain |
|---|---|
| Subject | Re: PostgreSQL replication lag - Suggestions and questions |
| Date | |
| Msg-id | CAM+6J95VFDuaJXDQoEjGL4v+-UivGe5_zvZNZj57ozRgVjdd1w@mail.gmail.com Whole thread Raw |
| In response to | PostgreSQL replication lag - Suggestions and questions (Lucas <root@sud0.nz>) |
| List | pgsql-general |
> My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication.
I am not sure of the difference between read replica and a hot standby. At least with later versions hot standby allows read queries.
I mean unless you do not want queries to go to AZ2 node, both of them should be able to server the queries.
with libpq 10 onwards, you can provide multiple nodes in the connection string, which would "automatically" figure out next available node in to query if one is not responding.
I mean unless you do not want queries to go to AZ2 node, both of them should be able to server the queries.
with libpq 10 onwards, you can provide multiple nodes in the connection string, which would "automatically" figure out next available node in to query if one is not responding.
See
PostgreSQL: Documentation: 13: 33.1. Database Connection Control Functions
> All read-only queries are sent to the read slave. Sometimes the replication lag between the master and the slaves reaches up to 10 minutes.I understand that the lag is expected in any replication scenario, and below you can find some suggestions that I think would help to minimize the lag time.
> All read-only queries are sent to the read slave. Sometimes the replication lag between the master and the slaves reaches up to 10 minutes.I understand that the lag is expected in any replication scenario, and below you can find some suggestions that I think would help to minimize the lag time.
lags can be a result of multiple reasons like,
Intermittent connection issues,
network unable to keep up with WAL changes. A typical example would be any action that performs a table rewrite of a huge table.
this can result in query getting cancelled on the replica and it may think you might query stale data.
you can tune the replica with these params if you want to avoid query cancellations but are ok with little stale data.
also if the application can initiate a retry for the cancelled query exception if they can handle at app layer itself.
If you cannot deal with stale data at all, then you have two options.
1) synchronous replication
synchronous replication in very simple terms would mean, if you have a cluster like
Primary -> ( Replica R1, Replica R2) the primary would wait for acknowledgement from at least one of the replicas. this may impact your tps, but you get consistency.
but geo setups with synchronous replication would be problematic.
2) read from the primary alone.
this is always the safest option, as no intermediate network, but this would also mean all the read load on the primary will impact the db performance and the housekeeping work like
vacuuming. and incase the server goes down, till the time you failover, you cannot perform any read or writes depending on the catch up time.
> Having the read slave in the same AZ as its master - for better network throughput;
AZ spread is meant for dealing with availability. It may be ok to have some read replica in some AZ, but also have some in another AZ in case of a major outage.
if you can use a load balancer, you can provide more weight to local replicas then the remote replicas etc and use a health check like replication lag to ensure read request
are served by nodes with tolerable lag only.
> Having the latest PostgreSQL version to get its best performance
mostly always. not just improvements, but a lot of new features like partitioning, logical replication, stored procedures etc are now available in newer versions.
> Having the latest Operational System behind PostgreSQL to get its best IO performance
yes at the software layer, latest versions would be more performant "mostly".
but still the hardware layer plays an important role.
If you want to really validate improvements, you can run pgbench/sysbench stress tests on the interested versions and see the changes yourself.
upgrade is always needed. not just for performance reasons, but security reasons as well.
Also, keeping the upgrade on hold for a time, may mean jumping across multiple versions which although might work,
but you would need to read the change log across the versions to ensure nothing would be broken,
Hence regular upgrades avoid the pain.
--
Thanks,
Vijay
Mumbai, India
--
Thanks,
Vijay
Mumbai, India
pgsql-general by date: