From owner-freebsd-questions@FreeBSD.ORG Tue Jun 3 10:43:28 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 BAD9710656CC for ; Tue, 3 Jun 2008 10:43:28 +0000 (UTC) (envelope-from maanjee@gmail.com) Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mx1.freebsd.org (Postfix) with ESMTP id 8A4FE8FC3C for ; Tue, 3 Jun 2008 10:43:28 +0000 (UTC) (envelope-from maanjee@gmail.com) Received: by rv-out-0506.google.com with SMTP id b25so1577382rvf.43 for ; Tue, 03 Jun 2008 03:43:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=EsGXBWApOfwTaeKvBICa79Yc8H5H3QXchn8Wr2jaqiA=; b=RHa9svWlBG8uRG1XQFgfI1bEeKoYi6kqpKDt8gTWChz3nB06EaaqivVWax+nAq4ib7H+Izyg+/MbO+6c78ud9SR23z9B/y/qu5rW/QaGJt0Z5VRCOj8/aZbysBmQL2O3NApsUzb5lU9T9kFYmkLgYOwmlvgo2b6SJ/JYvnlqQrQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=A8/kRew9WnvK24dbKISzBHKxHPpHbP61lTGc5E7cb71M5DkN1xlNfK/WW6/2di1wnT36Swvex5S4EY5kDfOAkPWKf6idniiMwJ1TMcw0NdZVe01JRv8ijNI6PUw61bGEnDprvtonUYaUzJo5VISOmHaQuEp7nhZW/BE11Qw7MkA= Received: by 10.115.78.1 with SMTP id f1mr3658153wal.223.1212489807954; Tue, 03 Jun 2008 03:43:27 -0700 (PDT) Received: by 10.114.95.6 with HTTP; Tue, 3 Jun 2008 03:43:27 -0700 (PDT) Message-ID: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> Date: Tue, 3 Jun 2008 12:43:27 +0200 From: VeeJay To: VeeJay , FreeBSD-Questions , mysql@lists.mysql.com MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: 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 10:43:28 -0000 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 ")/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