From owner-freebsd-stable@FreeBSD.ORG Sun Oct 3 01:52:55 2010 Return-Path: Delivered-To: freebsd-stable@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id E5984106564A for ; Sun, 3 Oct 2010 01:52:55 +0000 (UTC) (envelope-from prvs=1892e23437=killing@multiplay.co.uk) Received: from mail1.multiplay.co.uk (mail1.multiplay.co.uk [85.236.96.23]) by mx1.freebsd.org (Postfix) with ESMTP id 4C3B08FC16 for ; Sun, 3 Oct 2010 01:52:55 +0000 (UTC) X-MDAV-Processed: mail1.multiplay.co.uk, Sun, 03 Oct 2010 02:42:24 +0100 X-Spam-Processed: mail1.multiplay.co.uk, Sun, 03 Oct 2010 02:42:23 +0100 X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on mail1.multiplay.co.uk X-Spam-Level: X-Spam-Status: No, score=-5.0 required=6.0 tests=USER_IN_WHITELIST shortcircuit=ham autolearn=disabled version=3.2.5 Received: from r2d2 by mail1.multiplay.co.uk (MDaemon PRO v10.0.4) with ESMTP id md50011345411.msg for ; Sun, 03 Oct 2010 02:42:23 +0100 X-Authenticated-Sender: Killing@multiplay.co.uk X-MDRemoteIP: 188.220.16.49 X-Return-Path: prvs=1892e23437=killing@multiplay.co.uk X-Envelope-From: killing@multiplay.co.uk X-MDaemon-Deliver-To: freebsd-stable@freebsd.org Message-ID: <4EA584AB40D644BFAC3E46787D468CE8@multiplay.co.uk> From: "Steven Hartland" To: "Rumen Telbizov" References: Date: Sun, 3 Oct 2010 02:42:22 +0100 MIME-Version: 1.0 X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: freebsd-stable@freebsd.org Subject: Re: MySQL performance concern X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 03 Oct 2010 01:52:56 -0000 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=3D37092,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 -----=20 From: Rumen Telbizov=20 To: Steven Hartland=20 Cc: freebsd-stable@freebsd.org=20 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 =3D 8192M max_allowed_packet =3D 16M table_open_cache =3D 2048 sort_buffer_size =3D 64M read_buffer_size =3D 8M read_rnd_buffer_size =3D 16M myisam_sort_buffer_size =3D 256M thread_cache_size =3D 64 query_cache_size =3D 32M thread_concurrency =3D 8 max_heap_table_size =3D 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 =3D 4G max_heap_table_size =3D 6G max_allowed_packet =3D 1M table_cache =3D 64 sort_buffer_size =3D 512K net_buffer_length =3D 8K read_buffer_size =3D 256K read_rnd_buffer_size =3D 512K myisam_sort_buffer_size =3D 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 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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.=20 In the event of misdirection, illegible or incomplete transmission please telephone +44 845 868 1337 or return the E.mail to postmaster@multiplay.co.uk.