From owner-freebsd-performance@FreeBSD.ORG Fri May 11 17:15:40 2007 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id EBF6D16A403 for ; Fri, 11 May 2007 17:15:39 +0000 (UTC) (envelope-from mksmith@adhost.com) Received: from mail-defer01.adhost.com (mail-defer01.adhost.com [216.211.128.150]) by mx1.freebsd.org (Postfix) with ESMTP id CF89713C45A for ; Fri, 11 May 2007 17:15:39 +0000 (UTC) (envelope-from mksmith@adhost.com) Received: from mail-in07.adhost.com (mail-in07.adhost.com [10.211.128.140]) by mail-defer01.adhost.com (Postfix) with ESMTP id B8996EC62D for ; Fri, 11 May 2007 09:43:46 -0700 (PDT) (envelope-from mksmith@adhost.com) Received: from ad-exh01.adhost.lan (unknown [216.211.143.69]) by mail-in07.adhost.com (Postfix) with ESMTP id 7CD6E1B5048; Fri, 11 May 2007 09:43:46 -0700 (PDT) (envelope-from mksmith@adhost.com) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Fri, 11 May 2007 09:43:34 -0700 Message-ID: <17838240D9A5544AAA5FF95F8D52031602018EFA@ad-exh01.adhost.lan> In-Reply-To: <46443103.9010008@otel.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: 6.2 Stable + Mysql 5.0 poor performance Thread-Index: AceT6b6um4N+hXNmQ92Bh3Q3dXWOqQAARwPg References: <46443103.9010008@otel.net> From: "Michael K. Smith - Adhost" To: "ivo tasev" , Cc: Subject: RE: 6.2 Stable + Mysql 5.0 poor performance X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 11 May 2007 17:15:40 -0000 Hello Taseff: > -----Original Message----- > From: owner-freebsd-performance@freebsd.org [mailto:owner-freebsd- > performance@freebsd.org] On Behalf Of ivo tasev > Sent: Friday, May 11, 2007 2:02 AM > To: freebsd-performance@freebsd.org > Subject: 6.2 Stable + Mysql 5.0 poor performance >=20 > Hi all, > i have machine with 2 x Quad Core Xeon cpu`s 2G ram and 4 3ware disks > in > raid10. There is 6.2 Stable on it and mysql 5.0 from the ports. >=20 > The mysql is installed with the following build options: > WITH_XCHARSET=3Dall WITH_OPENSSL=3Dyes BUILD_OPTIMIZED=3Dyes WITH_ARCHIVE=3Dyes > WITH_FEDERATED=3Dyes WITH_NDB=3Dyes >=20 > I`m using libmap.conf with this in it: >=20 > [mysqld] > libpthread.so.2 libthr.so.2 >=20 > This is what I have in my.cnf : >=20 > set-variable =3D key_buffer=3D1024M > set-variable =3D max_allowed_packet=3D64M > set-variable =3D thread_stack=3D1024K > set-variable =3D read_buffer_size=3D2M > set-variable =3D read_buffer_size=3D2M >=20 > set-variable =3D max_connections=3D350 > set-variable =3D interactive_timeout=3D100 > set-variable =3D wait_timeout=3D120 > set-variable =3D max_user_connections=3D340 >=20 > set-variable =3D query_cache_limit=3D1M > set-variable =3D query_cache_size=3D32M > set-variable =3D query_cache_type=3D1 >=20 > set-variable =3D table_cache=3D1024 > set-variable =3D thread_cache=3D128 > set-variable =3D thread_cache_size=3D40 > set-variable =3D thread_concurrency=3D16 >=20 > I`m performing the following test with super-smack: > :~# time super-smack -d mysql > /usr/local/share/super-smack/select-key.smack 10 10000 >=20 > the results are:Query Barrel Report for client smacker1 > connect: max=3D1ms min=3D0ms avg=3D 0ms from 10 clients > Query_type num_queries max_time min_time q_per_s > select_index 200000 0 0 18599.71 >=20 > I can achieve better performance even on my colleague`s notebook with > 6.2 stable !? >=20 > I`ll be very thankful if someone can give me any ideas:) >=20 Here are some system tweaks that may help. /etc/sysctl.conf kern.threads.max_groups_per_proc=3D40000 kern.threads.max_threads_per_proc=3D40000 kern.maxfiles=3D65535 kern.maxfilesperproc=3D65535 /etc/libmap.conf [mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so /boot/loader.conf kern.maxdsiz=3D"1073741824" # 1GB kern.dfldsiz=3D"1073741824" # 1GB kern.maxssiz=3D"134217728" # 128MB Mike