From owner-freebsd-questions@FreeBSD.ORG Thu Jun 5 06:37:42 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 6431D106564A for ; Thu, 5 Jun 2008 06:37:42 +0000 (UTC) (envelope-from antonycurtis@verizon.net) Received: from vms173003pub.verizon.net (vms173003pub.verizon.net [206.46.173.3]) by mx1.freebsd.org (Postfix) with ESMTP id 4384E8FC14 for ; Thu, 5 Jun 2008 06:37:42 +0000 (UTC) (envelope-from antonycurtis@verizon.net) Received: from [10.71.0.229] ([12.108.175.130]) by vms173003.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0K1Z008PP6AHURUE@vms173003.mailsrvcs.net> for freebsd-questions@freebsd.org; Thu, 05 Jun 2008 00:37:31 -0500 (CDT) Date: Wed, 04 Jun 2008 22:37:31 -0700 From: Antony T Curtis In-reply-to: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> To: VeeJay Message-id: <1E364D6C-22D7-4DFD-AD8B-8FCDD4A57D5B@verizon.net> MIME-version: 1.0 (Apple Message framework v924) X-Mailer: Apple Mail (2.924) Content-type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-transfer-encoding: 7bit References: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> 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: Thu, 05 Jun 2008 06:37:42 -0000 Hi, FreeBSD 7 should offer much better performance for MySQL. The FreeBSD kernel developers have found ways to relieve some of the kernel bottlenecks which permit multithreaded applications to operate much better. Regards, Antony. On 3 Jun 2008, at 03:43, VeeJay wrote: > Hi Guys > > I need some performance tuning suggestions/help from you. > > At my job, I am going to build a Web Server with > > 1. FreeBSD 7.0-RELEASE amd64 > 2. Apache 2.2.8 > 3. PHP 4.4.8 (or may be PHP5, what do you suggest?) > > Server's hardware configuration is as follow: > > 2 x Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB > 16GB (8x2GB Dual Rank DIMMs) 667MHz FBD > 6 x 450GB SAS 15k 3.5" HD Hot Plug > PERC 6/i, Integrated Controller Card x6 backplane > PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S > TCP/IP Offload Engine 2P > Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled > > For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ? > 1. FreeBSD 7.x (x86_64) > or > 2. FreeBSD 6.x (x86) > > I have done some googling and made these configuration files for > Apache and > MySQL? > > Apache: > httpd.conf----------------------------------------------------- > start------------ > # ================================================= > # Basic settings > # ================================================= > ServerType standalone > ServerRoot "/usr/local/apache" > PidFile /usr/local/apache/logs/httpd.pid > ScoreBoardFile /usr/local/apache/logs/httpd.scoreboard > ResourceConfig /dev/null > AccessConfig /dev/null > # ================================================= > # Performance settings > # ================================================= > Timeout 300 > KeepAlive On > MaxKeepAliveRequests 100 > KeepAliveTimeout 15 > MinSpareServers 5 > MaxSpareServers 10 > StartServers 5 > MaxClients 256 > MaxRequestsPerChild 0 > # ================================================= > # Apache modules > # ================================================= > ClearModuleList > AddModule mod_log_config.c > AddModule mod_mime.c > AddModule mod_dir.c > AddModule mod_access.c > AddModule mod_auth.c > AddModule mod_php4.c > AddModule mod_rewrite.c > AddModule mod_security.c > AddModule mod_setenvif.c > # ================================================= > # General settings > # ================================================= > Port 80 > User apache > Group apache > ServerAdmin adminsrv@gmail.com > UseCanonicalName Off > ServerSignature Off > HostnameLookups Off > ServerTokens Prod > > DirectoryIndex index.html > > DocumentRoot "/home/apache/www" > # ================================================= > # Access control > # ================================================= > > Options None > AllowOverride None > Order deny,allow > Deny from all > > > Order allow,deny > Allow from all > > > Order allow,deny > Allow from all > > # ================================================= > # MIME encoding > # ================================================= > > TypesConfig /usr/local/apache/conf/mime.types > > DefaultType text/plain > > AddEncoding x-compress Z > AddEncoding x-gzip gz tgz > AddType application/x-tar .tgz > AddType application/x-httpd-php .html > > # ================================================= > # Logs > # ================================================= > LogLevel warn > LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i > \"" > combined > LogFormat "%h %l %u %t \"%r\" %>s %b" common > LogFormat "%{Referer}i -> %U" referer > LogFormat "%{User-agent}i" agent > ErrorLog /var/apache/logs/error_log > CustomLog /var/apache/logs/access_log combined > # ================================================= > # Virtual hosts > # ================================================= > NameVirtualHost * > > DocumentRoot "/home/apache/www/vhosts/mydomain.com/public_html" > ServerName "www.mydomain.com" > ServerAlias "mydomain.com" > ErrorLog /var/apache/logs/vhosts/mydomain.com/error_log > CustomLog /var/apache/logs/vhosts/mydomain.com/access_log > combined > > RewriteEngine on > RewriteRule ^/([a-z]{2})/index.html$ /index.html?topicid=$1 > > ErrorDocument 400 /page_error.html > ErrorDocument 401 /page_error.html > ErrorDocument 403 /page_error.html > ErrorDocument 404 /page_error.html > ErrorDocument 500 /page_error.html > > # ================================================ > # Logging GET/POST requests, defending against > # Cross-Site-Scripting (XSS) and SQL Injection attacks > # ================================================ > > AddHandler application/x-httpd-php .html > > #Turn the filtering engine On or Off > SecAuditEngine On > # Only log suspicious requests > SecAuditEngine RelevantOnly > SecAuditLog /var/apache/logs/audit_log > SecFilterScanPOST On > SecFilterEngine On > > SecFilterDefaultAction "deny,log,status:500" > SecFilter "\"" > > #generic bogus path sigs > SecFilterSelective THE_REQUEST "\.\.\./" > "id:300006,rev:1,severity:2,msg:'Bogus Path denied'" > SecFilterSelective POST_PAYLOAD "[[:space:]]+\.\.\.+\;" > "id:300007,rev:1,severity:2,msg:'Bogus Path denied'" > > #XCC:Signatures for XSS > SecFilter "/((\%3D)|(=))[^\n]*((\%3C)|<)[^\n]+((\%3E)|>)" > > #XCC:Regex for simple CSS attack > SecFilter "/((\%3C)|<)((\%2F)|\/)*[a-z0-9\%]+((\%3E)|>)/ix" > > #XCC:Regex for " #SecFilter > "/((\%3C)|<)((\%69)|i|(\%49))((\%6D)|m|(\%4D))((\%67)|g|(\%47))[^\n]+ > ((\%3E)|>)/i" > > #XCC:Paranoid regex for CSS attacks > SecFilter "/((\%3C)|<)[^\n]+((\%3E)|>)/i" > > #SQL:Regex for detection of SQL meta-characters > SecFilter "/(\%27)|(\')|(\-\-)|(\%23)|(#)/ix" > > #SQL:Modified regex for detection of SQL meta-characters > #SecFilter "/((\%3D)|(=))[^\n]*((\%27)|(\')|(\-\-)|(\%3B)| > (;))/i" > > #SQL:Regex for typical SQL Injection attack > SecFilter "/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\ > %52))/ix" > > #SQL:Regex for detecting SQL Injection with the > UNION,SELECT,UPDATE,DELETE,DROP,GRANT keyword > SecFilter "/((\%27)|(\'))union/ix" > SecFilter "/((\%27)|(\'))select/ix" > SecFilter "/((\%27)|(\'))insert/ix" > SecFilter "/((\%27)|(\'))update/ix" > SecFilter "/((\%27)|(\'))delete/ix" > SecFilter "/((\%27)|(\'))drop/ix" > SecFilter "/((\%27)|(\'))grant/ix" > > #PHP defenses > SecFilterSelective ARG_PHPSESSID "!^[0-9a-z]*$" > SecFilterSelective ARGS_NAMES "^(globals($|\[)|php:/)" > SecFilterSelective COOKIE_PHPSESSID "!^[0-9a-z]*$" > SecFilterSelective COOKIE_sessionid "!^[0-9a-z\.]*$" > > #Generic PHP payload command injection and upload > vulnerabilities > SecFilterSelective POST_PAYLOAD "<\?php" chain > SecFilter > "((fputs|fread)\(.*\,.*\)\;|fsockopen\(gethostbyname|chr\(.*\)\.chr\ > (.*\)\.chr\(|(fclose|fgets)\(.*\)\;|(system|exec)\(.*\)\;)" > chain > SecFilter "\<\?php" > > > Apache: > httpd.conf----------------------------------------------------- > end-------- > > > MySQL: > my.cnf------------------------------------------------------------ > start-------- > > # The following options will be passed to all MySQL clients > [client] > #password = your_password > port = 3306 > socket = /tmp/mysql.sock > #ng below 1 line > default-character-set=utf8 > # Here follows entries for some specific programs > # The MySQL server > [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 > [mysqldump] > quick > max_allowed_packet = 16M > [mysql] > no-auto-rehash > #ng below 1 line > default-character-set=utf8 > [isamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > [myisamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > [mysqlhotcopy] > interactive-timeout > > MySQL: > my.cnf-------------------------------------------------------------end > > > Is there something you can tune? > > Regards > > -- > Thanks! > > BR / vj