CODEWITHSUNDEEP
mysql
user2960592
user2960592

Reputation: 41

how to increase innodb_buffer_pool_size in windows command line

I was trying to create a table in MySQL, then got error 1206 (total number of locks exceeds lock table size).

I understand I need to increase innodb_buffer_pool_size. (The current setting is about 100m) and my dataset is quite large.

The table I intended to create should return more than 1 million rows with lots of textual returns. I am a historian and not very experienced in changing MySQL option files.

Upvotes: 2

Views: 20343

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Add this to your config file

innodb_buffer_pool_size=1G

Then restart the mysql service. You should ensure that your logfiles are of minimum 250M.

Since the variable innodb_buffer_pool_size is of type read-only, you have to set the value in the config file and restart the service.

Edit

Step by Step

  1. Find the config file on the MySQL server. (my.cnf or my.ini)
  2. Change the innodb_buffer_pool_size=1G
  3. Make sure your innodb_log_file_size=250M (minimum, log file size should be minimum 25% of buffer_pool)
  4. Delete the old log files
  5. Restart your service!

Upvotes: 7

Nickolai Nielsen
Nickolai Nielsen

Reputation: 942

Download MySQL workbench from MySQL.com, install on the server and it will help you doing edits in the config file

Upvotes: -2

Related Questions