Speed up your MySQL database with the buffer pool

MySQL has TONS of options you can configure to adjust behavior and performance of your DB. However, there are a few that stand out as being absolutely critical for database performance. One of these is innodb_buffer_pool_size. Tuning the size of the innodb buffer pool can unlock much better performance for your database, especially if you have a large data set and a lot of RAM to put that data in!

In this video, watch Ben as he given an overview of the buffer pool, and shows a demonstration of how adjusting this value can significantly improve your database's ability to handle the workload you want to throw at it.

💬 Follow PlanetScale on social media

[aFfoochen]

Thank you so much for this video. Really high quliaty and direct to the point. Learned a lot!

[Ostap]

Hope there will a followup video addressing other innodb_x config options. Preferably starting with brothers and sisters of the buffer_pool_size. There are some rules of thumb, but it is not overly trivial to get them right.

[vighnesh._]

Super helpful, was trying to get my head around what buffer pool is and how i could optimise the performance. Exactly what i needed to proceed. Thanks a ton!!!

[NedumEze]

Wow! A stunner.
Thanks a million.

May I ask, is this possible via PHPMYADMIN? If yes, please, how?

While I have your attention, is there a way to enlarge the max size of mysql database Tables? Again, in PHPMYADMIN interface.

[SuperRockmate]

I hope you can make video about mysql router

[Smit-SundaramTech]

MySQL returning same select query result from buffer, where there is update query parallelly executing from client application, how to prevent this, so i can get updated result from mysql?

[ahmad-murery]

I hope you do more videos explaining some other useful configurations.
Thanks Ben!

[michailgiannopoulos]

Another key performance setting if you do not use replication and can afford to lose 1 sec of transactions in case of a server crash is to disable log_bin and set = 2

[swiftybase]

Does PlanetScale automatically configure the option based on your plan, or do you need to adjust it manually?

[shindohikaru]

What is the tradeoff when increasing the values of innodb_buffer_pool_size and Will it affect the server performance for other operations, such as creating indexes when a new record has been inserted?

[kamurashev]

It’s very workload dependent, you are only reading so you’re showing kind of best case scenario.
If you also have mutations it would evict parts of cache so the result wouldn’t be as great.
Needless to say except that detail it is very very relevant.
Moreover I had opportunity to monitor a hardware MySQL server which were backing up a highly loaded web app before AWS migration and an RDS after one. The MySQL is so underestimated, with right config it was delivering sub ms for majority of queries. Later we had to add redis caching (to compensate for AWS RDS disadvantages) and even redis was for the very list not better than the hardware MySQL. I’d say for majority of cases MySQL was beating up redis. But RDS Aurora wasn’t even close. When we analyzed the most of it was coming from network delays. The back side of cloud services.

[drT-gp]

Don't get me wrong, it's a great video. But i'm still a little bit disappointed because there is so many parameters to control the buffer cache and i was expecting more. Please remember that a "good" hit-ratio does not mean that your application is tuned.

[TheMrBazil]

Better make a video of why you removed the free tier plan