From owner-freebsd-performance@FreeBSD.ORG Wed Jun 29 01:32:35 2005 Return-Path: X-Original-To: performance@freebsd.org Delivered-To: freebsd-performance@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E2F2D16A41C for ; Wed, 29 Jun 2005 01:32:35 +0000 (GMT) (envelope-from mv@roq.com) Received: from p4.roq.com (ns1.ecoms.com [207.44.130.137]) by mx1.FreeBSD.org (Postfix) with ESMTP id AE5B343D49 for ; Wed, 29 Jun 2005 01:32:33 +0000 (GMT) (envelope-from mv@roq.com) Received: from p4.roq.com (localhost.roq.com [127.0.0.1]) by p4.roq.com (Postfix) with ESMTP id B0B364E395; Wed, 29 Jun 2005 01:32:59 +0000 (GMT) Received: from [192.168.46.52] (ppp166-27.static.internode.on.net [150.101.166.27]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by p4.roq.com (Postfix) with ESMTP id 5285C4E394; Wed, 29 Jun 2005 01:32:57 +0000 (GMT) Message-ID: <42C1FA31.3050806@roq.com> Date: Wed, 29 Jun 2005 11:32:33 +1000 From: Michael Vince User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Steve Roome References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> In-Reply-To: <20050627134146.GA626@bibipentium.lonres.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP Cc: performance@freebsd.org Subject: Re: FreeBSD MySQL still WAY slower than Linux X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 29 Jun 2005 01:32:36 -0000 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: >>>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 >>>Features2=0x441d> >>>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 irqs 0-23 on motherboard >>>ioapic1 irqs 32-55 on motherboard >>>ioapic2 irqs 64-87 on motherboard >>>ioapic3 irqs 96-119 on motherboard >>>npx0: [FAST] >>>npx0: on motherboard >>>npx0: INT 16 interface >>>acpi0: on motherboard >>>acpi0: Power Button (fixed) >>>pci_link0: irq 7 on acpi0 >>>pci_link1: irq 5 on acpi0 >>>pci_link2: irq 10 on acpi0 >>>pci_link3: on acpi0 >>>pci_link4: on acpi0 >>>pci_link5: on acpi0 >>>pci_link6: on acpi0 >>>pci_link7: on acpi0 >>>Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000 >>>acpi_timer0: <24-bit timer at 3.579545MHz> port 0x808-0x80b on acpi0 >>>cpu0: on acpi0 >>>cpu1: on acpi0 >>>pcib0: port 0xcf8-0xcff on acpi0 >>>pci0: on pcib0 >>>pcib1: at device 2.0 on pci0 >>>pci1: on pcib1 >>>pcib2: at device 0.0 on pci1 >>>pci2: on pcib2 >>>amr0: mem >>>0xd80f0000-0xd80fffff,0xdfec0000-0xdfefffff irq 46 at device 14.0 on pci2 >>>amr0: Firmware 513O, BIOS H418, 256MB RAM >>>pcib3: at device 0.2 on pci1 >>>pci3: on pcib3 >>>pcib4: at device 4.0 on pci0 >>>pci4: on pcib4 >>>pcib5: at device 5.0 on pci0 >>>pci5: on pcib5 >>>pcib6: at device 0.0 on pci5 >>>pci6: on pcib6 >>>em0: 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: at device 0.2 on pci5 >>>pci7: on pcib7 >>>em1: 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: at device 6.0 on pci0 >>>pci8: on pcib8 >>>pcib9: at device 0.0 on pci8 >>>pci9: on pcib9 >>>pcib10: at device 0.2 on pci8 >>>pci10: on pcib10 >>>pcib11: at device 30.0 on pci0 >>>pci11: on pcib11 >>>pci11: at device 13.0 (no driver attached) >>>isab0: at device 31.0 on pci0 >>>isa0: on isab0 >>>atapci0: port >>>0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xfc00-0xfc0f at device 31.1 on pci0 >>>ata0: on atapci0 >>>ata1: on atapci0 >>>fdc0: port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on acpi0 >>>fdc0: [FAST] >>>fd0: <1440-KB 3.5" drive> on fdc0 drive 0 >>>atkbdc0: port 0x60,0x64 irq 1 on acpi0 >>>atkbd0: 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: at iomem 0xc0000-0xcafff,0xec000-0xeffff on isa0 >>>sc0: at flags 0x100 on isa0 >>>sc0: VGA <16 virtual consoles, flags=0x300> >>>vga0: 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 at ata0-master UDMA33 >>>pass0 at amr0 bus 0 target 6 lun 0 >>>pass0: Fixed Processor SCSI-2 device >>>amrd0: on amr0 >>>amrd0: 69360MB (142049280 sectors) RAID 5 (optimal) >>>amrd1: 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" > >