Re: FreeBSD MySQL still WAY slower than Linux

[Michael VInce]

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
 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/PE2650_i386_steve
ACPI APIC Table: <DELL   PE BKC  >
Timecounter "i8254" frequency 1193182 Hz quality 0
CPU: Intel(R) Xeon(TM) CPU 2.80GHz (2793.01-MHz 686-class CPU)
Origin = "GenuineIntel"  Id = 0xf34  Stepping = 4
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> 

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


    

_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to 
"[EMAIL PROTECTED]"
 


_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to 
"[EMAIL PROTECTED]"


_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[EMAIL PROTECTED]"