Cara menggunakan replication delay mysql
The total lag is observed with the Show
Faster replica configurationWe have two ways to make a MySQL replica apply changes faster. Users can configure their replicas with the following options:
Parallel replicationParallel replication might help replication lag by configuring the replica to use multiple threads acting in parallel to apply changes on the replica. For information about using parallel replication, see . High performance flushingBy default, Cloud SQL for MySQL flushes the redo logs to disk after each transaction. High performance flushing reduces the frequency with which the redo logs are flushed to the disk to once per second, which improves write performance. Set the flag on the read replica to 2. You must also set the See for more information about this flag. When the flag is set on the read replica and Cloud SQL detects that a crash might have occurred, Cloud SQL automatically recreates the replica. Optimize queries and schemaThis section suggests some common query and schema optimizations you can make to improve replication performance. Query isolation level in the read replicaThe 0 transaction isolation levels acquire locks that might block the replication changes. Consider reducing the isolation level for your queries in the replica. The 1 transaction isolation level might perform better.Note: The MySQL default transaction isolation level is REPEATABLE READ for historical reasons.Long-running transactions in the primary databaseIf a large number of rows are updated in a single transaction, it can cause a sudden spike in the number of changes that need to be applied to the primary instance and then sent to the replica. This applies to single-statement updates or deletes that affect many rows at once. Changes are sent to the replica after they are committed. Applying a sudden spike of changes in the replica can increase the possibility of lock contention in the replica if the query load on the replica is also high, leading to replication lag. Consider breaking large transactions into multiple smaller transactions. Missing primary keysCloud SQL read replicas use row-based replication, which performs poorly if the MySQL tables that are replicated don't have primary keys. We recommend that all of the replicated tables have primary keys. Exclusive locks due to DDLData definition language (DDL) commands, such as 3 and 4, can cause replication lag in the replica due to exclusive locks. To avoid lock contention, consider scheduling DDL execution during times when the query load is lower on the replicas.Overloaded replicaIf a read replica is receiving too many queries, replication could be blocked. Consider splitting the reads among multiple replicas to reduce the load on each one. To avoid query spikes, consider throttling replica read queries in your application logic or in a proxy layer if you use one. If there are spikes of activity on the primary instance, consider spreading out updates. Monolithic primary databaseConsider sharding the primary database vertically (or horizontally) to prevent one or more lagging tables from holding back all the other tables. Monitor replication lagYou can use the ( 7)The number of seconds that the replica's state is lagging behind the state of the primary instance. This is the difference between the current time and the original timestamp at which the primary database committed the transaction that is currently being applied on the replica. In particular, writes might be counted as lagging even if they have been received by the replica, if the replica hasn't yet applied the write to the database. This metric reports the value of 8 when 9 is run on the replica. For more information, see Checking Replication Status in the MySQL Reference Manual.Last I/O thread error number( network_lag 0)Indicates the last error that caused the I/O thread to fail. If this is non-zero, replication is broken. This is rare, but it might happen. Check MySQL documentation to understand what the error code indicates. For example, binlog files in the primary instance might have been deleted before the replica received them. Cloud SQL usually automatically recreates the replica if replication is broken. This ( network_lag 2)Indicates the last error that caused the SQL thread to fail. If this is non-zero, replication is broken. This is rare, but it might happen. Check MySQL documentation to understand what the error code indicates. Cloud SQL will usually automatically recreate the replica if replication is broken. This ( network_lag 4)The length of time, in seconds, that it takes from writing the binlog in the primary database to reaching the IO thread in the replica. If the Verify replicationTo verify that replication is working, run the following statement against the replica:
If replication is happening, the If replication is not happening, the According to the MySQL documentation, a few other interesting fields that relate to replication lag are: FieldDescriptionreplica_lag 4The name of the source binary log file that the I/O thread is currently reading from. replica_lag 5The position in the current source binary log file the I/O thread has read up to. replica_lag 6The name of the relay log file the SQL thread is currently reading and executing from. replica_lag 7The position in the current relay log file the SQL thread has read and executed up to. replica_lag 8The name of the source binary log file containing the most recent event executed by the SQL thread. In the above example In this case, the SQL thread is lagging in the replica. You can also connect to the primary database and execute:
This command shows you which binlog file is being written in the primary database. If the primary database binary log file is newer than the When the I/O thread is lagging, the The previous commands let you observe lag details while the lag is happening, but the metrics |