Date: Wed, 04 Jun 2008 22:37:31 -0700 From: Antony T Curtis <antonycurtis@verizon.net> To: VeeJay <maanjee@gmail.com> Cc: mysql@lists.mysql.com, FreeBSD-Questions <freebsd-questions@freebsd.org> Subject: Re: FreeBSD MySQL Performance Tunning suggestions??? Message-ID: <1E364D6C-22D7-4DFD-AD8B-8FCDD4A57D5B@verizon.net> In-Reply-To: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com> References: <2cd0a0da0806030343o68dcf30bj447d4174b93c30d2@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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
> <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?1E364D6C-22D7-4DFD-AD8B-8FCDD4A57D5B>