Skip site navigation (1)Skip section navigation (2)
Date:      Tue, 3 Jun 2008 12:43:27 +0200
From:      VeeJay <maanjee@gmail.com>
To:        VeeJay <maanjee@gmail.com>,  FreeBSD-Questions <freebsd-questions@freebsd.org>, mysql@lists.mysql.com
Subject:   FreeBSD MySQL Performance Tunning suggestions???
Message-ID:  <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com>

next in thread | raw e-mail | index | archive | help
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
<IfModule mod_dir.c>
    DirectoryIndex index.html
</IfModule>
DocumentRoot "/home/apache/www"
# =================================================
# Access control
# =================================================
<Directory />
    Options None
    AllowOverride None
    Order deny,allow
    Deny from all
</Directory>
<Directory "/home/apache/www">
    Order allow,deny
    Allow from all
</Directory>
<Directory "/home/apache/www/vhosts/mydomain.com/public_html">
    Order allow,deny
    Allow from all
</Directory>
# =================================================
# MIME encoding
# =================================================
<IfModule mod_mime.c>
    TypesConfig /usr/local/apache/conf/mime.types
</IfModule>
DefaultType text/plain
<IfModule mod_mime.c>
    AddEncoding x-compress Z
    AddEncoding x-gzip gz tgz
    AddType application/x-tar .tgz
    AddType application/x-httpd-php .html
</IfModule>
# =================================================
# 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 *
<VirtualHost *>
        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
        <IfModule mod_rewrite.c>
         RewriteEngine on
         RewriteRule ^/([a-z]{2})/index.html$ /index.html?topicid=$1
        </IfModule>
        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
</VirtualHost>
# ================================================
# Logging GET/POST requests, defending against
# Cross-Site-Scripting (XSS) and SQL Injection attacks
# ================================================
<IfModule mod_security.c>
        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 "<img src" CSS attack
         #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"

</IfModule>
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



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?2cd0a0da0806030343o68dcf30bj447d4174b93c30d2>