From owner-freebsd-performance@FreeBSD.ORG Sat Jul 30 02:49:11 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 8957516A41F for ; Sat, 30 Jul 2005 02:49:11 +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 20D5443D45 for ; Sat, 30 Jul 2005 02:49:10 +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 AFCE34C9A9 for ; Sat, 30 Jul 2005 02:48:45 +0000 (GMT) Received: from [10.0.1.8] (ppp71-99.lns1.mel2.internode.on.net [59.167.71.99]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by p4.roq.com (Postfix) with ESMTP id BA2A34C67D for ; Sat, 30 Jul 2005 02:48:43 +0000 (GMT) Message-ID: <42EAEAA1.2090609@roq.com> Date: Sat, 30 Jul 2005 12:49:05 +1000 From: Michael VInce User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.7.8) Gecko/20050524 X-Accept-Language: en-us, en MIME-Version: 1.0 To: performance@freebsd.org References: <20050623145041.GC64879@bibipentium.lonres.com> <42BD64F1.4080001@roq.com> <20050627134146.GA626@bibipentium.lonres.com> <42C1FA31.3050806@roq.com> In-Reply-To: <42C1FA31.3050806@roq.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP Cc: 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: Sat, 30 Jul 2005 02:49:11 -0000 I would just like to finish my own thoughts on the thread I created here for the sakes of other people who may be googling up this mailing list thread in the future and judging performance on this particular benchmark method. I just wanted to point out that this benchmark test is using 50 simultaneous connections doing thousands of querys at the same time, I was thinking about performance always being a big issue but also the other factors that make a over all OS choice for the database. For me I am using MySQL in my database app its usage of MySQL is quite different as it only uses 1 connection thread most of the time or sometimes a few connections doing querys at the same time. The database app does an average of 600 querys a minute or 36,000 querys an hour, so its biggest difference is that it only uses 1 or 2 persistent connections instead of 50, I think anyone should consider how many people are going to be doing a large amount simultaneous querys because this is where the MySQL people would of concentrated a lot of effort in making Linux succeed in this area since Linux is probably MySQLs biggest market is at this time. If I was going to be doing mass cheap virtual php hosting and want to have everyone on the smallest amount of MySQL database installs as possible then I might choose a different OS, but if the difference between 25,000 querys/sec or 30,000 querys/sec could be over come by spending an extra couple of 100$ on getting a CPU that could go a extra 400mhz higher that could over come the difference. I look my total OS choice based on license and things like software upgrade ability point of view, I am quite happy with portupgrade and I can easily update any port on the system. GPL is a riddle of a license and the only people who disagree always seem to be a riddle with in them selfs, maybe because they always just willing to accept straight out lies. To me the possibility of handing over millions of dollars for a large Linux setup to SCO is a scary possible thought, and any company who is on the stock exchange or has private investment should be afraid. http://news.zdnet.com/2100-3513_22-5167825.html http://www.iseriesnetwork.com/nodeuk/ukarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=18174 I believe that "lawyer death stars" powered by companies like MS can be a very powerful thing, as far as I am concerned if things get in the way of the movement of technology which I ultimately GPL does, with its greed fueled riddle rules then these companies will aim to 'blow' it out of the way. Ultimately its always been private companies who have been the biggest movers of technology and they wont stand for things getting in the way. If GPL was about technology then it would NOT be so eager to sue the hell out of companies who adopt GPL technologies into their products as they do, so as far as I am concerned the last thing GPL and FSF are about is technology. For every company FSF goes after there are 1,000s of others who have quietly take the same thing/code/whatever, GPLs greatest evil is the fact it creates a illusion of protection to software naive developers, there are no illusions,tricks or riddles under BSD. I kind of screwed off the topic about MySQL performance, oh well, I guess there was something else building up in my mind that felt like coming out. My rant is complete. Thank you. Michael Vince wrote: > 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" >> >> > > _______________________________________________ > 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"