From owner-freebsd-performance@FreeBSD.ORG Sun Jun 26 03:21:34 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 0419E16A41C for ; Sun, 26 Jun 2005 03:21:34 +0000 (GMT) (envelope-from shawnm@iodamedia.net) Received: from kcmop04.iodamedia.net (kcmop04.iodamedia.net [66.39.199.5]) by mx1.FreeBSD.org (Postfix) with ESMTP id BDF9A43D48 for ; Sun, 26 Jun 2005 03:21:33 +0000 (GMT) (envelope-from shawnm@iodamedia.net) Received: from localhost (localhost.iodamedia.net [127.0.0.1]) by kcmop04.iodamedia.net (Postfix) with ESMTP id 342AD18EA3F; Sat, 25 Jun 2005 22:21:33 -0500 (CDT) Received: from kcmop04.iodamedia.net ([127.0.0.1]) by localhost (kcmop04.iodamedia.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17789-06; Sat, 25 Jun 2005 22:21:29 -0500 (CDT) Received: from zeus (CPE-65-30-37-142.kc.res.rr.com [65.30.37.142]) by kcmop04.iodamedia.net (Postfix) with ESMTP id CB954204F16; Sat, 25 Jun 2005 22:21:28 -0500 (CDT) From: "Shawn Mitchell" To: "'Daniel Gerzo'" Date: Sat, 25 Jun 2005 22:21:28 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 In-reply-to: <1986158848.20050625215848@rulez.sk> Thread-Index: AcV5xdNFcjvBZ0OITU2HsQeo9z4H4AAOC/mw Message-Id: <20050626032128.CB954204F16@kcmop04.iodamedia.net> X-Virus-Scanned: amavisd-new at kcmop04.iodamedia.net Cc: 'Michael Vince' , 'Steve Roome' , performance@freebsd.org Subject: RE: Re[2]: 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: Sun, 26 Jun 2005 03:21:34 -0000 Can't remember off the top of my head, I'll throw 5.x onto a lab box here in a bit and see. -Shawn -----Original Message----- From: Daniel Gerzo [mailto:danger@rulez.sk] Sent: Saturday, June 25, 2005 2:59 PM To: Shawn Mitchell Cc: 'Michael Vince'; 'Steve Roome'; performance@freebsd.org Subject: Re[2]: FreeBSD MySQL still WAY slower than Linux Nemam dobru naladu Shawn, Saturday, June 25, 2005, 7:26:22 PM, si odoslal: > I tested a MySQL install on a Dell 6600. It's specs were 8 GB ram, 12 x 73 > 15k rpm drives (Ultra320) on a RAID5, 4 Xeon MP w/ 2 meg of cache, HT > enabled so the OS saw 8 CPU's. > Every time, a stock linux install (SuSE, CentOS, and Fedora) were always > faster than FreeBSD stock, or custom kernel utilizing different options. > I never could fully benchmark it running on Linux; as I never got the server > to max out before my benchmarking machine maxed out. > If your using 4.x, add these to your config file > options MAXDSIZ="(256*1024*1024)" > options MAXSSIZ="(256*1024*1024)" > options DFLDSIZ="(256*1024*1024)" these are not possible to use under 5.x? > I noticed a HUGE difference on FreeBSD with those added, but it still > doesn't crank up there with Linux. > Also, default linux install's do not make it use more ram. You still have > to copy one of the my-xxx.cnf files over to /etc/my.cnf or > /usr/local/etc/my.cnf > MySQL is developed on Linux, and ported to FreeBSD. That's the best > explanation you'll get probably. > 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. -- Nieze by som chcel este nieco napisat, ale uz som to napisal. DanGer, ICQ: 261701668 | e-mail protecting at: http://www.2pu.net/ http://danger.rulez.sk | proxy list at: http://www.proxy-web.com/ | FreeBSD - The Power to Serve! [ s pokrokom by nic nebolo, len nie a nie ho skoncit. ] From owner-freebsd-performance@FreeBSD.ORG Sun Jun 26 07:38:52 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 F273F16A41C for ; Sun, 26 Jun 2005 07:38:51 +0000 (GMT) (envelope-from pfak@telus.net) Received: from priv-edtnes40.telusplanet.net (outbound05.telus.net [199.185.220.224]) by mx1.FreeBSD.org (Postfix) with ESMTP id A487E43D1D for ; Sun, 26 Jun 2005 07:38:51 +0000 (GMT) (envelope-from pfak@telus.net) Received: from [192.168.1.151] (really [64.180.103.26]) by priv-edtnes40.telusplanet.net (InterMail vM.6.01.04.04 201-2131-118-104-20050224) with ESMTP id <20050626073851.PKZQ18930.priv-edtnes40.telusplanet.net@[192.168.1.151]>; Sun, 26 Jun 2005 01:38:51 -0600 Message-ID: <42BE5B8E.6070702@telus.net> Date: Sun, 26 Jun 2005 00:38:54 -0700 From: Peter Kieser User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Shawn Mitchell References: <20050626032128.CB954204F16@kcmop04.iodamedia.net> In-Reply-To: <20050626032128.CB954204F16@kcmop04.iodamedia.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: 'Michael Vince' , 'Steve Roome' , 'Daniel Gerzo' , 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: Sun, 26 Jun 2005 07:38:52 -0000 They work fine on RELENG_5. --Peter Shawn Mitchell wrote: >Can't remember off the top of my head, I'll throw 5.x onto a lab box here in >a bit and see. > >-Shawn > > >-----Original Message----- >From: Daniel Gerzo [mailto:danger@rulez.sk] >Sent: Saturday, June 25, 2005 2:59 PM >To: Shawn Mitchell >Cc: 'Michael Vince'; 'Steve Roome'; performance@freebsd.org >Subject: Re[2]: FreeBSD MySQL still WAY slower than Linux > >Nemam dobru naladu Shawn, > >Saturday, June 25, 2005, 7:26:22 PM, si odoslal: > > > >>I tested a MySQL install on a Dell 6600. It's specs were 8 GB ram, 12 x >> >> >73 > > >>15k rpm drives (Ultra320) on a RAID5, 4 Xeon MP w/ 2 meg of cache, HT >>enabled so the OS saw 8 CPU's. >> >> > > > >>Every time, a stock linux install (SuSE, CentOS, and Fedora) were always >>faster than FreeBSD stock, or custom kernel utilizing different options. >> >> > > > >>I never could fully benchmark it running on Linux; as I never got the >> >> >server > > >>to max out before my benchmarking machine maxed out. >> >> > > > >>If your using 4.x, add these to your config file >> >> > > > >>options MAXDSIZ="(256*1024*1024)" >>options MAXSSIZ="(256*1024*1024)" >>options DFLDSIZ="(256*1024*1024)" >> >> > >these are not possible to use under 5.x? > > > >>I noticed a HUGE difference on FreeBSD with those added, but it still >>doesn't crank up there with Linux. >> >> > > > >>Also, default linux install's do not make it use more ram. You still have >>to copy one of the my-xxx.cnf files over to /etc/my.cnf or >>/usr/local/etc/my.cnf >> >> > > > >>MySQL is developed on Linux, and ported to FreeBSD. That's the best >>explanation you'll get probably. >> >> > > > >>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. >> >> > > > > From owner-freebsd-performance@FreeBSD.ORG Mon Jun 27 13:41:20 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 A23C816A41C for ; Mon, 27 Jun 2005 13:41:20 +0000 (GMT) (envelope-from steve@pepcross.com) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by mx1.FreeBSD.org (Postfix) with ESMTP id 256B243D5E for ; Mon, 27 Jun 2005 13:41:19 +0000 (GMT) (envelope-from steve@pepcross.com) Received: from mail.lonres.com ([194.70.153.187]) by anchor-post-33.mail.demon.net with esmtp (Exim 4.42) id 1DmtrP-0009SQ-Bg; Mon, 27 Jun 2005 13:41:15 +0000 Received: from pepcross.com (bibipentium.lonres.com [10.10.10.225]) by mail.lonres.com (Postfix) with SMTP id 83A4D2E06C; Mon, 27 Jun 2005 14:41:15 +0100 (BST) Received: by pepcross.com (sSMTP sendmail emulation); Mon, 27 Jun 2005 14:41:46 +0100 Date: Mon, 27 Jun 2005 14:41:46 +0100 From: Steve Roome To: Michael Vince Message-ID: <20050627134146.GA626@bibipentium.lonres.com> References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42BD64F1.4080001@roq.com> User-Agent: Mutt/1.4.2.1i 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: Mon, 27 Jun 2005 13:41:20 -0000 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" > > > > > From owner-freebsd-performance@FreeBSD.ORG Tue Jun 28 05:59:28 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 63FC516A42A for ; Tue, 28 Jun 2005 05:59:28 +0000 (GMT) (envelope-from martin@gneto.com) Received: from mxfep01.bredband.com (mxfep01.bredband.com [195.54.107.70]) by mx1.FreeBSD.org (Postfix) with ESMTP id 78CB443D49 for ; Tue, 28 Jun 2005 05:59:26 +0000 (GMT) (envelope-from martin@gneto.com) Received: from as6-1-5.kr.m.bonet.se ([83.227.181.30] [83.227.181.30]) by mxfep01.bredband.com with ESMTP id <20050628055922.LRSS24612.mxfep01.bredband.com@as6-1-5.kr.m.bonet.se>; Tue, 28 Jun 2005 07:59:22 +0200 Received: from [192.168.10.11] (euklides.gneto.com [192.168.10.11]) by as6-1-5.kr.m.bonet.se (Postfix) with ESMTP id 4E353678B9; Tue, 28 Jun 2005 07:59:12 +0200 (CEST) Message-ID: <42C0E730.5010703@gneto.com> Date: Tue, 28 Jun 2005 07:59:12 +0200 From: Martin Nilsson User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050326) 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 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: Tue, 28 Jun 2005 05:59:28 -0000 Steve Roome wrote: > 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. Hi, Have you tested some more real-world queries on Linux vs. FreeBSD? The select-key.smack is a very simple test, a very small table (5.3MB on disk, 90k rows), no joins/sorts and only selects from index. Maybe the performance difference just affects the connect/communication/thread syncronistaion and thus this simple test is a worst case test of performance between Linux & FreeBSD. I'll try to set up something here so I can make some tests too... How does a P4/Xeon compare to Athlon64/Opteron on these tests (Linux vs FreeBSD) the long pipeline in the P4 (Prescott/Nocona) is difficult to optimize for, SMP syncronisation is also much more expensive on netburst, maybe the are better at doing this in Linux? Regards, Martin From owner-freebsd-performance@FreeBSD.ORG Tue Jun 28 09:47:48 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 1C32E16A41C for ; Tue, 28 Jun 2005 09:47:48 +0000 (GMT) (envelope-from jmartin37@speakeasy.net) Received: from mail21.sea5.speakeasy.net (mail21.sea5.speakeasy.net [69.17.117.23]) by mx1.FreeBSD.org (Postfix) with ESMTP id E8ABB43D48 for ; Tue, 28 Jun 2005 09:47:47 +0000 (GMT) (envelope-from jmartin37@speakeasy.net) Received: (qmail 20480 invoked from network); 28 Jun 2005 09:47:47 -0000 Received: from dsl027-163-157.atl1.dsl.speakeasy.net (HELO [192.168.0.200]) (moonlightcheese@[216.27.163.157]) (envelope-sender ) by mail21.sea5.speakeasy.net (qmail-ldap-1.03) with SMTP for ; 28 Jun 2005 09:47:47 -0000 Message-ID: <42C11CC7.3030704@speakeasy.net> Date: Tue, 28 Jun 2005 05:47:51 -0400 From: Jarrod Martin User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Nilsson References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C0E730.5010703@gneto.com> In-Reply-To: <42C0E730.5010703@gneto.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: Steve Roome , 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: Tue, 28 Jun 2005 09:47:48 -0000 Martin Nilsson wrote: > Steve Roome wrote: > >> 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. > > > Hi, > Have you tested some more real-world queries on Linux vs. FreeBSD? > The select-key.smack is a very simple test, a very small table (5.3MB > on disk, 90k rows), no joins/sorts and only selects from index. Maybe > the performance difference just affects the > connect/communication/thread syncronistaion and thus this simple test > is a worst case test of performance between Linux & FreeBSD. > > I'll try to set up something here so I can make some tests too... > > How does a P4/Xeon compare to Athlon64/Opteron on these tests (Linux > vs FreeBSD) the long pipeline in the P4 (Prescott/Nocona) is difficult > to optimize for, SMP syncronisation is also much more expensive on > netburst, maybe the are better at doing this in Linux? > > Regards, > Martin > it's better to have several tests that use different functions. for example, testing inserts, then joins and inserts or just joins, selects only, maybe some more complicated queries with math functions involved. perhaps we should come up with a test bed that will specifically test all of these areas on their own and then come up with some real world queries for an all-encompassing overview. and as far as Xeon/P4 goes... it's not very practical. AMDs are far and away the better performing processor right now and the Intel part will only introduce bottlenecks in the testing... don't even get me started on EM64T... 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" > > From owner-freebsd-performance@FreeBSD.ORG Wed Jun 29 09:45:23 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 F0BA216A41C for ; Wed, 29 Jun 2005 09:45:23 +0000 (GMT) (envelope-from tataz@tataz.chchile.org) Received: from postfix3-1.free.fr (postfix3-1.free.fr [213.228.0.44]) by mx1.FreeBSD.org (Postfix) with ESMTP id B710343D48 for ; Wed, 29 Jun 2005 09:45:23 +0000 (GMT) (envelope-from tataz@tataz.chchile.org) Received: from tatooine.tataz.chchile.org (vol75-8-82-233-239-98.fbx.proxad.net [82.233.239.98]) by postfix3-1.free.fr (Postfix) with ESMTP id 9E6C31734D4; Wed, 29 Jun 2005 11:45:22 +0200 (CEST) Received: by tatooine.tataz.chchile.org (Postfix, from userid 1000) id 98C02405B; Wed, 29 Jun 2005 11:45:34 +0200 (CEST) Date: Wed, 29 Jun 2005 11:45:33 +0200 From: Jeremie Le Hen To: Michael Vince Message-ID: <20050629094533.GA49933@obiwan.tataz.chchile.org> References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C1FA31.3050806@roq.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42C1FA31.3050806@roq.com> User-Agent: Mutt/1.5.9i Cc: Steve Roome , 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 09:45:24 -0000 Hi Michael, hi Steve, > 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. Could you try mounting the filesystem where the database lives with the noatime option, and re-run your tests ? IIRC from previous threads on this subject, Linux doesn't really honor this while FreeBSD does, which pulls down the performances. Regards, -- Jeremie Le Hen < jeremie at le-hen dot org >< ttz at chchile dot org > From owner-freebsd-performance@FreeBSD.ORG Wed Jun 29 12:29:51 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 E216716A41C for ; Wed, 29 Jun 2005 12:29:51 +0000 (GMT) (envelope-from rwatson@FreeBSD.org) Received: from cyrus.watson.org (cyrus.watson.org [204.156.12.53]) by mx1.FreeBSD.org (Postfix) with ESMTP id B688843D48 for ; Wed, 29 Jun 2005 12:29:51 +0000 (GMT) (envelope-from rwatson@FreeBSD.org) Received: from fledge.watson.org (fledge.watson.org [204.156.12.50]) by cyrus.watson.org (Postfix) with ESMTP id 42B2546B14; Wed, 29 Jun 2005 08:29:51 -0400 (EDT) Date: Wed, 29 Jun 2005 13:33:56 +0100 (BST) From: Robert Watson X-X-Sender: robert@fledge.watson.org To: Steve Roome In-Reply-To: <20050623144611.GA64879@bibipentium.lonres.com> Message-ID: <20050629133209.Q74117@fledge.watson.org> References: <6.2.1.2.2.20050620105116.06376688@wheresmymailserver.com> <20050623144611.GA64879@bibipentium.lonres.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed Cc: David Sze , 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 12:29:52 -0000 On Thu, 23 Jun 2005, Steve Roome wrote: >> The different threading libraries are more for completeness. In my >> last test I saw <10% difference between them on amd64. > > Well, I finally got some tests out for FreeBSD/i386 with -current, Here > we go with a bunch of results of FreeBSD 6 with mysql and different > threading libraries, as for you, there's sadly not enough in it so far. > Maybe I need to tune a whole bunch of stuff I've missed ? What version of MySQL are you using? I saw much better FreeBSD MySQL performance with 4.0.x than 4.1.x in terms of transactions per second, and am wondering what changed in MySQL -- did it start using different IPC primitives, threading primitives, etc? I realize it complicates testing, but if you have a chance to compare 4.0.x and 4.1.x across linux and freebsd, that would be quite helpful, so we can see if there's a change in relative performance. Most of my work to optimize MySQL performance with SMP has been using 4.0.x, FYI, so 4.1.x might require different optimization strategies. Robert N M Watson From owner-freebsd-performance@FreeBSD.ORG Wed Jun 29 13:58:34 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 D1CB016A41C; Wed, 29 Jun 2005 13:58:34 +0000 (GMT) (envelope-from dsze@alumni.uwaterloo.ca) Received: from mail.distrust.net (mail.distrust.net [69.93.230.10]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9FB2343D4C; Wed, 29 Jun 2005 13:58:34 +0000 (GMT) (envelope-from dsze@alumni.uwaterloo.ca) Received: from eeyore.distrust.net (CPE00a0c978120d-CM00122570472e.cpe.net.cable.rogers.com [70.28.248.121]) (authenticated bits=0) by mail.distrust.net (8.13.3/8.13.3) with ESMTP id j5TDwUHk083869 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 29 Jun 2005 08:58:33 -0500 (CDT) (envelope-from dsze@alumni.uwaterloo.ca) Message-Id: <6.2.1.2.2.20050629095600.05a54268@mail.distrust.net> X-Mailer: QUALCOMM Windows Eudora Version 6.2.1.2 Date: Wed, 29 Jun 2005 09:58:39 -0400 To: Robert Watson From: David Sze In-Reply-To: <20050629133209.Q74117@fledge.watson.org> References: <6.2.1.2.2.20050620105116.06376688@wheresmymailserver.com> <20050623144611.GA64879@bibipentium.lonres.com> <20050629133209.Q74117@fledge.watson.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: ClamAV 0.85.1/960/Tue Jun 28 23:31:06 2005 on mail.distrust.net X-Virus-Status: Clean X-Mailman-Approved-At: Wed, 29 Jun 2005 14:06:39 +0000 Cc: Steve Roome , 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 13:58:34 -0000 At 01:33 PM 29/06/2005 +0100, Robert Watson wrote this to All: >On Thu, 23 Jun 2005, Steve Roome wrote: > >>>The different threading libraries are more for completeness. In my last >>>test I saw <10% difference between them on amd64. >> >>Well, I finally got some tests out for FreeBSD/i386 with -current, Here >>we go with a bunch of results of FreeBSD 6 with mysql and different >>threading libraries, as for you, there's sadly not enough in it so far. >>Maybe I need to tune a whole bunch of stuff I've missed ? > >What version of MySQL are you using? I saw much better FreeBSD MySQL >performance with 4.0.x than 4.1.x in terms of transactions per second, and >am wondering what changed in MySQL -- did it start using different IPC >primitives, threading primitives, etc? I realize it complicates testing, >but if you have a chance to compare 4.0.x and 4.1.x across linux and >freebsd, that would be quite helpful, so we can see if there's a change in >relative performance. Most of my work to optimize MySQL performance with >SMP has been using 4.0.x, FYI, so 4.1.x might require different >optimization strategies. I haven't got around to finishing all the benchmarks yet, but on 5.4-RELEASE-p2 amd64, I see about 5-7% better performance with 4.0.x versus 4.1.x for the select-key benchmark, and no improvement for update-select (both with InnoDB tables, using the "my-innodb-heavy-4G.cnf" file). From owner-freebsd-performance@FreeBSD.ORG Wed Jun 29 21:00:45 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 D7F1916A41C for ; Wed, 29 Jun 2005 21:00:45 +0000 (GMT) (envelope-from nevans@talkpoint.com) Received: from relay.talkpoint.com (relay.talkpoint.com [204.141.15.158]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9684C43D49 for ; Wed, 29 Jun 2005 21:00:45 +0000 (GMT) (envelope-from nevans@talkpoint.com) Received: from ASSP-nospam ([127.0.0.1]) by relay.talkpoint.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 29 Jun 2005 17:00:44 -0400 Received: from 204.141.15.194 ([204.141.15.194] helo=pleiades.nextvenue.com) by ASSP-nospam ; 29 Jun 05 21:00:44 -0000 Date: Wed, 29 Jun 2005 17:00:44 -0400 From: Nick Evans To: Jeremie Le Hen Message-ID: <20050629170044.0ea27b23@pleiades.nextvenue.com> In-Reply-To: <20050629094533.GA49933@obiwan.tataz.chchile.org> References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C1FA31.3050806@roq.com> <20050629094533.GA49933@obiwan.tataz.chchile.org> X-Mailer: Sylpheed-Claws 1.9.11 (GTK+ 2.6.7; i386-portbld-freebsd5.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 29 Jun 2005 21:00:44.0572 (UTC) FILETIME=[9DC16DC0:01C57CED] Cc: Michael Vince , Steve Roome , 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 21:00:46 -0000 I just tried this on my dual opteron test rig and didn't notice a difference in performance with noatime set. What did make a difference was moving from fxp to bge network cards. bge supports checksum offloading where fxp only supports interrupt bundling. Freed up another 20% idle during my test runs. I'm now hitting ~23,000 selects/sec, ~42% idle when running select-key.smack from 3 client hosts. If we establish a suitable test mix and distribute the smack/data files at least we can get consistent test results across systems. Nick 6-CURRENT as of Jun 27th Dual Opteron 246 Tyan K8SD Thunder Pro 4GB RAM my-huge.cnf with tweaks. On Wed, 29 Jun 2005 11:45:33 +0200 Jeremie Le Hen wrote: > Hi Michael, hi Steve, > > > 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. > > Could you try mounting the filesystem where the database lives with > the noatime option, and re-run your tests ? IIRC from previous threads > on this subject, Linux doesn't really honor this while FreeBSD does, > which pulls down the performances. > > Regards, > -- > Jeremie Le Hen > < jeremie at le-hen dot org >< ttz at chchile dot 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" From owner-freebsd-performance@FreeBSD.ORG Thu Jun 30 00:21:42 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 BD1AD16A41C for ; Thu, 30 Jun 2005 00:21:42 +0000 (GMT) (envelope-from bde@zeta.org.au) Received: from mailout1.pacific.net.au (mailout1.pacific.net.au [61.8.0.84]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5919443D53 for ; Thu, 30 Jun 2005 00:21:42 +0000 (GMT) (envelope-from bde@zeta.org.au) Received: from mailproxy1.pacific.net.au (mailproxy1.pacific.net.au [61.8.0.86]) by mailout1.pacific.net.au (8.13.4/8.13.4/Debian-3) with ESMTP id j5U0LfmC020685; Thu, 30 Jun 2005 10:21:41 +1000 Received: from katana.zip.com.au (katana.zip.com.au [61.8.7.246]) by mailproxy1.pacific.net.au (8.13.4/8.13.4/Debian-3) with ESMTP id j5U0LcbJ019626; Thu, 30 Jun 2005 10:21:39 +1000 Date: Thu, 30 Jun 2005 10:21:38 +1000 (EST) From: Bruce Evans X-X-Sender: bde@delplex.bde.org To: Jeremie Le Hen In-Reply-To: <20050629094533.GA49933@obiwan.tataz.chchile.org> Message-ID: <20050630100814.O45160@delplex.bde.org> References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C1FA31.3050806@roq.com> <20050629094533.GA49933@obiwan.tataz.chchile.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed Cc: Michael Vince , Steve Roome , 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: Thu, 30 Jun 2005 00:21:42 -0000 On Wed, 29 Jun 2005, Jeremie Le Hen wrote: > Could you try mounting the filesystem where the database lives with > the noatime option, and re-run your tests ? IIRC from previous threads > on this subject, Linux doesn't really honor this while FreeBSD does, > which pulls down the performances. I think you mean the async option. Neither would make much differerce. atimes are cached in ffs, so mounting with noatime only makes much difference if lots of different files are read, which is exactly what doesn't happen when data is in a database instead of in separate files. Mounting with async can make a large difference, but normally makes no difference with ffs, since ffs is normally configured with soft updates and the async option is silently ignored in this configuration. You could try mounting with the sync option for all OSes. This should make them all very slow, but the results might be interesting since it is the only sync-related mount option that gives similar behaviour at the fs level. It might also show if the problem is related to syncing. Bruce From owner-freebsd-performance@FreeBSD.ORG Thu Jun 30 14:58:50 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 86B1516A41C for ; Thu, 30 Jun 2005 14:58:50 +0000 (GMT) (envelope-from nevans@talkpoint.com) Received: from relay.talkpoint.com (pobox.talkpoint.com [204.141.15.158]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2AF9143D48 for ; Thu, 30 Jun 2005 14:58:47 +0000 (GMT) (envelope-from nevans@talkpoint.com) Received: from ASSP-nospam ([127.0.0.1]) by relay.talkpoint.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 30 Jun 2005 10:58:46 -0400 Received: from 204.141.15.194 ([204.141.15.194] helo=pleiades.nextvenue.com) by ASSP-nospam ; 30 Jun 05 14:58:46 -0000 Date: Thu, 30 Jun 2005 10:58:46 -0400 From: Nick Evans To: Nick Evans Message-ID: <20050630105846.380aa7ba@pleiades.nextvenue.com> In-Reply-To: <20050629170044.0ea27b23@pleiades.nextvenue.com> References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C1FA31.3050806@roq.com> <20050629094533.GA49933@obiwan.tataz.chchile.org> <20050629170044.0ea27b23@pleiades.nextvenue.com> X-Mailer: Sylpheed-Claws 1.9.11 (GTK+ 2.6.7; i386-portbld-freebsd5.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 30 Jun 2005 14:58:47.0085 (UTC) FILETIME=[3789A5D0:01C57D84] 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: Thu, 30 Jun 2005 14:58:50 -0000 Update: Switched threading libraries from libpthread to libthr and now I'm getting 27,500 selects/sec at 45% idle. This is with version 4.1.12 of mysql using myISAM tables compiled with the following options WITHOUT_INNODB BUILD_OPTIMIZED relevant parts of my.cnf: # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 16M read_buffer_size = 16M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 96M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # max conns max_connections=8000 On Wed, 29 Jun 2005 17:00:44 -0400 Nick Evans wrote: > I just tried this on my dual opteron test rig and didn't notice a difference > in performance with noatime set. What did make a difference was moving from > fxp to bge network cards. bge supports checksum offloading where fxp only > supports interrupt bundling. Freed up another 20% idle during my test runs. > I'm now hitting ~23,000 selects/sec, ~42% idle when running select-key.smack > from 3 client hosts. If we establish a suitable test mix and distribute the > smack/data files at least we can get consistent test results across systems. > > Nick > > 6-CURRENT as of Jun 27th > Dual Opteron 246 > Tyan K8SD Thunder Pro > 4GB RAM > my-huge.cnf with tweaks. > > > On Wed, 29 Jun 2005 11:45:33 +0200 > Jeremie Le Hen wrote: > > > Hi Michael, hi Steve, > > > > > 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. > > > > Could you try mounting the filesystem where the database lives with > > the noatime option, and re-run your tests ? IIRC from previous threads > > on this subject, Linux doesn't really honor this while FreeBSD does, > > which pulls down the performances. > > > > Regards, > > -- > > Jeremie Le Hen > > < jeremie at le-hen dot org >< ttz at chchile dot 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"