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

[Nick Evans]

On Tue, 05 Mar 2013 18:12:19 +0100
Ivan Voras <ivoras at freebsd.org> wrote:

> 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.
> 

1) I had not noticed that. Thanks.

2) Here's iostat -x 1 output for both cases.

slow: http://pleiades.nextvenue.com/mysql/iostat_x_slow.txt

fast: http://pleiades.nextvenue.com/mysql/iostat_x_fast.txt

3) The load generators are constant, so I'm guessing this is file
system related, but I can't see into MySQL very much to tell for sure.
Would the ktraces show mysql doing fsync? These tables are all MyISAM
so no transactions as such, and each super-smack agent is running
insert queries individually.

4) Technically 2 threads. I am only running a single super-smack on
each load server of which there are two. I tried to keep the test as
simple as possible to start.

5) I'll trash the array and change the stripe to 16k as well as the
block size and see if it helps.

6) As I said before, these are MyISAM tables, so they are already
separate table files. Unfortunately, MySQL doesn't seem to have a
reserve x space per table for MyISAM tables. I can manually do this for
my high access tables by inserting bogus rows and then deleting them
(which I may do) as a temporary fix.