Re: FreeBSD MySQL still WAY slower than Linux

Tue, 28 Jun 2005 18:32:44 -0700

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
 [EMAIL PROTECTED]:/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 
"[EMAIL PROTECTED]"




     


_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[EMAIL PROTECTED]"

 



_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[EMAIL PROTECTED]"























					Reply via email to