[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: FreeBSD MySQL still WAY slower than Linux



I moved this thread to -performance as that's entirely what it's about
now and we seem not to care which version of freebsd we'll try out.

Thanks for your comments on this, rather than write you an essay (I
just deleted that) here's the results I've just got from trying this
with vmstat and iostat running at the same time.

They're all running at the same time so it's a pain to read, but it
looks to me like there's only very minimal filesystem activity either
at the end or start of the query run. If that is the slow part then
surely it must be VERY VERY slow for us to get twice the figures on
Linux. (i.e. there's only disk access in the last second, and the rest
of the query takes 4+ seconds to run.)

It could be an issue, it could even be THE issue we have, but I'm not
sure that it looks like it could be responsible for all of it.  Also,
note that the CPU is at 100% usage pretty much all the way through,
and Linux still goes twice as fast ?  There's got to be something else
wrong. Or hopefully I'm just interpreting the figures (below) wrong.

So, still getting about 15k qps.

Anyway, I'll try and get this all configured properly async, but
mounting async,noatime just sets noatime for me (async is noneffective
and doesn't appear in the mount list) and that's how it was mounted
for the test results below.

root_(_at_)_lithium 21 130 # mount
/dev/amrd0s1a on / (ufs, local)
devfs on /dev (devfs, local)
/dev/amrd0s1g on /data (ufs, local, noatime, soft-updates)
/dev/amrd0s1e on /usr (ufs, local, soft-updates)
/dev/amrd0s1f on /var (ufs, local, soft-updates)

Here's even more results then:

vmstat 1 &
iostat 1 &
foreach f (1 2 3 4 5) {/data/supersmack-1.3/bin/super-smack select-key.smack 50 1000|grep select_index}


 procs      memory      page                    disks     faults      cpu
 r b w     avm    fre  flt  re  pi  po  fr  sr am0 pa0   in   sy  cs us sy id
      tty           amrd0            pass0             cpu
 2 1 0 1499072 2699820    6   0   0   0   6   0   0   0  328  494 420  0  0 100
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in id
   0    1 10.16   0  0.00   0.14   0  0.00   0  0  0  0 100
   0  262  0.00   0  0.00   0.00   0  0.00  46  0 51  0  3
 3 1 0 1539380 2692080 2091   0   0   0 166   0   0   0  342 426991 59315 45 52  3
   0  143  0.00   0  0.00   0.00   0  0.00  42  0 57  0  0
 3 1 0 1539380 2692080    0   0   0   0   0   0   0   0  338 461227 64672 43 57  0
   0  143  0.00   0  0.00   0.00   0  0.00  46  0 53  0  1
 4 1 0 1539380 2692080    0   0   0   0   0   0   0   0  335 449944 62900 45 54  1
   0  143  0.00   0  0.00   0.00   0  0.00  43  0 56  0  1
 4 1 0 1539380 2692080    1   0   0   0   0   0   0   0  334 456473 64005 43 56  1
   0  143  0.00   0  0.00   0.00   0  0.00  40  0 57  0  3
 3 1 0 1539380 2692080    0   0   0   0   0   0   0   0  334 456581 63700 40 57  3
   0  143  0.00   0  0.00   0.00   0  0.00  39  0 60  0  1
 3 1 0 1539380 2692080    0   0   0   0   0   0   0   0  335 456716 63878 39 60  1
   0  143  0.00   0  0.00   0.00   0  0.00  42  0 56  0  2
 3 1 0 1530740 2693724  166   0   0   0 469   0   0   0  334 454858 63687 41 57  2
select_index    100000  27      1       14175.41
   0  177  0.00   0  0.00   0.00   0  0.00  47  0 50  0  3
 3 1 0 1539380 2692080 2754   0   0   0 2457   0   0   0  347 470893 43658 47 50  3
   0  144  0.00   0  0.00   0.00   0  0.00  44  0 53  0  3
 4 1 0 1539380 2692080    0   0   0   0   0   0   0   0  336 451858 62920 44 53  3
   0  143  0.00   0  0.00   0.00   0  0.00  45  0 53  0  1
 3 1 0 1539380 2692080    1   0   0   0   0   0   0   0  335 457440 63747 45 54  1
   0  143  0.00   0  0.00   0.00   0  0.00  45  0 53  0  2
 3 1 0 1539380 2692080    0   0   0   0   0   0   0   0  334 457705 63865 45 53  2
   0  143  0.00   0  0.00   0.00   0  0.00  47  0 52  0  2
 4 1 0 1539380 2692080    0   0   0   0   0   0   0   0  335 457634 63799 47 51  2
   0  143  0.00   0  0.00   0.00   0  0.00  51  0 47  0  2
 3 1 0 1539380 2692080    0   0   0   0   0   0   0   0  338 462670 64419 51 48  2
select_index    100000  10      3       14441.46
   0  177 16.00   5  0.08   0.00   0  0.00  42  0 55  0  3
51 1 0 1537044 2692708 2758   0   0   0 2926   0   5   0  341 415206 57104 42 55  3
   0  144  0.00   0  0.00   0.00   0  0.00  53  0 46  0  1
50 1 0 1539380 2692064  162   0   0   0   0   0   0   0  335 564638 33688 54 46  0
   0  143  0.00   0  0.00   0.00   0  0.00  55  0 45  0  0
50 1 0 1539380 2692064    0   0   0   0   0   0   0   0  335 565593 33753 55 45  0
   0  143  0.00   0  0.00   0.00   0  0.00  43  0 56  0  2
 3 1 0 1539380 2692064    0   0   0   0   0   0   0   0  337 498784 51893 42 56  2
   0  143  0.00   0  0.00   0.00   0  0.00  42  0 56  0  2
 3 1 0 1539380 2692064    0   0   0   0   0   0   0   0  334 457060 63578 42 56  2
   0  143  0.00   0  0.00   0.00   0  0.00  45  0 54  0  0
 3 1 0 1539380 2692064    0   0   0   0   0   0   0   0  336 457381 63928 46 53  0
      tty           amrd0            pass0             cpu
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in id
   0  142  0.00   0  0.00   0.00   0  0.00  44  0 55  0  2
 4 1 0 1539380 2692064    0   0   0   0   0   0   0   0  338 456654 63909 41 57  2
select_index    100000  10      0       14852.39
   0  296  0.00   0  0.00   0.00   0  0.00  41  0 55  0  5
50 1 0 1539380 2692080 2920   0   0   0 2930   0   0   0  337 444283 48341 42 54  4
   0  144  0.00   0  0.00   0.00   0  0.00  52  0 48  0  0
50 1 0 1539380 2692080    0   0   0   0   0   0   0   0  336 565342 33734 52 48  0
   0  143  0.00   0  0.00   0.00   0  0.00  50  0 50  0  0
50 1 0 1539380 2692080    0   0   0   0   0   0   0   0  335 564739 33736 50 50  0
   0  143  0.00   0  0.00   0.00   0  0.00  50  0 50  0  0
50 1 0 1539380 2692080    0   0   0   0   0   0   0   0  334 565227 33760 50 50  0
   0  143  0.00   0  0.00   0.00   0  0.00  59  0 41  0  0
50 1 0 1539380 2692080    0   0   0   0   0   0   0   0  334 564694 33663 61 39  0
   0  143  0.00   0  0.00   0.00   0  0.00  52  0 48  0  0
50 1 0 1539380 2692080    0   0   0   0   0   0   0   0  338 570659 34101 51 49  0
   0  143  0.00   0  0.00   0.00   0  0.00  51  0 49  0  0
select_index    100000  6       0       15800.56
40 1 0 1518496 2696768 1694   0   0   0 2916   0   0   0  339 524302 31026 49 48  3
   0  177  0.00   0  0.00   0.00   0  0.00  46  0 51  0  3
51 1 0 1539380 2692064 1226   0   0   0  10   0   0   0  336 556488 33533 47 53  0
   0  143  0.00   0  0.00   0.00   0  0.00  50  0 50  0  0
50 1 0 1539380 2692064    0   0   0   0   0   0   0   0  335 566623 33760 50 50  0
   0  143  0.00   0  0.00   0.00   0  0.00  52  0 48  0  0
50 1 0 1539380 2692064    0   0   0   0   0   0   0   0  334 569522 33964 52 48  0
   0  143  0.00   0  0.00   0.00   0  0.00  50  0 50  0  0
50 1 0 1539380 2692064    0   0   0   0   0   0   0   0  336 568597 33894 51 49  0
   0  143 16.00   2  0.03   0.00   0  0.00  49  0 51  0  0
50 1 0 1539380 2692064    0   0   0   0   0   0   2   0  336 567234 33929 48 52  0
   0  143  0.00   0  0.00   0.00   0  0.00  50  0 50  0  0
19 1 0 1517060 2696428  528   0   0   0 1619   0   0   0  334 570948 34320 49 51  0
select_index    100000  3       0       15928.08
root_(_at_)_lithium 20 0 #    0  226  0.00   0  0.00   0.00   0  0.00  13  0 17  0 71
 0 0 0 1498948 2699796  371   0   0   0 1173   0   0   0  339 114112 7520  9 13 78
   0  143  0.00   0  0.00   0.00   0  0.00   0  0  0  0 100

Ta,
        Steve Roome
_______________________________________________
freebsd-performance_(_at_)_freebsd_(_dot_)_org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe_(_at_)_freebsd_(_dot_)_org"