mysql scaling questions

[Ivan Voras]

Kris Kennaway wrote:
> Gergely CZUCZY wrote:

>>> It looks like myisam is doing huge numbers of concurrent reads of the
>>> same file which is running into exclusive locking in the kernel
>>> (vnode interlock and lockbuilder mtxpool).  Does it not do any
>>> caching of the data in userspace but relies on querying into the
>>> kernel every time? innodb doesn't have this behaviour.
>> Sorry, but was this a rethorical kind of question, or was this
>> addressed to me? :)
>> If the later, then how do I find this out?
> 
> 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
don't think the applications should be blamed for relying on performance
optimizations not present in FreeBSD. Saying applications must do their
own caching instead of relying on the kernel and need to avoid
concurrent accesses to the same file seems like a doctrine from the dark
ages.

Does anyone have a theory why syscalls are so expensive in FreeBSD? Here
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):

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


Both runs are from the same sources, and the compiler is the same on
both systems (4.2.1). I think the only possibly significant difference
is that the Linux machine runs a NOHZ kernel (it's a laptop).

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.

I don't think this is a matter of Intel vs AMD, since benchmarks on
comparable Xeons 51xx give similar results on FreeBSD.


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 250 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20071231/24092f92/signature.pgp