orthomcl error: The total number of locks exceeds the lock table size at orthomclPairs

Question

[sckinta]

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

[spaul8505]

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

[biomonte]

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

Answer 1

[Jean-Karim Heriche]

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.

[sckinta]

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.

[Jean-Karim Heriche]

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.

[sckinta]

Thank you! It works now :)

[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

[Jean-Karim Heriche]

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.

[spaul8505]

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

[Jean-Karim Heriche]

Did you try any of the suggestions above ?

[spaul8505]

I tried everything and then started a new post as I was unable to figure out the problem. It is posted at orthomclpairs table lock size error