From: Dyr, Dennis Yusupoff <dyr@homelink.ru.>
Newsgroups: email
Date: Mon, 25 Apr 2006 14:31:37 +0000 (UTC)
Subject: Тестирование производительности MySQL на DualCore процессоре под FreeBSD 6.0
Тестирование производительности MySQL на DualCore процессоре под FreeBSD 6.0
Решил протестировать производительность MySQL на разных машинах и под
разными условиями. Поскольку тема эта весьма обьёмная, тестирование
разбито на несколько частей.
Итак, сегодня тестирование MySQL 5.0.20 и влияния на него двухядерности процессора.
Сервер: CPU Intel Pentium D 820 (2,8GHz DualCore)
MB IntelR Server Board SE7230NH1-E (LX Version)
RAM 2x1Gb DDR2 ECC
HDD 1x200Gb Seagate 7200.8 SATA
Методика тестирования:
Тестирование производится с помощью утилиты SysBench, с
последовательным увеличением количества потоков в арифметической
прогрессии. Запуск утилиты производится три раза, данные усредняются.
Включение и выключение SMP осуществляется путём изменения
machdep.hyperthreading_allowed.
for a in "on" "off"; do
for t in 1 2 4 8 16 32 64; do
echo "Now testing $table_type table type with --oltp-read-only=$a and --num-threads=$t"
sysbench --max-requests=5000000 --num-threads=$t --max-time=60
--test=oltp --mysql-table-type=$table_type --mysql-user=root
--oltp-read-only=$a run
done;
done;
Значения количества потоков выше 64 не поддерживались MySQL.
Результаты:
Таблица результатов:
Графики результатов:
Выводы:
Использование второго ядра у Intel Pentium D под FreeBSD влечёт за
собой уменьшение производительности MySQL при работе с выборками из
таблиц при количестве одновременных процессов до восьми и увеличении
производительности при дальнейшем увеличении этих процессов. Причём
увеличение количество транзакций прямо пропорционально увеличению
количества параллельных процессов.
Операционная система:
dmesg
FreeBSD 6.0-RELEASE #0: Sat Apr 22 08:29:18 UTC 2006 dyr@white2u.homelink.ru:/usr/obj/usr/src/sys/WHITE2U
acpi_alloc_wakeup_handler: can't alloc wake memory ACPI APIC Table: <INTEL 04DT044>
Timecounter "i8254" frequency 1193182 Hz quality 0
CPU: Intel(R) Pentium(R) D CPU 2.80GHz (2798.60-MHz 686-class CPU)
Origin = "GenuineIntel" Id = 0xf44 Stepping = 4
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,P
GE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
Features2=0x641d<SSE3,RSVD2,MON,DS_CPL,CNTX-ID,CX16,<B14>>
AMD Features=0x20100000<NX,LM>
Hyperthreading: 2 logical CPUs
real memory = 2145906688 (2046 MB)
avail memory = 2094764032 (1997 MB)
FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
cpu0 (BSP): APIC ID: 0
cpu1 (AP): APIC ID: 1
ioapic0: Changing APIC ID to 5
ioapic1: WARNING: intbase 30 != expected base 24
ioapic0 <VERSION 2.0>irqs 0-23 on motherboard
ioapic1 <VERSION 2.0>irqs 30-53 on motherboard
npx0: [FAST]
npx0: <MATH processor>on motherboard
npx0: INT 16 interface
acpi0: <INTEL 04DT044>on motherboard
acpi0: Power Button (fixed)
pci_link0: <ACPI LNKA Link PCI>irq 11 on acpi0
pci_link1: <ACPI Link PCI LNKB>irq 9 on acpi0
pci_link2: <ACPI Link PCI LNKC>irq 11 on acpi0
pci_link3: <ACPI Link PCI LNKD>irq 10 on acpi0
pci_link4: <ACPI Link PCI LNKE>on acpi0
pci_link5: <ACPI Link PCI LNKF>on acpi0
pci_link6: <ACPI Link PCI LNKG>on acpi0
pci_link7: <ACPI Link PCI LNKH>irq 11 on acpi0
Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000
acpi_timer0: <<a 24-bit timer at 3.579545MHz> port 0x408-0x40b on acpi0
cpu0: <ACPI CPU>on acpi0
cpu1: <ACPI CPU>on acpi0
acpi_button0: <SLEEP Button>on acpi0
pcib0: <ACPI bridge Host-PCI>port 0xcf8-0xcff on acpi0
pci0: <ACPI PCI bus>on pcib0
pcib1: <ACPI bridge PCI-PCI>at device 28.0 on pci0
pci1: <ACPI PCI bus>on pcib1
pcib2: <ACPI bridge PCI-PCI>at device 0.0 on pci1
pci2: <ACPI PCI bus>on pcib2
pcib3: <ACPI bridge PCI-PCI>at device 28.4 on pci0
pci3: <ACPI PCI bus>on pcib3
pcib4: <ACPI bridge PCI-PCI>at device 28.5 on pci0
pci4: <ACPI PCI bus>on pcib4
em0: <INTEL(R) 2.1.7 - Version Connection, Network 1000 PRO>port
0x2000-0x201f mem 0x88180000-0x8819ffff,0x88100000-0x8817ffff irq 17 at device 0.0 on pci4
em0: Ethernet address: 00:16:76:00:c1:76
em0: Speed:N/A Duplex:N/A
atapci0: <GENERIC controller ATA>port
0x2040-0x2047,0x2050-0x2053,0x2038-0x203f,0x204c-0x204f,0x2020-0x202f irq 18 at device 0.2 on pci4
ata2: <ATA 0 channel>on atapci0
ata3: <ATA channel 1>on atapci0
pci4: <SIMPLE UART comms,>at device 0.3 (no driver attached)
pci4: <SERIAL bus>at device 0.4 (no driver attached)
uhci0: <UHCI controller USB (generic)>port 0x3080-0x309f irq 23 at device 29.0 on pci0
uhci0: [GIANT-LOCKED]
usb0: <UHCI controller USB (generic)>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: <UHCI controller USB (generic)>port 0x3060-0x307f irq 19 at device 29.1 on pci0
uhci1: [GIANT-LOCKED]
usb1: <UHCI controller USB (generic)>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: <UHCI controller USB (generic)>port 0x3040-0x305f irq 18 at device 29.2 on pci0
uhci2: [GIANT-LOCKED]
usb2: <UHCI controller USB (generic)>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
uhci3: <UHCI controller USB (generic)>port 0x3020-0x303f irq 16 at device 29.3 on pci0
uhci3: [GIANT-LOCKED]
usb3: <UHCI controller USB (generic)>on uhci3
usb3: USB revision 1.0
uhub3: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
uhub3: 2 ports with 2 removable, self powered
pci0: <SERIAL USB bus,>at device 29.7 (no driver attached)
pcib5: <ACPI bridge PCI-PCI>at device 30.0 on pci0
pci5: <ACPI PCI bus>on pcib5
pci5: <DISPLAY, VGA>at device 4.0 (no driver attached)
em1: <INTEL(R) 2.1.7 - Version Connection, Network 1000 PRO>port
0x1100-0x113f mem 0x88020000-0x8803ffff,0x88000000-0x8801ffff irq 17 at device 5.0 on pci5
em1: Ethernet address: 00:16:76:00:c1:77
em1: Speed:N/A Duplex:N/A
isab0: <PCI-ISA bridge>at device 31.0 on pci0
isa0: <ISA bus>on isab0
atapci1: <INTEL controller UDMA100 ICH7>port
0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0x30b0-0x30bf irq 18 at device 31.1 on pci0
ata0: <ATA 0 channel>on atapci1
ata1: <ATA channel 1>on atapci1
atapci2: <INTEL controller ICH7 SATA150>port 0x30c8-0x30cf,0x30e4-0x30e7,0x30c0-0x30c7,0x30e0-0x30e3,0x30a0-0x30af
mem 0x88200000-0x882003ff irq 19 at device 31.2 on pci0
ata4: <ATA 0 channel>on atapci2
ata5: <ATA channel 1>on atapci2
ata6: <ATA channel 2>on atapci2
ata7: <ATA channel 3>on atapci2
pci0: <SERIAL bus, SMBus>at device 31.3 (no driver attached)
speaker0: <PC speaker>port 0x61 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 NetMouse/NetScroll Optical, device ID 0
pmtimer0 on isa0
orm0: <ISA ROM Option>at iomem 0xc0000-0xcafff on isa0
sc0: <SYSTEM console>at flags 0x100 on isa0
sc0: VGA <16 virtual consoles, flags=0x300>
vga0: <GENERIC VGA ISA>at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0
Timecounters tick every 1.000 msec
ipfw2 (+ipv6) initialized, divert loadable, rule-based forwarding
enabled, default to accept, logging limited to 400 packets/entry by default
acd0: DVDR <NEC 1.01 ND-3540A RW DVD>at ata0-slave UDMA33
ad8: 190782MB <SEAGATE 3.01 ST3200822AS>at ata4-master SATA150
ad10: 190782MB <SEAGATE 3.03 ST3200826AS>at ata5-master SATA150
acd0: FAILURE - READ_BIG ILLEGAL REQUEST asc=0x64 ascq=0x00 error=4<ABORTED>
SMP: AP CPU #1 Launched!
cd0 at ata0 bus 0 target 1 lun 0
cd0: <_NEC DVD_RW ND-3540A 1.01> Removable CD-ROM SCSI-0 device
cd0: 33.000MB/s transfers
cd0: cd present [282426 x 2048 byte records]
Ядро скомпилировано со следующими опциями:
cat /usr/src/sys/i386/conf/WHITE2U
machine i386
cpu I686_CPU
ident WHITE2U
options SMP # Symmetric MultiProcessor Kernel
options SCHED_ULE
options PREEMPTION # Enable kernel thread preemption
options IPI_PREEMPTION
options INET # InterNETworking
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 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 KTRACE # ktrace(1) support
options SYSVSHM # SYSV-style shared memory
options SYSVMSG # SYSV-style message queues
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
# output. Adds ~128k to driver.
# output. Adds ~215k to driver.
options ADAPTIVE_GIANT # Giant mutex is adaptive.
options ACCEPT_FILTER_DATA
options ACCEPT_FILTER_HTTP
device apic # I/O APIC
options GEOM_BDE # поддержка шифрованых дисков
device isa
device pci
device ata
device da
device atadisk # ATA disk drives
device atapicd # ATAPI CDROM drives
device ataraid # ATA RAID drives
device atapifd # ATAPI floppy drives
device atapist # ATAPI tape drives
device atapicam
options ATA_STATIC_ID # Static device numbering
device iir # Intel Integrated RAID
device atkbdc # AT keyboard controller
device atkbd # AT keyboard
device psm # PS/2 mouse
device vga # VGA video card driver
device splash # Splash screen and screen saver support
device sc
device agp # support several AGP chipsets
device npx
device apm
device pmtimer # Adjust system timer at wakeup time
device speaker #Play IBM BASIC-style noises out your speaker
device em # Intel PRO/1000 adapter Gigabit Ethernet Card
device miibus # MII bus support
device loop # Network loopback
device mem # Memory and kernel memory devices
device io # I/O device
device random # Entropy device
device ether # Ethernet support
device tun # Packet tunnel.
device pty # Pseudo-ttys (telnet etc)
device md # Memory "disks"
device bpf # Berkeley packet filter
device uhci # UHCI PCI->USB interface
device ohci # OHCI PCI->USB interface
device usb # USB Bus (required)
device udbp # USB Double Bulk Pipe devices
device ugen # Generic
device uhid # "Human Interface Devices"
device ukbd # Keyboard
device umass # Disks/Mass storage - Requires scbus and da
device ums # Mouse
options IPFIREWALL
options IPFIREWALL_VERBOSE
options IPFIREWALL_VERBOSE_LIMIT=400
options IPFIREWALL_DEFAULT_TO_ACCEPT
options IPFIREWALL_FORWARD #enable transparent proxy support
options IPSTEALTH
options TCP_DROP_SYNFIN
device pf
device pflog
options ALTQ
options ALTQ_CBQ # Class Bases Queueing
options ALTQ_RED # Random Early Detection
options ALTQ_RIO # RED In/Out
options ALTQ_HFSC # Hierarchical Packet Scheduler
options ALTQ_CDNR # Traffic conditioner
options ALTQ_PRIQ # Priority Queueing
options ALTQ_NOPCC # Required for SMP build
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)
options SC_DISABLE_REBOOT
options SC_HISTORY_SIZE=2000
device speaker #Play IBM BASIC-style noises out your speaker
options HZ=1000
options PERFMON
options NETGRAPH #netgraph(4) system
options NETGRAPH_ASYNC
options NETGRAPH_BPF
options NETGRAPH_ECHO
options NETGRAPH_ETHER
options NETGRAPH_HOLE
options NETGRAPH_IFACE
options NETGRAPH_KSOCKET
options NETGRAPH_L2TP
options NETGRAPH_MPPC_ENCRYPTION
options NETGRAPH_PPP
options NETGRAPH_PPPOE
options NETGRAPH_PPTPGRE
options NETGRAPH_RFC1490
options NETGRAPH_SOCKET
options NETGRAPH_TEE
device gre
options PPP_BSDCOMP
options PPP_DEFLATE
options PPP_FILTER
options GEOM_MIRROR
options ENABLE_ALART # Control alarm on Intel intpm driver
device intpm # Intel power management
device iicbus
device smbus
device iicsmb
Переменные системы, отвечающие за процессор:
sysctl -a | grep -a machdep; sysctl -a | grep -a cpu
Включение и выключение SMP осуществлялось путём изменения
machdep.hyperthreading_allowed
Программное обеспечение:
pkg_info
autoconf-2.59_2 Automatically configure source code on many Un*x platforms
cvsup-without-gui-16.1h_2 General network file distribution system optimized for CVS
db4-4.0.14_1,1 The Berkeley DB package, revision 4
fastest_cvsup-0.2.9_1 Finds fastest CVSup server
freecolor-0.8.7_1 Displays free memory as a bargraph
freetype2-2.1.10_3 A free and portable TrueType font rendering engine
gd-2.0.33_4,1 A graphics library for fast creation of images
gettext-0.14.5 GNU gettext package
glib-2.6.6 Some useful routines of C programming (current stable versi
gmake-3.80_2 GNU version of 'make' utility
help2man-1.36.3 Automatically generating simple manual pages from program o
jpeg-6b_4 IJG's jpeg compression utilities
ldconfig_compat-1.0_7 Ldconfig compatibility script
libiconv-1.9.2_2 A character set conversion library
libslang-1.4.9 Routines for rapid alpha-numeric terminal applications deve
libstatgrab-0.13 Provides a useful interface to system statistics
libtool-1.5.22_2 Generic shared library support script
m4-1.4.4 GNU m4
mc-4.6.0_15 Midnight Commander, a free Norton Commander Clone
mrtg-2.13.2,1 The multi-router traffic grapher
mysql-client-5.0.20 Multithreaded SQL database (client)
mysql-scripts-5.0.20 Multithreaded SQL database (scripts)
mysql-server-5.0.20 Multithreaded SQL database (server)
net-snmp-5.2.2_1 An extendable SNMP implementation
nginx-0.3.40 Robust and small WWW server
nload-0.6.0 Console application which monitors network traffic in real
openssl-stable-0.9.7i SSL and crypto library
p5-DBD-mysql50-3.0002 MySQL 5.0 driver for the Perl5 Database Interface (DBI)
p5-DBI-1.50 The perl5 Database Interface. Required for DBD::* modules
p5-PathTools-3.17 A Perl module for portably manipulating file specifications
p5-Pod-Parser-1.34 Modules to work with POD (Plain Old Documentation)
p5-SNMP_Session-1.08 A perl5 module providing rudimentary access to SNMPv1 and v
p5-Scalar-List-Utils-1.18,1 Perl subroutines that would be nice to have in the perl cor
p5-Storable-2.15 Persistency for perl data structures
p5-gettext-1.05_1 Message handling functions
pcre-6.6_1 Perl Compatible Regular Expressions library
perl-5.8.7 Practical Extraction and Report Language
pkg_cutleaves-20040517 Interactive script for deinstalling 'leaf' packages
pkg_tree-1.1_1 Get a 'graphical' tree-overview of installed packages
pkgconfig-0.17.2 A utility to retrieve information about installed libraries
png-1.2.8_3 Library for manipulating PNG images
popt-1.7_1 A getopt(3) like library with a number of enhancements, fro
port-maintenance-tools-1.0 "meta-port" for administrative tools to maintain ports
portaudit-0.5.11 Checks installed ports against a list of security vulnerabi
portdowngrade-0.6 Sets a port back to a previous version
porteasy-2.8.3 A tool for fetching and building ports
portell-0.2_1 Quick display of FreeBSD port descriptions
portupgrade-20041226_7 FreeBSD ports/packages administration and management tool s
python-2.4.3 An interpreted object-oriented programming language
ruby-1.8.2_4 An object-oriented interpreted scripting language
ruby18-bdb4-0.5.3 Ruby interface to Sleepycat's Berkeley DB revision 2 or lat
sudo-1.6.8.12_1 Allow others to run commands as root
super-smack-1.3 A benchmarking, stress testing, and load generation tool fo
sysbench-0.4.3 A modular, cross-platform and multi-threaded benchmark tool
trafshow-4.0 Full screen visualization of network traffic
unzip-5.52_2 List, test and extract compressed files in a ZIP archive
vim-lite-6.4.6_1 Vi "workalike", with many additional features (Lite package
cat /etc/make.conf
NO_SENDMAIL= true # do not build sendmail and related programs
NO_ATM= true
NO_GAMES= true # do not build games (games/ subdir)
NO_INET6= true # do not build IPv6 related programs and libraries
NO_BIND9= true
NO_PROFILE= true # Avoid compiling profiled libraries
MAKE_IDEA= YES # IDEA (128 bit symmetric encryption)
BOOTWAIT= 2
DOC_LANG= en_US.ISO8859-1 ru_RU.KOI8-R
MAKE_KERBEROS5= no
ENABLE_SUID_K5SU= no
CFLAGS=-O -pipe
WITH_OPENSSL_PORT=yes
CVS_UPDATE= true
PERL_VER=5.8.7
PERL_VERSION=5.8.7
WITHOUT_X11="yes"
NO_GUI="true"
NO_X="true"
MySQL установлен из портов со следующими параметрами:
cd /usr/ports/databases/mysql50-server && make -DBUILD_OPTIMIZED=YES BUILD_STATIC=YES install
Vova
