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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

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

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 '14 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 '14 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 '14 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 '14 at 14:16

don't increase memory usage, but instead disable the row locks by setting the transaction isolation level to what ever suits the individual situation.

In your situation I would recommend READ UNCOMMITTED.

share|improve this answer
Have you checked the OP's answer there, too? – dezso Sep 30 '15 at 15:20
The OP on never claims to of even tried READ UNCOMMITTED, READ COMMITTED still leaves locks on ONLY the index entries that have been changed. READ COMMITTED is obviously not useful in his situation. – user3338098 Oct 1 '15 at 14:42
and yes the mysql 5.6 docs state that READ COMMITTED is esentially "consistent (nonlocking) reads", but that is obviously a lie as consistency would either require a temporal database storage engine or locking. I think what mysql means to say is that it locks less. But that's not what they say so it is a lie. – user3338098 Oct 2 '15 at 14:22
I'm not at all sure what this answer is suggesting. Please edit and clarify ("by setting the transaction isolation level." To what?). Link only answers are not usually welcome in this site. – ypercubeᵀᴹ Oct 2 '15 at 14:31
@ypercube I suggested EXACTLY what was stated: "[set] the transaction isolation level" to "disable the row locks". – user3338098 Oct 2 '15 at 15:12

Your Answer


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.