This MySQL setting makes your PHP app dead slow or crazy fast

[00:02] of slow PHP applications. But there are vastly different reasons that can lead to a slow query. In this video, we are going to look at the most important MySQL server configuration variable, the InnoDB buffer pool size, and I will

[00:18] explain why it's so important, how to monitor and change it. Mo, my name is Benjamin and I have helped thousands of developers with PHP application performance over the last 10 years. So what is the InnoDB buffer pool? Learning

[00:32] about MySQL performance will quickly lead you to all kinds of resources about the InnoDB buffer pool size variable. On a regular database load, it's the configuration variable with the highest leverage on performance. It's simple to

[00:48] explain why this is the case. InnoDB organizes all data that is stores in a concept that it calls pages. A page has a specific size and it includes information about the data in a table, the indexes in a table and much more.

[01:04] Consider this graphic where we represent the database in all parts that are stored in ROM and all parts that are stored on a disk. For inb uh all storage

[01:17] happens in pages and on the disk all this um the tables and indexes will be this um the tables and indexes will be stored in those pages. So we have a list of all those pages. Let's say page zero until page 1 billion 1 trillion or

[01:34] something like this. All the number of pages that are necessary to represent your tables and indexes on disk. And during the operation of InnoDB, it during the operation of InnoDB, it copies all those tables into the InnoDB

[01:48] buffer pool. And this is happening in the ROM of the server. So if you assign as much um RAM to the InnoDB buffer pool as you

[02:03] are storing on disk then you can store all the pages on the disk also in the memory and this makes the database much faster. Let's consider a second graphic where the RAM allocated to the InnoDB buffer pool is much smaller than the

[02:18] data that is stored on disk. And there you can easily see the problem. We see all the pages on disk do not fit in the ROM and InnoDB now has to go and evict ROM and InnoDB now has to go and evict pages to read other pages to the RAM and

[02:33] it will do this using a least recently used algorithm and depending on like how small the InnoDB buffer pool is. You will see a lot of evictions, a lot of changes happening there and data is constantly being read and being evicted

[02:48] constantly being read and being evicted um from RAM and onto the disk. This is an extremely simplified representation uh that I use to explain this context. A blog post by the author Dutato explained this with a little bit more advanced

[03:02] graphic that includes and differentiate between more components of InnoDB. So see in his graphic he has the in-memory part. The buffer pool is part of the in-memory part. Um but it includes additional concepts like the

[03:17] change buffer and also an adaptive hash index. There's also a separate in-memory data structure called the lock buffer and all these interact with each other to like store the data on disk into several different parts. So the per file

[03:33] per table table spaces, system table space, the general table space, there's a concept called undo table space and temporary table space and then a redo temporary table space and then a redo lock on disk. And all these concepts

[03:47] work in combination and allow in DB to make this cache completely transparent to a user. So as a user I only write insert, update, delete statements and uh

[03:59] select statements obviously and it will either use the cache or read on disk and I don't really see that it's just either working very fast using the in-memory data structures or slower when it's needs to write read things from disk.

[04:15] So what is the difference between in memory and disk? So for a long time now we have SSD discs which are much faster than sort of legacy discs that existed

[04:27] before. Still there's an order of magnitude difference in the performance between reading something from memory or reading it from disk. So it's still much much faster if we can access everything in memory. Let's dive into InnoDB and uh

[04:41] determining different metrics and ways of working with the InnoDB buffer pool. of working with the InnoDB buffer pool. And I have an application um that I experiment with using a MySQL database. And we're going to look at how this

[04:55] works with um InoDB. So we are starting the MySQL command uh MySQL server the MySQL command uh MySQL server version 8. And uh what we can do now is try to find out what is the current InnoDB buffer pool size. And there's a

[05:09] InnoDB buffer pool size. And there's a query for that. So select InnoDB buffer pool size. This is uh in bytes. So I calculate it down to megabytes

[05:25] megabytes as InnoB buffer pool size megabytes. And this says that the InnoDB buffer pool is just 8 mgabytes big. So this is

[05:37] pool is just 8 mgabytes big. So this is an extremely low value. But historically there have been like Linux distributions that um actually ship MySQL with this as a default value. So um it's always good to know like if you haven't set up the

[05:52] database yourself, what is the InnoDB buffer pool size on that server? So the next question would be um how good is the InnoDB buffer pool actually

[06:04] working and for that there's a command that you will find in tons of blog posts out there. they will give you this show engine inb status query and if you call it it will um

[06:21] print a big block of unstructured data um and you need to uh look at different things uh and get familiar with it. For the buffer pool statistics, there's a

[06:34] section buffer pool in memory. Buffer pool in memory. And we see Buffer pool in memory. And we see there's a buffer pool hit rate of 83 of 1,000 here. So, it's um sort of a percentage value showing how good the

[06:49] the buffer pull hit rate works. Um I would recommend uh an another way to do this that I've um found out because

[07:02] uh reading this output is not that easy uh especially on a database server with a lot of load in it. The output will be much bigger and it's it's going it's really hard to find the the real value and understand it. What you can do is

[07:16] actually query the hit rate from the information schema of uh InnoDB information schema of uh InnoDB information schema information schema in DB buffer pool stats

[07:32] and there you can see the hit rate is listed as 958 of 1,00. So that is what the output also shows in the command from before.

[07:48] that um with a more complex query and I'm going to copy paste that into the screen because I won't want to type this. And the query is um calculating this. And the query is um calculating the variable value of a um a global

[08:03] status variable called inoB buffer pool reads and uh divide this by the InnoDB buffer pool read requests. So how many read requests were there and uh what

[08:15] part of them led to actual reads on the disk and the inverse is uh not uh reading from the disk. So um there's some multiplication going on here um some multiplication going on here um that makes the uh variable comparable to

[08:30] the hit rate of the previous query. As you can see here and in the queries you can see here and in the queries before the hit rate of 958 um is quite big like if you compare this or like calculate this as a percentage it means

[08:44] calculate this as a percentage it means we have a cash hit rate of 95.8 or 94.9% in our two examples that we see here. And you can also um reach 1,000 of And you can also um reach 1,000 of 1,000. And um so it means um in this

[09:00] 1,000. And um so it means um in this case the cache uh works in a lot of cases reading the data from memory and instead of uh from disk. But what is the time frame that those numbers are relating to? But what time

[09:15] frame do these metrics relate to? For this we need to read in the buffer pool um documentation very carefully. It has a section about um the InnoDB standard monitor output which we saw here and it mentions

[09:31] that the per second averages provided in the output are based on the elapse time since InnoDB standard monitor output was last printed. For me, this interpretation means that when I run the query um

[09:46] the complex one here using the global status variables, I know that these are status variables, I know that these are based on the startup of the server and I can find this out by running them uh multiple times and we can see um they

[10:02] are incrementing over time and they are not reset to the last value since they not reset to the last value since they were read. uh compare this with the show were read. uh compare this with the show uh engine inb status output

[10:21] hit rate if we run this multiple times now it's 1,00 and then if we uh insert a query in between and run it again then we see it dropped so this has to be a rate of some sort um as the documentation says

[10:38] And the same is true for the hit rate query. If we run that in quick consecutive reasons, we see it's iterating. So it seems to be the same value as the engine output and it's depending on a time frame.

[10:53] So if you want to calculate um the hit rate for a specific time frame, let's rate for a specific time frame, let's say uh 1 minute, then you need to run say uh 1 minute, then you need to run the query we saw before.

[11:11] here it you see where we access the um two inb variables. Um a script that would calculate the hit rate for 1 minute would need to run the buffer pool

[11:23] reads and buffer pool read requests cache them for a minute and then calculate the difference between those values as they pass 60 seconds. So how big is the MySQL data set including indexes? This question is important to

[11:38] indexes? This question is important to answer because it um it helps you understand how big the InnoDB buffer pool is. And uh you could go and let's calculate this from the disk size of the InnoDB um buffer u um t files on disk.

[11:56] it from a perspective of how big are the individual tables and their indexes. The reason for this is MySQL has different or your application has different patterns of reading those data. Some

[12:08] tables are used just for logging data. They are not read a lot and some tables They are not read a lot and some tables are used very actively. So for archiving tables, you actually don't need them to be in the buffer pool all the time and

[12:22] that might make a difference on uh how big you size the application um the database server. If you have a database that is many gigabytes big or even in the terabytes, then it might not even be possible to have that much RAM as in a

[12:38] server to have it all in the buffer pool. So, um there's also a reason of um understanding with more nuance which tables contribute to the size. So, I'm going to copy uh a query into here, not typing it because it's a little bit

[12:53] bigger. Um this initially started from uh a query I found on Stack Overflow, but I um adjusted it quite a bit. Um also had input from Chris Kuntop who also had input from Chris Kuntop who helped me improve it more. Um and we'll

[13:08] helped me improve it more. Um and we'll see in a bit how it works. So it will print each database and table that is an InnoDB database. So it only looks at InnoDB um engine tables of the table type base type. It excludes MySQL SIS

[13:23] information and performance schema and it does a group by with roll up and this is like the interesting part. Let's look at the tables. So we have a few of them that are big. So category and category tech table for this Shopware store

[13:37] translations and then also the product search keyword table z table and they all and this is what the roll up command does. they all sum up to 132 megabytes

[13:53] um in this database and across the whole database server it's also the same because we only have one database. So this application needs 132 this application needs 132 um megabytes of data in the MySQL um

[14:08] database and that is not that big uh of an amount and you would be e uh it would be easy to have a database server of that size. um using a buffer pool that

[14:21] is maybe 500 megabytes for example. So you don't in this case even need a database server that has uh many GB of RAM. Uh two 3 or 4 GB of RAM uh would

[14:33] probably be enough um uh more than enough for this application. So how do we change the InnoDB buffer pool? Uh now there are two ways to do that. You can do it um at runtime which uh we will do now. So you can say set global InoDB

[14:50] now. So you can say set global InoDB buffer pool size to 500 buffer pool size to 500 to 512 mgabytes

[15:12] string variable. So let's again run the query select inb buffer pool inb buffer pool size

[15:28] and then we can see the innov buffer pool size was set to 488 megabytes. Um the reason it doesn't fit the set variable perfectly is that um the buffer variable perfectly is that um the buffer pool is only set in chunks of 8

[15:41] pool is only set in chunks of 8 mgabytes. So there's a variable called select in DB buffer pool chunk size.

[15:53] It shows it's 8 mgabytes. So it's can only be multiples of this value. If you change this value at runtime then um a server restart will reset it to the um

[16:05] configured value. So after changing this at runtime, you also need to change the configuration file of the server and this is different between different uh this is different between different uh MySQL or Linux distributions. So in my

[16:19] MySQL or Linux distributions. So in my case um I need to change it in say MySQL case um I need to change it in say MySQL MySQL confd MySQLD conf. So there is the variable we see it here. So let's change it to 500

[16:33] trend. And then we restart MySQL. And after the restart um we can look at And after the restart um we can look at the variable again.

[16:49] Oh no, this the chunk size. it was increased to 132 mgabytes dynamically because um with the actual size is now um four with the actual size is now um four times that value 512 megabytes.

[17:04] So this is what the server now has as InnoDB buffer pool. If you are hosting on a managed server provider, then you might not be able to ch change this variable and you need to contact them or you need to go to the cloud uh hosting

[17:19] panel, control panel and change the value of the server there. This was a quick rundown of InnoDB buffer pool, how it works, how you can uh log it, um see metrics about it, how you can change this values and uh some ideas on how to

[17:34] set it. I hope this was valuable to you. If you like this content about uh PHP performance um application performance um please subscribe to this channel or to the newsletter and the link is in the description. Thank you. Bye.