MySQL Server 8.0 - Increase innodb_buffer_pool_size

Question

I have a question regarding MySQL Server 8.0 Configuration. I don't know, if this question is maybe to broad. If so, please let me know, then I can delete it, but I would be happy about any comments, hints or links to my question.

I have a Windows Server 2022 with 32 GB RAM (I don't know, if it makes any difference if it is running on Windows or Linux). On this server is running a MySQL Server 8.0 Community installtion. Actually I did no special configuration to the MySQL Server. I realized, that actually only 16% of the RAM is being used by the system. As far as I know, it would be good to allocate as much as possible of the RAM to the MySQL Server to increase performance, stability etc.. The MySQL Server is running actually 2 databases. Actually the main database is a mix of MyISAM and InnoDB tables. For InnoDB there is an option innodb_buffer_pool_size. As far as I read, this option seems to be quite high to allocate the databases in the RAM.

I queried this value on the system with

SELECT @@innodb_buffer_pool_size;

The result of this query is currently: 134217728. As far as I know, this value is given in bytes, so the current config of @@innodb_buffer_pool_size is around 134 MB, which is not so much.

I'm asking me, if it is worth to increase this value i.e. to 16GB. But then I'm asking me, if I need to increase other innodb options as well or if it would be OK, to increase just this value. Actually I know the query language and stored procedures so on well, but I'm not a MySQL Server DB Admin. So I would be happy to get any helpful feedbacks on this issue. Many thanks in advance.

Answer 1

A few quick things you should know about the innodb buffer pool:

• The default size of the innodb buffer pool is 128MB. This size is meant to make a good experience for folks who just download MySQL to evaluate it on a laptop. This is fine for a tiny server or development on a laptop, but it's probably way too small for a production server.

• It's not always true that increasing the innodb buffer pool "as much as possible" is necessary. If your database is small, a huge buffer pool won't give any improvement. The buffer pool holds only one copy of each page of your database, so there's no need to make the buffer pool much larger than the database itself.

  By analogy, if you need to put a single can of soup in a box, does the box need to be 2 meters square? No, a smaller box is enough.

• The buffer pool usually doesn't even need to be as large as your database. Most applications query a small subset of data frequently, and the rest of the data infrequently. So a small buffer pool can be adequate for a much larger database.

  At my last job, we had a default that the buffer pool was configured to be 10% of the size of the database. At some point, we realized disks on new servers were larger, but RAM wasn't, so we had to change our default configuration so the buffer pool was allocated at 5% of the size of the database. There was no significant difference in performance for most applications.

  Of course there are always exceptions. Some applications may need a greater portion of the database in RAM to get good performance. There is no single rule for this, because it depends on your application, and whether it needs access to all the data all the time.

  You can experiment with different buffer pool sizes with your database and your application traffic. Keep increasing the buffer pool and measure performance. If you see diminishing returns, then the buffer pool is ample for your workload.

• It is possible to oversize the buffer pool and cause problems with overallocation of RAM on your server. Some people say you should allocate the buffer pool as 75-80% of your server's RAM, but this is oversimplified. What other processes are using RAM on your server? How much RAM is MySQL using besides the buffer pool? Measure this in a tool like top (or equivalent on Windows, I don't use Windows so I don't know the tools).

• MyISAM never uses the innodb buffer pool. Each storage engine in MySQL minds its own business. :-)

• I would recommend avoiding MyISAM in all cases. See my answer to 'MyISAM versus InnoDB'

• A lot of information is available to guide you on tuning MySQL options. You will have to do some reading to understand it better.

  • MySQL Manual on the buffer pool

  • High Performance MySQL 4th Edition

  • Percona blog articles about the buffer pool

Answer 2

There are 3 methods,

Method 1: Permanent edit in the config file.
Stop the service anyhow. For the Linux distribution is like sudo service mysql stop to stop the running service and prevent any interference with other processes. Then edit the Mysql config file by sudoer privilege that based on your installation may vary, for me is like sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then add the following lines at the end of the file and provide your numbers:

innodb_buffer_pool_size = 24G
innodb-buffer-pool-instances = 12
innodb-buffer-pool-chunk-size = 2G

There is one important point mentioned by the MySQL page. Its summary is the pool size should be equal to the multiplication of the number of instances and chunk size, otherwise would be adjusted to the closest one. One hint may help is to use 50% up to 65% of the memory for pool size to have the best performance.

Method 2: define when open MySQL
This method is straightforward with a command like mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4 --innodb-buffer-pool-chunk-size=1073741824;

Method 3: within the MySQL terminal
You can use the command inside the MySQL terminal with such a command:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Answer 3

• "... mix of MyISAM and..." -- probably not, since MySQL 8.0 does not support it.
• "@@innodb_buffer_pool_size is around 134 MB" -- That is terribly small, ans smells like a leftover setting from before several upgrades. Change it to about 70% of available RAM (after accounting for any other apps running on the same server.)
• "good to allocate as much as possible of the RAM" -- There is no benefit in setting it any higher than about twice the size of all the data.