mysql scaling questions
Gergely CZUCZY
phoemix at harmless.hu
Sat Dec 1 09:04:12 PST 2007
On Fri, Nov 30, 2007 at 02:31:50PM -1000, Jeff Roberson wrote:
> 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?
Answering the questions:
I am using sysbench, with read-write tests.
The test-table has 10M tuples, and i'm querying it for two minutes.
On Sat, Dec 01, 2007 at 04:41:58PM +0100, Miroslav Lachman wrote:
>
> As Mike Tancsa suggested - rebuild MySQL dynamic and use these values in /etc/libmap.conf
>
> # /etc/libmap.conf
> #
> # candidate mapping
> #
> [/usr/local/libexec/mysqld]
> libpthread.so.2 libthr.so.2
> libpthread.so libthr.so
>
> Miroslav Lachman
no need for this, since on 7 libthr is the default threading
implementation.
Now, some test results. The numbers show Queries/Second and in
order they correspond to the following number of threads:
1 2 4 8 16 32 64 128 192 256 384 512
fbsd7_ufs_TSC_rw_plot:
1891.74 1935.85 1630.94 1966.75 1816.05 1700.58 1590.84 1536.18 1533.99 1526.05 1520.00 1464.44
fbsd7_ufs_rw_plot:
1740.98 1605.12 1520.87 1548.42 1566.22 1465.80 1464.17 1420.33 1384.02 1433.44 1489.23 1445.75
(ZM stands for zmirror with 2 drives)
fbsd7_zfs_zm_rw_plot:
333.62 297.55 265.34 312.15 315.67 241.05 200.08 177.46 162.11 147.02 146.52 125.72
(cdb2 means a setup we have. It's mysql 5.0.22)
linux26_cdb2_rw_plot:
2013.59 2591.36 3515.02 3629.28 3129.89 3139.59 2747.31
linux26_mysql5041_rw_plot:
3415.20 4193.24 4670.92 4619.93 4288.13 3942.70 3591.34 3192.13 2966.72 2855.82 2580.51 2405.44
(This one is still in progress)
linux26_mysql5041_tcmalloc_rw_plot:
2916.28 3551.13 3728.86 3856.79 3866.78 3707.94 3440.99 3251.88 3180.07 3070.65 2937.17
The testbox has a dual opteron 246, 12G of memory and a 3ware-9550 with
two disks in a mirror for the rootfs (also this is where the mysql datafiles
were, except for the ZFS setup). The freebsd has mysql-5.0.45_1.
As you see for the best case linux outperforms fbsd7 around twice,
this means that i couldn't reproduce jeff@'s results.
>
> 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.
> >
>
>
>
>
>
>
>
>
>
Sincerely,
Gergely Czuczy
mailto: gergely.czuczy at harmless.hu
--
Weenies test. Geniuses solve problems that arise.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 6719 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20071201/4d3ce7c2/attachment.pgp
More information about the freebsd-performance
mailing list