 | |
The total number of locks exceeds the lock table size
comp.databases.mysql comp.databases.mysql
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: The total number of locks exceeds the lock table size -
09-19-2011
, 02:54 PM
|
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. |
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.
[...]
Quote:
|
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. |
transaction and handle it from there?
#12
| |||
| |||
|
|
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. |
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.
Quote:
|
[...] 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? |
#13
| |||
| |||
|
|
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.  |
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.
Quote:
|
the statement above makes me wonder whether innodb is the best choice for a DW. |
Quote:
|
I googled for "mysql datawarehouse engine" and it appears to be a couple of engines that claims to be better than innodb for DW. |
XL
#14
| |||
| |||
|
|
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. |
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.
Quote:
|
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
#15
| |||
| |||
|
|
Hi $UNKNOWN, Ignoramus19458 <ignoramus19458 (AT) NOSPAM (DOT) 19458.invalid> 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: |
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.
Quote:
|
- 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? p7toj8-9ps.ln1 (AT) xl (DOT) homelinux.org |
i
#16
| |||
| |||
|
|
I am doing a lot of INSERTs in parallel (populating a data warehouse). |
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.