Inserting large amount of data into mysql. Out of memory

letharion
letharion used Ask the Experts™
on
I have
"insert into $1 (decimal x 4, int x 2) values ("
in a text file, and do
cat insert-command values-file > file2
mysql < file2

This works well with smaller files, but dies with:
ERROR 5 (HY000) at line 13: Out of memory (Needed 2579824 bytes)
on a file about 130mb.
Any suggestions on how to work around this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

Have you tried changing configurations in mysql configuration file. Try changing values of
key_buffer_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
tmp_table_size
table_cache

DJ

Author

Commented:
First I tried to raise all tenfold, which made the "needed" value fall a bit lower.
I reverted and starting changing key_buffer_size, and noticed that it made a difference.
If I push it up to 2000M, the error message changes to:

ERROR 2006 (HY000) at line 13: MySQL server has gone away

Author

Commented:
Googling indicates that max_packet_size might be relevant. It's set to 64 on both client and server.

Author

Commented:
I gave it a try and raised the value to 640 on both sides. The query seems to take a while longer before it fails, but still does so.
I just chopped the data up with split and scripted several inserts.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial