FreeBSD MySQL still WAY slower than Linux
Michael VInce
mv at roq.com
Sat Jul 30 02:49:11 GMT 2005
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 at unoctbium.lonres.com:/usr/obj/usr/src/sys/PE2650_i386_steve
>>>> ACPI APIC Table: <DELL PE BKC >
>>>> Timecounter "i8254" frequency 1193182 Hz quality 0
>>>> CPU: Intel(R) Xeon(TM) CPU 2.80GHz (2793.01-MHz 686-class CPU)
>>>> Origin = "GenuineIntel" Id = 0xf34 Stepping = 4
>>>> Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
>>>>
>>>> Features2=0x441d<SSE3,RSVD2,MON,DS_CPL,CNTX-ID,<b14>>
>>>> real memory = 3489398784 (3327 MB)
>>>> avail memory = 3419168768 (3260 MB)
>>>> FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
>>>> cpu0 (BSP): APIC ID: 0
>>>> cpu1 (AP): APIC ID: 6
>>>> ioapic0: Changing APIC ID to 7
>>>> ioapic1: Changing APIC ID to 8
>>>> ioapic1: WARNING: intbase 32 != expected base 24
>>>> ioapic2: Changing APIC ID to 9
>>>> ioapic2: WARNING: intbase 64 != expected base 56
>>>> ioapic3: Changing APIC ID to 10
>>>> ioapic3: WARNING: intbase 96 != expected base 88
>>>> ioapic0 <Version 2.0> irqs 0-23 on motherboard
>>>> ioapic1 <Version 2.0> irqs 32-55 on motherboard
>>>> ioapic2 <Version 2.0> irqs 64-87 on motherboard
>>>> ioapic3 <Version 2.0> irqs 96-119 on motherboard
>>>> npx0: [FAST]
>>>> npx0: <math processor> on motherboard
>>>> npx0: INT 16 interface
>>>> acpi0: <DELL PE BKC> on motherboard
>>>> acpi0: Power Button (fixed)
>>>> pci_link0: <ACPI PCI Link LNKA> irq 7 on acpi0
>>>> pci_link1: <ACPI PCI Link LNKB> irq 5 on acpi0
>>>> pci_link2: <ACPI PCI Link LNKC> irq 10 on acpi0
>>>> pci_link3: <ACPI PCI Link LNKD> on acpi0
>>>> pci_link4: <ACPI PCI Link LNKE> on acpi0
>>>> pci_link5: <ACPI PCI Link LNKF> on acpi0
>>>> pci_link6: <ACPI PCI Link LNKG> on acpi0
>>>> pci_link7: <ACPI PCI Link LNKH> on acpi0
>>>> Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000
>>>> acpi_timer0: <24-bit timer at 3.579545MHz> port 0x808-0x80b on acpi0
>>>> cpu0: <ACPI CPU> on acpi0
>>>> cpu1: <ACPI CPU> on acpi0
>>>> pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0
>>>> pci0: <ACPI PCI bus> on pcib0
>>>> pcib1: <ACPI PCI-PCI bridge> at device 2.0 on pci0
>>>> pci1: <ACPI PCI bus> on pcib1
>>>> pcib2: <ACPI PCI-PCI bridge> at device 0.0 on pci1
>>>> pci2: <ACPI PCI bus> on pcib2
>>>> amr0: <LSILogic MegaRAID 1.51> mem
>>>> 0xd80f0000-0xd80fffff,0xdfec0000-0xdfefffff irq 46 at device 14.0
>>>> on pci2
>>>> amr0: <LSILogic PERC 4e/Di> Firmware 513O, BIOS H418, 256MB RAM
>>>> pcib3: <ACPI PCI-PCI bridge> at device 0.2 on pci1
>>>> pci3: <ACPI PCI bus> on pcib3
>>>> pcib4: <ACPI PCI-PCI bridge> at device 4.0 on pci0
>>>> pci4: <ACPI PCI bus> on pcib4
>>>> pcib5: <ACPI PCI-PCI bridge> at device 5.0 on pci0
>>>> pci5: <ACPI PCI bus> on pcib5
>>>> pcib6: <ACPI PCI-PCI bridge> at device 0.0 on pci5
>>>> pci6: <ACPI PCI bus> on pcib6
>>>> em0: <Intel(R) PRO/1000 Network Connection, Version - 2.1.7> port
>>>> 0xecc0-0xecff mem 0xdfbe0000-0xdfbfffff irq 64 at device 7.0 on pci6
>>>> em0: Ethernet address: 00:11:43:33:9c:e3
>>>> em0: Speed:N/A Duplex:N/A
>>>> pcib7: <ACPI PCI-PCI bridge> at device 0.2 on pci5
>>>> pci7: <ACPI PCI bus> on pcib7
>>>> em1: <Intel(R) PRO/1000 Network Connection, Version - 2.1.7> port
>>>> 0xdcc0-0xdcff mem 0xdf9e0000-0xdf9fffff irq 65 at device 8.0 on pci7
>>>> em1: Ethernet address: 00:11:43:33:9c:e4
>>>> em1: Speed:N/A Duplex:N/A
>>>> pcib8: <ACPI PCI-PCI bridge> at device 6.0 on pci0
>>>> pci8: <ACPI PCI bus> on pcib8
>>>> pcib9: <ACPI PCI-PCI bridge> at device 0.0 on pci8
>>>> pci9: <ACPI PCI bus> on pcib9
>>>> pcib10: <ACPI PCI-PCI bridge> at device 0.2 on pci8
>>>> pci10: <ACPI PCI bus> on pcib10
>>>> pcib11: <ACPI PCI-PCI bridge> at device 30.0 on pci0
>>>> pci11: <ACPI PCI bus> on pcib11
>>>> pci11: <display, VGA> at device 13.0 (no driver attached)
>>>> isab0: <PCI-ISA bridge> at device 31.0 on pci0
>>>> isa0: <ISA bus> on isab0
>>>> atapci0: <Intel ICH5 UDMA100 controller> port
>>>> 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xfc00-0xfc0f at device 31.1 on
>>>> pci0
>>>> ata0: <ATA channel 0> on atapci0
>>>> ata1: <ATA channel 1> on atapci0
>>>> fdc0: <floppy drive controller> port 0x3f0-0x3f5,0x3f7 irq 6 drq 2
>>>> on acpi0
>>>> fdc0: [FAST]
>>>> fd0: <1440-KB 3.5" drive> on fdc0 drive 0
>>>> atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0
>>>> atkbd0: <AT Keyboard> irq 1 on atkbdc0
>>>> kbd0 at atkbd0
>>>> atkbd0: [GIANT-LOCKED]
>>>> sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags
>>>> 0x10 on acpi0
>>>> sio0: type 16550A
>>>> orm0: <ISA Option ROMs> at iomem 0xc0000-0xcafff,0xec000-0xeffff on
>>>> isa0
>>>> sc0: <System console> at flags 0x100 on isa0
>>>> sc0: VGA <16 virtual consoles, flags=0x300>
>>>> vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff
>>>> on isa0
>>>> sio1: configured irq 3 not in bitmap of probed irqs 0
>>>> sio1: port may not be enabled
>>>> Timecounters tick every 1.000 msec
>>>> acd0: CDROM <TEAC CD-ROM CD-224E/K.9A> at ata0-master UDMA33
>>>> pass0 at amr0 bus 0 target 6 lun 0
>>>> pass0: <PE/PV 1x6 SCSI BP 1.0> Fixed Processor SCSI-2 device amrd0:
>>>> <LSILogic MegaRAID logical drive> on amr0
>>>> amrd0: 69360MB (142049280 sectors) RAID 5 (optimal)
>>>> amrd1: <LSILogic MegaRAID logical drive> on amr0
>>>> amrd1: 69360MB (142049280 sectors) RAID 5 (optimal)
>>>> SMP: AP CPU #1 Launched!
>>>> Trying to mount root from ufs:/dev/amrd0s1a
>>>> em0: link state changed to UP
>>>> _______________________________________________
>>>> freebsd-performance at freebsd.org mailing list
>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>> To unsubscribe, send any mail to
>>>> "freebsd-performance-unsubscribe at freebsd.org"
>>>>
>>>>
>>>>
>>>
>> _______________________________________________
>> freebsd-performance at freebsd.org mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to
>> "freebsd-performance-unsubscribe at freebsd.org"
>>
>>
>
> _______________________________________________
> freebsd-performance at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to
> "freebsd-performance-unsubscribe at freebsd.org"
More information about the freebsd-performance
mailing list