Date: Sat, 2 Oct 2010 13:18:20 -0700 From: Rumen Telbizov <telbizov@gmail.com> To: Steven Hartland <killing@multiplay.co.uk> Cc: freebsd-stable@freebsd.org Subject: Re: MySQL performance concern Message-ID: <AANLkTi=WmvuDEkzbLZQ8BRLpCZtq8R8jKgjPUZmFc4fy@mail.gmail.com> In-Reply-To: <ACD11509724249559BD89DDD26F67C62@multiplay.co.uk> References: <AANLkTikGHByF0dJ-hj6zPoRhV6YoHGSrW3g0audama3M@mail.gmail.com> <ACD11509724249559BD89DDD26F67C62@multiplay.co.uk>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?AANLkTi=WmvuDEkzbLZQ8BRLpCZtq8R8jKgjPUZmFc4fy>