Professional Cloud Database Engineer Exam - Question 18

Question

Your application uses Cloud SQL for MySQL. Your users run reports on data that relies on near-real time; however, the additional analytics caused excessive load on the primary database. You created a read replica for the analytics workloads, but now your users are complaining about the lag in data changes and that their reports are still slow. You need to improve the report performance and shorten the lag in data replication without making changes to the current reports. Which two approaches should you implement? (Choose two. ).

Examice · Accepted Answer

To improve report performance and reduce replication lag without making changes to the current reports, you can take two steps. First, creating secondary indexes on the replica can improve the speed of query execution by allowing the replica to efficiently locate the required data. Second, configuring parallel replication can help reduce the lag in data changes by allowing replication processes to run in parallel, thus speeding up the data replication process. Disabling replication on the read replica, setting the flag for parallel replication, and then re-enabling replication while optimizing performance on both the primary and the replica can effectively reduce the lag.

ssaporylo · Answer

Vote for AC
A https://cloud.google.com/sql/docs/mysql/replication/read-replica-indexes  increase performance on read operation
C https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#basic-steps-to-change-parallel-replication-flags

dynamic_dba · Answer

B, C.
You have 2 problems. Replication lag and slow report performance. E is eliminated because using BigQuery would mean changes to the current reports. Report slowness could be the result of poor indexing or just too much read load (or both!). Since excessive load is mentioned in the question, creating additional read replicas and spreading the analytics workload around makes B correct and eliminates A as a way to speed up reporting. That leaves the replication problem. Cloud SQL enables single threaded replication by default, so it stands to reason enabling parallel replication would help the lag. To do that you disable replication on the replica (not the primary), set flags on the replica and optionally set flags on the primary instance to optimize performance for parallel replication. That makes C correct and D incorrect.
https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication

csrazdan · Answer

The question has 2 issues - replication lag and reports running slow.
B - will address reports running snow since fewer uses will be on the replica server
C - will address replication lag.

muky31dec · Answer

Ans is AC

KennyHuang · Answer

B. By creating additional read replicas, you can distribute the load of analytics workloads across multiple instances. Partitioning your analytics users to use different read replicas allows you to further distribute the workload and improve performance. This helps to alleviate the excessive load on the primary database and enhances the reporting experience for users.
C. Disabling replication on the read replica can help reduce the data replication lag. By setting the flag for parallel replication on the read replica, you allow parallel execution of replication threads, which can expedite data replication. Additionally, optimizing performance by setting flags on the primary instance can help improve the overall performance of the replication process and reduce the lag experienced by the read replica.

cardareel · Answer

There's no discussion about C.
A & B both sounds reasonable. Why I would choose A instead of B? Due to keywords "without making changes to the current reports" and "MySQL". Option B would require to point to new IP addresses (the new read replicas) and split which group of users which run X reports and which group of user which run Y reports connect to which read replica. Option A (secondary indexes) is only available for Cloud SQL (the question's use case is about MySQL) and explicitly mentions "for reporting purposes".

sp57 · Answer

AC - Not understanding votes for E, can't be done without some changes to reports.

Swapnil54 · Answer

A & C looks fine.

Nirca · Answer

A. Create secondary indexes on the replica. - No indication that the reports will benefit from indexes. 
B. Create additional read replicas, and partition your analytics users to use different read replicas. --> might rebalance the load.  
C. Disable replication on the read replica, and set the flag for parallel replication on the read replica. Re-enable replication and optimize performance by setting flags on the primary instance. --> might add parallelism to the replication lag.  
D. Disable replication on the primary instance, and set the flag for parallel replication on the primary instance. Re-enable replication and optimize performance by setting flags on the read replica. --> na
E. Move your analytics workloads to BigQuery, and set up a streaming pipeline to move data and update BigQuery.--> according to question statement , no SQL rewrite is possible.

PKookNN · Answer

Just got this question and there is no A, and it is not 'choose two' but one answer only.

TFMV · Answer

Moving workload to BQ is not an option. That, at a minimum, would require connection changes in the reports and the question specifically states that report changes are unacceptable. Aside from that, we do not know if the reports are being generated by a tool and whether that tool supports BQ.

muky31dec · Answer

Ans is AC

muky31dec · Answer

Creating secondary indexes on the replica can help improve the performance of the reports by allowing the read replica to quickly locate the data it needs without having to scan the entire table. This can help speed up the queries

H_S · Answer

B. Create additional read replicas, and partition your analytics users to use different read replicas. Most Voted
C. Disable replication on the read replica, and set the flag for parallel replication on the read replica. Re-enable replication and optimize performance by setting flags on the primary instance.

Hilab · Answer

B. Create additional read replicas, and partition your analytics users to use different read replicas.
D. Disable replication on the primary instance, and set the flag for parallel replication on the primary instance. Re-enable replication and optimize performance by setting flags on the read replica.

Creating additional read replicas can distribute the analytics workload and reduce the lag in data replication. By partitioning your analytics users to use different read replicas, you can further reduce the load on each replica and improve performance.

studymoreoften · Answer

B - Addresses performance improvements: Reduce the burden on the primary instance by offloading replication work to multiple read replicas.
https://cloud.google.com/sql/docs/mysql/replication#cascading-replicas
C- Addresses lag time because Parallel replication reduces replication lag by increasing the number of SQL threads that work to execute these transactions.
https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication

Lenifia · Answer

The two main issues at hand are replication lag and slow report performance. While option B, creating additional read replicas, could address the load issue, it would require changes to the current reports, which is not desirable. Instead, option A, implementing secondary indexes in Cloud SQL, could enhance report speed without altering the reports. As for the replication lag, option C suggests enabling parallel replication in Cloud SQL, which is a plausible solution. To do that you disable replication on the replica (not the primary), set flags on the replica and optionally set flags on the primary instance to optimize performance for parallel replication. Therefore, considering the constraints and the context, options A and C are the most suitable choices.

Professional Cloud Database Engineer Exam - Question 18

Discussion