mysql scaling questions

[Bruce Evans]

On Mon, 31 Dec 2007, Ivan Voras wrote:

> Kris Kennaway wrote:
>> It's a general question.  It looks like myisam either has a design
>> deficiency in this regard or it has poor defaults.  If it can be made to
>> improve caching of the data in userland then performance should improve.
>
> Isn't this common for software developed for Linux? I mean assuming
> syscalls are cheap; for example: gettimeofday(2), settitle(2), etc. I
> ...
> Does anyone have a theory why syscalls are so expensive in FreeBSD? Here

FreeBSD has more layers, with less optimization in each layer.  Normally
this doesn't matter, since everyone knows that syscalls are expensive
and avoids them :-).

> are the results of unixbench 4.1 on two machines. First is the machine
> running FreeBSD HEAD (debugging disabled) on a dual-core Athlon 64 (i386
> mode), 2 GHz:
>
>                     INDEX VALUES
> TEST                                        BASELINE     RESULT      INDEX
>
> Dhrystone 2 using register variables        116700.0  6467105.1      554.2
> Double-Precision Whetstone                      55.0     1633.7      297.0
> Execl Throughput                                43.0     2030.9      472.3
> File Copy 1024 bufsize 2000 maxblocks         3960.0    63783.0      161.1
> File Copy 256 bufsize 500 maxblocks           1655.0    57489.0      347.4
> File Copy 4096 bufsize 8000 maxblocks         5800.0    53476.0       92.2
> Pipe Throughput                              12440.0   930715.9      748.2
> Pipe-based Context Switching                  4000.0   204248.8      510.6
> Process Creation                               126.0     5373.3      426.5
> Shell Scripts (8 concurrent)                     6.0      563.7      939.5
> System Call Overhead                         15000.0   720641.0      480.4
>                                                                 =========
>     FINAL SCORE                                                     387.4
>
> The second result is from a machine running Linux 2.6.22, dual-core Core
> 2 Duo, 1.7 GHz, T2250, i386 mode (i.e. slower than the first machine):

Isn't Core2 significantly faster than A64 at the same clock speed?  Its
larger L2 cache is likely to be especially helpful in benchmarks.

>                     INDEX VALUES
> TEST                                        BASELINE     RESULT      INDEX
>
> Dhrystone 2 using register variables        116700.0  5576224.9      477.8
> Double-Precision Whetstone                      55.0     1521.6      276.7
> Execl Throughput                                43.0     2734.1      635.8
> File Copy 1024 bufsize 2000 maxblocks         3960.0   316794.0      800.0
> File Copy 256 bufsize 500 maxblocks           1655.0    97964.0      591.9
> File Copy 4096 bufsize 8000 maxblocks         5800.0   665840.0     1148.0
> Pipe Throughput                              12440.0   679339.1      546.1

No Pipe-based Context Switching?  That should be included in benchmarks to
show FreeBSD slowness :-), since it is affected by both slow syscalls and
slow context switches.

> Process Creation                               126.0    11562.9      917.7
> Shell Scripts (8 concurrent)                     6.0      316.0      526.7
> System Call Overhead                         15000.0  1134690.6      756.5
>                                                                 =========
>     FINAL SCORE                                                     625.2

> This is almost comparing apples to oranges, but the difference in
> syscall-intensive operations (execl, syscall, process creation) seems
> anomalous, since everything should go against the second machine.

Um, execl and process creation are not syscall-intensive.  They take about
1 syscall each.

Linux wins this benchmark by a lot mainly because too much weight is given
to the file copy benchmarks, using a number of files that just happens to
cause more physical writes under FreeBSD than under Linux, mainly due to
FreeBSD's policy of not letting the buffer cache/VMIO fill up with dirty
buffers/pages.  FreeBSD does much better with more files.  FreeBSD does
worse than I like for cases where physical writes aren't made by either
OS.  The source of this slowness seems to be the same as for syscalls but
more so (vfs intentionally has lots of layers in FreeBSD).

Bruce