This is a test version of Biostars. For the public version, visit https://www.biostars.org.
orthomcl error: The total number of locks exceeds the lock table size at orthomclPairs

I used orthomcl to classify proteome from several non-model species to into orthogroups. After all-to-all blast, I got a ~6GB blast tabular file and loaded it to mysql database as instruction. However, when I tried to run orthomclPair step, it kept reporting DBD::mysql::st execute failed: The total number of locks exceeds the lock table size.

I changed default innodb_buffer_pool_size to 2GB and restarted mysql (https://major.io/2010/02/16/mysql-the-total-number-of-locks-exceeds-the-lock-table-size-2/), but it did not work and still reported same error. Did anyone have same problem when using orthomcl? How did you solve it?

Thank you

mysql orthomcl
• 9,502 views • link
updated by Ram Ram • written by sckinta sckinta

I have the same issue were you able to figure this out?

• 0 views • link
written by spaul8505 • spaul8505

I had the same issue and I solve it by adding the line innodb_buffer_pool_size=100G in mysql.cnf. I completely deleted and created back the "orthomcl" database on mysql. Thanks .-)

• 0 views • link
updated by Ram Ram • written by biomonte biomonte

1 answer

It could be you didn't increase innodb_buffer_pool_size to a value high enough for your case. If you don't need the InnoDB engine, just use MyISAM. InnoDB locks data by rows while MyISAM locks the whole table. You could always convert your table to InnoDB after having loaded all the data. If you don't need transactions, you should probably be using MyISAM, In my experience, it's faster than InnoDB.

• 0 views • link
updated by Ram Ram • written by Jean-Karim Heriche Jean-Karim Heriche

I changed innodb_buffer_pool_size to 40GB but it did not work either. Based on my data size, 40 GB should be enough. I do not know what is going on.

I tried to change storage engine to myisam but setting default-storage-engine = myisam in my.cnt file and restarted mysql. mysqld reports storage engine has been changed to myisam but when I login into orthomcl database and show engines, default storage engine is still innodb. What should I do? by the way, I have altered previously created table to myisam engine.

• 0 views • link
updated by Ram Ram • written by sckinta sckinta

Changing the default in the configuration file is only going to apply to new tables. If you already created the database with the table, its engine won't be affected. You can change a table engine after creation with ALTER TABLE mytable ENGINE=MYISAM;. Check the MySQL doc.

• 0 views • link
updated by Ram Ram • written by Jean-Karim Heriche Jean-Karim Heriche

Thank you! It works now :)

• 0 views • link
written by sckinta sckinta

New problem comes up! I changed to default storage engine to myisam in my.cnt file. However new tables were still created as innodb table and I had to alter them again. When I was altering one of them, using ALTER TABLE InplgOrthoInplg ENGINE=MyISAM; it reports the error same error as "ERROR 1206 (HY000): The total number of locks exceeds the lock table size". I tried to created new table and copy InplgOrthoInplg to new table. still same error. This error exists even when I changed innodb_buffer_pool_size to 100GB. Any advice?

Here is my system and mysql version

mysqld  Ver 5.5.46-0ubuntu0.14.04.2 for debian-linux-gnu on x86_64

Following is the part of mysqld output

default-storage-engine                            myisam
innodb-buffer-pool-size                           107374182400
innodb-log-buffer-size                            26843545600
innodb-log-file-size                              26843545600
• 0 views • link
updated by Ram Ram • written by sckinta sckinta

You're probably running into the same issue because you're trying to modify an InnoDB table that already has lots of data. You should create your table(s) as myisam:

CREATE TABLE t (i INT) ENGINE = MYISAM;

then add the data then convert to InnoDB if you really need to (e.g. no need if your table is read-only).

Now you also have the problem that you my.cnf file doesn't seem to be read. First, any change to the configuration must be followed by a restart of the server so make sure you did that. Also make sure that default-storage-engine = myisam is under the [mysqld] section of the my.cnf file.

• 0 views • link
updated by Ram Ram • written by Jean-Karim Heriche Jean-Karim Heriche

Hi, I have the same issue. Were you able to figure this out?

• 0 views • link
written by spaul8505 • spaul8505

Log in to answer this question.

More posts like this