I am trying to partition a table with 200 million records in it. And it throws exception as 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)?
