Database Chunking - Performant Laravel

so the next layer of help of hermas win I want to show you is related to how we get data out of the database and specifically what to do when we have some rather large queries or a large amounts of data getting returned from some queries and in this case what I want to show you is how to use levels chunking future now I'll just show you where after that what we can do for this so I have a query we want to run I want to get some domain information out of my pageviews table so if we head on over here and we'll see the pageviews table we have a lot of pageviews it's all fake data the URI is the domain and created at dates and what customer and user ID that all relates to so we're gonna select domain from page views where domain ends and dot biz and we're created that it's greater than or equal to a date which is I believe is about 30 days from today's date and we run that and we get quite a few results about 102 thousand results in 16 milliseconds so it's pretty quick just running direct query from here let's see that in phpstorm I'm gonna use the app page view class and using page view we're going to select domain so I only want just the field domain I don't want everything from the table or say where domain is like that biz so and basically we have the wild card there and we're saying any domain that ends and that biz and where it's created at it's greater than or equal to the following day so I'm gonna do let's see new date/time and new date/time object I'm going to throw it at timestamp so we'll start that with the @ symbol and I'll do string two time negative 30 days and it'll just get that let's see uh so I'll do page views and I'm not gonna return the page views I don't want to show those all in the browser just going to get and do that query and have all those page views saved in this variable so let's go to the browser and we'll head to the slash play page and we'll see how much time and memory this takes now the other thing we're going to do is see some weird things here right so it takes 110 mega bytes that makes sense I think because it's a lot of data and 2.7 seconds because it's a slow query now I'm gonna show you something weird here and that is if I keep refreshing this the amount of memory usage goes way down and the number of seconds stays kind of pretty sistent but every time i refresh this the memory usage goes down and I think this is based off of the way debug bar actually grabs the amount of the memory or measures it it uses the C PHP function about getting peak memory usage and that doesn't a certain way where it might not for reporting this accurately every time or at least I'm not sure I believe it so one thing I add here is actually ax debug specific function because I have X debug enabled and it uses X debug peak memory usage and then I just divide it to get the amount of megabytes instead of just showing it as byte usage so we can refresh this again and we'll see that we're still gonna be at a high memory usage 106 megabytes and that's a lot per request even though it says 14 I don't think I really believe this we're getting the same amount of data out of that database every request so I think it's still a pretty high amount if you know better definitely let me know but in either case let's see what we can do about this so we are getting those I think I said what 120,000 requests or so or pageviews what I'm gonna do is add the chunk function at the end of this and what chunk is gonna let us do is divide this query up so we know I'm gonna get like 120,000 out I want to get 10,000 page views at a time what this is gonna do is make separate queries so it only grabs 10,000 at a time let's say I have 120,000 and I divide that by 10,000 that's gonna make 12 queries overall so we'll see what it actually does but it should be something about that and then we can add a closure to handle the chunked pageviews so I'll do just the variable called pageviews in here and what its gonna do is pass in the 10,000 page views into this function through this closure and then I can just do something with pageviews so I can do something on that 10,000 page views and maybe aggregate the results with a temporary function or something like that either way this lets me chunk them so I'll use less memory overall even if I do it at the expense of more database queries I have to do this a lot for reporting I typically do it in a queue because the curry queries are pretty slow something where it can periodically turn some memories and save that result somewhere this isn't necessarily something you want to do if the query takes a long time on every page request for your users but you know your use cases or all be different so I'm just gonna hit refresh here and just let this go it's gonna take a little bit of time because it does like I said more queries in fact we can go here and see run this it was 102 so that's gonna be a hundred and two 228 divided by 10,000 requests for queries so 11 queries alright and it's done and the court 11 query is exactly like we said the total memory usage here look only 22 megabytes it's no longer in the hundred megabytes range so while it took 10 seconds it's a two and a half seconds the trade-off was the memory usage reduction was a lot now you have to decide when you have large queries like this or queries that result and a lot of rows back how to handle them right but we can see how chunking works gets offset 0 get 10,000 and then it just keeps offsetting until it finishes and there's of course other optimizations you can make in the database level which will actually get into in the series but for now just know that chunking is a great way to do large queries or handle larger amounts of data coming from your database using a lot less memory so you won't choke your server up and I think that is a good trade-off for time especially because you have options or hopefully will have options like being able to push a large query off into a queue and then just save the results somewhere and show those results to like an admin user or the report section or actual user of your application if you can make it in such a way where the user expects that query to take some time and the results to come back after a little of time like using a loading screen or saying will email you when these results are done or something like that there's a lot of options for you but this is a great way to reduce the memory usage so if you have a lot of these queries happening at the same time you're not overloading your server you're kind of spreading out that memory usage over more time which servers are more able to handle that kind of lets you have more going on in that server more requests happening part time which are quite Stu more users using that application on less servers but of course like I mentioned the trade-off there is more time for the less memory usage last thing I'll do is just got the docs for that so I'll see you level com you can go to chunking check out the chunking section this is on query builder so I actually did it off of a page view object here as a model but you can do it off of a regular query as well so just the table users in order by etc etc so use a chunk message you get to feed it into a closure is very useful for rating artisan commands that process dozens of Records it's basically acknowledging that trade off it takes more time but you use less memory and therefore you might not necessarily have to have a larger server or more servers to handle churning through queries like this

Channel: devbits

Views: 2,694

Keywords: performance, php, aws, laravel, server, digitalocean

Id: xGBlyHf_z_4

Length: 7min 5sec (425 seconds)

Published: Tue Mar 27 2018