mysql scaling questions
Jeff Roberson
jroberson at chesapeake.net
Fri Nov 30 16:29:25 PST 2007
I've forwarded this mail to the freebsd performance list so more people
can take a look at it. Thanks for all of the details. What was the test
that you're doing? sysbench? With writes or without? Or some other
benchmark?
Thanks,
Jeff
On Thu, 29 Nov 2007, Gergely CZUCZY wrote:
> Hello
>
> I've been testing mysql on 7-BETA2 for a few days, compared to a
> linux -2.6.19.2-grsec, and i've found that the linux setup does
> around two times more queries then the FreeBSD ones. This seems
> to be a bit different according to your test results.
>
> Could you help me to check out what am I missing?
>
> I've installed mysql from ports, 5.0.45, and i'm using a production
> config file from a linux system, slightly modified (i've updated the
> paths, and the bind-address).
>
> For 1 2 4 8 16 32 64 128 192 256 384 512 threads it does the
> following results (queries/sec) on freebsd with UFS:
> 1740.98
> 1605.12
> 1520.87
> 1548.42
> 1566.22
> 1465.80
> 1464.17
> 1420.33
> 1384.02
> 1433.44
> 1489.23
> 1445.75
>
> And for linux (it stops at 128 threads, i'm debugging this atm):
> 2791.05
> 3328.08
> 4459.14
> 3668.25
> 3489.43
> 3954.07
> 3055.25
>
> As you see, it's a bit more the two times more. Something isn't right
> it seems to me.
>
> About the FreeBSD system:
> FreeBSD sqltest.in.publishing.hu 7.0-BETA2 FreeBSD 7.0-BETA2 #3: Mon Nov 5 10:49:48 CET 2007 toor at sqltest.in.publishing.hu:/usr/obj/usr/src/sys/SQLTEST amd64
>
> I have the following kernel config:
> cpu HAMMER
> ident GENERIC
> options SCHED_ULE
> options PREEMPTION # Enable kernel thread preemption
> 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
> options UFS_GJOURNAL # Enable gjournal-based UFS journaling
> options MD_ROOT # MD is a potential root device
> options NFSCLIENT # Network Filesystem Client
> options NFSSERVER # Network Filesystem Server
> options NFS_ROOT # NFS usable as /, requires NFSCLIENT
> options NTFS # NT File System
> options MSDOSFS # MSDOS Filesystem
> options CD9660 # ISO 9660 Filesystem
> options PROCFS # Process filesystem (requires PSEUDOFS)
> options PSEUDOFS # Pseudo-filesystem framework
> options GEOM_PART_GPT # GUID Partition Tables.
> options GEOM_LABEL # Provides labelization
> options COMPAT_43TTY # BSD 4.3 TTY compat [KEEP THIS!]
> options COMPAT_IA32 # Compatible with i386 binaries
> options COMPAT_FREEBSD6 # Compatible with FreeBSD6
> options SCSI_DELAY=5000 # 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 STOP_NMI # Stop CPUS using NMI instead of IPI
> options AUDIT # Security event auditing
> options SMP # Symmetric MultiProcessor Kernel
> device cpufreq
> device acpi
> device pci
> device fdc
> device ata
> device atadisk # ATA disk drives
> device ataraid # ATA RAID drives
> device atapicd # ATAPI CDROM drives
> device atapifd # ATAPI floppy drives
> device atapist # ATAPI tape drives
> options ATA_STATIC_ID # Static device numbering
> device scbus # SCSI bus (required for SCSI)
> device ch # SCSI media changers
> device da # Direct Access (disks)
> device sa # Sequential Access (tape etc)
> device cd # CD
> device pass # Passthrough device (direct SCSI access)
> device ses # SCSI Environmental Services (and SAF-TE)
> device twa # 3ware 9000 series PATA/SATA RAID
> device atkbdc # AT keyboard controller
> device atkbd # AT keyboard
> device psm # PS/2 mouse
> device kbdmux # keyboard multiplexer
> device vga # VGA video card driver
> device sc
> device sio # 8250, 16[45]50 based serial ports
> device uart # Generic UART driver
> device de # DEC/Intel DC21x4x (``Tulip'')
> device em # Intel PRO/1000 adapter Gigabit Ethernet Card
> device le # AMD Am7900 LANCE and Am79C9xx PCnet
> device txp # 3Com 3cR990 (``Typhoon'')
> device vx # 3Com 3c590, 3c595 (``Vortex'')
> device miibus # MII bus support
> device bge # Broadcom BCM570xx Gigabit Ethernet
> device fxp # Intel EtherExpress PRO/100B (82557, 82558)
> device msk # Marvell/SysKonnect Yukon II Gigabit Ethernet
> device loop # Network loopback
> device random # Entropy device
> device ether # Ethernet support
> device ppp # Kernel PPP
> device tun # Packet tunnel.
> device pty # Pseudo-ttys (telnet etc)
> device md # Memory "disks"
> device firmware # firmware assist module
> device bpf # Berkeley packet filter
> device uhci # UHCI PCI->USB interface
> device ehci # EHCI PCI->USB interface (USB 2.0)
> device usb # USB Bus (required)
> device ugen # Generic
> device uhid # "Human Interface Devices"
> device ukbd # Keyboard
> device ulpt # Printer
> device umass # Disks/Mass storage - Requires scbus and da
> device ums # Mouse
>
> It's a slightly modified GENERIC.
>
> Loaded modules:
> Id Refs Address Size Name
> 1 2 0xffffffff80100000 65cb90 kernel
> 2 1 0xffffffff8075d000 f4da0 zfs.ko
>
> The my.cnf:
> # grep '^[\[a-z]' /var/db/mysql/my.cnf
> [client]
> port = 3307
> socket = /tmp/mysql.sock
>
> [mysqld_safe]
> socket = /tmp/mysql.sock
> nice = 0
>
> [mysqld]
> default-character-set = latin2
> default-collation = latin2_hungarian_ci
> user = mysql
> pid-file = /var/run/mysqld/mysqld.pid
> socket = /var/run/mysqld/mysqld.sock
> port = 3306
> basedir = /usr/local
> datadir = /var/db/mysql/
> tmpdir = /tmp
> old_passwords = 1
> bind-address = 10.0.0.1
> key_buffer = 1024M
> max_allowed_packet = 16M
> thread_stack = 512K
> query_cache_limit = 20M
> query_cache_size = 1024M
> query_cache_type = 1
> max_connections = 3600
> thread_cache_size = 200
> wait_timeout = 60
> table_cache = 2048
> sort_buffer = 12M
> join_buffer_size = 12M
> record_buffer = 12M
> read_rnd_buffer_size = 8M
> max_heap_table_size = 128M
> tmp_table_size = 128M
> myisam_sort_buffer_size = 64M
> max_connect_errors = 64
> server-id = 2
> skip-bdb
> skip-innodb
>
> [mysqldump]
> quick
> quote-names
> max_allowed_packet = 16M
>
> [mysql]
>
> [isamchk]
> key_buffer = 16M
>
> The box is a dual opteron 246 with 12GB of memory with 10K RPM
> SATA disks on a 9550 3ware.
>
> So, what can cause this big difference?
>
> the MySQL binary is statically linked, i've built it
> that way.
>
> ldd: /usr/local/libexec/mysqld: not a dynamic executable
>
> Though, maybe I should rebuild it dynamically to ensure it's
> linked against libthr (and not pthread or c_r)...
>
> So, any tips, guesses, anything what can cause this?
>
> Thanks in advance.
>
> Sincerely,
>
> Gergely Czuczy
> mailto: gergely.czuczy at harmless.hu
>
> --
> Weenies test. Geniuses solve problems that arise.
>
More information about the freebsd-performance
mailing list