Amazon RDS and Aurora

28. Troubleshooting replica issues in RDS

Now, the replica lag, as we just talked about, can impact your recovery. So how do you troubleshoot high replica lag? So, any asynchronous replication will always have some replica lag Asynchronous replication does not have any replication lag. But in the case of asynchronous replication, you'll always see some replica lag. In cloud watch, you can also monitor the replicable metrics. It's going to show you how many seconds behind the master your replica is lagging. It typically reports that this is seconds behind master values. And there are various reasons why replication delays occur. They could be due to long-running queries on the master instance. You can view your slow query lock to see what the long-running queries that are running on the instance. Another reason for high replica lag could be that you're using a smaller instance than required.

The instance class size or storage is not sufficient. And if you're using parallel queries on the primary instance, that also results in higher replica lag. Now let's look at how to troubleshoot the replication errors. So there could be errors in replication. How do you troubleshoot them? What are the recommendations? Let's have a look at that. The recommendation from AWS is that the size of your replica should match your source database. The storage size as well as the instance class size should be the same as the source database. And you should use compatible database parameter group settings for the source as well as for the replica. For example, the maximum allowed packet size for read replica must be the same as that of the source database instance. This is just one example, but in general, the parameter group settings for the source and the replica should be the same or compatible. You can monitor the application state of your replica instance and if it shows an error, then you can see the details in the replication error field. Apart from this, you can also subscribe to Audiusevents to get alerts on any replica issues. Then writing to tables on a read replica. We have discussed this before that you can set the read only parameter to zero to make your read Replica write able. But remember to use it only for maintenance purposes.

Like, you want to create some indexes only on the replica. Then you set the read-only mode to zero to make your replica readable. And once you're done with your maintenance task, remember to set the read-only parameter back to one. Another important thing to note here is that if you write to the tables on the read replica, it might make them incompatible with the source database and break the replication. So, as I said, you should set the read-only parameter back to one immediately after completing your maintenance tasks. And replication is only supported with transactional storage engines like Innov. Inodb is a storage engine used in MySQL. Another engine that MySQL provides is my isam. And this is not very compatible with replication, and it's likely to cause replication errors. ODB is more common in use. In general, if you want to use replication, you should consider using inodb. Again, unsafe non-deterministic queries like Sydney can also break replication, so you should avoid using such queries. And finally, after you review the replication errors, if you find that these are not major errors, and if these are not a cause for concern, you can simply skip those errors.

Or another option is to delete and recreate the replica. All right, now how do you troubleshoot the read Replica issues? For example, in MySQL, replica issues are the errors or data inconsistencies between the source and the replica. And this could happen due to bin log events or inodb. Redo logs aren't flushed during replication or source instance failures. So if something like this happens, the only way is to delete and recreate the replica. There are some recommendations to prevent these issues from happening. So what you could do is set certain parameters. For example, if you sync binlock equal to one, or innov flash lockapp to your x commit equal to one, or in ODBsupport for XA, you can set it to one. And these settings are the recommendations from AWS. They might affect the performance of your database. So it's a good idea to test before implementing this in production. And from an exam perspective, you don't have to know the names of these parameters, but just remember that you have certain options to address the replica issues in my sequel. Alright, let's continue.

29. Performance hit on new read replicas

Now, this is kind of an important thing to know that when you create a new replica, you might experience slow performance on the replica. This occurs frequently because RDS snapshots are EBSnapshots that are stored in S3. And when you create your new replica, what happens when these EBS volumes are loaded lazily in the background? So only when you read certain data, that data gets loaded into the database, and other data still resides on S 3. It doesn't get loaded into the database until you specifically query that data. This is something that is called a first touch penalty. So when you query any data, it takes longer to retrieve it the first time. So how do you work around this? The simple option is, if your database is small, you can simply run the select star from table so you get all the data from S Three into your database. Another option is to initiate a full table scan with vacuum analysis options. This is supported in postgres sequel. And another reason for performance issues besides this lazy loading thing is that there could be an empty buffer pool. So the buffer pool is the cache for table and index data, and the master instance will always have this cache. But when you create a new read replica, this cache is going to be empty. So that could be another reason why there is slow performance on a new read replica.

30. Scaling and sharding in RDS

Let's look at the scaling in RDS. So you can have vertical scaling or you can have horizontal scaling. So vertical scaling means scaling up. So you have your RDS instance and you increase the size of your RDS instance. So if you have a single AC instance, when you do scale up, it's not going to be available during the scaling operation. But if you have a multi-AC set up, then you will have minimal downtime during your scaling operation because the scaling operation will first happen on the standby, and then there will be a failover to the standby, and after that, the primary instance will be upgraded. The other way to scale is through horizontal scaling, which is also called scaling out. So, this simply means that you can create additional replicas for your database instance. So, adding more read capacity is how you scale horizontally. So this is kind of useful for reading heavy workloads. And the replicas also act as DR targets, so you can also use them for disaster recovery. Now, apart from this, there is one more option that I would like to discuss, and that is sharding. So what exactly is shading? Sharding is actually horizontal partitioning. So you actually distribute the data across different databases. You can think of it as breaking your database down into smaller chunks.

So you simply split and distribute your data across multiple databases. And each database is called a "shard". Okay? So you can have your data split across multiple chunks and your application can then read or write from individual databases. And you have to really manage this at the application level. RDS doesn't inherently support shouting, you have to manage it at the application level. So mapping or routing logic should be maintained at the application tier. So you can say that shard number one contains the data of customers from one to ten thousand, shard number two contains data from customer number 10,000 to 20,000, and so on. In addition to splitting the load, this also offers some kind of fault tolerance because now there is no single point of failure. As a result, if one shark fails, the other sharks are unaffected. All right, so that's about it. Let's continue to the next lecture.

31. RDS monitoring

Now let's look at RDS monitoring. Now, monitoring in RDS is integrated with Cloud Watch. So you can do the monitoring within the RDS dashboard as well as in the Cloud Watch dashboard. So some of the common metrics that you can monitor are CPU, RAM, disc space consumption, network traffic connections, IOps metrics, and so on. You can also use native database engine logs or database extensions. For example, you can use the PG Auditextension in PostgreSQL for auditing purposes. When you enable this extension, you can really monitor the database activity that's happening on your PostgreSQL database. DML, DCL, and DDL queries can be monitored.

So, DML is a data manipulation language. That is, any insert updates that are happening in your tables correspond to DML. DCL is a data control language. Any access controls changing in your database, like grants and reworks, that's happening in your database fall into DCL. And DDL is a data definition language. So any table structure changes or schema changes fall under DDL. So you can use extensions like PG Audit to keep track of your database activity. And then there are some manual monitoring tools like Trusted Advisor, Cloud Watch, and so on. So you can definitely monitor it using the RDS console. So all the Cloud Watch metrics can be monitored through the RDS console. The AWS Trusted Advisor is a tool that you can use for cost optimization, security fault tolerance, or performance improvement checks. The Trusted Advisor gives you some recommendations that you can implement in your database, and you can also monitor the RDS service health status using Cloud Watch.

All right? And apart from this, you also have automated tools for monitoring purposes, like the RDS Event Notifications. So you can subscribe to Audius events anytime your database is modified, updated, or deleted. Whatever happens to your database, you can set up the event notifications. You can also enable the database logs of your database engines, and these can also be exported to Cloud Watch logs. Again, Cloud Watch is an automated tool. Then, apart from this, you also have something called Enhanced Monitoring, which provides a real-time dashboard for your CPU metrics. Then you have performance inside. This is a really handy tool to identify performance bottlenecks. And we're going to go into detail about these tools right now. I'm just listing it here. Then you have RDS recommendations, which are automatically generated for your databases, and you can see them in your RDS console. And you also have Cloud Trail integrated with RDS just like any other AWS service. And all it does is capture the RDS API calls. These are viewable in the Cloud Trail console. Or you can also deliver it as a trail to an S3 bucket. Okay, now you can view up to 90 days of your account activity within the Cloud Trail Console. But if you want to store it for longer, then you should create a trail and deliver that trail to S Three, so you can then access the audit logs in s three. Alright, that's about it. Let's continue.

32. RDS event subscriptions, recommendations and logs

Now let's look at the RDS notifications. They are also called the event subscriptions. These are available within the RDS console. We'll go through a hands-on and look at everything, but for now, just know that these are available in the Arias Console.

And all it does is allow you to create Cloud Watch alarms to notify you whenever certain metric data crosses a threshold. So it's simply integrated with Cloud Watch and SMS services. So you get the notifications through the SMS service. What you can do is you can send your alarm notifications to an SMS topic. So you can get an email or a text message whenever these events occur. So all you have to do is to subscribe to any of the audience events that you need notification for right now. Event sources can be like snapshot instances, securitygroups, parameter groups, clusters, cluster snapshots, so on almost anything that's there in the Audius architecture, you can get event notifications on that. So you can have notifications on events like database instance creation, deletion, shutdown, restart, backup, recovery failure, and so on. So you can be notified about things that are relevant to your use case. Right, then let's look at the audience recommendations. These are periodic automated suggestions that are generated within your RDS Console.

Okay? You can see certain recommendations like enhanced monitoring are not enabled and databaseclusters with only one database instance. What they are suggesting is that you use multiple instances or you use a multiAZ setup for improved performance and availability. All right, so these are the kinds of recommendations that are available, and they are automatically generated within the RDS Console. All right, now let's look at the logs. The RDS logs Of course, you can view, watch, or download these logs from the RDS Console. You can export them to Cloud Watch logs. Of course, the types of logs are determined by the database engine. Another important thing to remember is that Cloud Watch logs do not expire. If you really want to expire them, you can set the group retention policy from one day to about ten years. If you don't want to expire them, don't expire them unless you want them to. And these logs are accessible from the RDS Console even if you disable the log export to Cloud Watch logs. Okay? Generally, it's a good idea to enable log export.

So the logs are exported from RDS to the Cloud Wash logs, and there are different lock types that can be exported to the Cloud Wash logs, which vary by the database engine. So you can have all the logs for Oracle, you can have an Audit Log for Oracle, MariaDB, and MySQL, and you have the MariaDB Audit Plugin,which you can enable using the Option group. And this will help you audit the database activity. So any DML, DDL, DCL, or queries running on your MySQL or MariaDB can now be audited using the MariaDBaudit plugin; similarly, the PG audit extension, as we just discussed, can audit the database depending on the type of database engine.

So you may want to remember that sometimes it might help in your example, right? Then you have listener login in Oracle. You also have atrace log in in SQL Server. MySQL Maradib, you have error logs in Postgres. You have a Postgres SQL log, which also contains audit logs. Postgres SQL also has an upgrade log. Mariahdb and MySQL have a general log, and they also provide you with a slow query log before we close. One thing I want to emphasise here is that you don't have to know which database engine provides which lock; you don't have to know it by the letter. But it's a good idea to have these things in the back of your mind. They'll help you choose the right responses when questions related to these topics get asked in the examination. All right, so that's about it. Let's continue.

Prepared by Top Experts, the top IT Trainers ensure that when it comes to your IT exam prep and you can count on ExamSnap AWS Certified Database - Specialty certification video training course that goes in line with the corresponding Amazon AWS Certified Database - Specialty exam dumps, study guide, and practice test questions & answers.