http.conf

1. http.conf

   #1

   Hello,
   
   I’d like to share with the interworx-cp community my knowledge about tuning / tweaking mysql.
   
   First of all, sorry if my English is not perfect. Maybe some sentences may be difficult to understand. So do not hesitate to ask me for explanations :-p
   
   Secondly, the default INTEWORX-CP mysql setup works very fine and is setup for all common usage.
   DO NOT CHANGE your setting without understanding what you do.
   Make a backup of your my.cnf before editing the /etc/my.cnf
   DO THESE CHANGES AT YOUR OWN RISKS.
   
   
   My thread is only to help you to more well understand how tuning mysql.
   
   Optimising mysql is very well commented on the net, and you’ll find huge information on how to do this. There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage…
   So, I’ll not give the best parameters but rather how to define these ones. Make some tests, and you’ll quickly find your own parameters.
   
   I’ll give you at the end of this post some web pointers which may help you.
   
   There a lot of available parameters but only few one are very important to tweak your mysql box.
   
   
   The most important variables are (for me, and it is not exhaustive)
   Quote:
   - max_connections
   - wait_timeout
   - thread_cache_size
   -
   - table_cache
   -
   - key_buffer_size
   - query_cache_size
   - tmp_table_size
   
   
   First of all, how to find your variable, and the mysql usage ?
   
   *VARIABLES
   Quote:
   from mysql :
   show variables;
   
   or from command line :
   mysqladmin variables
   
   *PROCESS / STATUS
   Quote:
   from Mysql :
   show status;
   
   or from command line
   mysqladmin –i10 processlist extended-status
   
   *SOME USEFUL COMMAND FOR YOU BOX USAGE
   Quote:
   >Top
   
   >ps –axfu
   
   >vmstat 1
   
   
   * OPTIMISING MYSQL
   
   To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.
   
   1 - The two most important variables : Table_cache and Key_buffer_size
   
   * If Opened_tables is big, then your table_cache variable is probably
   too small.
   table_cache 64
   Open_tables 64
   Opened_tables 544468
   
   This is the first serious problem. "The table_cache is the number of open
   tables for all threads. MySQL, being multi-threaded, may be running many
   queries on the table at one time, and each of these will open a table."
   Therefore, even though we only have a few tables, we will need many more
   open_tables.
   
   The Opened_tables value is high and shows the number of
   cache misses. Getting the table_cache size correct is one of the two best
   things you can do to improve performance.
   
   
   
   * If Key_reads is big, then your key_buffer_size variable is probably
   too small. The cache hit rate can be calculated with
   Key_reads/Key_read_requests.
   key_buffer_size 16M
   Key_read_requests 2973620399
   Key_reads 8490571
   (cache hit rate = 0.0028)
   
   “The key_buffer_size affects the size of the index buffers and the speed
   of index handling, particularly reading." The MySQL manual (and other sources) say that
   "Key_reads/Key_read_request ratio should normally be < 0.01." This is the
   other most important thing to get correct. Here the value seems to be correct (< 0.01)
   
   Also check key_write_requests and key_writes.
   The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)
   
   
   Here is a very interesting web pointer : http://www.databasejournal.com/featu...0897_1402311_3
   
   
   
   2 - Others important settings are : Wait_timeout, max_connexion, thread_cache
   
   A little explanation :
   Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.
   
   The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
   The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.
   
   So the solution is to use the Thread_cache (from mysql doc) :
   “How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.”
   
   
   * If Threads_created is big, you may want to increase the
   thread_cache_size variable. The cache hit rate can be calculated with
   Threads_created/Connections.
   thread_cache_size 0
   Threads_created 150022
   Connections 150023
   
   This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.
   
   you may try this formula : table_cache = opened table / max_used_connection
   
   
   3 - Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next
   
   * If Created_tmp_disk_tables is big, you may want to increase the
   tmp_table_size variable to get the temporary tables memory-based instead
   of disk based.
   
   tmp_table_size 32M
   Created_tmp_disk_tables 3227
   Created_tmp_tables 159832
   Created_tmp_files 4444
   
   Created_tmp_disk_tables are the "number of implicit temporary tables on
   disk created while executing statements" and Created_tmp_tables are
   memory-based. Obviously it is bad if you have to go to disk instead of
   memory. About 2% of temp tables go to disk, which doesn't seem too bad
   but increasing the tmp_table_size probably couldn't hurt either.
   
   
   * If Handler_read_rnd is big, then you probably have a lot of queries
   that require MySQL to scan whole tables or you have joins that don't use
   keys properly.
   Handler_read_rnd 27712353
   Handler_read_rnd_next 283536234
   
   These values are high, that we could probably stand to improve
   the indexes and queries.
   
   
   I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.
   
   Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)
   
   Pascal
   Last edited by pascal : 01-21-2005 at 09:11 PM.
   Reply With Quote
   pascal
   View Public Profile
   Send a private message to pascal
   Visit pascal's homepage!
   Find all posts by pascal
     #2 
   Old 01-21-2005, 08:28 PM
   pascal pascal is offline
   Registered User
    
   Join Date: Jun 2004
   Posts: 899
   I forgot to tell you two or three importants things like :
   
   Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)
   
   Notice the max_connexion and the multiplier.
   connexion increase = memory usage increase too.
   
   Notice key_buffer
   for a given memory :
   more you add mem to key buffer, less connexion is
   less is key buffer, more connexion is
   
   If you change one of these settings for a high value, you system may swap.
   If you system swap, try lot decrease these values
   
   Also, about table_cache :
   Increasing the size of the table cache may really help you.
   But you must be careful not to make the value too large. All operating systems have a limit on the number "open file pointer" (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
   If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.
   
   Pascal

2. Re: http.conf

   #2

   VPSes are really hard to use with the memory restrictions and CPU limitations...but with some optimization they can definitely serve your websites fast!
   
   MySQL Optimization
   Here are my suggested settings for the my.cnf file. This should work well for a VPS with 256-512MB RAM.
   Code:
   
   max_connections = 400 key_buffer = 16M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1024 thread_cache_size = 286 interactive_timeout = 25 wait_timeout = 1000 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 tmp_table_size = 16M skip-innodb [mysqld_safe] open_files_limit = 8192 quick max_allowed_packet = 16M key_buffer = 32M sort_buffer = 32M read_buffer = 16M write_buffer = 16M
   
   In order to make things even faster, you can customize these settings specifically for your VPSs' usage. There's a great howto on InterWorx's forum for this --> http://www.interworx.com/forums/showthread.php?p=2346
   
   Lastly, I recommend installing mytop to help you monitor your usage...
   Code:
   
   wget http://dll.elix.us/mytop-1.4.tar.gz tar -zxvf mytop-1.4.tar.gz cd mytop-1.4 perl Makefile.PL make make test make install
   
   Once that's done, just enter in "mytop" .
   
   PHP & Apache Optimization
   I strongly recommend installing eAccelerator. There's an easy to follow howto here: http://forum.ev1servers.net/showthre...t=eaccelerator. If you use the default cache dir for eAccelerator (/tmp/eaccelerator) make sure you check it reguarily and clean it every once and a while. (it can really get quite large from my experience)
   
   For httpd.conf I suggest:
   Timeout 200
   KeepAlive On
   maxKeepAliveRequests 100
   KeepAliveTimeout 3
   MinSpareServers 10
   MaxSpareServers 20
   StartServers 15
   MaxClients 250
   MaxRequestsPerChild 0
   HostnameLookups Off
   
   You can use ab to benchmark your Apache before and after you make changes.
   
   ab -c 5 -n 20 somephpbasedsiteonyourserver.com/file.php
   
   I suggest doing 2 or 3 tests like that to get an average.
   
   If you want to check the Apache error log, try this -->
   cat /usr/local/apache/logs/error_log
   
   Monitoring Usage
   On a Virtuozzo VPS you can use cat /proc/usr_beancounters to output your usage of the VZ parameters. You should pay most attention to oomguarpages and privmpages. (although anything with a failure is generally bad)
   
   You can find the amount of connections to Apache with this command:
   netstat -nt | grep :80 | wc -l
   
   To find the amount of Apache processes use this command:
   ps -A | grep httpd | wc -l (this will show the process count)
   ps -aux | grep httpd (this will show the actual processes)
   
   To find the amount of MySQL processes use this command:
   ps -A | grep mysql | wc -l (this will show the process count)
   ps -aux | grep mysql (this will show the actual processes)
   
   Just simply using top (standard view) or top -c (will show the actual command being used and/or location of each process as opposed to just the name) can help you monitor your VPS usage very wel.
   
   To see your disk space usage, try using this command --> df -h
   
   Mitigating (D)DOS
   If you're being DDOS'd or DOS'd you can use this command:
   netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
   
   That will help you see how many connections each IP address has in total to your server.
   
   There's a very decent script you can use to automate the banning of IP addresses available here --> http://forums.deftechgroup.com/showthread.php?t=825
   
   Although I haven't tried it myself, I suggest you take a look at Scrutinizer as well which sounds very useful --> http://www.solutix.ch/cgi-bin/index.pl
   
   Spam Assassin
   Spam Assassin can take up a lot of memory and make it really hard to host just a few sites on a VPS, but there is a way around this...
   
   Login to WHM as root, scroll down to "cPanel 10.8.1-R15" (it may be slightly different depending on what version you are using) then goto "Addon Modules" and install "spamdconf". Once it's done, refresh the WHM page, scroll down to "Add-ons" on the nav bar and then click on 'Setup Spamd Startup Configuration". Set "Maximum Children" to "2". Then hit Submit. Wait a few seconds (15-30, but usually less) for exim to restart and you're done .
   
   cPanel Tweak Setings
   Login to WHM as root, and under "Server Configuration" on the nav bar hit "Tweak Settings".
   
   Here are some suggested settings:
   Default catch-all/default address behavior for new accounts. fail will generally save the most CPU time.
   - Use "FAIL". If you already have some accounts setup not to use "FAIL" (by default it will not) then run this command to convert to FAIL from BLACKHOLE --> perl -pi -e "s/:blackhole:/:fail:/g;" /etc/valiases/*
   
   Mailman
   - Mailman tends to use a lot of resources, so if you don't need cpanel mailing lists then uncheck this.
   
   Number of minutes between mail server queue runs (default is 60).:
   - You may want to set this to 180 to reduce load.
   
   Track the origin of messages sent though the mail server by adding the X-Source headers (exim 4.34+ required)
   - This is just generally a good idea. So check this.
   
   Analog Stats
   - I find this useless, so uncheck this. If you want to delete the existing analog stats files just run this command --> rm -rf /home/*/tmp/analog/*
   
   Awstats Reverse Dns Resolution
   - Make sure this is unchecked, I find it pretty much useless for most users.
   
   Awstats Stats
   - You can check this if you need a robust stats software that integrates with cPanel, if you don't need it, then don't check it. *Note most hosting clients will want to use this. If you want to delete the existing awstats stats files just run this command --> rm -rf /home/*/tmp/awstats/*
   
   Webalizer Stats
   - Not many hosting clients will want to use this so, you can uncheck this to reduce load. If you want to delete the existing webalizer stats files just run this command --> rm -rf /home/*/tmp/webalizer/*
   
   Delete each domain's access logs after stats run
   - Make sure this is checked, otherwise disk space usage can really rack up!
   
   That's about it for now, I may do some more later....
   
   Hope it helps!

3. Re: http.conf

   #3

   http://www.ubbdev.com/forums/ubbthreads.php/ubb/showflat/Number/253430/page/1

4. Re: http.conf

   #4

   http://www.strolen.com/phpsysinfo/vpsinfo.php
   
   http://www.strolen.com/phpsysinfo/status.php?mysql_info=1

Strolen's World · All categories · Guildhall