From owner-freebsd-questions@FreeBSD.ORG Tue Jun 3 11:25:33 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id DCEE010656C6 for ; Tue, 3 Jun 2008 11:25:33 +0000 (UTC) (envelope-from crapsh@monkeybrains.net) Received: from ape.monkeybrains.net (ape.monkeybrains.NET [208.69.40.11]) by mx1.freebsd.org (Postfix) with ESMTP id ABB1E8FC3F for ; Tue, 3 Jun 2008 11:25:33 +0000 (UTC) (envelope-from crapsh@monkeybrains.net) Received: from monchichi.monkeybrains.net (adsl-75-63-16-199.dsl.pltn13.sbcglobal.net [75.63.16.199]) (authenticated bits=0) by ape.monkeybrains.net (8.14.1/8.14.1) with ESMTP id m53BPWqE024445 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 3 Jun 2008 04:25:33 -0700 (PDT) (envelope-from crapsh@monkeybrains.net) Message-ID: <48452A83.9000406@monkeybrains.net> Date: Tue, 03 Jun 2008 04:26:59 -0700 From: Rudy User-Agent: Thunderbird 2.0.0.12 (X11/20080310) MIME-Version: 1.0 To: VeeJay References: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> In-Reply-To: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.93, clamav-milter version 0.93 on pita.monkeybrains.net X-Virus-Status: Clean Cc: mysql@lists.mysql.com, FreeBSD-Questions Subject: Re: FreeBSD MySQL Performance Tunning suggestions??? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 03 Jun 2008 11:25:33 -0000 > MySQL: > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > skip-networking > skip-name-resolve > server-id=1 > #2008-02-10 > max_connections=499 > interactive_timeout=100 > wait_timeout=100 > connect_timeout=10 > thread_cache_size=256 > connect_timeout=10 > myisam_sort_buffer_size=32M > key_buffer=16M > join_buffer=3M > record_buffer=3M > sort_buffer=5M > table_cache=1024 > #- > # increase until threads_created doesnt grow anymore > thread_cache=512 > query_cache_type=1 > query_cache_limit=2M > query_cache_size=64M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency=4 > set-variable=local-infile=0 > #ng below 5 lines > init_connect='SET collation_connection = utf8_unicode_ci' > init_connect='SET NAMES utf8' > default-character-set=utf8 > character-set-server=utf8 > collation-server=utf8_unicode_ci > #NG: new variables for fulltext search: > ft_min_word_len=2 > ft_max_word_len=15 > > # Replication Master Server (default) > # binary logging is required for replication > log-bin=mysql-bin > # required unique id between 1 and 2^32 - 1 > # defaults to 1 if master-host is not set > # but will not function as a master if omitted > server-id = 1 I don't see innodb tunings... if you have innodb tables, you should use about 4GB of RAM for that. DOn't forget, Edit your /boot/loader.conf to allow your MySQL to grow up to about 8GB (for starters). People forget this about as often as they forget to put INDEXES on their tables. Speaking of that! Turn on your slow_query_log... set it to 5 seconds and monitor the slow_query_log file. Are you going to replicate? If not, disable log-bin (you have skip-networking, so I assume you are not setting up a master/slave). Only reason to do log-bin is to fill your disk up with files you will never yse. Up query_cache_limit=8M (depends, may not matter for your site). (That is the max limit per query to cache...). Keep an eye on your cache fill rate and up those values if you need to. Keep an eye on your table_cache... table_cache=1024 may be fine, but if you have 4000 tables, that may need to be higher. Set your key_buffer higher (depending on your site) --- several 100GB. Connections can *probably* be lower... the only reason to have a real hig number for connections is to get your machine to swap. :p Oh, check out the my-huge.cnf file that comes with MySQL... I think it is in /usr/local/share/mysql/ or somewhere.... good tips there. Good luck. Rudy