FreeBSD MySQL Performance Tunning suggestions???
VeeJay
maanjee at gmail.com
Tue Jun 3 10:43:28 UTC 2008
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 at 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
More information about the freebsd-questions
mailing list