Skip site navigation (1)Skip section navigation (2)
Date:      Wed, 29 Jun 2005 11:32:33 +1000
From:      Michael Vince <mv@roq.com>
To:        Steve Roome <steve@pepcross.com>
Cc:        performance@freebsd.org
Subject:   Re: FreeBSD MySQL still WAY slower than Linux
Message-ID:  <42C1FA31.3050806@roq.com>
In-Reply-To: <20050627134146.GA626@bibipentium.lonres.com>
References:  <20050623145041.GC64879@bibipentium.lonres.com>	<42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com>

next in thread | previous in thread | raw e-mail | index | archive | help
I tried some benchmark testing on a Dell 1850 5.4-Release-P2 with 
generic kernel.
 From what I have seen from your postings I was able to get a higher 
supersmack result, 23626.76 .
I compiled 4.0 MySQL with some optimizations
portupgrade -RN -m 'BUILD_STATIC=yes BUILD_OPTIMIZED=yes' 
/usr/ports/databases/mysql40-server/

Downloaded and installed the supersmack source from here 
http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz
with just ./configure --with-mysql

Copied the default large my.cnf profile
cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf

# super-smack /usr/share/smacks/select-key.smack 50 1000
Query Barrel Report for client smacker1
connect: max=44ms  min=11ms avg= 20ms from 50 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    100000  3       0       23626.76

If I delete the /var/db/mysql/my.cnf and restart mysql so I get default 
configuration, I get 13398.76
# super-smack /usr/share/smacks/select-key.smack 50 1000
Query Barrel Report for client smacker1
connect: max=29ms  min=7ms avg= 17ms from 50 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    100000  7       0       13398.76

For me this is as fast as I need my database to be but I can understand 
there is a difference here between FreeBSD and Linux that would make you 
prefer it as the db OS choice.

Regards,
Mike

Steve Roome wrote:

>On Sun, Jun 26, 2005 at 12:06:41AM +1000, Michael Vince wrote:
>  
>
>>Your posting a lot of configuration here except the most easily 
>>important one for performance in MySQL, thats your my.cnf configuration file
>>You will more then double your performance if you just start off by copying
>>/usr/local/share/mysql/my-large.cnf
>>to
>>/var/db/mysql
>>MySQL out of the box setup to use a tiny amount of ram and it wouldn't 
>>surprise me if a lot of Linux distributions have a much more high 
>>performance my.cnf file since most distributions are aimed at people who 
>>don't know what they are doing.
>>    
>>
>
>
>Sorry, good point, here's the my.cnf we're using. Please note however
>that although the configuration may not be optimal, we have been using
>the same config for benchmarking on Linux also. No matter how broken
>this my.cnf is we still shouldn't find MySQL running half the speed on
>an "identical" setups only switching from Linux to FreeBSD.
>
>Ta,
>
>        Steve Roome
>
>[mysqld]
>log-bin
>log_slow_queries
>log_long_format
>server-id       = 182
>warnings
>skip-slave-start
>skip-locking
>#read-only
>
>datadir = /data/mysql_data
>
>innodb_file_per_table
>innodb_data_home_dir = /data/mysql_data/
>innodb_data_file_path = ibdata1:10M:autoextend
>innodb_log_group_home_dir = /data/mysql_data/
>innodb_log_arch_dir = /data/mysql_data/
>innodb_additional_mem_pool_size = 20M
>innodb_flush_log_at_trx_commit = 1
>innodb_buffer_pool_size = 1G
>innodb_thread_concurrency = 5
>innodb_log_files_in_group = 3
>innodb_log_buffer_size = 8M
>innodb_log_file_size = 64M
>innodb_max_dirty_pages_pct = 90
>
>
>port            = 3306
>socket          = /tmp/mysql.sock
>max_binlog_size=256M
>key_buffer = 128M
>max_allowed_packet = 4M
>table_cache = 2048
>sort_buffer_size = 8M
>read_buffer_size = 8M
>join_buffer_size = 8M
>myisam_sort_buffer_size = 128M
>thread_cache = 8
>thread_cache_size = 40
>query_cache_size= 64M
>thread_concurrency = 8
>wait_timeout    = 60
>max_connections= 120
>#skip-networking
>
>[client]
>#password       = 
>port            = 3306
>socket          = /tmp/mysql.sock
>
>[mysqldump]
>quick
>max_allowed_packet = 32M
>
>[mysql]
>#no-auto-rehash
>#safe-updates
>
>[isamchk]
>key_buffer = 128M
>sort_buffer_size = 128M
>read_buffer = 2M
>write_buffer = 2M
>
>[myisamchk]
>key_buffer = 256M
>sort_buffer_size = 256M
>read_buffer = 2M
>write_buffer = 2M
>
>[mysqlhotcopy]
>interactive-timeout
>
>[mysqld_safe]
>open-files-limit = 8192
>
>
>  
>
>>Steve Roome wrote:
>>
>>    
>>
>>>Oops, here's what I was supposed to attach to the email with test results 
>>>in it.
>>>
>>>Sorry about that,
>>>
>>>      Steve Roome
>>>
>>>########################################################################
>>>/etc/make.conf
>>>########################################################################
>>>
>>>WITHOUT_X11=yes
>>>WITHOUT_NLS=yes
>>>
>>>CFLAGS?=-O3 -pipe
>>>CPUTYPE?=p4
>>>KERNCONF?=PE2650_i386_steve
>>>NO_PROFILE=true
>>>
>>>MASTER_SITE_BACKUP?= 
>>>ftp://ftp.uk.freebsd.org/pub/FreeBSD/ports/distfiles/${DIST_SUBDIR}/
>>>MASTER_SITE_OVERRIDE?= ${MASTER_SITE_BACKUP}
>>>
>>>SUP_UPDATE=yes
>>>SUP=/usr/local/bin/cvsup
>>>SUPFLAGS=-g -L 2
>>>SUPHOST=cvsup.uk.FreeBSD.org
>>>SUPFILE=/usr/src/supfile
>>>PORTSSUPFILE=/usr/ports/ports-supfile
>>># added by use.perl 2005-06-23 10:21:11
>>>PERL_VER=5.8.6
>>>PERL_VERSION=5.8.6
>>>
>>>
>>>########################################################################
>>># Kernel configuration
>>>########################################################################
>>># $FreeBSD: src/sys/i386/conf/GENERIC,v 1.413.2.8 2004/10/24 17:42:08 
>>>scottl Exp $
>>>
>>>machine		i386
>>>cpu		I686_CPU
>>>ident		PE2650_i386_1
>>>
>>>options         MAXDSIZ=(2048UL*1024*1024)
>>>
>>>options 	SCHED_4BSD		# 4BSD scheduler
>>>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
>>># can be dynamically loaded: options 	NFSCLIENT		# Network 
>>>Filesystem Client
>>># can be dynamically loaded: options 	NFSSERVER		# Network 
>>>Filesystem Server
>>># can be dynamically loaded: options 	MSDOSFS			# MSDOS 
>>>Filesystem
>>># can be dynamically loaded: options 	CD9660			# ISO 9660 
>>>Filesystem
>>># can be dynamically loaded: 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 	SCSI_DELAY=1000		# Delay (in ms) before probing SCSI
>>>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
>>>options 	ADAPTIVE_GIANT		# Giant mutex is adaptive.
>>>
>>>options		SMP
>>>device		apic		# I/O APIC
>>>
>>>device		isa
>>>device		pci
>>>
>>>device		fdc
>>>
>>>device		ata
>>>device		atapicd		# ATAPI CDROM drives
>>>options 	ATA_STATIC_ID	# Static device numbering
>>>
>>>device		scbus		# SCSI bus (required for SCSI)
>>># can be dynamically loaded: device		ch		# SCSI media 
>>>changers
>>>device		da		# Direct Access (disks)
>>># can be dynamically loaded: device		sa		# Sequential 
>>>Access (tape etc)
>>>device		pass		# Passthrough device (direct SCSI 
>>>access)
>>># can be dynamically loaded: device		ses		# SCSI 
>>>Environmental Services (and SAF-TE)
>>>
>>>device		aac		# Adaptec FSA RAID
>>>device		aacp		# SCSI passthrough for aac (requires 
>>>CAM)
>>>
>>>device		atkbdc		# AT keyboard controller
>>>device		atkbd		# AT keyboard
>>>
>>>device		vga		# VGA video card driver
>>>device		sc
>>># can be dynamically loaded: device		agp		# support 
>>>several AGP chipsets
>>>device		npx
>>>device		sio		# 8250, 16[45]50 based serial ports
>>>
>>>device		miibus		# MII bus support
>>>device		bge		# Broadcom BCM570xx Gigabit Ethernet
>>>
>>>device		loop		# Network loopback
>>>device		mem		# Memory and kernel memory devices
>>>device		io		# I/O device
>>>device		random		# Entropy device
>>>device		ether		# Ethernet support
>>># can be dynamically loaded: device		ppp		# Kernel PPP
>>># can be dynamically loaded: device		tun		# Packet 
>>>tunnel.
>>>device		pty		# Pseudo-ttys (telnet etc)
>>>device		md		# Memory "disks"
>>>device		gif		# IPv6 and IPv4 tunneling
>>>device		bpf		# Berkeley packet filter
>>>device		amr
>>>device		em
>>>
>>>
>>>########################################################################
>>># dmesg output
>>>########################################################################
>>>
>>>
>>>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-CURRENT #2: Wed Jun 22 17:30:47 BST 2005
>>>  root@unoctbium.lonres.com:/usr/obj/usr/src/sys/PE2650_i386_steve
>>>ACPI APIC Table: <DELL   PE BKC  >
>>>Timecounter "i8254" frequency 1193182 Hz quality 0
>>>CPU: Intel(R) Xeon(TM) CPU 2.80GHz (2793.01-MHz 686-class CPU)
>>>Origin = "GenuineIntel"  Id = 0xf34  Stepping = 4
>>>Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
>>>Features2=0x441d<SSE3,RSVD2,MON,DS_CPL,CNTX-ID,<b14>>
>>>real memory  = 3489398784 (3327 MB)
>>>avail memory = 3419168768 (3260 MB)
>>>FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
>>>cpu0 (BSP): APIC ID:  0
>>>cpu1 (AP): APIC ID:  6
>>>ioapic0: Changing APIC ID to 7
>>>ioapic1: Changing APIC ID to 8
>>>ioapic1: WARNING: intbase 32 != expected base 24
>>>ioapic2: Changing APIC ID to 9
>>>ioapic2: WARNING: intbase 64 != expected base 56
>>>ioapic3: Changing APIC ID to 10
>>>ioapic3: WARNING: intbase 96 != expected base 88
>>>ioapic0 <Version 2.0> irqs 0-23 on motherboard
>>>ioapic1 <Version 2.0> irqs 32-55 on motherboard
>>>ioapic2 <Version 2.0> irqs 64-87 on motherboard
>>>ioapic3 <Version 2.0> irqs 96-119 on motherboard
>>>npx0: [FAST]
>>>npx0: <math processor> on motherboard
>>>npx0: INT 16 interface
>>>acpi0: <DELL PE BKC> on motherboard
>>>acpi0: Power Button (fixed)
>>>pci_link0: <ACPI PCI Link LNKA> irq 7 on acpi0
>>>pci_link1: <ACPI PCI Link LNKB> irq 5 on acpi0
>>>pci_link2: <ACPI PCI Link LNKC> irq 10 on acpi0
>>>pci_link3: <ACPI PCI Link LNKD> on acpi0
>>>pci_link4: <ACPI PCI Link LNKE> on acpi0
>>>pci_link5: <ACPI PCI Link LNKF> on acpi0
>>>pci_link6: <ACPI PCI Link LNKG> on acpi0
>>>pci_link7: <ACPI PCI Link LNKH> on acpi0
>>>Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000
>>>acpi_timer0: <24-bit timer at 3.579545MHz> port 0x808-0x80b on acpi0
>>>cpu0: <ACPI CPU> on acpi0
>>>cpu1: <ACPI CPU> on acpi0
>>>pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0
>>>pci0: <ACPI PCI bus> on pcib0
>>>pcib1: <ACPI PCI-PCI bridge> at device 2.0 on pci0
>>>pci1: <ACPI PCI bus> on pcib1
>>>pcib2: <ACPI PCI-PCI bridge> at device 0.0 on pci1
>>>pci2: <ACPI PCI bus> on pcib2
>>>amr0: <LSILogic MegaRAID 1.51> mem 
>>>0xd80f0000-0xd80fffff,0xdfec0000-0xdfefffff irq 46 at device 14.0 on pci2
>>>amr0: <LSILogic PERC 4e/Di> Firmware 513O, BIOS H418, 256MB RAM
>>>pcib3: <ACPI PCI-PCI bridge> at device 0.2 on pci1
>>>pci3: <ACPI PCI bus> on pcib3
>>>pcib4: <ACPI PCI-PCI bridge> at device 4.0 on pci0
>>>pci4: <ACPI PCI bus> on pcib4
>>>pcib5: <ACPI PCI-PCI bridge> at device 5.0 on pci0
>>>pci5: <ACPI PCI bus> on pcib5
>>>pcib6: <ACPI PCI-PCI bridge> at device 0.0 on pci5
>>>pci6: <ACPI PCI bus> on pcib6
>>>em0: <Intel(R) PRO/1000 Network Connection, Version - 2.1.7> port 
>>>0xecc0-0xecff mem 0xdfbe0000-0xdfbfffff irq 64 at device 7.0 on pci6
>>>em0: Ethernet address: 00:11:43:33:9c:e3
>>>em0:  Speed:N/A  Duplex:N/A
>>>pcib7: <ACPI PCI-PCI bridge> at device 0.2 on pci5
>>>pci7: <ACPI PCI bus> on pcib7
>>>em1: <Intel(R) PRO/1000 Network Connection, Version - 2.1.7> port 
>>>0xdcc0-0xdcff mem 0xdf9e0000-0xdf9fffff irq 65 at device 8.0 on pci7
>>>em1: Ethernet address: 00:11:43:33:9c:e4
>>>em1:  Speed:N/A  Duplex:N/A
>>>pcib8: <ACPI PCI-PCI bridge> at device 6.0 on pci0
>>>pci8: <ACPI PCI bus> on pcib8
>>>pcib9: <ACPI PCI-PCI bridge> at device 0.0 on pci8
>>>pci9: <ACPI PCI bus> on pcib9
>>>pcib10: <ACPI PCI-PCI bridge> at device 0.2 on pci8
>>>pci10: <ACPI PCI bus> on pcib10
>>>pcib11: <ACPI PCI-PCI bridge> at device 30.0 on pci0
>>>pci11: <ACPI PCI bus> on pcib11
>>>pci11: <display, VGA> at device 13.0 (no driver attached)
>>>isab0: <PCI-ISA bridge> at device 31.0 on pci0
>>>isa0: <ISA bus> on isab0
>>>atapci0: <Intel ICH5 UDMA100 controller> port 
>>>0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xfc00-0xfc0f at device 31.1 on pci0
>>>ata0: <ATA channel 0> on atapci0
>>>ata1: <ATA channel 1> on atapci0
>>>fdc0: <floppy drive controller> port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on acpi0
>>>fdc0: [FAST]
>>>fd0: <1440-KB 3.5" drive> on fdc0 drive 0
>>>atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0
>>>atkbd0: <AT Keyboard> irq 1 on atkbdc0
>>>kbd0 at atkbd0
>>>atkbd0: [GIANT-LOCKED]
>>>sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags 0x10 on 
>>>acpi0
>>>sio0: type 16550A
>>>orm0: <ISA Option ROMs> at iomem 0xc0000-0xcafff,0xec000-0xeffff 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
>>>sio1: configured irq 3 not in bitmap of probed irqs 0
>>>sio1: port may not be enabled
>>>Timecounters tick every 1.000 msec
>>>acd0: CDROM <TEAC CD-ROM CD-224E/K.9A> at ata0-master UDMA33
>>>pass0 at amr0 bus 0 target 6 lun 0
>>>pass0: <PE/PV 1x6 SCSI BP 1.0> Fixed Processor SCSI-2 device 
>>>amrd0: <LSILogic MegaRAID logical drive> on amr0
>>>amrd0: 69360MB (142049280 sectors) RAID 5 (optimal)
>>>amrd1: <LSILogic MegaRAID logical drive> on amr0
>>>amrd1: 69360MB (142049280 sectors) RAID 5 (optimal)
>>>SMP: AP CPU #1 Launched!
>>>Trying to mount root from ufs:/dev/amrd0s1a
>>>em0: link state changed to UP
>>>_______________________________________________
>>>freebsd-performance@freebsd.org mailing list
>>>http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>To unsubscribe, send any mail to 
>>>"freebsd-performance-unsubscribe@freebsd.org"
>>>
>>>
>>>      
>>>
>_______________________________________________
>freebsd-performance@freebsd.org mailing list
>http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"
>  
>




Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?42C1FA31.3050806>