Re: mysql scaling questions

classic Classic list List threaded Threaded
63 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Jeff Roberson
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     [hidden email]:/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: [hidden email]
>
> --
> Weenies test. Geniuses solve problems that arise.
>
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 07:31 PM 11/30/2007, Jeff Roberson wrote:

>>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?

I would make it dynamic instead of static.  I seem to recall this
issue in the past

         ---Mike

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Miroslav Lachman
In reply to this post by Jeff Roberson
Jeff Roberson wrote:
[...]

>> 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?
>>

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
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
In reply to this post by Jeff Roberson
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    
> >[hidden email]:/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: [hidden email]
> >
> >--
> >Weenies test. Geniuses solve problems that arise.
> >
>
>
>
>
>
>
>
>
>
Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 11:33 AM 12/1/2007, Gergely CZUCZY wrote:
> > >
> > >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?

Are the caching options for the 3ware the same on FreeBSD as Linux ?

         ---Mike

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
On Sat, Dec 01, 2007 at 03:53:08PM -0500, Mike Tancsa wrote:
> At 11:33 AM 12/1/2007, Gergely CZUCZY wrote:
> >> >
> >> >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?
>
> Are the caching options for the 3ware the same on FreeBSD as Linux ?
I don't quite understand the question. It's the very same box, with
a dualboot configuration.

Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (948 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
>I don't quite understand the question. It's the very same box, with
>a dualboot configuration.

Fire up the 3ware controller's RAID management software and make sure
the same write caching strategy is set for FreeBSD and Linux. The
driver my default to different values.

i.e. under "controller settings" make sure "write cache" and
"queuing" are the same values for linux and freebsd.

         ---Mike

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> >I don't quite understand the question. It's the very same box, with
> >a dualboot configuration.
>
> Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The
> driver my default to different values.
>
> i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
Let's get back to this on monday. I'm at home now, and the
box is at me workplace, still running a test (i can't reboot it).

Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Jeff Roberson
On Sat, 1 Dec 2007, Gergely CZUCZY wrote:

> On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
>> At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
>>> I don't quite understand the question. It's the very same box, with
>>> a dualboot configuration.
>>
>> Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The
>> driver my default to different values.
>>
>> i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
> Let's get back to this on monday. I'm at home now, and the
> box is at me workplace, still running a test (i can't reboot it).

Also, can you verify with a read-only test to see where it's at?  I have
not tested writes with that many threads.  I notice mysql goes much faster
with a fresh table too.  So can you blow away and recreate the sysbench
tables and then do read-only?  If that is much slower we'll know there is
some configuration problem or similar.

Thanks,
Jeff


>
> Sincerely,
>
> Gergely Czuczy
> mailto: [hidden email]
>
> --
> Weenies test. Geniuses solve problems that arise.
>
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Boris Samorodov
In reply to this post by Gergely CZUCZY
On Sat, 1 Dec 2007 17:33:34 +0100 Gergely CZUCZY wrote:

> I am using sysbench, with read-write tests.
> The test-table has 10M tuples, and i'm querying it for two minutes.
[...]
> 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.

3ware driver is under GIANT at 7.x. I don't know if it's the same for
linux.


WBR
--
Boris Samorodov (bsam)
Research Engineer, http://www.ipt.ru Telephone & Internet SP
FreeBSD committer, http://www.FreeBSD.org The Power To Serve
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Manjunath Ranganathaiah
On 12/1/07, Boris Samorodov <[hidden email]> wrote:
>
>
> 3ware driver is under GIANT at 7.x. I don't know if it's the same for
> linux.



It is not under GIANT any more, MPSAFE starting from 7.0 BETA1.

-Manjunath
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Peter Losher
Manjunath R Gowda wrote:
> On 12/1/07, Boris Samorodov <[hidden email]> wrote:
>>
>> 3ware driver is under GIANT at 7.x. I don't know if it's the same for
>> linux.
>
> It is not under GIANT any more, MPSAFE starting from 7.0 BETA1.

I know in one case on a box running BETA2 the kernel dmesg reported that
the twe driver was both GIANT and MPSAFE... (dunno if that has been
fixed in BETA3 yet, I should check...)

-Peter
--
[hidden email] | ISC | OpenPGP 0xE8048D08 | "The bits must flow"


signature.asc (193 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 08:54 PM 12/1/2007, Peter Losher wrote:

>Manjunath R Gowda wrote:
> > On 12/1/07, Boris Samorodov <[hidden email]> wrote:
> >>
> >> 3ware driver is under GIANT at 7.x. I don't know if it's the same for
> >> linux.
> >
> > It is not under GIANT any more, MPSAFE starting from 7.0 BETA1.
>
>I know in one case on a box running BETA2 the kernel dmesg reported that
>the twe driver was both GIANT and MPSAFE... (dunno if that has been
>fixed in BETA3 yet, I should check...)

Hi,
         I think the card in question is twa in this case.

On a RELENG_7 box from the other day, it shows up as

3ware device driver for 9000 series storage controllers, version: 3.70.05.001
twa0: <3ware 9000 series Storage Controller> port 0x3000-0x30ff mem
0x88000000-0x89ffffff,0x8a200000-0x8a200fff irq 16 at device 0.0 on pci2
twa0: [ITHREAD]
twa0: INFO: (0x15: 0x1300): Controller details:: Model 9650SE-2LP, 2
ports, Firmware FE9X 3.06.00.005, BIOS BE9X 3.06.00.002
da0 at twa0 bus 0 target 0 lun 0
da0: <AMCC 9650SE-2LP DISK 3.06> Fixed Direct Access SCSI-5 device
da0: 100.000MB/s transfers
da0: 76283MB (156227584 512 byte sectors: 255H 63S/T 9724C)

         ---Mike

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Boris Samorodov
In reply to this post by Manjunath Ranganathaiah
On Sat, 1 Dec 2007 17:48:24 -0800 Manjunath R Gowda wrote:
> On 12/1/07, Boris Samorodov <[hidden email]> wrote:

> > 3ware driver is under GIANT at 7.x. I don't know if it's the same for
> > linux.

> It is not under GIANT any more, MPSAFE starting from 7.0 BETA1.

Wow, that's great news! Thanks.


WBR
--
Boris Samorodov (bsam)
Research Engineer, http://www.ipt.ru Telephone & Internet SP
FreeBSD committer, http://www.FreeBSD.org The Power To Serve
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Peter Losher
In reply to this post by mdtancsa
Mike Tancsa wrote:

>         I think the card in question is twa in this case.

Not in our case...

-=-
twe0: <3ware Storage Controller. Driver version 1.50.01.002> port
0x9c00-0x9c0f mem 0xfb6ffc00-0xfb6ffc0f,0xfa800000-0xfaffffff irq 29 at
device 3.0 on pci1
twe0: [GIANT-LOCKED]
twe0: [ITHREAD]
twe0: 2 ports, Firmware FE8S 1.05.00.068, BIOS BE7X 1.08.00.048
twed0: <Unit 0, TwinStor, Normal> on twe0
twed0: 305244MB (625140400 sectors)
Trying to mount root from ufs:/dev/twed0s1a
-=-

Best Wishes - Peter
--
[hidden email] | ISC | OpenPGP 0xE8048D08 | "The bits must flow"


signature.asc (193 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 04:10 PM 12/2/2007, Peter Losher wrote:
>Mike Tancsa wrote:
>
> >         I think the card in question is twa in this case.
>
>Not in our case...

Sorry, I was referring to the original posters card
http://lists.freebsd.org/pipermail/freebsd-performance/2007-November/002942.html

>The box is a dual opteron 246 with 12GB of memory with 10K RPM
>SATA disks on a 9550 3ware.



_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
In reply to this post by mdtancsa
On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> >I don't quite understand the question. It's the very same box, with
> >a dualboot configuration.
>
> Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The driver my
> default to different values.
>
> i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
I fail to see such an option, not under the 3ware BIOS software, nor in the tw_cli interface.

Could you be more specific on this?

Note: in the BIOS i've seen WC turned on for all units, and there were no
OS-specific options.

Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

mdtancsa
At 04:22 AM 12/4/2007, Gergely CZUCZY wrote:

>On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> > At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> > >I don't quite understand the question. It's the very same box, with
> > >a dualboot configuration.
> >
> > Fire up the 3ware controller's RAID management software and make
> sure the same write caching strategy is set for FreeBSD and Linux.
> The driver my
> > default to different values.
> >
> > i.e. under "controller settings" make sure "write cache" and
> "queuing" are the same values for linux and freebsd.
>I fail to see such an option, not under the 3ware BIOS software, nor
>in the tw_cli interface.
>
>Could you be more specific on this?
>
>Note: in the BIOS i've seen WC turned on for all units, and there were no
>OS-specific options.


Hi,
         Its not in the bios, but in the management daemon.  On
FreeBSD, go to /usr/ports/sysutils/3dm and install the daemon.  Start
up 3dm2, point your browser to https://127.0.0.1:888  and login.  Go
to management menu and look at "controller settings" and there will
be the values of write cache and queuing and storsav.  Make sure the
values set there, are the same on Linux and FreeBSD.  You can
download the LINUX version from 3ware's website.

         ---Mike

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
On Tue, Dec 04, 2007 at 04:40:55AM -0500, Mike Tancsa wrote:

> At 04:22 AM 12/4/2007, Gergely CZUCZY wrote:
> >On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> >> At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> >> >I don't quite understand the question. It's the very same box, with
> >> >a dualboot configuration.
> >>
> >> Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The driver my
> >> default to different values.
> >>
> >> i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
> >I fail to see such an option, not under the 3ware BIOS software, nor in the tw_cli interface.
> >
> >Could you be more specific on this?
> >
> >Note: in the BIOS i've seen WC turned on for all units, and there were no
> >OS-specific options.
>
>
> Hi,
>         Its not in the bios, but in the management daemon.  On FreeBSD, go to /usr/ports/sysutils/3dm and install the daemon.  Start up 3dm2, point
> your browser to https://127.0.0.1:888  and login.  Go to management menu and look at "controller settings" and there will be the values of write cache
> and queuing and storsav.  Make sure the values set there, are the same on Linux and FreeBSD.  You can download the LINUX version from 3ware's website.
Thank you very much, I've made it worked. In both FreeBSD and Linux the write cache seems to be turned on in the web-based management. However, I still don't think this is OS-specific, since I see no OS-specific options, and 3ware makes the devices available through SCSI, and WC is handled differently there.



Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql scaling questions

Gergely CZUCZY
In reply to this post by Jeff Roberson
On Sat, Dec 01, 2007 at 12:22:34PM -1000, Jeff Roberson wrote:

> On Sat, 1 Dec 2007, Gergely CZUCZY wrote:
>
> >On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> >>At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> >>>I don't quite understand the question. It's the very same box, with
> >>>a dualboot configuration.
> >>
> >>Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The
> >>driver my default to different values.
> >>
> >>i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
> >Let's get back to this on monday. I'm at home now, and the
> >box is at me workplace, still running a test (i can't reboot it).
>
> Also, can you verify with a read-only test to see where it's at?  I have not tested writes with that many threads.  I notice mysql goes much faster
> with a fresh table too.  So can you blow away and recreate the sysbench tables and then do read-only?  If that is much slower we'll know there is some
> configuration problem or similar.
It will all be available here:
http://phoemix.harmless.hu/mysql/

Some notes.

With the ZFS tests, mysql seems to be a lot in zfs(&: state in top, and
vmstate shows lots of the CPU spent in system:
 r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy   cs us sy id
(32 threads)
 5 0 0 2904868 8563836  7259   0   0   0  7783   0   0   0 1009 33097 24196 17 24 59
32 0 0 2921252 8565732  7445   0   0   0  7810   0   0   3 1579 48135 25277 19 80  1
 6 0 0 3167012 8563304  7731   0   0   0  7789   0   0   0 1581 49608 24088 20 79  1
 7 0 0 2861860 8564460  7226   0   0   0  7427   0   0   0 1547 47430 25276 17 82  1
 7 0 0 2968356 8563624  7591   0   0   0  7752   0   2   0 1588 48899 23958 20 80  1
32 0 0 2984740 8562660  7495   0   0   0  7914   0   0   8 1583 48698 25508 17 82  1
26 0 0 3040036 8563708  6852   0   0   0  7035   0   0   0 1446 44358 25176 18 82  1
(64 threads)
 5 0 0 3646244 8549136  6322   0   0   0  6552   0   0   0 1368 41438 30397 17 83  0
47 0 0 3908388 8547924  6425   0   0   0  6525   0   0   0 1395 41779 33059 18 81  1
65 0 0 3748644 8548356  6507   0   0   0  6689   0   0   0 1426 42855 29754 18 82  0
57 0 0 3785508 8549040  6452   0   0   0  6583   0   0   0 1390 42103 30140 18 81  1
 8 0 0 4180772 8547492  6480   0   0   0  6604   0   0   0 1426 42261 30397 15 84  1

So on.
"zpool iostat" shows no activty on the zm pool i have, only occasionally 1-3K in 5sec
intervals, that's nothing. So I think everything is returned from the fscache/zfs cache.
I've increased vm.kmem_size a bit to fit for zfs:
vm.kmem_size: 1073741824

The test hasn't yet finished, but it still seems to have a very poor performance:
1        2      4      8      16      32    64    threads
436.83 1038.33 879.85 826.63 757.92 969.31 845.84 qps
(this is the read-only, keep in mind)
With UFS:
1926.87 2172.59 2093.41 2478.06 2577.58 2543.55 2341.46 2166.81 2026.50 1891.09 1753.52 1647.64
and the linux-2.6.19.2+mysql-5.0.41+tcmalloc:
3431.56 4135.05 4984.12 5487.01 5448.19 5354.64 5226.64 5113.96 5011.94 4705.62 4362.06 3996.42

vmstat when running the test on UFS:
 procs      memory      page                    disks     faults      cpu
 r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy   cs us sy id
(8 threads)
 7 0 0 2385660 9399000 19128   0   0   0 19601   0   0   0 3235 123806 43490 37 61  2
 8 0 0 2461436 9399180 18975   0   0   0 19468   0   0   0 3213 122856 51389 39 60  1
 6 0 0 2410236 9399508 19141   0   0   0 19706   0   0   0 3230 123783 50353 38 61  2
 5 0 0 2348796 9399744 19273   0   0   0 19817   0   0   0 3272 124558 51281 38 60  2
(16 threads)
14 0 2 2664228 9393172 19988   0   0   0 20462   0   0   0 3148 123556 17475 35 65  0
 9 0 0 2666276 9393004 20146   0   0   0 20661   0   0   0 3231 125252 17340 37 63  0
16 0 0 2596644 9394436 20157   0   0   0 20704   0   0   0 3204 124366 17421 38 62  0
 9 0 0 2590500 9394556 19712   0   0   0 20197   0   0   0 3113 122209 17610 36 64  0
(32 threads)
30 0 0 2930468 9386688 19357   0   0   0 19919   0   0   0 3096 120375 18285 39 61  0
26 0 0 2760484 9386848 19372   0   0   0 19913   0   0   0 3112 121284 18020 39 60  0
10 0 0 2908964 9385772 19238   0   0   0 19672   0   1   0 3019 119013 18037 35 64  0
17 0 0 2981668 9384308 19265   0   0   0 19715   0   0   0 3088 120462 18040 39 61  0
(64 threads)
43 1 0 3662632 9372396 18201   0   0   0 18612   0   0   0 2864 113344 20063 38 62  0
18 0 0 4131624 9372004 17703   0   0   0 18172   0   0   0 2808 110922 21348 36 64  0
58 0 0 3562280 9374428 18016   0   0   0 18593   0   0   0 2840 111615 21078 36 64  0
58 0 0 3990312 9375276 17834   0   0   0 18361   0   0   0 2886 112559 20662 38 61  0

There was around 20% of CPU time spent in system state when mysql was running off
a ZFS filesystem, then a UFS one. And also, there were more context switches, but less
system caalls and interrupts.


So, the result is basically the same as in the RW case.

Where should I start investigating this issue?
May I try again with the 4BSD scheduler? Currentl as you can see, I'm using
the new ULE one.

Sincerely,

Gergely Czuczy
mailto: [hidden email]

--
Weenies test. Geniuses solve problems that arise.

attachment0 (3K) Download Attachment
1234