Dealing with a Monster Ecto Query: Mackenzie Morgan, Axios // Elixir Wizards Conference 2021

welcome to mckenzie's wonderful talk on dealing with a monster ecto query um mckenzie i'm gonna let you take it away from here hi um yes so dealing with monster ectoquery um so i've only been doing elixir for a year um so i learned elixir a year ago because i work for axios which is a news company and we launched our mobile app last spring and uh the back end we wrote with apollo you know javascript and it immediately crashed apollo um every morning at 6 a.m and i'm not a morning person um there it was you know our push notification would go out for the day's news and that that wasn't good um so there was a hasty rewrite into elixir uh to be clear i am not the one who did the initial report into elixir there were contractors involved um but i picked it up and started extending it along with my team and it generally worked great except for this one query um there was a single complex query that had a ton of ores um that was responsible for the majority of database load and you know like often you get the advice to move as much computation as possible into the database and out of the code um for for optimization right um you know do your sorts and your limits on the database so the database has less stuff to return to start with well this is the story of when and how to refactor the opposite direction um see the thing was we had the biggest day in u.s political news coming up you know the presidential election where everybody is refreshing constantly um and so everyone's on call that night the users are hammering the servers trying to find out who won and it's just generally not a good time um to be on to be on tech at a newspaper so for context for the app we have these channels in there um which i have martiers aggregator and content can make it into the channels um through various taxonomies and you know it's pretty standard for cms to support multiple taxonomies um if you've used wordpress you've got your categories and your tags but you've also got like subcategories and you know again in wordpress you've got the primary category which is secondary we're not using wordpress but it's a handy example as we're saying okay well we want anything that's tagged this way or in this category to end up in this spot which doing a query for that ends up looking like this which is a lot that's that's a long query and to be clear i have simplified this query um i took out the the date ranges i took out the sorting i took out the only show things that have a published status um yeah there are a ton of joins there's all these ores and what's going on there really is that we're trying to traverse across each of these sets of many to many's in order to find them all that took a long time to run for the full version that includes the dates and the sorting and everything um i ran postgres analyze on it and it told me the cost was 3600 and that it would have an 8 millisecond execution time so we're going to walk through how i refactor this to be super fast first off um breaking it down there was a lot of parallel stuff going on there so what if we did four smaller queries one for each of those associations okay so that looks like this this is a lot simpler right um and postgres said that the cost on that was 16 not 3 600. that's a lot better uh it also said the execution time it also said the execution time would be 0.125 milliseconds so we're on the right track there but i got to write that out four times once for each taxonomy that's that's not very pretty um you know it's just it's not dry enough um but now here's where elixir language features come in thanks to the ability to pass around atoms and dereference them inside ecto queries we can take advantage of the atoms and the pin operator and get ourselves a function where you can pass in which taxonomy you want to use but and so like and so you see here the um the taxonomy is coming in and that will contain an atom and because we're pinning it we can put in whatever atom we need there um ins by calling the the function with an atom in the second argument but we're still gonna have to call it four times once for each taxonomy so it's still not very dry let's see if we can go a little further what if we used elixirs concurrency functions um elixir is known for being able to do all this asynchronous stuff do a bunch of things at once like that's why people use elixir in our lane right okay let's try that out so what what i ended up doing was using an async stream to go through all the taxonomies which like i said at the time i had four actually up to five now um which you know a nice thing here you ever need to add more add them to the list and it'll uh it'll just go so it goes through each of them and it calls that previous function on it based on what's in your list and at the end of it assembles it all into um into a single list like the original query would have done so the first result we saw was that we went from 50 to 40 percent database utilization um or cpu utilization on the database server just as our like standard running level um in between push notifications um that is a 20 reduction and i know you're going 50 minus 40 is 10 macko how are you getting 20 it 40 is 80 of 50 therefore it's a 20 reduction math is hard um as i said earlier the uh postgres [Laughter] someone in the comments just said andrew in the comments said it was my understanding there'd be no math sorry there was math uh but i explained the math um the as i said the analyze cost went from 3600 down to a nice 16. and the execution time went from eight milliseconds to we said it was 0.125 milliseconds per query if they all ran in serial that would be a half a millisecond which hey 16 times faster great um in practice got multi-core machines using the um the async stream so that we're sending multiple at a time and it can be responding to you know can be calculating multiple of them at a time it it's probably a bit faster than that i also did some load tests on this i ran a load test before doing this code change to see how much how many how many requests of you know typical uh traffic ratios can we send at the server before we start crashing basically right before we start sending out uh service unavailables and then i ran it again after deploying this 700 we got we got seven times as many uh queries through or yeah as many graphql calls through as we had before awesome so that meant we were ready for election night we had this nice stress-free election night and i don't know if you've worked for a newspaper but this is not the first newspaper i've worked for i remember the stress of midterm elections so having smooth sailing for a presidential election was magical um that was great i i spent the night watching the returns and um i think i was playing with personal code because i was on call but there wasn't actually anything to worry about so that is the end of my my refactoring story you can find me on the internet and uh if you have any questions put them in the little box good job mckenzie um we actually have one question in the q a so far alex how did you get into 16th century fashion and are your looms operational uh i'm part of the society for creative anachronism which is a group that does um that researches history and how people lived prior to the 17th century um i am apprenticed to a weaver and yes i have two operational floor looms um a couple of tablet weaving looms and um i think one working table loom uh also one table and nuts and pieces that is amazing um we have more questions uh one from andrew do you run into any issues with data consistency by running separate operations and is there any need to single transaction uh no we're not running into any problems with them with with consistency as far as i'm aware um because things that generally things that would come through on one of the ore paths doesn't come through on any of the others and um you know new articles aren't published every uh you know 15 milliseconds or something like that they're they're they're published more on the order of 15 to 30 minutes apart awesome we have more um this one's from alex uh is your app serving graphql over rest or over websockets well ws i believe it's websockets is that correct alex actually he can't say anything i think it's websockets uh like rest we're doing we're doing get requests uh we have another one from garrett um do you implementing layer to remove additional load off the database yes we already had caching going um with um caches i mean they're very they're short-lived caches because you want to have breaking news come through um you don't want people waiting five yeah it would be terrible to send out a push notification saying breaking news this thing happened and the person tries to look at it and we're like hang on gotta wait for the five minute cash to expire so they're very short caches but yes there are there are uh some shortcuts that there are short caches in there um holding on to um what's in each channel and we uh do it on a per channel or as i had in the code aggregator basis i love it and i have a follow-up question um you might remember that possibly um are there any times where we should just throw dry out the window and just say we're just gonna not dry anything yes they're they're they're well okay not try anything or not do anything yet because um you know there's that there's that whole rule against premature optimization right um so don't worry about it when you're first writing but if if it starts getting to the point where you are having difficulty having to update things in multiple places like you have the same logic happening in multiple places then then that's a good time to go back and refactor and and dry it up premature optimization i'm going to turn that into a shirt or something that is amazing i love it all the questions unless anyone has any more and for those who don't know you could have a q a pot or tab and looks like we have some folks in the chat we have rose uh who wants to talk with you about looms um she also leaves and what else do we have i think that's unless anyone else has any other ones yeah i think that's it thank you thanks for having me that was wonderful and i am also very interested in seeing some of these looms so if you have any pictures that you could send to be in rose that'd be great because i'm very curious what one of those looks like okay my craft room's a bit of a mess but for sure alright folks thank y'all

Channel: SmartLogicTV

Views: 300

Keywords:

Id: -82nj9rWSwc

Length: 14min 56sec (896 seconds)

Published: Fri Jul 16 2021