If you're using MySQL, you can look at the general query log and the Slow Query Log to see what queries have room for further optimization. The slow query log is not turned on by default, but you can enable it with some command-line options when you start up MySQL. They'll be saved to the data directory by default, but you can also configure where they are saved. To do this, change a few settings:
SELECT * FROM global_variables WHERE VARIABLE_NAME LIKE '%slow%';
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0; /* Or whatever your threshold is */
SET GLOBAL slow_query_log_file = '/tmp/slow.log';
You can set a threshold on the slow query log, but if you want to see the timing for all queries, you can select the time threshold to zero. For an actual web application, you may have hundreds or even thousands of query patterns that your server uses. You'd have to dig through the logs to identify the queries executing the most or ones that take a long time. This process should reveal that some of the queries are running slowly.
If you are running your database on PlanetScale, you can use the Insights dashboard to gather this information instead of digging through the log files.
Head on over to the "queries" tab. Here, you can easily see which queries have run, how often, and which are poor performers. Now that you've identified some slow performers, how can you speed them up?
Returning the minimum viable dataset can boost the performance of your application. For example, if I want to show the top-10 ranked players on my game, make sure to LIMIT 10 rather than just returning the whole sorted set.
SELECT username FROM player ORDER BY rating DESC;
Versus:
SELECT username FROM player ORDER BY rating DESC LIMIT 10;
Creating relevant indexes on commonly filtered columns can also lead to huge gains. For example, say these queries are running slow:
EXPLAIN ANALYZE SELECT * FROM player WHERE username LIKE 'amaz%';
SELECT * FROM earned_achievement WHERE achieve_date > '2023-12-15 00:00:00';
After putting an index on these, you can get them to run much faster.
CREATE INDEX player_username_index ON player(username);
CREATE INDEX earned_achievement_index ON earned_achievement(achieve_date);
If you attempt those queries again, you should see much better performance.
Depending on your server's specs and MySQL settings, your db server instance may not optimally use available RAM. Several parameters can be adjusted to tune this. You can find a more detailed list in the MySQL docs, but for now, I will focus on one in particular: the Innodb buffer pool.
InnoDB is the default (and most common) storage engine powering many of the world's MySQL databases. MySQL maintains a buffer for InnoDB, which acts as a cache for information read from indexes and tables in your InnoDB-powered database. The default value for this in MySQL is only 128 megabytes. If you have yet to configure this, you could be missing out on some serious performance gains, especially if you use a server with a large amount of RAM.
MySQL recommends setting this to somewhere between MySQL's 50%-75% of the available RAM. If you have 16 gigabytes of RAM, consider values in the 8-12 gigabyte range. In order to configure this option, you can use the following MySQL commands:
USE performance_schema;
select * from global_variables WHERE VARIABLE_NAME LIKE '%innodb_buffer%'
SELECT @@innodb_buffer_pool_size/1024/1024/1024; /* Get the current buffer pool size in GB */
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024; /* Set the buffer pool size */
This can reduce the number of costly I/O operations on the server's hard drive and lead to faster queries as more data can be kept in and read from RAM directly.
After tuning your database and queries, you will see that your existing database can handle more significant capacity. This video does NOT represent a comprehensive list of optimizations and tuning that can be done at this stage, but it acts as a starting point and inspiration to getting performance honed in. However, it's essential to realize that in some cases, significant capacity gains can be had with some performance tuning.