Insert 1M Rows to MySQL Laravel DB: 6 Ways (Benchmarks)

Hello guys, have you ever tried to seed 1 million rows into for example users table or different table like posts? I have tried it and in this video I will show you different ways how to see the data with eloquent without eloquent into MySQL also will compare against posgrql SQLite also locally on my MacBook Pro versus cheapest server. So a lot of benchmarks and in this video we will show the tables for now blurred out but I will show the data how I seated users and post tables and why they are very different numbers. Also we will talk about how to minimize the RAM consumption the memory with buffered and streaming approaches. Then we will compare MySQL versus SQLite. Then we will add posgrql as two columns to the same table. And finally, I will show how different are the benchmark numbers locally on my MacBook Pro versus Forge Digital Ocean server for $6 per month. And here on YouTube, you will see shortened version of this video. So everything around MySQL and all the approaches to MySQL but comparison against SQLite, posgrql and Laravel forge remote server will be in the premium version of this video for Laravel Daily members. So this is kind of a reminder that Laravel Dailycom is not just for longer courses. I also do premium videos which are longer and deeper into the subject. The link to that will be in the description below. But in this video, let's talk about MySQL. First, how I measured and how I seated the data. This is artisan command seed benchmark with a lot of parameters. I worked with cloud code to generate that command, but it totally wasn't one shot. It was a lot of hit and miss and retries. And for premium members of Laravel Daily, I will put that on GitHub and we'll put the link below this video. But basically what you need to understand is this function. I was executing that cedar multiple times and each time the strategy was different. So there are five strategies how to seed million records. First is eloquent. So just user create or post create. Second is with factory like this. Third is query builder with chunking and with database transaction. Then the fourth approach is insert. It's called insert extended. It's just insert into user with DB insert. Almost the same as DB query. But was it different? I will show in a minute. And final approach was bulk native which is implemented differently in MySQL and posgrql. And in SQLite it doesn't even work from what I know. And the approach was to generate the file and then load that file like this. And by now I think you could guess which approach is the fastest. But what are the drawbacks and by how much it is fastest? Let's take a look at the numbers. So this is the first table. MySQL with users and posts with those five approaches. And yes, those of you who guessed that bulk native is the fastest, of course you are right. So it took 5 and a half seconds to generate that file SQL and load it in line for users table which is a bit bigger than post table and post table was even faster but there are a lot of nuances in this table. So let me comment one by one. So this number is pretty huge and that extrapolation means that I didn't actually wait for 53 hours. I tried with 1,000 rows and then did the math how it would be with 1 million records. So this looks kind of insane. Five rows per second for eloquent. Is it that slow? And this is where you need to understand the difference for users table and post table. So this is the eloquent operation for user table and the field password. If we take a look at the user model is hashed which means for every user it's encrypting the password and this is how long it takes. five rows per seconds. It's not eloquent. It's not database. It's 200 milliseconds roughly. To hash one password for one user. So if you are doing such bulk insert specifically for users, you need to probably use the same password, hash it up front and then it will be much faster, which is exactly what is done if you use factories. So this is also interesting detail you may not know. So user factory count create. How does it actually work? User factory definition is like this. You have password as a static password which is a property in the factory which is empty by default. But this operator is assignment operator. So the first time it runs it hashes the password which is by default password and then it saves the data into that property. So the next time for the next user it's taken from the property and it is not hashed. So that's why for factory we can achieve 4,000 rows per second instead of five. And this is by the way the default behavior of user factory. I didn't change anything myself. This is how it looks in the official Laravel Laravel repository with the same operator. And also as you can see eloquent approach in post is much quicker because it doesn't have any hashes on any columns. And that's why interestingly enough for post eloquent approach was even faster than factory but not by much. I would count them as equal. Now let's discuss the huge difference between 4,000 rows per second and 100,000 rows per second for query builder or insert approaches. And this is the question of eloquent versus query builder with insert statements. In this case you can see the similar difference in the users table or in the posts table. So if you do user create with eloquent model it does a lot of things under the hood. So generating timestamps like created at updated at or others if you have some observers or boot method in the model itself to automatically set some values or events and listeners. So all of that is happening for each eloquent model individually. So that's why again we have for each loop of million records and in this case I don't have any observers or anything but just the timestamps themselves. As you can see it makes a huge difference. Of course the other way around if you use query builder or db insert then you lose all the benefits of eloquent. So you need to build times time CR yourself manually automatically generate all the other values take care of adding all the parameters securely with question marks and everything if you're using raw query with insert so yeah as usual tradeoffs everywhere and the difference between query builder and plain DB insert it's not a lot but also it's not zero so you may care about those seconds or milliseconds if you do insert with data versus you do the building of the query manually in PHP into a string. But this may be insecure. As I said, you need to take care of bindings and parameters. I mean, it's just more risky, but maybe faster. And of course, as I mentioned in the very beginning, just loading the data from SQL from a file into database bypassing all eloquent and query builders. In this case, we have just DBX. we execute the statement and that statement happens to be load file. So yeah, this is the first table with most kind of straightforward approaches. But there's one more approach which is streaming the data and this is not about the time spent for million records but this is about memory usage. So the problem is if you work with million records or more those million records are in the memory and at some point your server may just physically run out of memory resources and just stop in the middle. So for example for my case as you see 560 megs or 500 something megs is a typical number for MySQL in this case for the examples you saw above. So what is that stream thing and why it is so magical? It is a bit slower as you can see but doesn't allow memory to grow higher. So one of the parameters to my benchmark command was stream and in this case the function run streaming this is how it works. We generate the rows and then we are flushing them from memory in a for each loop. So we don't actually work with arrays or collections of 1 million records. we work with chunks, then we do DB table and then we empty the buffer. So that specific line of code doesn't allow memory to bump up. So if you care about RAM specifically, then you may want to use streaming approach, which is again more seconds to run a bit slower, but it will be more reliable probably in most cases. Again, as with everything in this video, you need to test it on your use case because maybe there are some exceptions with some settings of your database or structure of your database table and stuff like that. So yeah, that's it for free version of this video here on YouTube. If you want to get deeper into comparison against posgrql and SQLite and also Laravel Forge, I specifically set up a testing server for that. The premium version of this video, the link will be in the description below. By purchasing Laravel daily membership, you also support me to do more free videos here on YouTube. And I really appreciate that because I want to do this thing for as long as possible to review things for you and share my findings. That's it for this time and see you guys in other videos.