Re: FreeBSD MySQL still WAY slower than Linux

From: Michael Vince (mv_at_roq.com)
Date: 06/29/05

  • Next message: Jeremie Le Hen: "Re: FreeBSD MySQL still WAY slower than Linux" 
    Date: Wed, 29 Jun 2005 11:32:33 +1000
To: Steve Roome <steve@pepcross.com>

    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"
    >
    >

    _______________________________________________
    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"

  • Next message: Jeremie Le Hen: "Re: FreeBSD MySQL still WAY slower than Linux"