From owner-freebsd-stable@FreeBSD.ORG Sat Oct 2 20:18:21 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 5CE4E1065672 for ; Sat, 2 Oct 2010 20:18:21 +0000 (UTC) (envelope-from telbizov@gmail.com) Received: from mail-qy0-f182.google.com (mail-qy0-f182.google.com [209.85.216.182]) by mx1.freebsd.org (Postfix) with ESMTP id 1607B8FC18 for ; Sat, 2 Oct 2010 20:18:20 +0000 (UTC) Received: by qyk33 with SMTP id 33so2324269qyk.13 for ; Sat, 02 Oct 2010 13:18:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=m8XrcdzOUcQfo2D+NeZe99as+VNnBOWOZqCwSeaj+o8=; b=NdQXZS6qQimUuR+FUxYSJMOBKQAy4PvcGxQJ87bagEVra26nL18ZBLT2bB1bJioN9o Ggq1i89dk2z7JaltvicJn40HSTyf+0V70j4o2jAPZd1z8jKo1T3UeLw/JNx/+AlculMN mbBSiDtzYXe8KPykQy8ft3Xo0ccRhphb/nPuo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=TSlNMPi4gqH405QgTqo+BEgon2OtSNBp7yKn2N5mjj424ve2q267DiwUn3Wq9M8rzN xI5Hd+oVf34KBYBIgjYO0hVyRuFzIIXgQbOmp3iWsp4YlnxPzYMvyE/6abZAhWtNcDl6 qxjQRsaTYvM2J0aCQZ4UKTMzf3idLDBxbVEWc= MIME-Version: 1.0 Received: by 10.229.240.76 with SMTP id kz12mr5390911qcb.65.1286050700405; Sat, 02 Oct 2010 13:18:20 -0700 (PDT) Received: by 10.229.191.132 with HTTP; Sat, 2 Oct 2010 13:18:20 -0700 (PDT) In-Reply-To: References: Date: Sat, 2 Oct 2010 13:18:20 -0700 Message-ID: From: Rumen Telbizov To: Steven Hartland Content-Type: text/plain; charset=ISO-8859-1 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: Sat, 02 Oct 2010 20:18:21 -0000 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