0

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:

# PER CLIENT SETTINGS #
# 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


# LOGGING #
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

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000


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

# CACHES AND LIMITS #
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 #
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. – Jaffer Wilson Feb 6 '17 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? – Wilson Hauck Feb 13 '18 at 15:02
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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