The total number of locks exceeds the lock table size

On 2011-09-18 23:59, Ignoramus30681 wrote:
[...]
>
> The above query was stuck. and then I started getting these errors
>
> "The total number of locks exceeds the lock table size"
>
> Why am I getting this error? I was expecting INSERTs to lock up until
> the transaction was done.
>

Googling for the error (you might want to give it a try indicates
that your bufferpool is to small.

Another opportunity is to lock the whole table and thereby eliminating
the need for row locks. I have not verified that innodb works this way,
so it might not work at all.


/Lennart
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, Lennart Jonsson wrote:
> On 2011-09-18 23:59, Ignoramus30681 wrote:
> [...]
>>
>> The above query was stuck. and then I started getting these errors
>>
>> "The total number of locks exceeds the lock table size"
>>
>> Why am I getting this error? I was expecting INSERTs to lock up until
>> the transaction was done.
>>
>
> Googling for the error (you might want to give it a try indicates
> that your bufferpool is to small.
>
> Another opportunity is to lock the whole table and thereby eliminating
> the need for row locks. I have not verified that innodb works this way,
> so it might not work at all.

Lennart, I changed my design yesterday.

Now I do

LOCK TABLES filltable WRITE, othertable WRITE
INSERT INTO othertable ... SELECT from filltable
UNLOCK TABLES

Unfortunately, I still get the error.

It seems to be that MySQL is not capable of INSERTing more than so
many rows at once.

I changed the buffer pool size to 2 GB, I will see if this helps.

i
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19 15:09, Ignoramus15208 wrote:
[...]
>
> I changed the buffer pool size to 2 GB, I will see if this helps.
>

It appears as if innodb uses row-locking no matter what (atleast I
intepret it that way). On

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

it is stated that:

The lock table in InnoDB is stored so space-efficiently that lock
escalation is not needed: Typically, several users are permitted to lock
every row in InnoDB tables, or any random subset of the rows, without
causing InnoDB memory exhaustion.


/Lennart
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, Lennart Jonsson wrote:
> On 2011-09-19 15:09, Ignoramus15208 wrote:
> [...]
>>
>> I changed the buffer pool size to 2 GB, I will see if this helps.
>>
>
> It appears as if innodb uses row-locking no matter what (atleast I
> intepret it that way). On
>
> http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
>
> it is stated that:
>
> The lock table in InnoDB is stored so space-efficiently that lock
> escalation is not needed: Typically, several users are permitted to lock
> every row in InnoDB tables, or any random subset of the rows, without
> causing InnoDB memory exhaustion.

Well, I guess I am not typical.

i
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 09/18/2011 05:59 PM, Ignoramus30681 wrote:
> I get the above error from MySQL.
> What is going on, is that I have a InnoDB table "filltable", into which
> I am doing a lot of INSERTs in parallel (populating a data warehouse).
> In addition, at some point during the ongoing INSERT process, I issued
> this command:
>
> START TRANSACTION
> INSERT INTO othertable ... SELECT from filltable
> DELETE FROM filltable
> COMMIT
> The above query was stuck. and then I started getting these errors
>
> "The total number of locks exceeds the lock table size"
>
> Why am I getting this error? I was expecting INSERTs to lock up until
> the transaction was done.
>
> Thanks

how many records are we talking about?
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, boris wrote:
> On 09/18/2011 05:59 PM, Ignoramus30681 wrote:
>> I get the above error from MySQL.
>> What is going on, is that I have a InnoDB table "filltable", into which
>> I am doing a lot of INSERTs in parallel (populating a data warehouse).
>> In addition, at some point during the ongoing INSERT process, I issued
>> this command:
>>
>> START TRANSACTION
>> INSERT INTO othertable ... SELECT from filltable
>> DELETE FROM filltable
>> COMMIT
>> The above query was stuck. and then I started getting these errors
>>
>> "The total number of locks exceeds the lock table size"
>>
>> Why am I getting this error? I was expecting INSERTs to lock up until
>> the transaction was done.
>>
>> Thanks
>
> how many records are we talking about?
>

Roughly 250,000 or so.

i
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, Lennart Jonsson wrote:
> On 2011-09-19 19:06, Ignoramus15208 wrote:
>> On 2011-09-19, Lennart Jonsson wrote:
>>> On 2011-09-19 15:09, Ignoramus15208 wrote:
>>> [...]
>>>>
>>>> I changed the buffer pool size to 2 GB, I will see if this helps.
>>>>
>>>
>>> It appears as if innodb uses row-locking no matter what (atleast I
>>> intepret it that way). On
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
>>>
>>> it is stated that:
>>>
>>> The lock table in InnoDB is stored so space-efficiently that lock
>>> escalation is not needed: Typically, several users are permitted to lock
>>> every row in InnoDB tables, or any random subset of the rows, without
>>> causing InnoDB memory exhaustion.
>>
>> Well, I guess I am not typical.
>
> Don't get me wrong, without knowing any details, your ETL process
> looks kinda normal to me. However, the statement above makes me wonder
> whether innodb is the best choice for a DW. I googled for "mysql
> datawarehouse engine" and it appears to be a couple of engines that
> claims to be better than innodb for DW. Whether this is true or not (I
> don't know), it's probably a good idea to read up on alternatives to
> innodb for DW.

Lennart, right now, I am looking at Postgres closely. It appears to be
somewhat more robust. However, I have some MySQL experience and no
Postgres experience, and I am not yes convinced that Postgres is much
superior to MySQL in that regard.

What I do know by now, is that MySQL claims about "scalability"
contain more hot air than I would like.

Regarding the problem that started this thread, for example, the
proper behavior of a well designed database engine, would be to use
another locking technique instead of failing everything in a manner
that is hard to recover.

i
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, Lennart Jonsson wrote:
> On 2011-09-19 21:36, Ignoramus15208 wrote:
> [...]
>>
>> Lennart, right now, I am looking at Postgres closely. It appears to be
>> somewhat more robust. However, I have some MySQL experience and no
>> Postgres experience, and I am not yes convinced that Postgres is much
>> superior to MySQL in that regard.
>>
>
> What I had in mind was alternative engines for mysql, but it probably
> wont hurt to look at other dbms as well.
>
> However, I just saw your answer to Boris question, and I'm pretty
> convinced that innodb will handle that amount of rows fairly well. I
> jumped to conclusions about the amount of rows that where about to be
> loaded.

Your initial impression was correct.

The final table size will be 40 million rows after the old data (that
I have right now) is loaded. I hope to grow it to 100 million rows.

I was doing those insert selects in chunks, with that particular chunk
being 250k rows.

> [...]
>
>> Regarding the problem that started this thread, for example, the
>> proper behavior of a well designed database engine, would be to use
>> another locking technique instead of failing everything in a manner
>> that is hard to recover.
>>
>
> Why is it hard to recover from the error, can't you just rollback the
> transaction and handle it from there?
>
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19 19:06, Ignoramus15208 wrote:
> On 2011-09-19, Lennart Jonsson wrote:
>> On 2011-09-19 15:09, Ignoramus15208 wrote:
>> [...]
>>>
>>> I changed the buffer pool size to 2 GB, I will see if this helps.
>>>
>>
>> It appears as if innodb uses row-locking no matter what (atleast I
>> intepret it that way). On
>>
>> http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
>>
>> it is stated that:
>>
>> The lock table in InnoDB is stored so space-efficiently that lock
>> escalation is not needed: Typically, several users are permitted to lock
>> every row in InnoDB tables, or any random subset of the rows, without
>> causing InnoDB memory exhaustion.
>
> Well, I guess I am not typical.

Don't get me wrong, without knowing any details, your ETL process
looks kinda normal to me. However, the statement above makes me wonder
whether innodb is the best choice for a DW. I googled for "mysql
datawarehouse engine" and it appears to be a couple of engines that
claims to be better than innodb for DW. Whether this is true or not (I
don't know), it's probably a good idea to read up on alternatives to
innodb for DW.


/Lennart
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19 21:36, Ignoramus15208 wrote:
[...]
>
> Lennart, right now, I am looking at Postgres closely. It appears to be
> somewhat more robust. However, I have some MySQL experience and no
> Postgres experience, and I am not yes convinced that Postgres is much
> superior to MySQL in that regard.
>

What I had in mind was alternative engines for mysql, but it probably
wont hurt to look at other dbms as well.

However, I just saw your answer to Boris question, and I'm pretty
convinced that innodb will handle that amount of rows fairly well. I
jumped to conclusions about the amount of rows that where about to be
loaded.

[...]

> Regarding the problem that started this thread, for example, the
> proper behavior of a well designed database engine, would be to use
> another locking technique instead of failing everything in a manner
> that is hard to recover.
>

Why is it hard to recover from the error, can't you just rollback the
transaction and handle it from there?
 >> Stay informed about: The total number of locks exceeds the lock table size 

Lennart Jonsson wrote:
> On 2011-09-19 19:06, Ignoramus15208 wrote:
>>>
>>> The lock table in InnoDB is stored so space-efficiently that lock
>>> escalation is not needed: Typically, several users are permitted to lock
>>> every row in InnoDB tables, or any random subset of the rows, without
>>> causing InnoDB memory exhaustion.
>>
>> Well, I guess I am not typical.

Things change. The above was written ~10 years ago. Since then the
available (affordable) disk space has much increased. More than the
affordable memory.

But of course we miss details here. 2G of buffer pool seem to be a
lot for normal people. But how many rows are you trying to move?
Was it not about data in the two-digit TB range?

IIRC InnoDB allows up to 80% of the buffer pool to be eaten by lock
structures before it bails out with the above error message. But of
course with several TB of disk space, you can easily reach that.
There should be messages in the error log.

The workaround would be to COMMIT every once in a while. Or - if
you copy from table to table (which makes not much sense IMHO) -
to form batches in the range of some 100K rows.

> the statement above makes me wonder
> whether innodb is the best choice for a DW.

It is not.

> I googled for "mysql
> datawarehouse engine" and it appears to be a couple of engines that
> claims to be better than innodb for DW.

Told you so.


XL
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-19, Axel Schwenke wrote:
> The workaround would be to COMMIT every once in a while. Or - if
> you copy from table to table (which makes not much sense IMHO) -
> to form batches in the range of some 100K rows.
>
>> the statement above makes me wonder
>> whether innodb is the best choice for a DW.
>
> It is not.
>

Axel,

I made changes, so that I do a COMMIT once in a while, roughly once
every 100,000 rows or so. I split the work into chunks and process
one chunk at a time, first populating another identical table, and
them doing INSERT SELECT.

This is going to be my modus operandi, as I want to be able to use the
master table most of the time. This allows me to lock it for
populating only once per day or per week, instead of badgering it with
INSERTs all the time.

This seems to at least work now. In about 24 hours, I have uploaded
about 4.6 million records into the database.

I fully realize that a database this large is a "pet elephant", which
means it has a lot more challenges than having a regular sized
pet. But, I hope that I learn smoething and make some money also.

>> I googled for "mysql
>> datawarehouse engine" and it appears to be a couple of engines that
>> claims to be better than innodb for DW.
>
> Told you so.

What engine would you suggest. though?

thanks

i
 >> Stay informed about: The total number of locks exceeds the lock table size 

On 2011-09-20, Axel Schwenke wrote:
> Hi $UNKNOWN,
>
> Ignoramus19458 wrote:
>>
>> I made changes, so that I do a COMMIT once in a while, roughly once
>> every 100,000 rows or so. I split the work into chunks and process
>> one chunk at a time, first populating another identical table, and
>> them doing INSERT SELECT.
>>
>> This is going to be my modus operandi, as I want to be able to use the
>> master table most of the time. This allows me to lock it for
>> populating only once per day or per week, instead of badgering it with
>> INSERTs all the time.
>
> Why do you believe INSERT ... SELECT being faster than INSERT alone?
> Especially if you use an optimized INSERT operation:

Once I set up my data warehouse, I will be continuousyl populating it
with new stuff, that I will be downloading all day.

It is not the speed if INSERTs that I care about. I want them to be
done in a big batch at night, rather than piece by piece throughout
the day. So, I populate a side table during the day, and I will do
INSERT SELECT at night.

>
> - LOAD DATA INFILE -or-
> - prepared INSERT statement -or-
> - multi-row INSERT syntax
>
> I can think of only one scenario where this would make sense: if your
> data arrives in random order and you can do INSERT ... SELECT in PK
> order.
>
>>>> I googled for "mysql
>>>> datawarehouse engine" and it appears to be a couple of engines that
>>>> claims to be better than innodb for DW.
>>>
>>> Told you so.
>>
>> What engine would you suggest. though?
>
>

I cannot find it in Google News, sorry

i
 >> Stay informed about: The total number of locks exceeds the lock table size