Re: Reducing memory usage of insert into select operations?

["Francisco Reyes" <lists@xxxxxxxxxxxxxxxx>]

On 11:25 am 07/18/08 Richard Huxton <dev@xxxxxxxxxxxx> wrote:

Strace of the single/large process.
Again, all the query is doing is
insert into <file> select <subquery>

The strace is pretty much a repetition of the lines below.

semop(557057, 0x7fbfffdfb0, 1)          = 0
lseek(100, 0, SEEK_END)                 = 671719424
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(508, 0, SEEK_END)                 = 55697408
write(508, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, "\0\0\0&\0\224\21\0\225o\10\0\30\331c\0c\225%w(\0\0\0\0\0\0\0\0\0\5\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1a0, 1)          = 0
lseek(100, 0, SEEK_END)                 = 671727616
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1)          = 0
semop(557057, 0x7fbfffd1a0, 1)          = 0
semop(557057, 0x7fbfffd1c0, 1)          = 0
read(381, "w\317\21\0<]9\0\177\246eA(\0\0\0\0\0\0\0\0\0\5\0\2\0\30\0.\v\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1a0, 1)          = 0
lseek(512, 0, SEEK_END)                 = 48144384
write(512, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1)          = 0
lseek(100, 0, SEEK_END)                 = 671735808
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(517, 0, SEEK_END)                 = 89309184
write(517, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1)          = 0
semop(557057, 0x7fbfffddd0, 1)          = 0
lseek(100, 0, SEEK_END)                 = 671744000
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, ">\212\225\202(\0\0\0\0\0\0\0\0\0\5\0\2\0\30\0000\v\0\0\1\0\23\2\0\0\0\t"..., 8192) = 8192
lseek(510, 0, SEEK_END)                 = 29351936
write(510, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(100, 0, SEEK_END)                 = 671752192
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffddf0, 1)          = 0
read(381, "\0\0\0\0\0\0\5\0\2\0\30\0001\v\0\0\0\0\23\2\0\0\0\30\0\4\20\0\302\326\0\0"..., 8192) = 8192
lseek(513, 0, SEEK_END)                 = 19316736
write(513, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(100, 0, SEEK_END)                 = 671760384
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(381, "\2\0\30\0004\v\0\0\0\0\23\2\0\0\0\16\0\254\r\0\t+\35\0\301\217@\0\304\346U\241"..., 8192) = 8192
lseek(100, 0, SEEK_END)                 = 671768576
write(100, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
lseek(518, 0, SEEK_END)                 = 55025664
write(518, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
semop(557057, 0x7fbfffd1c0, 1)          = 0
semop(557057, 0x7fbfffd1c0, 1)          = 0
semop(557057, 0x7fbfffd1c0, 1)          = 0
lseek(100, 0, SEEK_END)                 = 671776768