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
[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.