1

I am trying to deal with the dreaded "The total number of locks exceeds the lock table size" error. Many entries in Stack Overflow suggest increasing innodb_buffer_pool_size from its default 8 MB.

I verify the current buffer pool size with SELECT @@innodb_buffer_pool_size; and get the default 8388608. I get the same result in the output of mysqld --help --verbose

Following other stack overflow entries and the MySQL documentation I create a file c:\my.cnf that contains

[mysqld]
innodb_buffer_pool_size=67108864

and then I restart the Windows service MySQL80.

This does not solve the problem. In addition SELECT @@innodb_buffer_pool_size; still gives 8388608.

However mysqld --help --verbose gives innodb-buffer-pool-size 67108864

So it seems that the configuration file did take effect but an individual client process still sees the old value although that sounds impossible.

What do I do to actually increase the buffer pool size for my program?

WesR
  • 1,292
  • 1
  • 18
  • 30
  • Have you tried to disconnect the client and re-connect? – Raptor Jun 24 '22 at 04:05
  • You are not supposed to create my.cnf manually. MySQL server will create its own my.cnf (or my.ini on Windows) file. You have to locate the file. Normally on Windows, it is under C:\ProgramData\MySQL\MySQL Server, but can be configured during installation. Take a look at [this question](https://stackoverflow.com/questions/14597884/mysql-my-ini-location) to get more understanding. – Hermanto Jun 24 '22 at 04:56
  • @Raptor; thanks. I have rebooted the entire computer which, of course, included reconnecting the lient. – WesR Jun 24 '22 at 18:44
  • I might be finally able to notice your problem. You created a `my.cnf`, however in Windows OS, you should use: `C:\ProgramData\MySQL\MySQL Server 8.0\my.ini`. Add or modify the `innodb_buffer_pool_size` in the `[mysqld]` section in the file. Restart the service afterwards. `my.cnf` is for Linux. – Raptor Jun 27 '22 at 01:49
  • 1
    Thanks to @raptor and hermanto for offering assistance. Ultimately the problem turned out to be a silly typo of mine in the text above. I put innodb_buffer_pool_size=1G in C:\Program Files\MySQL\MySQL Server 8.0\my.ini and it worked. Note that the MySQL 8 documentation sec 4.2.2.2 and the output of mysqld --help --verbose both say that the options can be in multiple locations and will be checked in this order: C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf – WesR Jun 28 '22 at 04:43

0 Answers0