Exam Professional Cloud Database Engineer topic 1 question 18 discussion

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

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

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.

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

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

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".

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.

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.

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.

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.

A & C looks fine.

Ans is AC

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

Ans is AC

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.

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.

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