From owner-freebsd-database@FreeBSD.ORG Tue Nov 15 07:57:06 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id D197016A41F for ; Tue, 15 Nov 2005 07:57:06 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0622243D53 for ; Tue, 15 Nov 2005 07:57:05 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: by wproxy.gmail.com with SMTP id i31so1152564wra for ; Mon, 14 Nov 2005 23:57:05 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=SVln6foxu+xrckgpn1MN3Afc2Kjni++Pf/eZAYRn4O3z8bgJup7L6Sl6DCMRD9FeC9qjrQHm9A7hXH1fDOGpVnGrmX5E3vdq+8iihM0CG+5wa0Ag1AACam4MZ+Oi/URNLkheuvjJTIwV+3HhL3HEn3dsRy29BT0bmDadavo11vg= Received: by 10.54.76.9 with SMTP id y9mr3958499wra; Mon, 14 Nov 2005 23:57:04 -0800 (PST) Received: by 10.54.72.2 with HTTP; Mon, 14 Nov 2005 23:57:04 -0800 (PST) Message-ID: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> Date: Tue, 15 Nov 2005 02:57:04 -0500 From: Ashok Shrestha To: freebsd-database@freebsd.org, freebsd-performance@freebsd.org MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ?? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 15 Nov 2005 07:57:06 -0000 I'm using MySQL 5.0.15 with FreeBSD 5.4-RELEASE amd64. The box specs: CPU: Dual Core AMD Opteron(tm) Processor 275 (2193.76-MHz K8-class CPU) Memory: 4 GB My databases will be anywhere from 2GB-20GB. I would like to get the maximum performance out of this box. What options (MAKE, kernel, etc...) should I use to achieve this? What about loading the INDEX file into memory? Should I build WITH_LINUXTHREADS? Is it still necessary to do so on FreeBSD 5.4? I know it is better on a 4.X machine. Note: It is not possible to buil= d WITH_LINUXTHREADS on a FreeBSD 5.4 AMD64. What are the advantages of building with (MAKE) BUILD_OPTIMIZED? Is there an advantage in using a 64bit FreeBSD? How should I configure the following in /etc/loader.conf? I could not find an authoritative definition any of these variables; do you know of any? kern.maxdsiz=3D"3221225472" # 3GB; should it be closer to 4GB? kern.dfldsiz=3D"3221225472" # 3GB; should it be closer to 4GB? kern.maxssiz=3D"134217728" # 128MB; what is this? stack size? how large sho= uld I make it? -- Ashok Shrestha From owner-freebsd-database@FreeBSD.ORG Tue Nov 15 13:26:11 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id DB7B116A41F for ; Tue, 15 Nov 2005 13:26:11 +0000 (GMT) (envelope-from joseph.koshy@gmail.com) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.194]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6FA4543D46 for ; Tue, 15 Nov 2005 13:26:11 +0000 (GMT) (envelope-from joseph.koshy@gmail.com) Received: by xproxy.gmail.com with SMTP id s9so830961wxc for ; Tue, 15 Nov 2005 05:26:11 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Itq4WoQ4YAP63hJPYN9eJnSx7R1rOWlGKhePIx0O7+vAI/DmEbZ7lQderYn0ZolI1mGt4xp1WNbgt5izjgf3u2iSl7DnngcJG6a5NaEmoUlPE6vLPqcsUXLHbhf6MplE3LdaALlItTy9wyyOdj7QfcwIgjgI9XwjnzIvn1pBqK8= Received: by 10.70.54.2 with SMTP id c2mr2739510wxa; Tue, 15 Nov 2005 05:18:56 -0800 (PST) Received: by 10.70.105.13 with HTTP; Tue, 15 Nov 2005 05:18:56 -0800 (PST) Message-ID: <84dead720511150518m250e073bgdbf9afe2dd4a3894@mail.gmail.com> Date: Tue, 15 Nov 2005 18:48:56 +0530 From: Joseph Koshy To: Ashok Shrestha In-Reply-To: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> Cc: freebsd-database@freebsd.org, freebsd-performance@freebsd.org Subject: Re: HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ?? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 15 Nov 2005 13:26:12 -0000 On 11/15/05, Ashok Shrestha wrote: > I'm using MySQL 5.0.15 with FreeBSD 5.4-RELEASE amd64. > I would like to get the maximum performance out of this box. > What options (MAKE, kernel, etc...) should I use to achieve > this? What about loading the INDEX file into memory? You might want to take a look at: http://wikitest.freebsd.org/moin.cgi/MySQL -- FreeBSD Volunteer, http://people.freebsd.org/~jkoshy From owner-freebsd-database@FreeBSD.ORG Thu Nov 17 17:58:00 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 18C7B16A41F for ; Thu, 17 Nov 2005 17:58:00 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7FE0E43D46 for ; Thu, 17 Nov 2005 17:57:59 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: by wproxy.gmail.com with SMTP id i20so1775934wra for ; Thu, 17 Nov 2005 09:57:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=AzPUKpFc/jTPnhU1XOuRKAt3He2sn45qxXdGfYfC6fLSFbk3Jr46sMBQmjOc4wnyB2spVG3WxGxzFm5bx1hEkyN72hSKvO3Nw39Sqm5Os3mLZQIXKn1kgxsWcgiuXrj6APwqM7g7EsJCX4kbuaN0wMOtiAq9FpyJZb+BrXKiuXg= Received: by 10.54.101.18 with SMTP id y18mr7312103wrb; Thu, 17 Nov 2005 09:57:58 -0800 (PST) Received: by 10.54.72.2 with HTTP; Thu, 17 Nov 2005 09:57:58 -0800 (PST) Message-ID: <79e2026f0511170957l75c97958j33c278954cfc0b7c@mail.gmail.com> Date: Thu, 17 Nov 2005 12:57:58 -0500 From: Ashok Shrestha To: Joseph Koshy In-Reply-To: <84dead720511150518m250e073bgdbf9afe2dd4a3894@mail.gmail.com> MIME-Version: 1.0 References: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> <84dead720511150518m250e073bgdbf9afe2dd4a3894@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: freebsd-database@freebsd.org Subject: Re: HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ?? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 17 Nov 2005 17:58:00 -0000 Thanx. I wanted to add more info to the wiki, but it appears that only developers are allowed to make changes. =3D( Do you know if this wiki will turn more public? On 11/15/05, Joseph Koshy wrote: > > On 11/15/05, Ashok Shrestha wrote: > > I'm using MySQL 5.0.15 with FreeBSD 5.4-RELEASE amd64. > > > I would like to get the maximum performance out of this box. > > What options (MAKE, kernel, etc...) should I use to achieve > > this? What about loading the INDEX file into memory? > > You might want to take a look at: > > http://wikitest.freebsd.org/moin.cgi/MySQL > > > -- > FreeBSD Volunteer, http://people.freebsd.org/~jkoshy > -- Ashok Shrestha From owner-freebsd-database@FreeBSD.ORG Fri Nov 18 07:30:28 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5CC3D16A41F for ; Fri, 18 Nov 2005 07:30:28 +0000 (GMT) (envelope-from joseph.koshy@gmail.com) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.207]) by mx1.FreeBSD.org (Postfix) with ESMTP id DEE2443D46 for ; Fri, 18 Nov 2005 07:30:25 +0000 (GMT) (envelope-from joseph.koshy@gmail.com) Received: by xproxy.gmail.com with SMTP id s9so115915wxc for ; Thu, 17 Nov 2005 23:30:24 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=csGx2/o28iSwCcT7pFzBn1EE4uul0aYcveZIDMhD/1+IoyhSv1LizhI2cyNbGYJKs1h6l0jFRIXgS4GWrjOeUKmKJpMA0f2CJV0dbQJpswDO+XB/aP+N2xOyXQGIaDG66NCty0PoPDC17tBI0NaAJQm5omVy2DmCOwvhRfnC1Qo= Received: by 10.70.78.9 with SMTP id a9mr6376207wxb; Thu, 17 Nov 2005 23:30:24 -0800 (PST) Received: by 10.70.105.13 with HTTP; Thu, 17 Nov 2005 23:30:24 -0800 (PST) Message-ID: <84dead720511172330l6236ae08oa2e4524d63434ada@mail.gmail.com> Date: Fri, 18 Nov 2005 13:00:24 +0530 From: Joseph Koshy To: Ashok Shrestha In-Reply-To: <79e2026f0511170957l75c97958j33c278954cfc0b7c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> <84dead720511150518m250e073bgdbf9afe2dd4a3894@mail.gmail.com> <79e2026f0511170957l75c97958j33c278954cfc0b7c@mail.gmail.com> Cc: freebsd-database@freebsd.org Subject: Re: HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ?? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 18 Nov 2005 07:30:28 -0000 > I wanted to add more info to the wiki, but it appears that > only developers are allowed to make changes. =3D( The wiki is experimental I believe. You could just post your info here on the mailing lists. It will probably reach a wider audience in a more timely manner too. -- FreeBSD Volunteer, http://people.freebsd.org/~jkoshy From owner-freebsd-database@FreeBSD.ORG Fri Nov 18 10:36:21 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B9A2816A41F for ; Fri, 18 Nov 2005 10:36:21 +0000 (GMT) (envelope-from simon@eddie.nitro.dk) Received: from eddie.nitro.dk (zarniwoop.nitro.dk [83.92.207.38]) by mx1.FreeBSD.org (Postfix) with ESMTP id 17E4643D4C for ; Fri, 18 Nov 2005 10:36:21 +0000 (GMT) (envelope-from simon@eddie.nitro.dk) Received: by eddie.nitro.dk (Postfix, from userid 1000) id 25913119C50; Fri, 18 Nov 2005 11:36:20 +0100 (CET) Date: Fri, 18 Nov 2005 11:36:20 +0100 From: "Simon L. Nielsen" To: Ashok Shrestha Message-ID: <20051118103619.GC98443@eddie.nitro.dk> References: <79e2026f0511142357m6093e636y6a988725d783391f@mail.gmail.com> <84dead720511150518m250e073bgdbf9afe2dd4a3894@mail.gmail.com> <79e2026f0511170957l75c97958j33c278954cfc0b7c@mail.gmail.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="Qbvjkv9qwOGw/5Fx" Content-Disposition: inline In-Reply-To: <79e2026f0511170957l75c97958j33c278954cfc0b7c@mail.gmail.com> User-Agent: Mutt/1.5.11 Cc: freebsd-database@freebsd.org, Joseph Koshy Subject: Re: HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ?? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 18 Nov 2005 10:36:21 -0000 --Qbvjkv9qwOGw/5Fx Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2005.11.17 12:57:58 -0500, Ashok Shrestha wrote: > On 11/15/05, Joseph Koshy wrote: > > > > On 11/15/05, Ashok Shrestha wrote: > > > I'm using MySQL 5.0.15 with FreeBSD 5.4-RELEASE amd64. > > > > > I would like to get the maximum performance out of this box. > > > What options (MAKE, kernel, etc...) should I use to achieve > > > this? What about loading the INDEX file into memory? > > > > You might want to take a look at: > > > > http://wikitest.freebsd.org/moin.cgi/MySQL > > I wanted to add more info to the wiki, but it appears that only develope= rs > are allowed to make changes. =3D( > Do you know if this wiki will turn more public? The people administering the FreeBSD.org systems are already quite busy, so we unfortunatly don't have time to handle all the issues that comes with having a public wiki. That's the reason the FreeBSD wiki is only for FreeBSD committers and selected contributors (mainly the Google Summer of Code participants), which is also the purpose the wiki was originally created for. --=20 Simon L. Nielsen Hat of the day: wikitest.FreeBSD.org admin --Qbvjkv9qwOGw/5Fx Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (FreeBSD) iD8DBQFDfa6jh9pcDSc1mlERAqXyAKDDFY569A1/CwJhogp0mmJN04GezQCgh8/w phUcS8MUOpG1XxnaNkp5Hhk= =StiU -----END PGP SIGNATURE----- --Qbvjkv9qwOGw/5Fx-- From owner-freebsd-database@FreeBSD.ORG Fri Nov 18 23:05:55 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0033B16A41F for ; Fri, 18 Nov 2005 23:05:54 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by mx1.FreeBSD.org (Postfix) with ESMTP id 47EE343D4C for ; Fri, 18 Nov 2005 23:05:54 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: by wproxy.gmail.com with SMTP id i27so61446wra for ; Fri, 18 Nov 2005 15:05:53 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=F9T0qTVOqgcqUA+7VAmu8/MClLhww1OqwrWjQ9Utvc8tkazoYnBfDxwyOoyYAQNR/l/q9hqwi1WjstaSpYKG7IJ4xmdt7TtF13EzItTSJxjFYVJIZ2VPyTC7xIX0xdnR0FFJW+tN9HwDfH8ajLuIY/aLxxxISVmEdubOpVjrJGA= Received: by 10.54.112.19 with SMTP id k19mr251579wrc; Fri, 18 Nov 2005 15:05:53 -0800 (PST) Received: by 10.54.72.2 with HTTP; Fri, 18 Nov 2005 15:05:53 -0800 (PST) Message-ID: <79e2026f0511181505r3086b084g8f9ac369c318b7a8@mail.gmail.com> Date: Fri, 18 Nov 2005 18:05:53 -0500 From: Ashok Shrestha To: freebsd-database@freebsd.org, ale@FreeBSD.org MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: error in my-*.cnf files X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 18 Nov 2005 23:05:55 -0000 In the /usr/local/share/mysql/my-*.cnf files, there is an option "key_buffer". Shouldn't this be "key_buffer_size" as described in the MySQL Documentation ( http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html) ? -- Ashok Shrestha From owner-freebsd-database@FreeBSD.ORG Fri Nov 18 23:28:13 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id BFE7D16A41F for ; Fri, 18 Nov 2005 23:28:13 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by mx1.FreeBSD.org (Postfix) with ESMTP id D161C43D49 for ; Fri, 18 Nov 2005 23:28:12 +0000 (GMT) (envelope-from ashok.shrestha@gmail.com) Received: by wproxy.gmail.com with SMTP id i27so63928wra for ; Fri, 18 Nov 2005 15:28:12 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=O0t3ESR/VGFc8tkHYUl84ef7QV0xu2tUJDig4qxAEkapeXoCKj+/bUHqnv4c7pPN352OYkaTMdRJFmhTYj83R6npQGHbuV3Ym2Jpw+plOEEvLWBaU3SRtpD0fgYlwyweycbjWOCi37vMoZacFpD0zRnqG+rRuJjbFrFOQrIn8wc= Received: by 10.54.141.17 with SMTP id o17mr263750wrd; Fri, 18 Nov 2005 15:28:12 -0800 (PST) Received: by 10.54.72.2 with HTTP; Fri, 18 Nov 2005 15:28:12 -0800 (PST) Message-ID: <79e2026f0511181528l6aab0972m45a542a883d8c9a6@mail.gmail.com> Date: Fri, 18 Nov 2005 18:28:12 -0500 From: Ashok Shrestha To: freebsd-database@freebsd.org, ale@freebsd.org In-Reply-To: <79e2026f0511181505r3086b084g8f9ac369c318b7a8@mail.gmail.com> MIME-Version: 1.0 References: <79e2026f0511181505r3086b084g8f9ac369c318b7a8@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: Re: error in my-*.cnf files X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 18 Nov 2005 23:28:13 -0000 I am refering to the "key_buffer" variable in the "[mysqld]" section. On 11/18/05, Ashok Shrestha wrote: > > In the /usr/local/share/mysql/my-*.cnf files, there is an option > "key_buffer". Shouldn't this be "key_buffer_size" as described in the MyS= QL > Documentation (http://dev.mysql.com/doc/refman/5.0/en/server-system-varia= bles.html) > ? > > -- > Ashok Shrestha -- Ashok Shrestha