Balancing Database Read and Write Queries with Replication Lag Handling Anand Fri, 10/May/2024 - 18:15 pm

Co-author’s:

1. Pankaj Pandey (Senior Technical Architect @ Tata 1mg)
2. Prashant Mishra (Technical Architect @ Tata 1mg)
3. Vimal Sharma (Technical Architect @ Tata 1mg)

In our pursuit of optimizing query performance, we’ve devised a robust strategy to manage read and write queries effectively, despite replication lag. By segregating read and write operations, we prevent undue strain on our database during high loads. To tackle potential replication lag challenges, we’ve implemented an intelligent mechanism that closely monitors lag for specific tables. When lag is detected, we seamlessly redirect read requests to the primary node for those tables, ensuring that users always access the most up-to-date data. This innovative approach not only boosts system responsiveness but also upholds data consistency, ensuring a smooth user experience, regardless of underlying conditions.

Our Approach to Query Management

• Smarter Reads: We divert read queries to replicas, lightening the load on the master and freeing it up for write tasks.
• Mastering Writes: Write queries are directed straight to the master, ensuring data integrity and efficient handling.
• Replication Lag? No Drag: Our system adeptly tackles replica lag. When detected, read queries are intelligently routed to primary, so you always get the freshest data.

How did we Implement?

Initially, we segregated read queries to replicas and write queries to the primary using regex by identifying SELECT statements. Yet, replica data synchronization could lead to replication lag. Our solution? Leveraging PostgreSQL’s Log Sequence Number (LSN) concept to manage this complexity.There are two types of LSNs:

1. Write Log Sequence Number (write LSN): This marks where a data modification was written in the Write-Ahead Log (WAL), recording database changes.
2. Replay Log Sequence Number (replay LSN): This indicates the point till which changes are applied during replication or recovery.

Our strategy revolved around storing both the write LSN for individual tables and the replay LSN for the database within Redis. This enabled us to make informed decisions when it came to recognizing replication lag in tables crucial to our queries. If a lag was detected, we seamlessly routed those queries to the primary database, ensuring the freshest data.Each write action, encompassing insert, delete, or update operations on models or tables, triggered a post-save method within the application. This method tapped into PostgreSQL to retrieve both the write LSN and the replay LSN. Subsequently, these LSNs were updated in Redis, affixed with keys such as “DB_NAME-TABLE_NAME-write_lsn” and “DB_NAME-replay_lsn”.For LSN retrieval, we employed a PostgreSQL query:select write_lsn, replay_lsn, client_addr from pg_stat_replication where usename='rdsrepladmin' and application_name='walreceiver' order by replay_lsn asc limit 1Dealing with multiple replicas, we determined the minimum replay LSN to guarantee comprehensive coverage for replication lag, even in the least updated database.Before executing a select query, Redis came into play. We checked for the existence of keys such as “DB_NAME-TABLE_NAME-write_lsn” and “DB_NAME-replay_lsn”. Based on the values of these keys, our decision matrix was clear:i. If the keys were not found, the select query was sent to a replica.ii. If the write LSN key was located, a comparison ensued against the replay LSN key. If the write LSN was less than or equal to the replay LSN, the query took the path to the replica; otherwise, it headed to the primary.In the event of any condition faltering, our default protocol was to reroute the select query to the primary.While this approach introduced some latency because of Redis operations, the expected impact remained in the sub-millisecond range. In worse cases, it correlated with the timeout set in the Redis operation during the select process.We have centralized the implementation of this logic within our ORM wrapper layer. This allowed all our services to access this functionality without the need to duplicate the code across multiple service boundaries.The above approach is effective for handling write queries executed through the application and will work in all scenarios.However, it may not provide the same level of control when write queries are executed directly on the database. To address this, we have developed a dedicated service called “DB Tracker”. This service is designed to monitor and record the Log Sequence Numbers (LSNs) associated with direct database queries.

DB Tracker Responsibilities:

• The DB Tracker Tool establishes connections with each Primary RDS (Relational Database Service).
• When a service with read/write split changes is deployed, it pushes the details of all master databases it connects to into the “database_names” Redis set, using the “sadd” command.
• These changes are implemented within the service’s Object-Relational Mapping (ORM), and the DB details are pushed to the Redis set during the service’s restart.
• Sample master DB details to be pushed include host, port, database name, user, and password.

{ "host": connection_params["host"], "port": connection_params["port"], "database": connection_params["database"], "user": connection_params["superuser_user"], "password": connection_params["superuser_password"]}

• The DB Tracker tool periodically reads this Redis key to maintain an updated list of databases and establishes a connection with each one.

The DB Tracker has two primary responsibilities:1. Replication Lag Management: It identifies the replay_lsn for all databases listed in Redis and then updates this information in a designated Redis key for each respective database.2. Table Dependency Management:

• For each database listed in above json code snippet, the DB Tracker Tool examines all tables and generates a list of table dependencies.
• A table “B” is considered dependent on table “A” if one of the database triggers on table “A” updates data in table “B” whenever data in table “A” is updated.
• To identify these triggers and trigger-procedures, the tool queries the “information_schema.triggers” table, checking for events related to a specific DB table.

SELECT * FROM information_schema.triggers WHERE event_object_table = '';

• The “action_statement” column of the “information_schema.triggers” table contains the trigger-procedure name. Pattern matching is used to extract this name.
• Next, the “pg_proc” table is queried to retrieve the source code of the trigger-procedure, which is stored in the “prosrc” column.

SELECT prosrc FROM pg_proc WHERE proname = '';

• With the trigger-procedure source code in hand, pattern matching is again used to identify all the table names (dependent tables) mentioned in the source code.
• The core objective behind maintaining this dependency list is to facilitate the retrieval and updating of the write_lsn for all tables that are dependent on a specific table when a write query is executed on that particular table in application.

Essentially, the DB Tracker helps to manage non-application queries directly executed on the database, ensuring smooth operations.Trade Offs in Existing Open Source Solutions: We had explored numerous available tools. However, none of these solutions perfectly suited our needs.

• Many of the tools we evaluated relied on a simple yet less-than-ideal approach: routing all read queries to replica databases completely. While this approach was straightforward, but it introduced latency and replication lag into the system.
• Another approach we encountered was the use of static delays in query routing configurations. For instance, some tools suggested adding a fixed delay of, say, 30 seconds in application configuration. All read queries initiated within this time frame would be directed to the primary database before being allowed to reach the replicas. This approach proved less effective, especially in environments with frequent write operations, as it resulted in the majority of read queries being funneled to the primary database.
• While there were some of the paid solutions which incorporate caching mechanisms, but these came with a significant cost.Given the scale of our operations and the need for a highly efficient solution, we recognized the need for a more tailored and intelligent approach. We developed an in-house system that intelligently routes read queries, ensuring optimal performance and resource utilization for our specific use case.

In conclusion, our approach to query performance optimization, replication lag handling, intelligent routing, and effective resource utilization, along with the contribution of the DB Tracker tool, results in a robust, high-performance, and scalable system. This strategy ensures that our system consistently delivers exceptional user experiences, regardless of the underlying conditions. It also emphasizes optimal resource allocation, enabling our system to operate efficiently and at scale.

Balancing Database Read and Write Queries with Replication Lag Handling was originally published in Tata 1mg Technology on Medium, where people are continuing the conversation by highlighting and responding to this story. Reference https://medium.com/1mgofficial/balancing-database-read-and-write-queries-with-r… GUID https://medium.com/p/b43b2686ff11 Category Feed database-performance database-scaling distributed-database master-slave replication-lag Blog Author Aman Garg Feed Source 1mgofficial

Saving passwords on a computer can pose security risks if not done carefully. To mitigate these risks, consider following these best practices. 
 

Handloom artists have high design skills but produce by hand, making the process laborious. In this direction, #DigiBunai is helping weavers and designers to produce these products in less time and effort with the use of digital technology. #DigitalIndia 
@digibunai
 

Know what #ABHA is and how it works. The right information will help you make the right decisions! 

उमंग ऐप दिल्ली मेट्रो से आपकी यात्रा को आसान बनाता है। अभी ऐप डाउनलोड करें! 

New study suggests humans evolved to run on less water than our closest primate relatives Anand Mon, 15/Apr/2024 - 14:04 pm

Pop ‘N Go Playpen: Portable tent for babies, toddlers or pets Anand Mon, 15/Apr/2024 - 01:57 pm

Airbus unveils ‘bird of prey’ concept plane Anand Mon, 15/Apr/2024 - 01:56 pm

ALL ABOUT BRAKE BOOSTER !! Anand Mon, 15/Apr/2024 - 01:54 pm

Know Why Washer is Used With Nut and Blot !! Anand Mon, 15/Apr/2024 - 01:50 pm

COMMON RAIL DIRECT INJECTION (CRDi) EXPLAINED !! Anand Mon, 15/Apr/2024 - 01:49 pm

Why wind turbines are always painted white ? Anand Mon, 15/Apr/2024 - 01:48 pm

Know Why Manholes are Round in Shape!! Anand Mon, 15/Apr/2024 - 01:46 pm

What is difference between COOLANT and LUBRICANT explained ? Anand Mon, 15/Apr/2024 - 01:45 pm

FUNCTION OF SPOILER IN CARS EXPLAINED !! Anand Mon, 15/Apr/2024 - 01:43 pm

Vaccines and immunization Credits WHO response Anand Mon, 15/Apr/2024 - 13:05 pm

Doon Apex Vision (Dav) Pvt Iti is situated in Hanumangarh Rajasthan. Doon Apex Vision (Dav) Pvt Iti is Industrial Training Institute under NCVT Doon Apex Vision (Dav) Pvt Iti. Location of Doon Apex Vision (Dav) Pvt Iti is WARD NO. 41, BIJLI COLONY ROAD, SECTOR 6, HANUMANGARH JN. Hanumangarh Rajasthan.

Institute Type:  Private ITI

Private ITI is leading educational organisatin in india. Teching facualty of Private ITI is suprimo. ITI is  providing latest Job oriened cource for student. This Private ITI is powerd by 'Ministry of Skill Development and Entrepreneurship, Government of India for Craftsmen Training Scheme . ITI is registered by government under NCVT .

Estd: 20-May-2014ITI code: PU08001627Contact No: 263669E-mail: [email protected] Website: http://www.daviticollege.com ITI Website

Address: WARD NO. 41, BIJLI COLONY ROAD, SECTOR 6, HANUMANGARH JN.Rajasthan District: HanumangarhPin Code: 335512 State: RajasthanState:  Rajasthan Official Website: http://www.livelihoods.rajasthan.gov.in/dtetrg.html

Rajasthan was the first State in India to establish the Mission on Livelihoods, in September 2004, under the chairmanship of the Hon’ble Chief Minister in order to address the challenges of unemployment and ensuring gainful and sustainable employment. The objective of creation of RMoL was to formulate appropriate and innovative strategies to promote and facilitate large scale livelihoods for the poor and vulnerable people.

Trade:  Electrician Syllabus: Syllabus for ITI Trade ElectricianPowerTrades Duration: Two YearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Electrician is powered by NCVT.  ITI trade Electrician  is a job oriented trade ITI trade Electrician is suitable for government job and private job. This ITI trade Electrician is very powerful for self-empowerment. This ITI trade Electrician is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

• Electricians have a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. 
•  After successful completion of this course, Electricians can aspire to become Electrical Contractors by acquiring the ‘B” licence from the Electrical Licence Board. 
• They can set up their own Rewinding and servicing of Domestic Equipment shop.  
• Job opportunities are wide open in Defence, Railways, Transport, Ship Building, Electricity Board, various  Industries etc
• They can also go for further higher studies after successful completion of course.
• Students who have completed this course have found employment in the following areas:
• Service/Maintenance Technician for domestic appliances in Reputed Companies
• Winder of Electrical Motors in winding shop
• Contractor for domestic wiring and industrial wiring
• Armature winder of Electrical fans and motors
• Electrical appliance repair in electrical shops
• Indian Railway (Asst. Driver, Tech. Gr. III, Appr. Technician)
• Local Electricity Board
• Assembler of Electrical control Gears
• Telephone Department
• Installation and Testing division of Auditorium and Cinema Hall
• Factories
• As Instructor in Govt./Private ITI/ITC
• Merchant Navy
• Indian Air Force
• Self-Employment in Service Centre

Student Help Portal: https://electrician.iti.directory Map: 

Shri Krishna Private Industrial Training Institute is situated in Jaipur Rajasthan. Shri Krishna Private Industrial Training Institute is Industrial Training Institute under NCVT Shri Krishna Private Industrial Training Institute. Location of Shri Krishna Private Industrial Training Institute is Kalu Ka Bass Tigariy, Jaipur Jaipur Rajasthan.

Institute Type:  Private ITI

Private ITI is leading educational organisatin in india. Teching facualty of Private ITI is suprimo. ITI is  providing latest Job oriened cource for student. This Private ITI is powerd by 'Ministry of Skill Development and Entrepreneurship, Government of India for Craftsmen Training Scheme . ITI is registered by government under NCVT .

Estd: 01-Aug-2014ITI code: PR08001572Contact No: 9667040085Website: http://www.shrikrishanapvtiti.com ITI Website

Address: Kalu Ka Bass Tigariy, JaipurRajasthan District: JaipurPin Code: 303804 State: RajasthanState:  Rajasthan Official Website: http://www.livelihoods.rajasthan.gov.in/dtetrg.html

Rajasthan was the first State in India to establish the Mission on Livelihoods, in September 2004, under the chairmanship of the Hon’ble Chief Minister in order to address the challenges of unemployment and ensuring gainful and sustainable employment. The objective of creation of RMoL was to formulate appropriate and innovative strategies to promote and facilitate large scale livelihoods for the poor and vulnerable people.

Trade:  Electrician Syllabus: Syllabus for ITI Trade ElectricianPowerTrades Duration: Two YearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Electrician is powered by NCVT.  ITI trade Electrician  is a job oriented trade ITI trade Electrician is suitable for government job and private job. This ITI trade Electrician is very powerful for self-empowerment. This ITI trade Electrician is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

• Electricians have a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. 
•  After successful completion of this course, Electricians can aspire to become Electrical Contractors by acquiring the ‘B” licence from the Electrical Licence Board. 
• They can set up their own Rewinding and servicing of Domestic Equipment shop.  
• Job opportunities are wide open in Defence, Railways, Transport, Ship Building, Electricity Board, various  Industries etc
• They can also go for further higher studies after successful completion of course.
• Students who have completed this course have found employment in the following areas:
• Service/Maintenance Technician for domestic appliances in Reputed Companies
• Winder of Electrical Motors in winding shop
• Contractor for domestic wiring and industrial wiring
• Armature winder of Electrical fans and motors
• Electrical appliance repair in electrical shops
• Indian Railway (Asst. Driver, Tech. Gr. III, Appr. Technician)
• Local Electricity Board
• Assembler of Electrical control Gears
• Telephone Department
• Installation and Testing division of Auditorium and Cinema Hall
• Factories
• As Instructor in Govt./Private ITI/ITC
• Merchant Navy
• Indian Air Force
• Self-Employment in Service Centre

Student Help Portal: https://electrician.iti.directory Map: 

Shri Minesh Private Industrial Training Institute Jamwa is situated in Jaipur Rajasthan. Shri Minesh Private Industrial Training Institute Jamwa is Industrial Training Institute under NCVT Shri Minesh Private Industrial Training Institute Jamwa. Location of Shri Minesh Private Industrial Training Institute Jamwa is Near Bus Stand Jamwa Ramgarh Jaipur Rajasthan.

Institute Type:  Private ITI

Private ITI is leading educational organisatin in india. Teching facualty of Private ITI is suprimo. ITI is  providing latest Job oriened cource for student. This Private ITI is powerd by 'Ministry of Skill Development and Entrepreneurship, Government of India for Craftsmen Training Scheme . ITI is registered by government under NCVT .

Estd: 01-Aug-2008ITI code: PR08000425 ITI Website

Address: Near Bus Stand Jamwa RamgarhRajasthan District: JaipurState: RajasthanState:  Rajasthan Official Website: http://www.livelihoods.rajasthan.gov.in/dtetrg.html

Rajasthan was the first State in India to establish the Mission on Livelihoods, in September 2004, under the chairmanship of the Hon’ble Chief Minister in order to address the challenges of unemployment and ensuring gainful and sustainable employment. The objective of creation of RMoL was to formulate appropriate and innovative strategies to promote and facilitate large scale livelihoods for the poor and vulnerable people.

Trade:  Electrician Syllabus: Syllabus for ITI Trade ElectricianPowerTrades Duration: Two YearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Electrician is powered by NCVT.  ITI trade Electrician  is a job oriented trade ITI trade Electrician is suitable for government job and private job. This ITI trade Electrician is very powerful for self-empowerment. This ITI trade Electrician is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

• Electricians have a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. 
•  After successful completion of this course, Electricians can aspire to become Electrical Contractors by acquiring the ‘B” licence from the Electrical Licence Board. 
• They can set up their own Rewinding and servicing of Domestic Equipment shop.  
• Job opportunities are wide open in Defence, Railways, Transport, Ship Building, Electricity Board, various  Industries etc
• They can also go for further higher studies after successful completion of course.
• Students who have completed this course have found employment in the following areas:
• Service/Maintenance Technician for domestic appliances in Reputed Companies
• Winder of Electrical Motors in winding shop
• Contractor for domestic wiring and industrial wiring
• Armature winder of Electrical fans and motors
• Electrical appliance repair in electrical shops
• Indian Railway (Asst. Driver, Tech. Gr. III, Appr. Technician)
• Local Electricity Board
• Assembler of Electrical control Gears
• Telephone Department
• Installation and Testing division of Auditorium and Cinema Hall
• Factories
• As Instructor in Govt./Private ITI/ITC
• Merchant Navy
• Indian Air Force
• Self-Employment in Service Centre

Student Help Portal: https://electrician.iti.directory Mechanic Diesel Syllabus: Syllabus for ITI Trade Mechanic DieselAutomotiveTrades Duration: One yearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Mechanic Diesel is powered by NCVT.  ITI trade Mechanic Diesel  is a job oriented trade ITI trade Mechanic Diesel is suitable for government job and private job. This ITI trade Mechanic Diesel is very powerful for self-empowerment. This ITI trade Mechanic Diesel is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

Repairs, services and overhauls diesel or oil engines for efficient performance as prime mover to drive different machineries and equipments. Examines engine by dismantling it for locating defects and for necessary repair/replacement and then reassemble it ensuring accuracy of fittings and performance as per specified standards. Checks, adjusts and lubricates engine periodically and performs such other functions to keep engine in good working order. 

On successful completion of this course the candidates shall be gainfully employed as:

• 1. Mechanic of Diesel Engine, Oil Engine in Automobile Industries
• 2. Fitter of diesel or oil engines
• 3. Self-Employment
• 4. Instructor of Diesel Mechanic after doing CITS course under NCVT

Mechanic Diesel has a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. On successful completion of this course, the candidates shall be gainfully employed in the industries for following occupations:

• Mechanic
• Diesel Engine.

Map: 

Shri Narayan Private Industrial Training Institute, is situated in Jaipur Rajasthan. Shri Narayan Private Industrial Training Institute, is Industrial Training Institute under NCVT Shri Narayan Private Industrial Training Institute,. Location of Shri Narayan Private Industrial Training Institute, is Gram. Bagwada, Via-Moriza, Teh. Amer, Jaipur Rajasthan.

Institute Type:  Private ITI

Private ITI is leading educational organisatin in india. Teching facualty of Private ITI is suprimo. ITI is  providing latest Job oriened cource for student. This Private ITI is powerd by 'Ministry of Skill Development and Entrepreneurship, Government of India for Craftsmen Training Scheme . ITI is registered by government under NCVT .

Estd: 01-Aug-2012ITI code: PR08000887 ITI Website

Address: Gram. Bagwada, Via-Moriza, Teh. Amer,Rajasthan District: JaipurState: RajasthanState:  Rajasthan Official Website: http://www.livelihoods.rajasthan.gov.in/dtetrg.html

Rajasthan was the first State in India to establish the Mission on Livelihoods, in September 2004, under the chairmanship of the Hon’ble Chief Minister in order to address the challenges of unemployment and ensuring gainful and sustainable employment. The objective of creation of RMoL was to formulate appropriate and innovative strategies to promote and facilitate large scale livelihoods for the poor and vulnerable people.

Trade:  Electrician Syllabus: Syllabus for ITI Trade ElectricianPowerTrades Duration: Two YearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Electrician is powered by NCVT.  ITI trade Electrician  is a job oriented trade ITI trade Electrician is suitable for government job and private job. This ITI trade Electrician is very powerful for self-empowerment. This ITI trade Electrician is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

• Electricians have a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. 
•  After successful completion of this course, Electricians can aspire to become Electrical Contractors by acquiring the ‘B” licence from the Electrical Licence Board. 
• They can set up their own Rewinding and servicing of Domestic Equipment shop.  
• Job opportunities are wide open in Defence, Railways, Transport, Ship Building, Electricity Board, various  Industries etc
• They can also go for further higher studies after successful completion of course.
• Students who have completed this course have found employment in the following areas:
• Service/Maintenance Technician for domestic appliances in Reputed Companies
• Winder of Electrical Motors in winding shop
• Contractor for domestic wiring and industrial wiring
• Armature winder of Electrical fans and motors
• Electrical appliance repair in electrical shops
• Indian Railway (Asst. Driver, Tech. Gr. III, Appr. Technician)
• Local Electricity Board
• Assembler of Electrical control Gears
• Telephone Department
• Installation and Testing division of Auditorium and Cinema Hall
• Factories
• As Instructor in Govt./Private ITI/ITC
• Merchant Navy
• Indian Air Force
• Self-Employment in Service Centre

Student Help Portal: https://electrician.iti.directory Mechanic Diesel Syllabus: Syllabus for ITI Trade Mechanic DieselAutomotiveTrades Duration: One yearMinimum Qualification Eligibility: 10th Passed under 10+2 System with Science & MathsTrade Type: Engineering

ITI trade Mechanic Diesel is powered by NCVT.  ITI trade Mechanic Diesel  is a job oriented trade ITI trade Mechanic Diesel is suitable for government job and private job. This ITI trade Mechanic Diesel is very powerful for self-empowerment. This ITI trade Mechanic Diesel is perfectly design to fulfill industrial requirement of Indian Industries as well as International industries.

Repairs, services and overhauls diesel or oil engines for efficient performance as prime mover to drive different machineries and equipments. Examines engine by dismantling it for locating defects and for necessary repair/replacement and then reassemble it ensuring accuracy of fittings and performance as per specified standards. Checks, adjusts and lubricates engine periodically and performs such other functions to keep engine in good working order. 

On successful completion of this course the candidates shall be gainfully employed as:

• 1. Mechanic of Diesel Engine, Oil Engine in Automobile Industries
• 2. Fitter of diesel or oil engines
• 3. Self-Employment
• 4. Instructor of Diesel Mechanic after doing CITS course under NCVT

Mechanic Diesel has a wide scope of Employability ranging from self-employment, contractual employment to Industrial jobs. On successful completion of this course, the candidates shall be gainfully employed in the industries for following occupations:

• Mechanic
• Diesel Engine.

Map: