FreeBSD 6.0 MySQL Performance

I use databases/mysql50-{client,server} and use benchmarks/super-smack to test. There are 3*2*2*2 = 24 cases:

  • Compile options: none, WITH_PROC_SCOPE_PTH=yes, WITH_LINUXTHREADS=yes
  • /etc/libmap.conf: none (libpthread), libthr
  • kern.timecounter.choice: ACPI-fast, TSC
  • kernel: ULE+PREEMPTION, ULE

These benchmarking were tested on my laptop (IBM x31 2672-IQV, Pentium-M 1.5G with 512MB RAM), and powerd was disabled. Detail informations (dmesg, sysctl, and kernel config file) will post later.

The commands are:

for i in 1 2 3 4 5; do super-smack select-key.smack 10 1000 | grep select_index; done
for i in 1 2 3 4 5; do super-smack update-select.smack 10 1000 | grep select_index; done

mysql-linuxthreads-libpthread-acpifast-ule+pre.txt

select_index    20000   0       0       14097.47
select_index    20000   0       0       13741.43
select_index    20000   1       0       13704.01
select_index    20000   0       0       13626.05
select_index    20000   0       0       13769.32
select_index    10000   2       0       1891.63
select_index    10000   2       0       1758.65
select_index    10000   2       0       1836.00
select_index    10000   4       0       2058.71
select_index    10000   14      0       2050.05

mysql-linuxthreads-libpthread-acpifast-ule.txt

select_index    20000   1       0       12478.07
select_index    20000   0       0       13340.34
select_index    20000   1       0       13402.94
select_index    20000   0       0       13427.98
select_index    20000   0       0       13516.95
select_index    10000   2       0       3005.60
select_index    10000   2       0       3286.45
select_index    10000   2       0       3289.58
select_index    10000   3       0       3300.75
select_index    10000   3       0       3270.95

mysql-linuxthreads-libpthread-tsc-ule+pre.txt

select_index    20000   1       0       13638.09
select_index    20000   1       0       15204.89
select_index    20000   0       0       15126.16
select_index    20000   1       0       15199.22
select_index    20000   1       0       15111.09
select_index    10000   2       0       2079.97
select_index    10000   2       0       1914.26
select_index    10000   2       0       2154.83
select_index    10000   2       0       2089.02
select_index    10000   3       0       2151.76

mysql-linuxthreads-libpthread-tsc-ule.txt

select_index    20000   1       0       13799.12
select_index    20000   0       0       15007.75
select_index    20000   1       0       15016.11
select_index    20000   0       0       15021.71
select_index    20000   1       0       15034.78
select_index    10000   1       0       3561.01
select_index    10000   1       0       3554.91
select_index    10000   1       0       3554.55
select_index    10000   1       0       3563.48
select_index    10000   2       0       3521.84

mysql-linuxthreads-libthr-acpifast-ule+pre.txt

select_index    20000   0       0       12332.69
select_index    20000   0       0       13531.45
select_index    20000   1       0       13578.15
select_index    20000   0       0       13571.15
select_index    20000   1       0       13625.20
select_index    10000   2       0       1571.96
select_index    10000   2       0       2105.13
select_index    10000   3       0       2080.75
select_index    10000   3       0       2041.60
select_index    10000   3       0       2075.76

mysql-linuxthreads-libthr-acpifast-ule.txt

select_index    20000   1       0       12475.24
select_index    20000   1       0       13450.59
select_index    20000   1       0       13417.97
select_index    20000   1       0       13412.57
select_index    20000   2       0       13383.80
select_index    10000   2       0       3332.96
select_index    10000   2       0       3320.67
select_index    10000   1       0       3038.21
select_index    10000   2       0       3313.98
select_index    10000   1       0       3306.03

mysql-linuxthreads-libthr-tsc-ule+pre.txt

select_index    20000   0       0       13663.00
select_index    20000   0       0       15213.64
select_index    20000   1       0       15042.29
select_index    20000   1       0       15174.39
select_index    20000   1       0       15243.04
select_index    10000   32      0       1774.45
select_index    10000   2       0       2226.37
select_index    10000   1       0       2190.76
select_index    10000   2       0       1863.09
select_index    10000   3       0       2109.35

mysql-linuxthreads-libthr-tsc-ule.txt

select_index    20000   0       0       13818.04
select_index    20000   1       0       15044.14
select_index    20000   1       0       14952.98
select_index    20000   1       0       14997.97
select_index    20000   1       0       14994.28
select_index    10000   1       0       3559.50
select_index    10000   2       0       3555.00
select_index    10000   1       0       3551.69
select_index    10000   1       0       3544.28
select_index    10000   1       0       3558.09

mysql-none-libpthread-acpifast-ule+pre.txt

select_index    20000   1       0       12365.81
select_index    20000   0       0       13426.82
select_index    20000   0       0       13431.42
select_index    20000   1       0       13524.88
select_index    20000   1       0       13461.60
select_index    10000   3       0       2174.06
select_index    10000   1       0       2203.14
select_index    10000   6       0       1965.83
select_index    10000   2       0       1750.57
select_index    10000   2       0       2250.18

mysql-none-libpthread-acpifast-ule.txt

select_index    20000   1       0       12489.77
select_index    20000   1       0       13491.79
select_index    20000   0       0       13525.43
select_index    20000   0       0       13523.64
select_index    20000   1       0       13431.96
select_index    10000   2       0       3081.10
select_index    10000   1       0       3080.20
select_index    10000   3       0       3090.05
select_index    10000   1       0       3098.17
select_index    10000   2       0       3084.23

mysql-none-libpthread-tsc-ule+pre.txt

select_index    20000   1       0       13352.18
select_index    20000   1       0       14704.85
select_index    20000   1       0       14608.26
select_index    20000   1       0       14798.53
select_index    20000   0       0       14689.70
select_index    10000   0       0       2142.89
select_index    10000   1       0       2234.18
select_index    10000   1       0       2179.86
select_index    10000   2       0       2170.27
select_index    10000   1       0       2265.08

mysql-none-libpthread-tsc-ule.txt

select_index    20000   1       0       13656.62
select_index    20000   1       0       14898.67
select_index    20000   1       0       14752.02
select_index    20000   2       0       14852.14
select_index    20000   1       0       14837.82
select_index    10000   1       0       3007.47
select_index    10000   2       0       3229.50
select_index    10000   2       0       3224.31
select_index    10000   1       0       3231.67
select_index    10000   3       0       3227.73

mysql-none-libthr-acpifast-ule+pre.txt

select_index    20000   1       0       13554.22
select_index    20000   0       0       14843.70
select_index    20000   1       0       14866.88
select_index    20000   0       0       14887.39
select_index    20000   1       0       14928.93
select_index    10000   1       0       1629.05
select_index    10000   2       0       2236.91
select_index    10000   15      0       2309.37
select_index    10000   2       0       2165.72
select_index    10000   27      0       2159.57

mysql-none-libthr-acpifast-ule.txt

select_index    20000   1       0       13658.64
select_index    20000   1       0       14971.77
select_index    20000   1       0       14956.40
select_index    20000   0       0       14981.85
select_index    20000   1       0       14999.37
select_index    10000   2       0       3457.10
select_index    10000   2       0       3471.44
select_index    10000   1       0       3461.68
select_index    10000   1       0       3467.51
select_index    10000   1       0       3464.31

mysql-none-libthr-tsc-ule+pre.txt

select_index    20000   2       0       14777.00
select_index    20000   0       0       16435.89
select_index    20000   1       0       16594.25
select_index    20000   1       0       16396.86
select_index    20000   1       0       16459.05
select_index    10000   1       0       2231.66
select_index    10000   1       0       2167.76
select_index    10000   1       0       2196.55
select_index    10000   1       0       2206.77
select_index    10000   2       0       2339.63

mysql-none-libthr-tsc-ule.txt

select_index    20000   0       0       15248.38
select_index    20000   1       0       16618.56
select_index    20000   1       0       16648.67
select_index    20000   0       0       16538.45
select_index    20000   1       0       16674.13
select_index    10000   1       0       3666.43
select_index    10000   3       0       3674.07
select_index    10000   2       0       3686.61
select_index    10000   1       0       3677.49
select_index    10000   2       0       3675.10

mysql-pth-libpthread-acpifast-ule+pre.txt

select_index    20000   0       0       9851.83
select_index    20000   1       0       10051.59
select_index    20000   1       0       10078.66
select_index    20000   1       0       10004.09
select_index    20000   1       0       10079.65
select_index    10000   1       0       1666.05
select_index    10000   2       0       1984.58
select_index    10000   2       0       1898.87
select_index    10000   2       0       1966.90
select_index    10000   4       1       1874.67

mysql-pth-libpthread-acpifast-ule.txt

select_index    20000   1       0       9485.41
select_index    20000   1       0       10073.52
select_index    20000   1       0       10113.77
select_index    20000   1       0       10104.63
select_index    20000   2       0       10107.14
select_index    10000   2       0       2568.85
select_index    10000   1       0       2579.42
select_index    10000   2       0       2564.46
select_index    10000   1       0       2561.09
select_index    10000   1       0       2443.52

mysql-pth-libpthread-tsc-ule+pre.txt

select_index    20000   0       0       11129.51
select_index    20000   1       0       12125.62
select_index    20000   1       0       12108.58
select_index    20000   1       0       12148.99
select_index    20000   1       0       12142.83
select_index    10000   1       0       1769.50
select_index    10000   2       0       1961.15
select_index    10000   3       0       2132.04
select_index    10000   2       0       2175.26
select_index    10000   1       0       1964.11

mysql-pth-libpthread-tsc-ule.txt

select_index    20000   0       0       11282.64
select_index    20000   1       0       12221.35
select_index    20000   1       0       12176.64
select_index    20000   0       0       12207.59
select_index    20000   1       0       12196.39
select_index    10000   2       0       2850.48
select_index    10000   2       0       2847.16
select_index    10000   3       1       2839.71
select_index    10000   2       0       2846.03
select_index    10000   2       0       2854.38

mysql-pth-libthr-acpifast-ule+pre.txt

select_index    20000   1       0       13539.93
select_index    20000   1       0       14858.06
select_index    20000   1       0       15031.06
select_index    20000   0       0       14944.30
select_index    20000   1       0       14939.87
select_index    10000   1       0       1803.10
select_index    10000   1       0       2196.92
select_index    10000   1       0       2315.69
select_index    10000   1       0       2304.67
select_index    10000   6       0       2309.06

mysql-pth-libthr-acpifast-ule.txt

select_index    20000   1       0       13754.94
select_index    20000   1       0       14948.38
select_index    20000   1       0       15003.24
select_index    20000   1       0       14971.17
select_index    20000   0       0       14886.27
select_index    10000   1       0       3420.30
select_index    10000   1       0       3270.71
select_index    10000   2       0       3416.76
select_index    10000   2       0       3443.20
select_index    10000   2       0       3423.75

mysql-pth-libthr-tsc-ule+pre.txt

select_index    20000   0       0       14755.44
select_index    20000   0       0       16653.00
select_index    20000   1       0       16646.08
select_index    20000   1       0       16471.75
select_index    20000   1       0       16591.78
select_index    10000   1       0       2181.36
select_index    10000   1       0       2195.38
select_index    10000   1       0       2153.20
select_index    10000   1       0       2006.63
select_index    10000   2       0       2444.98

mysql-pth-libthr-tsc-ule.txt

select_index    20000   0       0       15189.03
select_index    20000   0       0       16818.74
select_index    20000   0       0       16624.18
select_index    20000   1       0       16792.51
select_index    20000   1       0       16731.49
select_index    10000   1       0       3652.88
select_index    10000   1       0       3685.77
select_index    10000   2       0       3670.86
select_index    10000   1       0       3665.19
select_index    10000   1       0       2843.89

my.cnf:

[mysqld]
skip-innodb
skip-bdb
safe-user-create
skip-name-resolve
skip-log-warnings
safe-show-database


set-variable = flush_time=86400
set-variable = max_connections=300
set-variable = max_connect_errors=100
set-variable = interactive_timeout=600
set-variable = wait_timeout=60
set-variable = myisam-recover=FORCE
set-variable = key_buffer_size=128M
set-variable = sort_buffer_size=1M
set-variable = read_buffer_size=512k
set-variable = table_cache=4096
set-variable = max_delayed_threads=0
set-variable = max_user_connections=100
set-variable = query_cache_size=128M
set-variable = thread_cache_size=50

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysql/error.log

dmesg:

Copyright (c) 1992-2005 The FreeBSD Project.
Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
        The Regents of the University of California. All rights reserved.
FreeBSD 6.0-STABLE #7: Mon Dec 12 11:59:54 CST 2005
    root@gslinnb.csie.NCTU.edu.tw:/usr/obj/usr/src/sys/KERNEL
Timecounter "i8254" frequency 1193182 Hz quality 0
CPU: Intel(R) Pentium(R) M processor 1500MHz (1498.73-MHz 686-class CPU)
  Origin = "GenuineIntel"  Id = 0x695  Stepping = 5
  Features=0xa7e9f9bf<FPU,VME,DE,PSE,TSC,MSR,MCE,CX8,SEP,MTRR,PGE,MCA,CMOV,PAT,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,TM,PBE>
  Features2=0x180<EST,TM2>
real memory  = 536215552 (511 MB)
avail memory = 515358720 (491 MB)
npx0: [FAST]
npx0: <math processor> on motherboard
npx0: INT 16 interface
acpi0: <IBM TP-1Q> on motherboard
acpi_ec0: <Embedded Controller: GPE 0x1c, ECDT> port 0x62,0x66 on acpi0
acpi0: Power Button (fixed)
Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000
acpi_timer0: <24-bit timer at 3.579545MHz> port 0x1008-0x100b on acpi0
cpu0: <ACPI CPU> on acpi0
acpi_perf0: <ACPI CPU Frequency Control> on cpu0
acpi_throttle0: <ACPI CPU Throttling> on cpu0
acpi_lid0: <Control Method Lid Switch> on acpi0
acpi_button0: <Sleep Button> on acpi0
pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0
pci0: <ACPI PCI bus> on pcib0
agp0: <Intel 82855 host to AGP bridge> mem 0xd0000000-0xdfffffff at device 0.0 on pci0
pcib1: <ACPI PCI-PCI bridge> at device 1.0 on pci0
pci1: <ACPI PCI bus> on pcib1
acpi_video0: <ACPI video extension> port 0x3000-0x30ff mem 0xe0000000-0xe7ffffff,0xc0100000-0xc010ffff irq 11 at device 0.0 on pci1
uhci0: <Intel 82801DB (ICH4) USB controller USB-A> port 0x1800-0x181f irq 11 at device 29.0 on pci0
uhci0: [GIANT-LOCKED]
usb0: <Intel 82801DB (ICH4) USB controller USB-A> on uhci0
usb0: USB revision 1.0
uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
uhub0: 2 ports with 2 removable, self powered
uhci1: <Intel 82801DB (ICH4) USB controller USB-B> port 0x1820-0x183f irq 11 at device 29.1 on pci0
uhci1: [GIANT-LOCKED]
usb1: <Intel 82801DB (ICH4) USB controller USB-B> on uhci1
usb1: USB revision 1.0
uhub1: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
uhub1: 2 ports with 2 removable, self powered
uhci2: <Intel 82801DB (ICH4) USB controller USB-C> port 0x1840-0x185f irq 11 at device 29.2 on pci0
uhci2: [GIANT-LOCKED]
usb2: <Intel 82801DB (ICH4) USB controller USB-C> on uhci2
usb2: USB revision 1.0
uhub2: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
uhub2: 2 ports with 2 removable, self powered
ehci0: <Intel 82801DB/DBL/DBM (ICH4) USB 2.0 controller> mem 0xc0000000-0xc00003ff irq 11 at device 29.7 on pci0
ehci0: [GIANT-LOCKED]
usb3: EHCI version 1.0
usb3: companion controllers, 2 ports each: usb0 usb1 usb2
usb3: <Intel 82801DB/DBL/DBM (ICH4) USB 2.0 controller> on ehci0
usb3: USB revision 2.0
uhub3: Intel EHCI root hub, class 9/0, rev 2.00/1.00, addr 1
uhub3: 6 ports with 6 removable, self powered
pcib2: <ACPI PCI-PCI bridge> at device 30.0 on pci0
pci2: <ACPI PCI bus> on pcib2
pci2: <bridge, PCI-CardBus> at device 0.0 (no driver attached)
pci2: <bridge, PCI-CardBus> at device 0.1 (no driver attached)
pci2: <serial bus, FireWire> at device 0.2 (no driver attached)
ipw0: <Intel(R) PRO/Wireless 2100 MiniPCI> mem 0xc0200000-0xc0200fff irq 11 at device 2.0 on pci2
ipw0: Ethernet address: 00:0c:f1:32:b2:92
fxp0: <Intel 82801DB (ICH4) Pro/100 VE Ethernet> port 0x8000-0x803f mem 0xc0201000-0xc0201fff irq 11 at device 8.0 on pci2
miibus0: <MII bus> on fxp0
inphy0: <i82562ET 10/100 media interface> on miibus0
inphy0:  10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto
fxp0: Ethernet address: 00:0d:60:8f:05:50
isab0: <PCI-ISA bridge> at device 31.0 on pci0
isa0: <ISA bus> on isab0
atapci0: <Intel ICH4 UDMA100 controller> port 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0x1860-0x186f at device 31.1 on pci0
ata0: <ATA channel 0> on atapci0
ata1: <ATA channel 1> on atapci0
pci0: <serial bus, SMBus> at device 31.3 (no driver attached)
pcm0: <Intel ICH4 (82801DB)> port 0x1c00-0x1cff,0x18c0-0x18ff mem 0xc0000c00-0xc0000dff,0xc0000800-0xc00008ff irq 11 at device 31.5 on pci0
pcm0: [GIANT-LOCKED]
pcm0: <Analog Devices AD1981B AC97 Codec>
pci0: <simple comms, generic modem> at device 31.6 (no driver attached)
acpi_tz0: <Thermal Zone> on acpi0
atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0
atkbd0: <AT Keyboard> irq 1 on atkbdc0
kbd0 at atkbd0
atkbd0: [GIANT-LOCKED]
psm0: <PS/2 Mouse> irq 12 on atkbdc0
psm0: [GIANT-LOCKED]
psm0: model Generic PS/2 mouse, device ID 0
sio0: configured irq 4 not in bitmap of probed irqs 0
sio0: port may not be enabled
sio0: configured irq 3 not in bitmap of probed irqs 0
sio0: port may not be enabled
battery0: <ACPI Control Method Battery> on acpi0
acpi_acad0: <AC Adapter> on acpi0
acpi_ibm0: <IBM ThinkPad ACPI Extras> on acpi0
sio0: configured irq 4 not in bitmap of probed irqs 0
sio0: port may not be enabled
sio0: configured irq 3 not in bitmap of probed irqs 0
sio0: port may not be enabled
pmtimer0 on isa0
orm0: <ISA Option ROMs> at iomem 0xd0000-0xd0fff,0xd1000-0xd1fff,0xdc000-0xdffff on isa0
sc0: <System console> at flags 0x100 on isa0
sc0: VGA <16 virtual consoles, flags=0x300>
vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0
sio0: configured irq 4 not in bitmap of probed irqs 0
sio0: port may not be enabled
sio1: configured irq 3 not in bitmap of probed irqs 0
sio1: port may not be enabled
ums0: Logitech Optical USB Mouse, rev 2.00/3.40, addr 2, iclass 3/1
ums0: 3 buttons and Z dir.
Timecounter "TSC" frequency 1498732909 Hz quality 800
Timecounters tick every 1.000 msec
ipfw2 (+ipv6) initialized, divert loadable, rule-based forwarding enabled, default to accept, logging disabled
ad0: 34791MB <IC25N040ATMR04 0 MO2OAD4A> at ata0-master UDMA100
Trying to mount root from ufs:/dev/ad0s1a
ipw0: Please load firmware
ipw0: link state changed to UP

The kernel config file (diff -ruN with /sys/i386/conf/GENERIC)

--- /sys/i386/conf/GENERIC      Sun Oct 30 03:08:44 2005
+++ KERNEL      Mon Dec 12 14:15:41 2005
@@ -16,40 +16,28 @@
 # If you are in doubt as to the purpose or necessity of a line, check first
 # in NOTES.
 #
-# $FreeBSD: src/sys/i386/conf/GENERIC,v 1.429.2.3.2.1 2005/10/28 19:22:41 jhb Exp $
+# $FreeBSD: src/sys/i386/conf/GENERIC,v 1.429.2.4 2005/10/28 19:21:27 jhb Exp $

 machine                i386
-cpu            I486_CPU
-cpu            I586_CPU
 cpu            I686_CPU
 ident          GENERIC

 # To statically compile in device wiring instead of /boot/device.hints
 #hints         "GENERIC.hints"         # Default places to look for devices.

-makeoptions    DEBUG=-g                # Build kernel with gdb(1) debug symbols
+#makeoptions   DEBUG=-g                # Build kernel with gdb(1) debug symbols

-#options       SCHED_ULE               # ULE scheduler
-options        SCHED_4BSD              # 4BSD scheduler
-options        PREEMPTION              # Enable kernel thread preemption
+options        SCHED_ULE               # ULE scheduler
+#options       SCHED_4BSD              # 4BSD scheduler
+#options       PREEMPTION              # Enable kernel thread preemption
 options        INET                    # InterNETworking
 options        INET6                   # IPv6 communications protocols
 options        FFS                     # Berkeley Fast Filesystem
 options        SOFTUPDATES             # Enable FFS soft updates support
 options        UFS_ACL                 # Support for access control lists
 options        UFS_DIRHASH             # Improve performance on big directories
-options        MD_ROOT                 # MD is a potential root device
-options        NFSCLIENT               # Network Filesystem Client
-options        NFSSERVER               # Network Filesystem Server
-options        NFS_ROOT                # NFS usable as /, requires NFSCLIENT
-options        MSDOSFS                 # MSDOS Filesystem
-options        CD9660                  # ISO 9660 Filesystem
-options        PROCFS                  # Process filesystem (requires PSEUDOFS)
-options        PSEUDOFS                # Pseudo-filesystem framework
 options        GEOM_GPT                # GUID Partition Tables.
 options        COMPAT_43               # Compatible with BSD 4.3 [KEEP THIS!]
-options        COMPAT_FREEBSD4         # Compatible with FreeBSD4
-options        COMPAT_FREEBSD5         # Compatible with FreeBSD5
 options        SCSI_DELAY=5000         # Delay (in ms) before probing SCSI
 options        KTRACE                  # ktrace(1) support
 options        SYSVSHM                 # SYSV-style shared memory
@@ -57,80 +45,32 @@
 options        SYSVSEM                 # SYSV-style semaphores
 options        _KPOSIX_PRIORITY_SCHEDULING # POSIX P1003_1B real-time extensions
 options        KBD_INSTALL_CDEV        # install a CDEV entry in /dev
-options        AHC_REG_PRETTY_PRINT    # Print register bitfields in debug
-                                       # output.  Adds ~128k to driver.
-options        AHD_REG_PRETTY_PRINT    # Print register bitfields in debug
-                                       # output.  Adds ~215k to driver.
 options        ADAPTIVE_GIANT          # Giant mutex is adaptive.

+options                AUTO_EOI_1
+options                DDB
+options                DUMMYNET
+options                IPFIREWALL
+options                IPFIREWALL_DEFAULT_TO_ACCEPT
+options                IPFIREWALL_FORWARD
+options                KDB
+options                NMBCLUSTERS=16384
+options                PQ_CACHESIZE=1024
+
 device         apic                    # I/O APIC

 # Bus support.
 device         eisa
 device         pci

-# Floppy drives
-device         fdc
-
 # ATA and ATAPI devices
 device         ata
 device         atadisk         # ATA disk drives
-device         ataraid         # ATA RAID drives
-device         atapicd         # ATAPI CDROM drives
-device         atapifd         # ATAPI floppy drives
-device         atapist         # ATAPI tape drives
 options        ATA_STATIC_ID   # Static device numbering

-# SCSI Controllers
-device         ahb             # EISA AHA1742 family
-device         ahc             # AHA2940 and onboard AIC7xxx devices
-device         ahd             # AHA39320/29320 and onboard AIC79xx devices
-device         amd             # AMD 53C974 (Tekram DC-390(T))
-device         isp             # Qlogic family
-#device        ispfw           # Firmware for QLogic HBAs- normally a module
-device         mpt             # LSI-Logic MPT-Fusion
-#device                ncr             # NCR/Symbios Logic
-device         sym             # NCR/Symbios Logic (newer chipsets + those of `ncr')
-device         trm             # Tekram DC395U/UW/F DC315U adapters
-
-device         adv             # Advansys SCSI adapters
-device         adw             # Advansys wide SCSI adapters
-device         aha             # Adaptec 154x SCSI adapters
-device         aic             # Adaptec 15[012]x SCSI adapters, AIC-6[23]60.
-device         bt              # Buslogic/Mylex MultiMaster SCSI adapters
-
-device         ncv             # NCR 53C500
-device         nsp             # Workbit Ninja SCSI-3
-device         stg             # TMC 18C30/18C50
-
 # SCSI peripherals
 device         scbus           # SCSI bus (required for SCSI)
-device         ch              # SCSI media changers
 device         da              # Direct Access (disks)
-device         sa              # Sequential Access (tape etc)
-device         cd              # CD
-device         pass            # Passthrough device (direct SCSI access)
-device         ses             # SCSI Environmental Services (and SAF-TE)
-
-# RAID controllers interfaced to the SCSI subsystem
-device         amr             # AMI MegaRAID
-device         arcmsr          # Areca SATA II RAID
-device         asr             # DPT SmartRAID V, VI and Adaptec SCSI RAID
-device         ciss            # Compaq Smart RAID 5*
-device         dpt             # DPT Smartcache III, IV - See NOTES for options
-device         hptmv           # Highpoint RocketRAID 182x
-device         iir             # Intel Integrated RAID
-device         ips             # IBM (Adaptec) ServeRAID
-device         mly             # Mylex AcceleRAID/eXtremeRAID
-device         twa             # 3ware 9000 series PATA/SATA RAID
-
-# RAID controllers
-device         aac             # Adaptec FSA RAID
-device         aacp            # SCSI passthrough for aac (requires CAM)
-device         ida             # Compaq Smart RAID
-device         mlx             # Mylex DAC960 family
-device         pst             # Promise Supertrak SX6000
-device         twe             # 3ware ATA RAID

 # atkbdc0 controls both the keyboard and the PS/2 mouse
 device         atkbdc          # AT keyboard controller
@@ -139,8 +79,6 @@

 device         vga             # VGA video card driver

-device         splash          # Splash screen and screen saver support
-
 # syscons is the default console driver, resembling an SCO console
 device         sc

@@ -149,131 +87,25 @@
 #options       XSERVER         # support for X server on a vt console
 #options       FAT_CURSOR      # start with block cursor

-device         agp             # support several AGP chipsets
-
 # Power management support (see NOTES for more options)
 #device                apm
 # Add suspend/resume support for the i8254.
 device         pmtimer

-# PCCARD (PCMCIA) support
-# PCMCIA and cardbus bridge support
-device         cbb             # cardbus (yenta) bridge
-device         pccard          # PC Card (16-bit) bus
-device         cardbus         # CardBus (32-bit) bus
-
-# Serial (COM) ports
-device         sio             # 8250, 16[45]50 based serial ports
-
-# Parallel port
-device         ppc
-device         ppbus           # Parallel port bus (required)
-device         lpt             # Printer
-device         plip            # TCP/IP over parallel
-device         ppi             # Parallel port interface device
-#device                vpo             # Requires scbus and da
-
 # If you've got a "dumb" serial or parallel PCI card that is
 # supported by the puc(4) glue driver, uncomment the following
 # line to enable it (connects to the sio and/or ppc drivers):
 #device                puc

-# PCI Ethernet NICs.
-device         de              # DEC/Intel DC21x4x (``Tulip'')
-device         em              # Intel PRO/1000 adapter Gigabit Ethernet Card
-device         ixgb            # Intel PRO/10GbE Ethernet Card
-device         txp             # 3Com 3cR990 (``Typhoon'')
-device         vx              # 3Com 3c590, 3c595 (``Vortex'')
-
-# PCI Ethernet NICs that use the common MII bus controller code.
-# NOTE: Be sure to keep the 'device miibus' line in order to use these NICs!
-device         miibus          # MII bus support
-device         bfe             # Broadcom BCM440x 10/100 Ethernet
-device         bge             # Broadcom BCM570xx Gigabit Ethernet
-device         dc              # DEC/Intel 21143 and various workalikes
-device         fxp             # Intel EtherExpress PRO/100B (82557, 82558)
-device         lge             # Level 1 LXT1001 gigabit Ethernet
-device         nge             # NatSemi DP83820 gigabit Ethernet
-device         nve             # nVidia nForce MCP on-board Ethernet Networking
-device         pcn             # AMD Am79C97x PCI 10/100(precedence over 'lnc')
-device         re              # RealTek 8139C+/8169/8169S/8110S
-device         rl              # RealTek 8129/8139
-device         sf              # Adaptec AIC-6915 (``Starfire'')
-device         sis             # Silicon Integrated Systems SiS 900/SiS 7016
-device         sk              # SysKonnect SK-984x & SK-982x gigabit Ethernet
-device         ste             # Sundance ST201 (D-Link DFE-550TX)
-device         ti              # Alteon Networks Tigon I/II gigabit Ethernet
-device         tl              # Texas Instruments ThunderLAN
-device         tx              # SMC EtherPower II (83c170 ``EPIC'')
-device         vge             # VIA VT612x gigabit Ethernet
-device         vr              # VIA Rhine, Rhine II
-device         wb              # Winbond W89C840F
-device         xl              # 3Com 3c90x (``Boomerang'', ``Cyclone'')
-
-# ISA Ethernet NICs.  pccard NICs included.
-device         cs              # Crystal Semiconductor CS89x0 NIC
-# 'device ed' requires 'device miibus'
-device         ed              # NE[12]000, SMC Ultra, 3c503, DS8390 cards
-device         ex              # Intel EtherExpress Pro/10 and Pro/10+
-device         ep              # Etherlink III based cards
-device         fe              # Fujitsu MB8696x based cards
-device         ie              # EtherExpress 8/16, 3C507, StarLAN 10 etc.
-device         lnc             # NE2100, NE32-VL Lance Ethernet cards
-device         sn              # SMC's 9000 series of Ethernet chips
-device         xe              # Xircom pccard Ethernet
-
 # ISA devices that use the old ISA shims
 #device                le

-# Wireless NIC cards
-device         wlan            # 802.11 support
-device         an              # Aironet 4500/4800 802.11 wireless NICs.
-device         awi             # BayStack 660 and others
-device         ral             # Ralink Technology RT2500 wireless NICs.
-device         wi              # WaveLAN/Intersil/Symbol 802.11 wireless NICs.
-#device                wl              # Older non 802.11 Wavelan wireless NIC.
-
 # Pseudo devices.
 device         loop            # Network loopback
-device         random          # Entropy device
 device         ether           # Ethernet support
-device         sl              # Kernel SLIP
-device         ppp             # Kernel PPP
-device         tun             # Packet tunnel.
 device         pty             # Pseudo-ttys (telnet etc)
-device         md              # Memory "disks"
-device         gif             # IPv6 and IPv4 tunneling
-device         faith           # IPv6-to-IPv4 relaying (translation)

 # The `bpf' device enables the Berkeley Packet Filter.
 # Be aware of the administrative consequences of enabling this!
 # Note that 'bpf' is required for DHCP.
 device         bpf             # Berkeley packet filter
-
-# USB support
-device         uhci            # UHCI PCI->USB interface
-device         ohci            # OHCI PCI->USB interface
-device         ehci            # EHCI PCI->USB interface (USB 2.0)
-device         usb             # USB Bus (required)
-#device                udbp            # USB Double Bulk Pipe devices
-device         ugen            # Generic
-device         uhid            # "Human Interface Devices"
-device         ukbd            # Keyboard
-device         ulpt            # Printer
-device         umass           # Disks/Mass storage - Requires scbus and da
-device         ums             # Mouse
-device         ural            # Ralink Technology RT2500USB wireless NICs
-device         urio            # Diamond Rio 500 MP3 player
-device         uscanner        # Scanners
-# USB Ethernet, requires miibus
-device         aue             # ADMtek USB Ethernet
-device         axe             # ASIX Electronics USB Ethernet
-device         cdce            # Generic USB over Ethernet
-device         cue             # CATC USB Ethernet
-device         kue             # Kawasaki LSI USB Ethernet
-device         rue             # RealTek RTL8150 USB Ethernet
-
-# FireWire support
-device         firewire        # FireWire bus code
-device         sbp             # SCSI over FireWire (Requires scbus and da)
-device         fwe             # Ethernet over FireWire (non-standard!)
This entry was posted in Computer, Database, FreeBSD, Murmuring, Software. Bookmark the permalink.

11 Responses to FreeBSD 6.0 MySQL Performance

  1. b6s says:

    嗯,我做出的結果似乎引導向「都用預設值就好」的結論 :(
    (目前暫時先比較 ULE)

    mysql41-pth-libpthread-tsc-pre:
    select_index 20000 1 0 4259.48
    select_index 20000 28 0 6022.90
    select_index 20000 1 0 4757.54
    select_index 20000 0 0 5639.19
    select_index 20000 0 0 5547.71
    select_index 10000 6 0 683.33
    update_index 10000 8 0 683.33
    select_index 10000 71 0 673.11
    update_index 10000 83 5 673.11
    select_index 10000 6 0 632.01
    update_index 10000 13 5 632.01
    select_index 10000 7 0 724.07
    update_index 10000 9 4 724.07
    select_index 10000 5 0 701.24
    update_index 10000 13 5 701.24

    mysql41-pth-libpthread-tsc-pre-ule:
    select_index 20000 1 0 2523.96
    select_index 20000 1 0 5227.88
    select_index 20000 8 0 1488.46
    select_index 20000 9 1 2840.13
    select_index 20000 1 0 2601.83
    select_index 10000 9 0 422.81
    update_index 10000 10 1 422.81
    select_index 10000 6 0 557.67
    update_index 10000 7 0 557.67
    select_index 10000 6 0 721.98
    update_index 10000 7 1 721.98
    select_index 10000 6 0 714.68
    update_index 10000 6 0 714.68
    select_index 10000 5 2 697.98
    update_index 10000 7 0 697.98

  2. b6s says:

    在不開 process scope threading 的情況下,核心使用 PRE+ULE 與否的比較。

    mysql41-none-libpthread-tsc-pre-ule:
    select_index 20000 2 0 2260.47
    select_index 20000 3 0 4619.47
    select_index 20000 3 0 4552.68
    select_index 20000 3 0 4578.26
    select_index 20000 3 0 3851.94
    select_index 10000 4 0 714.58
    select_index 10000 7 0 775.50
    select_index 10000 7 0 772.70
    select_index 10000 7 0 707.01
    select_index 10000 6 0 709.90

    mysql41-none-libpthread-tsc-none-none:
    select_index 20000 2 0 4776.43
    select_index 20000 2 0 4905.50
    select_index 20000 2 0 4881.35
    select_index 20000 2 0 4910.65
    select_index 20000 1 0 4894.56
    select_index 10000 5 0 701.28
    select_index 10000 9 0 697.95
    select_index 10000 6 0 725.00
    select_index 10000 10 0 628.51
    select_index 10000 6 0 701.13

  3. b6s says:

    這是一般系統的預設值(吧):
    mysql41-none-libpthread-tsc-pre-none:
    select_index 20000 2 0 2583.29
    select_index 20000 1 0 5007.72
    select_index 20000 1 0 4981.18
    select_index 20000 2 0 4937.11
    select_index 20000 1 0 4987.36
    select_index 10000 4 0 727.68
    select_index 10000 5 0 700.20
    select_index 10000 3 0 675.21
    select_index 10000 5 0 713.38
    select_index 10000 5 0 750.88

    不過有兩件事導致我這些實驗可能要砍掉重練:
    1. 爽皇想測 libthr 時發現了一件我忘了提的重要事項--我用了 BUILD_STATIC=yes ……
    2. 測試時機器的 loading 不夠穩定,使得 super-smack 在同樣組態下多跑幾次也可能出現極大差異。

  4. b6s says:

    重作所得到的排名 (freebsd 6-stable, mysql41, no pth, tsc)

    libthr-4bsd-pre:
    select_index 20000 2 0 5165.58
    select_index 20000 2 0 5096.83
    select_index 20000 1 0 5096.77
    select_index 20000 1 0 5116.92
    select_index 20000 1 0 5046.69
    select_index 10000 4 0 815.11
    select_index 10000 4 0 746.69
    select_index 10000 4 0 764.19
    select_index 10000 2 0 787.74
    select_index 10000 3 0 787.14

    libthr-4bsd:
    select_index 20000 2 0 2507.44
    select_index 20000 1 0 5705.03
    select_index 20000 1 0 5135.80
    select_index 20000 1 0 5179.54
    select_index 20000 2 0 5109.68
    select_index 10000 3 0 723.18
    select_index 10000 5 0 790.10
    select_index 10000 5 0 741.24
    select_index 10000 2 0 805.16
    select_index 10000 2 0 754.84

    libthr-ule-pre:
    select_index 20000 1 0 2281.18
    select_index 20000 2 0 5225.43
    select_index 20000 3 0 4679.19
    select_index 20000 2 0 4694.60
    select_index 20000 2 0 4698.70
    select_index 10000 5 0 775.20
    select_index 10000 5 0 752.01
    select_index 10000 6 1 799.21
    select_index 10000 5 0 705.33
    select_index 10000 7 1 798.59

    libthr-ule:
    select_index 20000 2 0 5598.51
    select_index 20000 2 0 5089.20
    select_index 20000 3 0 5095.78
    select_index 20000 1 0 5071.14
    select_index 20000 1 0 5124.13
    select_index 10000 4 0 819.70
    select_index 10000 8 0 769.60
    select_index 10000 6 0 782.60
    select_index 10000 7 0 825.56
    select_index 10000 7 0 780.80

    libpthread-4bsd-pre:
    select_index 20000 2 0 4843.92
    select_index 20000 2 0 4578.51
    select_index 20000 2 0 4567.29
    select_index 20000 1 0 4563.85
    select_index 20000 2 0 4589.73
    select_index 10000 7 0 688.09
    select_index 10000 6 0 726.53
    select_index 10000 10 0 710.14
    select_index 10000 7 0 737.06
    select_index 10000 4 0 666.36

    libpthread-4bsd:
    select_index 20000 3 0 4893.14
    select_index 20000 4 0 4595.75
    select_index 20000 1 0 4604.16
    select_index 20000 2 0 4594.44
    select_index 20000 1 0 4591.62
    select_index 10000 8 0 691.87
    select_index 10000 6 0 727.72
    select_index 10000 4 0 715.31
    select_index 10000 5 0 746.48
    select_index 10000 15 0 705.85

    libpthread-ule-pre:
    select_index 20000 2 0 4351.12
    select_index 20000 2 0 4162.48
    select_index 20000 3 0 4133.76
    select_index 20000 4 0 4128.27
    select_index 20000 3 0 3478.31
    select_index 10000 6 0 701.57
    select_index 10000 10 0 672.38
    select_index 10000 7 0 709.34
    select_index 10000 11 0 640.43
    select_index 10000 10 0 719.84

    libpthread-ule:
    select_index 20000 1 0 4743.03
    select_index 20000 1 0 5211.85
    select_index 20000 1 0 4039.21
    select_index 20000 2 0 4489.69
    select_index 20000 3 0 4466.95
    select_index 10000 7 0 714.00
    select_index 10000 96 0 734.32
    select_index 10000 7 0 708.44
    select_index 10000 7 0 693.83
    select_index 10000 5 0 715.12

  5. b6s says:

    average = SUM / 30

    998.57 libthr-ule
    980.79 libthr-4bsd-pre
    915.07 libthr-4bsd
    895.54 libpthread-4bsd
    889.05 libpthread-4bsd-pre
    883.88 libpthread-ule
    846.98 libthr-ule-pre
    789.92 libpthread-ule-pre

    看來我應該做更多 round 再求平均。

  6. R_T_F_M says:

    FreeBSD 6.0-STABLE #0: Fri Dec 23 21:41:40 CET 2005
    rtfm@amd64.home.org:/usr/obj/usr/src/sys/AMD64
    ACPI APIC Table:
    Timecounter "i8254" frequency 1193182 Hz quality 0
    CPU: AMD Athlon(tm) 64 Processor 3000+ (2010.06-MHz 686-class CPU)
    Origin = "AuthenticAMD" Id = 0xf48 Stepping = 8
    Features=0x78bfbff
    AMD Features=0xe0500800
    real memory = 805240832 (767 MB)
    avail memory = 778686464 (742 MB)

    [my.cnf]
    # The MySQL server
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    skip-locking
    key_buffer = 64M
    max_allowed_packet = 64M
    table_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache = 24
    query_cache_size = 192M

    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8

    mysql 5.0.17, libthr, ULE + TSC:
    Sat Dec 31 02:30:09 CET 2005
    select_index 20000 0 0 25164.77
    select_index 20000 0 0 24781.40
    select_index 20000 0 0 21857.76
    select_index 20000 0 0 21932.90
    select_index 20000 1 0 22043.21
    Sat Dec 31 02:30:14 CET 2005

    mysql 5.0.17, libthr, ULE + ACPI-fast:
    Sat Dec 31 02:32:25 CET 2005
    select_index 20000 0 0 21618.98
    select_index 20000 0 0 22079.83
    select_index 20000 0 0 19821.74
    select_index 20000 0 0 19771.64
    select_index 20000 1 0 19953.37
    Sat Dec 31 02:32:30 CET 2005

  7. Pingback: bsdforums.org - FreeBSD, OpenBSD, NetBSD, MacOS X, Darwin, Linux, BSD Unix forums, message boards, discussions and news. - mysql stability on fairly busy server

  8. Pingback: Benchmark for MySQL 5.0 + FreeBSD 6-STABLE

  9. Pingback: Anonymous

  10. Pingback: Benchmark for MySQL 5.0 + FreeBSD 6-STABLE

  11. Pingback: MySQL i FreeBSD6.0 - dlaczego nie mozna zwiekszyc max_connections | hilpers

Leave a Reply

Your email address will not be published. Required fields are marked *