[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: FreeBSD MySQL still WAY slower than Linux



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_(_at_)_unoctbium_(_dot_)_lonres_(_dot_)_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_(_at_)_freebsd_(_dot_)_org mailing list
> >http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> >To unsubscribe, send any mail to 
> >"freebsd-performance-unsubscribe_(_at_)_freebsd_(_dot_)_org"
> > 
> >
> 
_______________________________________________
freebsd-performance_(_at_)_freebsd_(_dot_)_org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe_(_at_)_freebsd_(_dot_)_org"


Visit your host, monkey.org