OutOfMemoryError when executing a large number of INSERT statement files extracted from a dump file about herddb HOT 8 CLOSED

Hi @ouovowo
To help us understand the problem, can you share us your JVM configuration (bin/setenv.sh file)?
Note that the exception you sent us indicates that the configured JVM memory is not enough.
One solution would be to try increasing JVM memory by changing the -Xmx and -Xms variables.

from herddb.

In theory while restoring the database the CLI should convert the INSERT statements to statements with placeholders ('?'),
it looks like the planner is parsing huge SQL statements.

You can try to do the dump with the --extended-insert mode in order to have smaller INSERT statements

from herddb.

oops, i didn't specify max memory size, just letting it by default. But it might be 4gb according to my observation.
Thanks for advice. I will have a try with changing the JVM configuration. But --extended-insert mode seems to be enabled by default. it has split the complete INSERT into parts, each part is 1MB.
Btw, should i comment out CREATE DATABASE, SET, USE, LOCK,... statements? It seems that even with --mysql, these statement cannot be dealt properly? To avoid unknown problems, i have commented them out.
Or is there any convenient way to load data from .csv file just like LOAD DATA in mysql.

from herddb.

Are you able to provide a sample of the dump file ?
usually restoring mysql dumps works well out of the box, but it has been passed quite some time that I did it

maybe @aluccaroni can share some suggestions about the command to use to dump the mysql files (I remember we tested importing databases for some widely used applications)

from herddb.

I have re-read the docs for mysqldump
actually you would have to try to disable extended inserts
and this seems possible by using --skip-opt

because otherwise (as you say) it is enabled by default

from herddb.

Cheers! It seems work well now by adding --extended-insert=FALSE, though it's quite slow. Thanks so much.

from herddb.

@eolivelli I don't have the procedure on hand on how we exported and imported into herd the few production mysql database we used to have (in the last few years we mainly created new herd installation without any migration), but using the "--skip-opt" option was pretty much the standard on our mysql backup procedures, so it's very likely that we used that at the time.

from herddb.

It seems that --skip-opt is not the key. Problem occurs only when continuous inserting tuples into large or GIANT table. Due to the limit of my device performance, my only solution is to restart the server every certain times of insertion to avoid catastrophic gc overhead. Troublesome, but simple and effective.

from herddb.