CODEWITHSUNDEEP
phpmysqlinsert
lcm
lcm

Reputation: 1767

MySQL insert exceeding memory. Do I use a php script to send data in chunks?

INSERT INTO events (venue_id, artist_id, name, description)
SELECT e.id, e.artist_id, d.a_song, d.a_lyrics
FROM dump_sql AS d
    INNER JOIN events AS e
    ON d.a_album = e.name

Above is the mysql query I am using...works fine. Problem is that I have way too much data (150k records) that is too much it appears for the amount of the memory the server or mysql will allow.

I think at a minimum I need a php script to insert the data in chunks and perhaps increasing the memory allowance in the php, mysql and ???

Any and all help here would be most appreciated...I am a php newb and could use some help coming up with a script or any other pointers.

Thank you!

Error:

Node 0 DMA32 free:2776kB min:2788kB low:3484kB high:4180kB active_anon:211288kB inactive_anon:211276kB active_file:16kB inactive_file:0kB unevictable:0kB isolated(anon):128kB isolated(file):0kB present:500960kB mlocked:0kB dirty:0kB writeback:0kB mapped:116kB shmem:12kB slab_reclaimable:11372kB slab_unreclaimable:32752kB kernel_stack:904kB pagetables:10656kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:640 all_unreclaimable? yes lowmem_reserve[]: 0 0 0 0 Node 0 DMA: 12*4kB 22*8kB 0*16kB 0*32kB 0*64kB 0*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 0*4096kB = 2016kB Node 0 DMA32: 676*4kB 12*8kB 4*16kB 0*32kB 0*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 2864kB 5001 total pagecache pages 4940 pages in swap cache Swap cache stats: add 1565880, delete 1560940, find 743932/825587 Free swap = 0kB Total swap = 1044216kB 131071 pages RAM 5577 pages reserved 2405 pages shared 118768 pages non-shared Out of memory: kill process 24373 (httpd) score 410236 or a child Killed process 24373 (httpd) vsz:1640944kB, anon-rss:345220kB, file-rss:28kB

Upvotes: 0

Views: 406

Answers (1)

Rafay
Rafay

Reputation: 6188

Try changing the default value of

max_allowed_packet

in my.ini.

Change it to something like:

max_allowed_packet = 100M

and see if that helps.

Upvotes: 1

Related Questions