MySQL performance concern

[Steven Hartland]

You similar hardware specs are hardly similar an 8 core 2.3Ghz box vs a 4 core 1.8Ghz according to Intel cpu comparison:-
http://ark.intel.com/Compare.aspx?ids=37092,33080,

If you want to compare you really need to do so on the same hardware or all bets are off.

    Regards
    Steve
  ----- Original Message ----- 
  From: Rumen Telbizov 
  To: Steven Hartland 
  Cc: freebsd-stable at freebsd.org 
  Sent: Saturday, October 02, 2010 9:18 PM
  Subject: Re: MySQL performance concern


  Hello everyone,

  Here's the requested information below:

  FreeBSD mysql 5.1.51:

  my.cnf:
  skip-external-locking
  key_buffer_size = 8192M
  max_allowed_packet = 16M
  table_open_cache = 2048
  sort_buffer_size = 64M
  read_buffer_size = 8M
  read_rnd_buffer_size = 16M
  myisam_sort_buffer_size = 256M
  thread_cache_size = 64
  query_cache_size = 32M
  thread_concurrency = 8
  max_heap_table_size = 6G

  hardware:
  FreeBSD 8.1-STABLE amd64 (Tue Sep 14 15:29:22 PDT 2010) running on a SuperMicro machine with X8DTU motherboard
  and 2 x Dual Core Xeon E5502 1.87Ghz ; 4 x SAS 15K in RAID10 setup under ZFS (two mirrored pairs) and 2 x SSD X25-E partitioned
  for: 8G for ZIL and the rest for L2ARC; 16G RAM.  Disk controller is LSI 4Hi in IT (Initiator Target) mode.

  -- Linux Gentoo (2.6.18-164.10.1.el5.028stab067.4) mysql 5.1.50 --

  my.cnf:
  skip-external-locking
  key_buffer                  = 4G
  max_heap_table_size         = 6G
  max_allowed_packet          = 1M
  table_cache                 = 64
  sort_buffer_size            = 512K
  net_buffer_length           = 8K
  read_buffer_size            = 256K
  read_rnd_buffer_size        = 512K
  myisam_sort_buffer_size     = 8M

  Linux runs as an OpenVZ VE inside CentOS. It's the only VE and has all the memory allocated to it

  hardware node:
  2 x Xeon Quad E5410 @ 2.33GHz on SuperMicro X7DBU motherboard; 16G RAM; 4 SATA 1T disks in hardware raid 5 attached
  to a 3ware controller; NO SSDs

  Some other notes:
   * It is indeed a single thread which inserts into the mysql so yes it's only one core which handles the application and another one for MySQL. What is interesting here, like I mentioned, is that on FreeBSD mysql process doesn't get more than 30-40% CPU utilization. So it has a lot of headroom. gstat also shows 0% disk load
   * It is exactly the same database schema. In fact it's only one table that's inserted heavily into. It is a partition table with only one HASH index which looks something like this: PRIMARY KEY (`IntField`,`DateField`,`Varchar150Field`) USING HASH. The speed difference is obvious right from the beginning. I don't have to wait for any data to accrue to see a degradation. I don't wait for more than a 100'000 records to be processed.
   * Application maintains only 1 local TCP connection to mysql. They both run on the same host
   * As for the ZFS. Here's the pool configuration:

    pool: tank
  config:

          NAME           STATE     READ WRITE CKSUM
          tank           ONLINE       0     0     0
            mirror       ONLINE       0     0     0
              gpt/tank0  ONLINE       0     0     0
              gpt/tank1  ONLINE       0     0     0
            mirror       ONLINE       0     0     0
              gpt/tank2  ONLINE       0     0     0
              gpt/tank3  ONLINE       0     0     0
          logs           ONLINE       0     0     0
            mirror       ONLINE       0     0     0
              gpt/zil0   ONLINE       0     0     0
              gpt/zil1   ONLINE       0     0     0
          cache
            gpt/l2arc0   ONLINE       0     0     0
            gpt/l2arc1   ONLINE       0     0     0

    pool: zroot
  config:

          NAME            STATE     READ WRITE CKSUM
          zroot           ONLINE       0     0     0
            mirror        ONLINE       0     0     0
              gpt/zroot0  ONLINE       0     0     0
              gpt/zroot1  ONLINE       0     0     0


  zroot is a couple of small partitions from two of the same SAS disks. zil and l2arc are 8 and 22G partitions from 32G SSDs

  I pretty much have no zfs tuning done since from what I've found there shouldn't be any needed since I'm running 8.1 on a 64bit machine.
  Let me know if you'd like me to experiment with any ...

  Some additional information:
  # sysctl vm.kmem_size
  vm.kmem_size: 5539958784
  # sysctl vm.kmem_size_max
  vm.kmem_size_max: 329853485875
  # sysctl vfs.zfs.arc_max
  vfs.zfs.arc_max: 4466216960

  I think this answers all the questions so far.
  Let me know what you think. I might be missing something obvious.

  Thank you,
  Rumen Telbizov


================================================
This e.mail is private and confidential between Multiplay (UK) Ltd. and the person or entity to whom it is addressed. In the event of misdirection, the recipient is prohibited from using, copying, printing or otherwise disseminating it or any information contained in it. 

In the event of misdirection, illegible or incomplete transmission please telephone +44 845 868 1337
or return the E.mail to postmaster at multiplay.co.uk.