I am trying to partition a table with 200 million records in it. This table already exists:

CREATE TABLE linkmap_ntoken (
  target_nToken varchar(200) NOT NULL,
  ntoken_hashcode int(15) NOT NULL,
  targeturl varchar(2000) NOT NULL,
  targetbreadcrumb varchar(400) NOT NULL,
  sourceurl varchar(2000) NOT NULL,
  sourcebreadcrumb varchar(400) NOT NULL
 ) ENGINE=InnoDB. 

Now I am trying to create 500 partition with this statement:

ALTER TABLE linkmap_ntoken partition by HASH (ntoken_hashcode) partitions 500;

It throws exception: total number of locks exceeded the lock table size.

I read few suggestions online and changed my.cnf file. Here are the configurations:

# bit high but I got tons of ram here #
sort_buffer_size                = 2M
read_buffer_size                = 2M
binlog_cache_size               = 1M
wait_timeout                    = 31536000
interactive_timeout             = 300
max_allowed_packet              = 12M
thread_stack                    = 128K
table_cache                     = 1024
myisam_sort_buffer_size         = 1M
tmp_table_size                  = 12M
max_heap_table_size             = 12M

log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/slowquery.log

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

max_allowed_packet             = 16M
max_connect_errors             = 1000000

log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G

I have restarted mysql after making these changes. Still it throws the exception. My server has 12 cores and 48GB Ram and I can dedicate this server for this partitioning process for time being. Are there any suggestions to bypass this exception (total number of locks exceeded)?

When I did show global variables, it is showing buffer pool size as 8388608. I changed innodb_buffer_pool_size under [mysqld] block and restarted. It doesn't seems to be affected. It is still 8388608.

Regarding "why partitioning", I do not have a unique column to have a primary key. Even If I add one I won't be able to query with it. I am using this table to query for an online application. I want my query to return results in less than a second.

  • Have you tried to change innodb_buffer_pool_size = 80M? It worked for me. Commented Feb 6, 2017 at 11:31
  • Please post your complete error log and complete my.cnf/ini. Having 48GB RAM is good, what is your max_connections variable set at? Commented Feb 13, 2018 at 15:02

2 Answers 2


Try this in my.cnf file,

[mysqld] innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.

  • It clearly states innodb-buffer-pool-size = 40G in the post.
    – P.Péter
    Commented Mar 17, 2020 at 9:29

It may be a late answer, but should someone experience a similar problem in the future, remember that the solution lies in tweaking the Innodb_buffer_pool_size. This of course assumes that your server is a dedicated mysql database server. By default, it is a good practice to give it 10% more that the actual memory size that the server has. MySQL is able to slowly scale up usage of buffer_pool and will give your tables good performance on CRUD actions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.