repetitive I/O during select

[mARK bLOORE]

I used strace on the mysqld thread doing the following, and got a strange result.

SELECT *
FROM results0047
WHERE `query`
IN (
SELECT id
FROM items
WHERE state = 'deleted'
)
LIMIT 10

here is a small part of the strace output:

lseek(106, 89035280, SEEK_SET) = 89035280
read(106, "\3\0L\0\0\376.\340\21\0?\0file:///", 20) = 20
pread(106, "\0\376.\340\21\0?\0file:///var/storage/visu"..., 76, 89035284) = 76
lseek(106, 89035280, SEEK_SET) = 89035280
read(106, "\3\0L\0\0\376.\340\21\0?\0file:///", 20) = 20
pread(106, "\0\376.\340\21\0?\0file:///var/storage/visu"..., 76, 89035284) = 76

I.e., it reads the same location over and over, quite rapidly, at least hundreds of times. It does keep incrementing the address, but slowly. The processor spends about 98% of its time in disk-wait state.
I don't know what file it is reading, or even whether it is a file, but the items table row length is 79, and that table does have those file URLs in it.

Can anyone tell me what is happening here?



Edited 1 time(s). Last edit at 05/10/2006 05:11PM by mARK bLOORE.