About the talk
SQL Query on a database table running slow? Let's create an index on the table. The query is still running slow although the query planner says the index is being utilized. What do we do next?
This talk is about a time when we were building personalized recommendations for our customers and were faced with high latencies in our systems. We pre computed relevant item recommendations for each user and dumped the results in a table of our OLTP PostgreSQL database to enable us to serve these recommendations through our APIs. The underlying cause of high latencies was a slow running query on a postgres table which was using the index. We will explore what does having an index really means for the database, how those indexes work to make our queries faster and times when they won't. In this particular case for us, it boiled down to how the data was distributed on the database disk which in turn forced us to look into how data was being written to the disk. This talk takes you through our Apache Spark based recommendation system to understand how Spark writes to the database and the changes we made to our Spark jobs to suit the distribution on the disk according to our query patterns
Hello. And thank you for coming to the store. I'm going to be talking about like why physical storage of a database tables might mattress. Some of you might know it's an online grocery company deal in providing affordable groceries to the people at the best price and best quality is also into wedding, great customer experience and it isn't causing a supply chain so that we can deliver. Value to our customers and prices. This truck is about a time when we were, we were building a recommendation systems and we realize that she is running very slow in a system.
Does the entire API was timing out? And developers are usually, like this morning TV engines, we are usually dealing with the secret, but there are some times where you at, what's up deal with internals Starting with the talk to like all good stories. This also this dark and stormy night. So, the new shiny that combination system that we had built. It was constantly timing out. It was constantly timing out and the data Engineers. They're trying to figure out at what is actually wrong with the system. So, we had a
flat space application, which was talking to my database, which was supposed to read through a sequel Alchemy based and related to Burt Lake Varsity, SQL query, which was running on the little bit so next week. Like, what was, what was the cause behind that could be running. So slow and deliberate, and you have to be with us, with the new best friend is explain online. It's been a life gives you that flexibility of looking at what the DB Engineers actually doing behind the scenes. So
we had this table call personal recommendations and the credit is that the applicant air condition system was running on the database Ware lights, like stars from the stable where customer IDs too. And so, I know you can look at the structure for the table. We already had an index on customer ID on this table so all that is needed for the Square Reader. Perform better is already there. So what is it that? It's not really making the 21st. So, I didn't like your best friend is a finalized, a video
on this particular pretty. And you can see that. Then that's kind of the only taking Luca into account index. Can and still, the visitation time is of the order. Given the fact that we mourn. Most often appear to be under hundred ms210 for simple database. Look up is huge. So that we can do to reduce this particular expensive query. The relays that in spite of the index can happen at this point. We need to understand what the next and is really doing it right as application
developers, you create an index, and the Beast takes care of the rest of the things. We don't really go into details of water in Disguise doing. But the situation, let us to figuring out, okay, let me figure out what the So impressed with your default. In Nick's is a between a b tree or the balance. Three of your default index entry, the customer ID, and underneath notes, you have those in the sentence itself. Like for example, you have the guards in which the physical location or
the, the location for that particular record on the table. So example, if you have to look at 5757, the drivers lined up at 57, will look at different interest and these two different injuries at your two different record for the tables that match and B-17, 50 and 29 diesel locations on the desk. Interesting in rosetree steps. First, if you are retired person to get the B-17 and 5029, total is actually catching the table data to the database. Also has to go to the location where b-17s and fetch the record from that position. So these are the three steps in the world. In the first
step, you can say goodbye or a flock of hens in, is it in your table. So, it's a huge entire index card of the entire tables and has only gone for that particular team. It is actually passing, the table is very random, access memory card when we had to unlace, okay. Where can I take off? Latency could beat the third is where you focus the most unreliable, okay? Since my first two are still at the matter. What can usually, what can actually be the option of the
data on the disk, which one is actually doing so. The post office are exposed to something known as ttid City. ID is a couple identify each element is actually the block number at which this particular corner store, and the second element is the index is the next Sunday block on this job. For like the first recorded ucd780, when is the block numbers on 28, is the position within that block feathered particular before. You can see that all the records are spread across different
blocks and blocks 2873 This means that if the database was performing the third step of actually getting onto the desk. I was seeing all of these different blocks and this can lead to a huge event and that can increase militancy. Soviet, are they located 4510 cluster, all of my data onto one block? Can that improve militancy? So, specify something special, and which actually physically rearranges all the rules on your desk on to a given. Call you say, if you say that I want to physically, look at all the rules for the Scholastic, a man can give you that flexibility, but there are some cons to
using it. For example, it required that I acquired it right on the table, which was not possible, since my application, and it was required me having a 2.5 XT table size for its operations. But like, I have two point five extra tables. I spent most of the time there's going to be able to deal with some of the things that we talked about that were the cons of against using the restroom and also, The next steps I took to fix. It was, can I write my data in a particular format? Example, the
application which is actually writing the house that application to aligned Medical in the way I want. Can I tell if he? Okay, so we went and we had a spark application which connector to write the deduction into posters. Okay, so Tupac uses the concept of partitions on a diseased bodies. There is Linda's birthday to the most beautiful lace front. How it partitions the data into your body? Take an example, where you have to calculate the number of words, in a text file,
on a person that you can specify the partition. He also or it doesn't randomly and it will have three workers on each of the other note, I need to book an apple have. So, you can see that eats partition on the auditing is assigned to each of the workers. In this case to four and five, other than one node. Similarly, one and three are in second note, and 3 and 6. I don't know if I like looking at April 1st and say, okay, okay. What is the count of tiger in each other? And at the very end basically
asked, okay. Addison has to count of tiger, 35 count. So you're late, is that okay? What if it is also writing that it's my entire production, according to various teeth? Is it doing? Is it writing that it apart is sunrise? In this case, we realize that for them for like speeding up a particular operation, we had petitioned commanded that I'm recommending to my customers so far, so this means that if I had to confirm I have with this is okay. If spark is when all the operations addition rice in my pulse rate has to be perverted
or Unprotected sex, like all they do is to lead us again. We went to City ID and say okay if my birthday is this month, now tell me what product ID are in one block itself. Almost continuously in the particular block, which means that it should be doing an in-depth scan. But the greatest Soviet MP3 reminder. 8:30. Okay. So we went back to spark and said, okay, Part D partition on customer ID to start exposing a p. I d partition and realign the data on. Again, check for distribution for somebody to first to 8460, but no, the the the data is still not aligned, right?
You can still see that for 28460. All of my Bros are a different block still. So, what is it that you did wrong? Because we took a step back, we kind of talked about it. We went deep in thought and songs without understanding the entire customer identities happening. I'm not. Okay. So I realize it in that part of the reason for a particular spot is 250, the weather, for all 10,000. Customers is being written in one position and when you ride the bus with all
those aren't intelligent enough to understand that as well as a customer ID information in it. I've been getting into it, did not really reflect. So how do you really drink all the rules for a particular customer ID together. Any guesses that you would like to make at this point? Okay. So how about we sort them? How about I stay here? Okay, already started on customer ID. So that should ensure that all the customer ID rules are at one place in a particular partition
to sort within the partitions. Are, you can see that you are now looks like this in this particular against it. So now it does it, rain in Bonita, So that is what the final test is for us. We wrote the city back to my database table. And so I looked at what is going to be my distribution. So yes, for short legs are now returning in one block as like, we wanted it to make my credit faster than they are in different places in the block. So this is really just like, okay, now everything is really looking good for us, but
this is one for my credits to be faster. Do the final test today, we went back to a best friend explained on my list and I said, okay, hey, I forgot my ID. 24001. Tell me what is a bit. My peeps can ask against a simple index and it was announced that he would jump in there. Didn't see that is a really good mood, but if it isn't hard, maybe it wasn't a simple index. Can it still be easy or maybe a bit not have come to see us at as what is doing right now, so they quite a bit Maps.
If you have something that you want to come, 95213, 95 us-12 first. Then all of them back to my application, the books. That is that like for example, if I had to run to 1215, it is arranging to send ships Cannon. This man is only doing two passes on the desk. If he still has to visit all the different dogs and still has to bring all of them into memory. So it will not save on Durand. Is Benedict means in relation to rate. It's been understand your decision is now and not spread across various box so that I can meet is intelligent enough
to understand that Thanks Gibson died and came down from hundred to 3 Ms for us. And, you know, the team is really happy because they bring us to Services cases. For example, if I wanted to send notifications of recommendations to my users recommendation, This House burn down on my knees have become really well, they are under So that's all. That was all the talk was about superheroes hiding. Like if you're interested in solving similar problems and you would like to
do nothing about it, you can always meet me at this particular email address and we can definitely take that power from their that's all You abusing the value that? Are you wearing? This is hurting. You think you can sleep in a bit during the day. This is a batch based on which ones once a day and once a day, it just fetches the recommendations and dates happening on this particular table. You'll be close to maintain the snow, roll back, and then you need to stop at all these things.
So have you tried clustering, your table clock, on the table. Whatever you can do it with PG repack? Because and then the remaining online, and then again, when it gets back before, which is a pointer at that, time to take Alan for a minute or two do CPAs, cause will start feeling at that time. That's my cousin. Yeah, we can do that. Like, I can probably create a temporary table, flustered, and then lighted, Back to the Future. How do you spell Tom Curry? Start running based on customer ID,
if this were any other not work in that situation, like we would be going for maybe a solution, which publication has the results. And then, Anything else you want to ask questions? Passionate. I'm sorry. I didn't get a person face-to-face. Oh, no. No, we we didn't go for Hudson, Texas. We were so, I'm not sure what the problem was at this point. That you actually went to fetch. The data is where the problem. Even if I'm asleep, I made the first two steps in the index. Retriever faster that starts at
the bottom for me. Beach Boys. Best version. And about the Katie Patterson is there. So maybe you can create different partitions, bring if this proposal not work. So I know if my table size is increasing, for example, if it went beyond the date, on customer ID, Do we have any more questions? Thank you without support, thank you so much.
Buy this talk
Buy this video
With ConferenceCast.tv, you get access to our library of the world's best conference talks.