MySQL performance concern

[Rumen Telbizov]

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