0

I'm getting the error message "ERROR 1206 (HY000): The total number of locks exceeds the lock table size" when running a query. Following a few searches I understand that increasing innodb_buffer_pool_size should resolve this. However I'm having some trouble doing so.

I'm using MySQL Workbench 6.3 CE and have found a file called "...\MySQL\MySQL Server 5.6\my-default.ini". I can't find any files called my.ini or my.cnf. Following advice from answers to other questions on this website I edited this file so that the part that read 

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

now reads

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 1024M

However this seems to make no difference to the original error and when I run 

SELECT variable_value FROM information_schema.global_variables
WHERE variable_name = 'innodb_buffer_pool_size';

it makes no difference to the size that gets returned: 263,192,576.

I have tried creating a copy of my-default.ini and saving it as my.ini, but this doesn't help either.

Any help would be massively appreciated!

MrTed
  • 1
  • I think you should look at alternative approach. I would consider that your query may badly structured or overly complex if this is happening. Can you show us your problem query? – Dragonthoughts May 17 '18 at 08:08
  • It's a fairly simple query, but I'm dealing with a fairly big dataset by my standards (80m rows, 200 variables). I'm looking to select a subset (or years) of the dataset into a new table. Here's the query - Create X as Select * from Y where Z in ('2012','2013', '2014', '2015'); – MrTed May 17 '18 at 12:37
  • If Mysql supports the `no lock` hint that may help. – Dragonthoughts May 17 '18 at 12:59

2 Answers2

0

With thanks to Dragonthoughts I searched for info on the no locks hint and found this - Any way to select without causing locking in MySQL?

I then used the following code 

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
Create X as Select * from Y where Z in ('2012','2013', '2014', '2015');
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

Which seemed to work.

MrTed
  • 1
0

You need to create two events in the database and then add the open event configuration in the my.ini

CREATE DEFINER=`admin`@`localhost` EVENT `setting` ON SCHEDULE EVERY 1 SECOND STARTS '2023-07-09 23:03:57' ON COMPLETION NOT PRESERVE ENABLE DO SET GLOBAL innodb_buffer_pool_size = 8589934592

CREATE DEFINER=`admin`@`localhost` EVENT `close` ON SCHEDULE EVERY 2 MINUTE STARTS '2023-07-10 22:08:47' ON COMPLETION NOT PRESERVE ENABLE DO SET event_scheduler = OFF

my.ini

[mysqld]
event_scheduler="ON"

or read this link 1、mysql5.7 https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html 10.4.28-MariaDB - mariadb.org binary distribution 2、https://mariadb.com/docs/server/storage-engines/innodb/operations/configure-buffer-pool/

woaidianqian
  • 1
  • 1