Have you ever been wondering why your application slow even tough you already fix your application code?
Performance of the application is not depend on application itself. Application and database need to be tune together. For example, imagine 100 customers enter very big fancy restaurant (application) and there is only 2 waiter available (database). How can 2 waiter serve all the customer without keeping the customer in queue for long period? End up, the customer left the restaurant and never visit again.
Here is my 2 cents of tips to speed up database query based on my experience.
Indexes is the most important part in Database. Indexing allow data retrieval efficiently faster. Indexes are used to quickly retrieve data without having to search every row in a table every time a database table is accessed. Small database wont be noticed but once it grows, the query getting time consuming.
How to use? All you need is to identify which column you want to take advantage from. For example, you have 1.3 million rows of users and you want to grab an email.
Without index, the process will be from top to bottom of the user data until it the
Without index — 750ms
With index — 2ms (400x faster)
In Laravel, the easy way is create a migration file to alter your desire table
Or if you currently on fresh project just add index to column you want to
- Index is not only for single column. (Refer snippet above)
- Speed of the query is depends on your Database CPU/Memory. If you have potato database server, don’t expect too much
Eloquent ORM Eager Loading
Eloquent Object Relation Mapper (ORM) makes managing and working with these relationships so easy rather than manually join each table. In order to use Eloquent, there is some you need to consider to use it carefully which is Eager loading. Wrong implementation cause N+1 query. Here is where common developer do which cause N+1.
Lets fetch users about 100 rows from the table and see what happen here,
Mistake 1: Common mistake used by Laravel developer
Why? Because, the query actually runs once to get all the users BUT for each loop, its query another query for devices for 100 times. So, here is 100 + 1 = N+1 problem. Imagine if you want to fetch thousand of data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.
Mistake 2: Wrong Manipulation relation table using append attribute.
Why? Because, the query actually runs once to get all the users BUT the moment Laravel want to transform it into collection mapping from appends attribute, the collection queried another 100 query. Its the same as Mistake 1 because it produce N+1 problem.
Simple mistake can cause a lot of trouble. It can be solve be using
with method in Eager loading in one go.
With this, instead of hitting database N+1,
with method will single query joining those table and transform it into collection. So you can freely access the collection object without hitting the database. For more info about eager loading you can refer Laravel docs
Using eloquent is great because its have many cool features but when it come to the speed, it slightly slower than query builder because of ORM.
Here is the example comparison between eloquent and query builder against 1.3 million rows
Based on the above comparison, Query builder are most likely 2–3 times faster than Eloquent. It doesn’t mean you can’t use Eloquent. I recommended to Eloquent but in certain cases, for simple approach like data retrieval without any complexity, you might need to use Query builder.
Paginate as many as you can for data listing (limit result per query) because its faster rather than retrieve all data at once. If you have 100 rows, it would be fine. But what if you what millions for rows. The best practice for data listing is to use pagination for best UI/UX.
In Laravel, there is 2 pagination —
paginate. Let’s test it against 1.3 million rows
Why paginate much slower like 70% slower?
Let’s drill down code snippet from
As you can see above snippet, at the line 21, the method
paginate have extra counter to count all the rows in the tables while the
simplePaginate not doing that. Like I said, small data won’t be effect. If you have large dataset, you might consider simplePaginate.
But, most cases, you actually need to use paginate to create table pagination with numbers right? Then, i would suggest you create a Macro method against
Builder class which fix the count method.
Based on experience, most likely if you have a lot of data like 10 million+, it shouldn’t go more than 10 seconds. If YES, then you should check your database infrastructure OR most likely your code
Always query what you really need
Here is also important to query only what you need. For example, let’s test on select query
Once again, comparison between Eloquent and Query Builder. Clearly Query builder much faster but the point is where to select what column you actually need is much-much faster. There is several reasons why. You can refer here for more info.
Same goes to the eager loading, don’t join table if you don’t need it. Some of developers most likely use
$with variable in Model. My advise, just don’t. You need to manually choose which table you want to eager load instead of automatically bind it.
If you have a logic condition to check data existence in other table, don’t join it, use
Caching results of the queries which frequently accessed would increase the performance of an application. Cache basically store queries made for the first time and use it for the next request for some period of time or forever stored.
Let test with this simple query
Without cache: 985ms (with index 17ms)
With cache: 0.4ms
Cache is faster than anything. Please avoid to use file or database as cache. File will produce high I/O and database will cause high connection — slow if high traffic. Recommended to use Redis or Memcached because it store in memory.
Here is some package you might to use to ease your implementation
Laravel Eloquent Query Cache brings back the remember() functionality that has been removed from Laravel a long time…
This Laravel package can cache an entire response. By default it will cache all successful get-requests that return…
The purpose of tuning is where we need to find the best resources for the database. Best database need to be tune. It always not come by default. You need to check this 4 resources— CPU, RAM, Storage and Network. Lack of this will result poor performance on your application. It doesn’t mean if you have high spec of CPU and RAM, you can support high traffic with slow network.
My advise is always try and error. Do the load testing on your database so that you will know the capabilities of your database. Fine tune every possible configuration can be configure and see what happen.
Always use latest version
It’s important to always use latest version of framework or database. Most update will give you performance upgrade also.
- Give you security updates
- Keep your application running smoothly
- Legacy system sometime give you problem in the future
- Increase performance
For example in performance,
The benchmark between MySQL 5 and 8 which MySQL 8 give huge difference in the time it takes to process transactions and improvement in high workload.
For Laravel, there is also different in performance between L7 and L8 which based on my test is 2x faster than L7 and 4x faster than L6.
So, database and programming framework need to upgrade along (unless you are not using framework). Do benchmarking on multiple version and see how its going.
Separate Database from Application Server
Traditionally and most commonly found, entire environment puts on a single machine — where application, web server, database, and etc.. all together using same resources. Commonly used stack is LEMP (Linux, Nginx, MySQL, PHP).
- Quick and easy for simple setup in little scalability.
- Shared resources — lead to poor performance
- Difficulties to determine the source of poor performance
- If the machine shut down, everything gone
What related to this topic? Shared resource cause database unable to allocate process properly. If the application use 70% of CPU, database only have 30% or less just to process queries which lead to long query problem and timeout.
So, separating the database from the application server would be better. Why? By splitting database from application server, we can properly allocate service only for database and also increase the security by put it in private network.
- Fully optimized number of connection without been overloaded by other processes.
- Security purpose by isolated it from public network.
- Problem diagnosis and performance monitoring is easier.
- Maintenance and upgrade much easier when only focus on database without worrying other component
I would recommended to use cloud provider (AWS, Alibaba, DigitalOcean) when it comes to splitting environment because cloud provider have shared responsibility where you only need to care about your application and data. For maintaining the hardware and software is cloud provider responsibility.
Use Read-only database to retrieve data
If you have enough cost, splitting the database into read-only database would be good. Its provide flexibility, error tolerance, speed, and no locking occurred. Furthermore, read-only provides enhanced security for databases by protecting them from accidental or malicious updates.
So you will have 2 connection string. 1 — Read-Write database and 2 — Read-Only database. Frequent access data retrieve you might need to use readonly database. For less data retrieval and frequent update use the read-write database.
This is just several tips what i can help developers out there. Thanks for reading and don’t forget to share with you friends.
That’s all. Thanks for your time ~~
Laravel Documentation — https://laravel.com/docs/8.x/eloquent-relationships
Query Performance — https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/
Server architecture separation — https://www.digitalocean.com/community/tutorials/5-common-server-setups-for-your-web-application