The total number of locks exceeds the lock table size

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:

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




XL

Hi,

make it as easy as possible!

> I am doing a lot of INSERTs in parallel (populating a data warehouse).

Why?
Do not do this unless you are forced to!

We use in my.cnf:

innodb_log_file_size = 1G
innodb_llog_buffer_size = 1G
innodb lock_wait_timeout = 1200

innodb_flush_log_at_trx_commit = 0 -> much better performance

innodb_buffer_pool_size = 48G
innodb_puffer_pool_instances = 25
innodb_additional_mem_pool_size = 160m

kind regards,

Maybe you should enable write buffering for you raid hardware.
I am shure it is hardware battery backed up?

regards,

Toni

box has 96 GB memory.