Re: FreeBSD MySQL Performance Tunning suggestions???
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 [EMAIL PROTECTED] 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:36,rev:1,severity:2,msg:'Bogus Path denied'" SecFilterSelective POST_PAYLOAD "[[:space:]]+\.\.\.+\;" "id:37,rev:1,severity:2,msg:'Bogus Pa
Re: FreeBSD MySQL Performance Tunning suggestions???
Hi all ! VeeJay wrote: [[...]] 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) Your machine has 16 GB of RAM. If you ever want to use really large caches in the MySQL server process, you need to use a 64 bit binary, called "x86_64" in the MySQL package file names. I have done some googling and made these configuration files for Apache and MySQL? Apache: httpd.conf-start [[... file snipped ...]] MySQL: my.cnfstart [[... file snipped ...]] Is there something you can tune? You cannot tune without knowing which bottleneck you should widen, and how much resources still are available. IMNSHO, you need to start with some configuration, put load onto it (as representative as possible), then watch the system's behavior (take measurements !), and only then determine which part you want to improve. Example: Database caches are good to reduce disk I/O and so to increase performance, but you will not increase caches if your system is already paging heavily (= your RAM is too small). There is no need to change any parameter unless a) its current setting restricts your performance, and you have sufficient resources to raise the limit, or b) its current setting allocates more resources than needed here, which could be used better at some other place. You need some initial run and measurement to check that. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: FreeBSD MySQL Performance Tunning suggestions???
MySQL: [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 I don't see innodb tunings... if you have innodb tables, you should use about 4GB of RAM for that. DOn't forget, Edit your /boot/loader.conf to allow your MySQL to grow up to about 8GB (for starters). People forget this about as often as they forget to put INDEXES on their tables. Speaking of that! Turn on your slow_query_log... set it to 5 seconds and monitor the slow_query_log file. Are you going to replicate? If not, disable log-bin (you have skip-networking, so I assume you are not setting up a master/slave). Only reason to do log-bin is to fill your disk up with files you will never yse. Up query_cache_limit=8M (depends, may not matter for your site). (That is the max limit per query to cache...). Keep an eye on your cache fill rate and up those values if you need to. Keep an eye on your table_cache... table_cache=1024 may be fine, but if you have 4000 tables, that may need to be higher. Set your key_buffer higher (depending on your site) --- several 100GB. Connections can *probably* be lower... the only reason to have a real hig number for connections is to get your machine to swap. :p Oh, check out the my-huge.cnf file that comes with MySQL... I think it is in /usr/local/share/mysql/ or somewhere good tips there. Good luck. Rudy ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
FreeBSD MySQL Performance Tunning suggestions???
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 [EMAIL PROTECTED] 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:36,rev:1,severity:2,msg:'Bogus Path denied'" SecFilterSelective POST_PAYLOAD "[[:space:]]+\.\.\.+\;" "id:37,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:Rege