MySQL Data_free = 0 Slowness (appending to files slow?)

[Ivan Voras]

On 04/03/2013 21:50, Nick Evans wrote:

> iostat (slow case):
> 
>        tty           mfid0            mfid1              cd0             cpu
>  tin  tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in id
>    0   479  0.00   0  0.00  80.00   2  0.16   0.00   0  0.00  11  0  1  0 88

>    0   600  0.00   0  0.00  32.00   3  0.09   0.00   0  0.00  13  0  1  0 86
>    0   553  0.00   0  0.00   0.00   0  0.00   0.00   0  0.00  13  0  1  0 85
>    0  1631  0.00   0  0.00  38.40   5  0.19   0.00   0  0.00  14  0  1  0 85
>    0   665  0.00   0  0.00  32.00   4  0.12   0.00   0  0.00  14  0  1  0 85
>    0   737  0.00   0  0.00  32.00   1  0.03   0.00   0  0.00  15  0  1  0 84
>    0   485 32.00   2  0.06  127.05 770 95.56   0.00   0  0.00  13  0  1  0 85
>    0   624  0.00   0  0.00  126.95  61  7.55   0.00   0  0.00  13  0  1  0 85
>    0  1586  0.00   0  0.00  32.00   1  0.03   0.00   0  0.00  14  0  1  0 85
>    0   565  0.00   0  0.00  32.00   1  0.03   0.00   0  0.00  14  0  1  0 85
>    0   488  0.00   0  0.00  32.00   2  0.06   0.00   0  0.00  14  0  1  0 85
>    0   515  0.00   0  0.00  32.00   1  0.03   0.00   0  0.00  13  0  1  0 85

> iostat (fast case):
> 
>        tty           mfid0            mfid1              cd0             cpu
>  tin  tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in id

>    0   542  0.00   0  0.00  32.00 300  9.37   0.00   0  0.00  14  0  6  1 79
>    0   540 32.00  10  0.31  32.00 318  9.93   0.00   0  0.00  14  0  5  1 79
>    0   688  0.00   0  0.00  32.00 331 10.33   0.00   0  0.00  14  0  5  1 79
>    0  1863  8.00   2  0.02  32.00 300  9.37   0.00   0  0.00  14  0  6  1 79
>    0   720  0.00   0  0.00  32.00 306  9.55   0.00   0  0.00  14  0  5  1 80
>    0   565  0.00   0  0.00  32.00 299  9.33   0.00   0  0.00  14  0  6  0 79
>    0  1470  0.00   0  0.00  116.51 3413 388.27   0.00   0  0.00  10  0  5  1 83
>    0   553  0.00   0  0.00  32.00 303  9.46   0.00   0  0.00  13  0  7  1 79
>    0  1730  0.00   0  0.00  32.00 337 10.52   0.00   0  0.00  14  0  6  0 80
>    0   649  0.00   0  0.00  32.00 342 10.68   0.00   0  0.00  14  0  6  1 79
>    0   901  0.00   0  0.00  32.00 317  9.90   0.00   0  0.00  14  0  6  1 80
>    0  1654  0.00   0  0.00  32.00 328 10.24   0.00   0  0.00  15  0  5  1 79


I don't know if you've noticed, but:

1) In your slow case, the load is almost entirely in userspace (USR),
i.e. mysql, while in the fast case, the kernel load (SYS) is 5x-6x
greater. Unless the first case actually excercises your disks more
(unlikely since you have almost no IO load at all) it means that it's
mysql that is slow, not the kernel.

2) Ordinary iostat doesn't show the disk busy-time estimation. You need
to use "-x" (e.g. "iostat -x 1") to see the service time and busy
percentage stats.

3) you seem to have bursts of traffic, probably writing, which may be
caused by the file system IFF mysql is not doing anything to fsync the
data sooner (i.e. you have everything in a single transaction), but the
burst in the "fast" case is 388 MB while the burst in the "slow" case is
100 MB

4) your benchmark seems to be single-threaded

5) You have a lot of 32 KB transactions, which probably means you have
created the file system in 9.x with the default options, leading to 32
KB blocks. This is probably not the source of your current problems, but
in the future you might want to match the block size to innodb's block
size (16 KiB). You should also do that on the RAID array level and match
the block alignments.

6) Are you using innodb_file_per_table? You probably should.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 260 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freebsd.org/pipermail/freebsd-hackers/attachments/20130305/56f1aaa8/attachment.sig>