Question: orthomcl error: The total number of locks exceeds the lock table size at orthomclPairs
2
gravatar for sckinta
4.2 years ago by
sckinta550
United States
sckinta550 wrote:

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

orthomcl mysql • 4.9k views
ADD COMMENTlink modified 4.2 years ago by Jean-Karim Heriche21k • written 4.2 years ago by sckinta550

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

ADD REPLYlink written 3.3 years ago by spaul850510

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

ADD REPLYlink written 2.9 years ago by Santiago Montero-Mendieta130
2
gravatar for Jean-Karim Heriche
4.2 years ago by
EMBL Heidelberg, Germany
Jean-Karim Heriche21k wrote:

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.

ADD COMMENTlink written 4.2 years ago by Jean-Karim Heriche21k

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.

ADD REPLYlink modified 8 weeks ago by RamRS25k • written 4.2 years ago by sckinta550
1

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.

ADD REPLYlink modified 8 weeks ago by RamRS25k • written 4.2 years ago by Jean-Karim Heriche21k

Thank you! It works now :)

ADD REPLYlink written 4.2 years ago by sckinta550

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
ADD REPLYlink modified 8 weeks ago by RamRS25k • written 4.2 years ago by sckinta550

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.

ADD REPLYlink modified 8 weeks ago by RamRS25k • written 4.2 years ago by Jean-Karim Heriche21k

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

ADD REPLYlink written 3.3 years ago by spaul850510

Did you try any of the suggestions above ?

ADD REPLYlink written 3.3 years ago by Jean-Karim Heriche21k

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

ADD REPLYlink written 3.3 years ago by spaul850510
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1385 users visited in the last hour