Please note that this content is targeted for SysOps administrators. If 1
you're a Solutions Architect or a developer you may want to skip over this 2
one. Welcome back to backspace Academy in this lecture we'll have a 3
look at some of the tools that are available for troubleshooting our RDS 4
instances. We're also going to have a look at ways that we can troubleshoot 5
and correct issues with excessive read replicas lag. Okay so here I am in the 6
RDS console we already know how to create an RDS instance so I'm just going 7
to run through this quite quickly so get started now free tier eligible only will 8
select mySQL Community Edition we'll leave everything as it is there will use 9
a t2.micro it's on the free tier. We will give our instance an identifier, we 10
just call it test and a username and a password and next. We'll leave the RDS 11
instance to create a new security group for us, we just launch this into the 12
default subnets, we give the database a name just use tests again we'll be fine 13
and we'll leave everything else as it is, we'll do a backup that will be fine, 14
what we also will do actually is we will put a maintenance window in there 15
as well, so it's going to select a maintenance window so what that means is 16
that when we do any major updates that require a reboot 17
they will occur during this maintenance window, So if you don't want an 18
interruption to your service it's a good idea to pick a maintenance window that 19
corresponds with the lowest time that you've got usage on your database so 20
here we've got here Monday at midnight at for half an hour that will be fine 21
we'll launch that DB instance 22
so after certain amount of time again our DB instance will go from creating through 23
to available for us and after a certain amount of time our mySQL RDS 24
instance is up and running with status available. Now if we found that it didn't 25
for whatever reason become available there is there are some things that we 26
can do we can go into instance actions and we can have a look at the details 27
and we can have a look at the recent events and logs and that will give us a 28
running view of what has happened to our instance and all the events that have 29
occurred and also additional to that we have here the mySQL error running 30
log and so we can actually download that or we can just view it from here and 31
there we can see that there are a number of notes there is a warning here and but 32
there are no errors as such they've been in the log so there's nothing 33
particularly alarming there but if you found something in there that would be 34
certainly a very good troubleshooting tool to find where the problem exists 35
now we just jump back into our DB instances and we'll have a look at some 36
of the issues that can arise with connecting to our our RDS instance and 37
what we can do about it so we've got here our endpoints so we're just going 38
to copy that over and we'll jump into the mySQL workbench and let's have a 39
look at connecting into that 40
so let's just test that connection now 41
so there we can see our connection has been successful, so let's just say we had 42
the wrong username and let's see what happens here and there we can see that 43
we have an access denied so we have been able to establish a connection as such 44
but we haven't been able to authorize that user and the same would happen if 45
we uses a wrong password as well now another thing that can occur is if we 46
use the wrong port so just say we tried to connect in on port 22 that will 47
basically just hang for quite some time before it gives any message, so nothing 48
is happening it's just hanging there but over time it will eventually give a 49
message, so I'm not gonna wait for that so I'm just going to get out of that and 50
jump into telnet so I've just now jumped into the command line and I'm just going 51
to run a telnet command to do a low-level connection to our RDS instance 52
now telnet is not by default enabled in Windows but you need to go into your 53
control panel and then go into turning Windows Features on and off and select 54
telnet and turn that on and that will install the windows telnet client for 55
you, so the command that we use after we've installed telnet is telling it and 56
simply the endpoint and the port so a space and then the port and we'll 57
connect in and that has actually connected in its return something that 58
doesn't really make any sense the reason for that is that although that we are 59
able to physically connect to our RDS instance we cannot actually do anything 60
from there so we cannot actually ssh into an RDS instance and we can't 61
connect in through telnet or anything like that it doesn't let us to do that 62
but if we get a message back like this it means that the low-level physical 63
connection is okay so it means that our security groups are okay 64
VPC is okay our endpoint is okay and our Porter's okay so if we try and connect 65
in with a bad URL let's see what or bad endpoint but to see what happens 66
put a D in there and straight away it comes back and could not connect to that 67
host so this is a really good tool that if you can't really find out what's 68
going on in the mySQL workbench just jump into telling it and just see if you 69
can do a quick connection through to telling it if you can't start having a 70
look at your port your port number start having a look at your your endpoint URL 71
and start looking at your security groups in your VPC as well was that are 72
associated to that RDS instance . Now just jumping back into the RDS console 73
again what I want to have a look at now is just looking at some of the issues 74
that can arise around read replicas or mySQL read replicas and what we can 75
do some of the troubleshooting tools that are available for us to identify 76
what's going on and causing that read replicas lag and and it also some of the 77
corrective actions that we can take. So first off let's look at launching a red 78
lip replica so we go into instance actions create read replicas we'll give 79
that a name and we'll leave everything there as it is and create that raed 80
replica. 81
okay so we can see there that the master database is being modified and we are 82
currently creating that replica so after a certain amount of time that will be 83
available for us okay so after a certain amount of time we've got our mySQL 84
replica database up and running I'm just going to expand those details and I'm 85
going to click on the replication tab here on the left and there we can see 86
the replication details we've got our master database called test we've got 87
our replica database test - we can also see that we've got our replicas lag at 0 88
milliseconds which is what you'd expect because there's no database in the 89
database now if we found that we're having excessive replicas lag the first 90
thing we would do is that we would have a look at our cloud watch our cloud 91
watch metrics and our cloud watch graphs and we would see if there's anything 92
that coincides with that increase in replica lag for example if you had 93
significant write operations to your database at that time then that would 94
obviously cause it because the replication is not keeping up with the 95
speed of what which with which you are changing your data if that's a one-off 96
situation you can look at pre-warming that replica by copying data from 97
your master database over to your replica database now the problem with 98
that is that a replica instance is read-only so what you need to do is that 99
you need to temporarily change that to be writable and then change it back to 100
being read-only if you find that it's not a one-off situation you find that 101
it's an ongoing situation then the next thing you would do is that you would 102
disable your query cache, and we'll have a look at how we do that soon and if you 103
still have problems then you're going to have to look at upgrading your your 104
master database and your replica database to a larger instance type with 105
more memory or more capacity whatever now to make these changes to the query 106
cache or to make our replicas writable temporarily we need to 107
change the DB parameter groups for that instance so again what a parameter group 108
is it defines the parameters around that database application and so it doesn't 109
go into anything low level as far as a networking layer and it doesn't go 110
anything to about the operating system behind that database application it's 111
about the parameters around the my sequel database application so just 112
click on parameter groups here on the left and we can see there that we've got 113
our default mySQL parameter group now that is applied by default to all of 114
these including the read replicas to all of our RDS instance or mySQL 115
earliest instances that are launched now one thing you'll notice from this is if 116
you select it you cannot edit the parameter everything is grayed out so 117
you cannot copy them you cannot you cannot change them in any way if you go 118
to delete them it'll come up with a message saying do you really want to 119
delete that and we obviously don't want to do that so what we do is we create a 120
new parameter group and we select my sequel parameter group family for that 121
we'll give it a name we just call it test again we leave the description just 122
a test that will be fine and we'll create that parameter group so that's 123
been created so we just select it and we'll edit the parameters so we can see 124
there there's a lot of parameters that we can edit edit for our database so the 125
ones that we are concerned with will be the query which I'll just go down to and 126
read as well 127
okay so we can see here we've got query cache size and there's nothing in there 128
so by default the query cache is empty and not being utilized so if it is 129
you can disable it here but by default it is actually disabled so with our 130
replicas we can see here read-only it is currently if it is actually a replica so 131
can see true replica so if this instance is a replica it will be read-only so 132
what we can do is we can select that and we can change that to zero and so that 133
means that it is not read-only its writable and then we can go back and we 134
can save that save the changes so now that we've got a parameter group that 135
allows us to write we can now associate this with our replica database so we go 136
back into instances we'll select our replica database and we go to instance 137
actions and we go to modify so we scroll down we'll leave everything as it is 138
there and we select DB parameter group so we can change this to our new test so 139
it'll provide you with a list of all of the mySQL parameter groups that we 140
have available and because we just created a new one testi available we can 141
go through there and we can continue and we can modify that DB instance and there 142
we can see that's done so that brings us to the end of the troubleshooting 143
section for RDS and by all means go ahead and how to play around with it 144
yourself try and connect into an instance to try and play around with 145
different port numbers see what happens see if you can create a new parameter 146
group and have a play around with that yourself because that's going to be the 147
best way to learn and I'll see you in the next one