Optimizing ScyllaDB Performance via Observability

the first one is things you can do to improve performance and maybe it’s not currently an issue but changing it now would prevent performance harm in the future the second topic is when you reach the limit of your cluster there are several potential bottlenecks such as CPU disk networks and even total memory size plays a role so even with the best setup there will always be limits and when you reach those limits you will you will need to resize your cluster so what’s on the agenda for today and we’ll talk a little bit about the cellar monitoring stock what it has to offer and we’ll take a sneak peek at a few dashboards and we’ll also be chatting about the common mistakes people makes with data models and queries and how to avoid them and last but not least we’ll talk about what to do if you notice the performance degradation including how to use alerts to your advantage so let’s let’s to cover so let’s get started and let’s talk a little bit about the monitoring stack um cell monitoring stack is usually a container based and you can you can also install it on-prem but usually we do it that they would prefer we suggest container base and it uses a Prometheus node export that grids from null exporter and ScyllaDB we have a grafana Loki alert manager and we use grafana for um shown dashboards check out check out this cool diagram of the cell monitoring stack and from the from the right bottom and we got Prometheus that collect the old Oliver matrixes it reads from both from ScyllaDB directly and OS related metrics from node exporter upper up we have the gear file locking and that’s a utility that can read logs it uses RC’s login in the pictures it does the blue lines and it passed logs and it both generate alerts based on those logs and it can also serve as a data source for grafana now the alert manager on top of that is the entity that gets alerts and moves them to other systems so it’s like uh the the monitoring stack Postman that sends you you send it to a letter and it sends it forward it’s also let me just also up to this data source for grafana and last name at the top we have gurfana that serves our dashboards rafana takes its input from Loki from Prometheus the alert and also it has an option to read directly from ScyllaDB and show you secure table so that was um about just the monitoring in general now let’s talk a little bit about the development phase so when you develop a develop it in in nosql situation it’s a little bit different than when you’re developing in in other situations first um the first thing to consider is your queries you need to know in advance what you’re going to query and that would impact your data model and when you create data model there are a few other things that you need to consider and you first of all indexing usually you know SQL database you only use one index so you need to to remember first like consider first what are you going to look or search for and second if you’re searching for multiple things in what order are you going to search searching for in case you’re you’re returning multiple results

now that’s a technical decision second does the consistency and the availability that goes together and most of the time when you’re talking about consistency we’re talking about read after write the consistency that means that you if an application first write something and then read that same entity you expect it to read what you just wrote now that’s and that’s consistency it’s not always the case but in some in most of most of the cases that’s what you expect from an application and that’s on the product level the question because it’s it’s a product decision it’s not only technical and last does the the replication aspect of things like how many time the data is replicated has you know additional meaning regarding how much does it cost and is it the system available or not and that’s also a business decision so all those things like I’m trying to I’m starting from the pure technical to the more business things has any implication when you’re designing your data model now when you one of the thing is when you do that like when you create your data model you thought about you created it you had some assumption about your traffic because you have some assumption about you know what you’re going to read and when you’re going to test your application in the testing phase make sure that the traffic is as close as possible to what you expect in production and last it’s very important to say after you know you simulate it and you tested it and you you finally you deploy your application to production make sure to return again and test your assumption test in real life testing production that actually that the traffic that you’re getting the results that you’re getting is what you expected all the way and make sure that you do that you know as soon as possible so while you can still make changes without a service interruption so replication a water bottle replication now let’s see what happens when a client tries to send a request to a cluster when we have three nodes replication now the client all nodes in ScyllaDB are symmetrical all nodes are the same so the client just choose a node that node becomes the coordinator for the rest of the of the request the coordinator node find out which nodes which other nodes hold the actual data let’s say that it’s uh it’s a read those nodes are the replicas they’re the ones that replicates the data so the coordinator node would send the request to all the replicas waits for the for the reply and that in the in when it rates for the replies there is a question like how many replies that you need to get before answering the client that’s the consistency level that we’ve we’ve mentioned before and we’ll talk about later and then it would enter the client if it’s successful success if not if not now one thing to see interesting here is that that situation because the client can reach any node it can reach a node that already has some of the data it can reach a coordinator that is also a replica okay so we have one loaded act just for this request in two rows it’s both a coordinator and a replica now when you do that when that happens okay when the client reach a coordinator that is also a replica we saved at least one hope it’s like it’s way better performance and to do that the client or it’s not the client it’s the driver of the client need to be do it you need to know where the data is and for that the clients need or again the driver need to be token aware token aware is to know where the data is okay so one thing to take from that is always use token aware and driver now we’ve talked about um in theory let’s talk a little bit about um about dashboards and the first dashboard that we want to talk about is a is the cql and specifically the SQL optimization and still a monitoring comes out of the box with multiple multiple dashboards for um the SQL optimization that is found inside SQL is here to help you identify potential problems and just a quick a quick note that I need to to say before it will continue something to consider each um sealer version has its own minor the the um its own dashboard set like if I’m using uh if you’re using Silo open source 5.1 and I’m using open source 502 there could be minor changes between the dashboards just keep that in mind it’s not a very it’s usually changes are not very big but there are minor changes so the SQL optimization is it looks this is how it looks like when there’s not nothing happens and you see that there are a bunch of gauges and graphs and ideally all gauges should be zero and so are the graphs and if something is not zero here and we’ll take a few examples it means that there is a potential problem so um we look at a few examples just to make it clear

so the mess the first that I want to start with it’s like it’s the most important uh by far the most important problem that still can happen not prepared statements now when you see this is like this is an example this is a test example that I did with non-prepared statement and one of the things that you’ll notice when you have done prepared statements is that you also see many times that the nantoken aware query gauge and graph is also non-zero now and the reason for that is that one of the reason why it’s such a big problem of using unprepared statement is if the if you don’t use prepared statement your driver will not find the coordinator that is also a replica that we just talked about before so we’ve all been we you know everyone that ever wrote code with a database did like they did this once this kind of thing like we um put our our data inside a query right so we all did that once so and by doing that there are multiple things that are wrong with that thing like it it would work okay but it’s but it’s wrong first of all and the one thing that you just talked about is that um your driver will not know where to find the data there are other things that beside performance um and performance also if if you’re doing the same query again and again you also save Siri also saves ScyllaDB to parse that query and but also security reasons and prepared statement are safer there are a a way to help you with SQL injection so the right way of doing that and as you can see the example is in Python just because it’s straightforward

the um as you can see it’s it’s pretty simple you prepare a statement once and use it that later

so um first and again the most important thing is always prepare your statement and that’s I guess the most important thing that you can take from this talk so let’s look at other examples now we’ve talked about um you know what’s something that is really important that’s something that is non-trivial

um in when you do select you can sort your results by um with the clustering key and you define the clustering key when you define the table and again that’s one of the thing why you should think in advance about your new data model about what you’re going to query it’s it is legal to sort your result regardless of the order that it was defined but you get better results if you sort your query by by the defined order another reversed one so reverse order reads is when you order something let’s say you you define your clustering key in a scanning way and you read them discounting and it’s it’s bad for performance it’s again something that you need to think in advance and even worth changing afterwards

um consistency means that if an application writes and then reads it should see the data I just wrote to make sure this happens we have something called the consistency level this determines whether the coordinator waits for the replicas to response before replying generally we use consistency level Quorum and specifically a local Quorum a local in any of the consistency means that if you have multiple data center your client or your driver would connect to the closest Data Center and that’s of course it’s good for performance so it also has a as an impact about costs specifically in the cloud and so as a general rule unless you have a good reason why not to use consistency level for room consistency level any that is mentioned here is something that is is still there for for historical reason in general don’t use it ever there’s no reason real reason why to and consistency level it may seem harmful it means that you always wait for all the coordinator always wait for all the replies um it’s not usually it’s not a good idea it can hurt your um availability because if not all nodes are up and the cluster will return it will reply with an error and so usually um it’s avoid using that and consistency level one you should really know what you’re doing if you’re using that for most cases it can hurt your consistency like in consistency level one you can write something and then we read something else so unless this is a very good reason for that don’t another pitfalls that I want to touch and allow filtering allow filtering is when you try to fill your data not according to um according to the to your index and as we’ve said before that’s part of the thing what you should consider what you’re going to query in filter so and definitely it’s it’s legal you can do that if you want to filter without a something that is not based on your index okay you do a tell the city with the cql Del ScyllaDB look I know what I’m doing and you add the allow filtering syntax to your query but but to be careful because what can happen is that Sarah would do a lot of work without you noticing it because it does that um the server would read more than it needs to and reply only a subset of the result so we do will have gauge and graphs to show you that but first that you’re doing that and second like what is the Unseen overhead like how much ScyllaDB is reading more than it actually uses we’ve talked about reverse order reads okay so again it’s legal you can do that but um it’s usually better to do um to query in the same order of the index and full range scans and can hurt your um when you do like a a scan that is really really big on a really big table and what happens is you pollute your cash so the cache is not very useful if you have a really either full range scan or almost full range scan so in that situation if you if it if you are doing that avoid using the cache and the way you do that is you add a bypass cache to a query and last thing that also is most of the time you know you don’t need to worry about it because the drivers does it for you but if you have a query that returns lots of results and use make sure that you’re using paging and don’t try to read everything at the same time it’s a better performance both Priscilla and for the client so

up until now with the focus on optimization like which is important and not only optimization but also pitfalls you know something that you did wrong no now let’s talk about when something is is going wrong like it’s still a used to be fine everything was fine but now it’s not and there could be many reasons for that or multiple reason for that

welcome to the overview dashboard this is usually like it’s the usually the first place and most of the time the last place that you look at to see what’s going on with your cluster or to put it simply to answer the question is everything okay the dashboard includes information about running nodes load request weight and latencies and sometimes it’s clear when there’s a problem like when a node is down and or when you run out of disk space and you see an indication you’ll see an alert on that in the dashboard and other sometimes it’s not it can be trickier and things like request for seconds and latencies is something that varies over the time of day and over and between their weeks we did try to show something like that like it’s a relative um graph as well but it is something that you need to be familiar with so I I do suggest you know to regularly monitoring your system you’ll need to do that you know every day and but do know your numbers so know like what to expect so if you think that there’s something wrong if something is wrong

you have a feeling like you you know okay this is like a higher latency that I I’m used to so what we are looking for when uh check for in the overview dashboard um so here’s a few things to keep an eye uh eye for first uh have a failure and a node is going down in this example we see one that is in red and timeout and latency spikes and this could indicate like when you start seeing time out the license spike it could be an indication that the system is overloaded and maybe it’s time to resize which a lot of times good and an imbalance in the cluster we have an imbalance section and because ScyllaDB is a distributed database and we do expect that all parts will behave the same more or less the same so if for example you have a node with uh way higher latency than the other node you will see an indication for that and that’s usually means either you have a hardware problem like a faulty like a faulty disk or for some reason there’s another application that runs beside ScyllaDB and fight over the resources if you see um an imbalance on The Shard level that’s usually an indication for heart partition which means that you have a in on the duster is not the data is not distributed evenly and you have like one part of like one chart one part of the data that gets all the traffic and that’s one child is doing all the work and everything else is just doing nothing just resting it’s not very good and we have the advisor table that also it’s similar to the optimization section the cql and it would show you a known issue that we find and just let you know look we found this issue you should take care of that so I’m um

after all that um there is I am right now at the time there is one small thing that I do want to talk about is alerts

monitoring stock has the alert manager and as we say the alert manager gets the alerts from Prometheus and Loki and show them in grafana but the alert merger can also send your alerts to other system so instead of just going to um to the dashboard and check for the alert the alert can hunt you or search you wherever you are and personally I strongly suggest to add a mail group and not just use a mail in email and send those alerts and you you have a preset of alerts that the ScyllaDB comes out of the box it’s really simple to add your own alerts and send it to an email group this way it’s easy to for other people to decide if they want to join if someone is new to the companies just join you can see previous alerts that they got and it’s something that is off often forgotten but it’s very very useful so um I do suggest like a lot I know that many customers that did move to that uh really appreciate it and I’m suggested you too and by that let me thank you all for uh for being in my talk and these are some ways of reaching out and don’t forget the Forum it’s also very good place to get the information and thank you again [Applause]