Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

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)?

share|improve this question
    
The only variable relevant to this error is innodb_buffer_pool_size because that's where the table of lock structures lives. The first step might be to SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; to verify that your configuration is actually setting the pool as large as you intend... but also, what are you thinking messing around with partitioning on such a relatively small table when you don't even have a primary key or any indexes? –  Michael - sqlbot Jan 25 at 5:37
    
when I did show global variables, it is showing buffer pool size as 8388608. And regarding your question about why partitioning, I do not have a unique column to have a primary key. Even If I add one I wont be able to query with it. I am using this table to query for a online application. I want my query to return results in less than a second. –  user2611221 Jan 26 at 7:20
    
I think I need to make changes with in [mysqld] block for my changes to take effect. let me try that. Thanks for pointing that out. –  user2611221 Jan 26 at 7:42
    
I changed innodb_buffer_pool_size under [mysqld] block and restarted. It doesn't seems to be effected. It is still 8388608. –  user2611221 Jan 27 at 14:16
add comment

1 Answer 1

Based on your comment, I would suggest raising table-open-cache beyond 2048. Why ?

You created a 500 partition table. Under the hood, it is really 500 tables.

That could push table caches limits and locking issues, especially if many table are already open.

If you are using MySQL 5.6, you could also create multiple table caches by setting table-open-cache-instances beyond 1.

Give it a Try !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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