Database performance is a crucial factor that dictates the overall performance of your application. This article will look into ways that can be useful in identifying some database performance issues, and testing database performance.
Run Explain on ActiveRecord Queries
Explain statements display the execution plan of an SQL query including the number of rows that will be scanned, the index that will be used, and how different tables are joined to each other.
The execution plan (how a query is to be executed) helps in figuring out what slows down the query execution by looking at the following:
- The index to be included to improve the performance of the query we are running
- If tables have been joined in an optimal order
1
2
3
4
5
6
7
8
9
10
2.4.2 :004 > User.where(id: 1).explain
=> EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.14..8.16 rows=1 width=792)
Index Cond: (id = 1)
(2 rows)
2.4.2 :005 >
The .explain gives us the query plan for the ActiveRecord commands. From the above example, we have used the id of the record (primary key) to fetch it. From the output we can see that we are using the pkey which tells us that the statement would be optimal and fast.
From the example below we can see a join query in action.
1
2
3
4
5
6
7
8
9
10
11
12
2.4.2 :062 > User.where(id: 1).joins(:collaborations).explain
=> EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "collaborations" ON "collaborations"."user_id" = "users"."uid" WHERE "users"."id" = $1 [["id", 1]]
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=8.17..27.31 rows=4 width=792)
Hash Cond: ((collaborations.user_id)::text = (users.uid)::text)
-> Seq Scan on collaborations (cost=0.00..17.20 rows=720 width=32)
-> Hash (cost=8.16..8.16 rows=1 width=792)
-> Index Scan using users_pkey on users (cost=0.14..8.16 rows=1 width=792)
Index Cond: (id = 1)
(6 rows)
Looking into the query plan of the example above the collaboration table is executing first while using sec scan. The user table on the other hand executes later and uses pkey index. Adding an index on the user_id column in the collaborations table will optimize the query.
Measure Key Database Metrics
Different database metrics give insight on whether queries are being performant. These metrics are as highlighted below:
- CPU usage
- Memory usage
- Disk queue for waiting IO
- Network bandwidth for inbound and outbound traffic
- Available disk space
- Throughput
Queries to the database may be affected by either one of the metrics mentioned above when they go above a certain threshold. The best way to understand performance issues is to look at a specific data point over a period of time.
The data points that need to be measured depend on a lot of factors such as the ones highlighted below:
- Type of database: Relational, In-memory, No-SQL, Data-Warehouse
- How the server is hosted: On-premises, On the cloud
There’s no single way to monitor database metrics, however, it’s important to be able to handle a specif way that serves the purpose you may have.
Using AppSignal
Visibility over all the queries being performed on your database is very important and comes in handy when looking for ways that you can optimize your database performance. Additionally, trying to manage all performance metrics, as well as adding performance code to every code block can become unmanageable and cumbersome very fast.
Luckily, tools such as AppSignal make it easy to integrate performance measurement into your application. Also, the tool supports Rails out of the box and has a simple installation process. Learn more on how to install it here.
Some of the metrics that can be optimized are:
- Queries that are slow
- Database performance based on throughput
- N+1 queries
- Database latency
- Number of active connections