Changes between Version 3 and Version 4 of HostingServerSetupRecommendations


Ignore:
Timestamp:
Aug 27, 2010, 6:59:35 AM (14 years ago)
Author:
AlexT
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • HostingServerSetupRecommendations

    v3 v4  
    1212== MySQL == 
    1313 
     14First you need to check if query_cache is enabled on your server. It was noticed that 80% of all SELECT queries are taken from query_cache when it is enabled, so if it is disabled on your server - mysql performance is '''5''' times slower !  
     15 
     16To check this go to phpMysqlAdmin tool (in most cases it is in your whm panel -> SQL Services -> phpMyAdmin). Then click "Variables" tab. 
     17"query cache size" must be at lesat 16M, "query cache type" must be ON and "query cache limit" value should be around 1M. 
     18Example of good configuration: 
     19 
     20 [[Image(hssr-mysql-1-query-cache.jpg)]] 
     21 
     22After you realized that you need to change these settings you need to edit "my.cnf" file on your server (in most cases it in in "/etc" folder) and add the foollowing lines in "[mysqld]" section of the file, if the same configuration options already exists it is better to comment out old ones (with hash # symbol ) and add new ones near them: 
     23 
     24{{{ 
     25[mysqld] 
     26query_cache_limit = 1M 
     27query_cache_size = 32M 
     28}}} 
     29 
     30You need to restart mysql server to apply the changes, you can do it from whm panel or from command line, most common command line for linux servers is: 
     31 
     32{{{ 
     33/etc/init.s/mysql restart 
     34}}} 
     35 
     36This most critical configuration options. Other useful options must be enabled too - "key buffer size", "max heap table size", "tmp table size", "thread_cache" are some of them. But exact values varies from server from server, for example for dedicated or VPS server with 1Gb RAM and one dolphin site, these values maybe like this: 
     37 
     38{{{ 
    1439[mysqld] 
    1540 
     
    2449thread_cache = 128 
    2550 
    26 First you need to check if query_cache is enabled on your server. It was noticed that 80% of all SELECT queries are taken from query_cache when it is enabled, so if it is disabled on your server - mysql performance is '''5''' times slower !  
    27  
    28 To check this go to phpMysqlAdmin tool (in most cases it is in your whm panel -> SQL Services -> phpMyAdmin). Then click "Variables" tab. 
    29 "query cache size" must be at lesat 16M, "query cache type" must be ON and "query cache limit" value should be around 1M. 
    30 Example of good configuration: 
    31  
    32  [[Image(hssr-mysql-1-query-cache.jpg)]] 
    33  
    34 After you realized that you need to change these settings you need to edit "my.cnf" file on your server (in most cases it in in "/etc" folder) and add the foollowing lines in "[mysqld]" section of the file, if the same configuration options already exists it is better to comment out old ones (with hash # symbol ) and add new ones near them: 
    35  
    36 {{{ 
    37 [mysqld] 
    38  
    39 query_cache_limit = 1M 
    40 query_cache_size = 32M 
    4151}}} 
    4252 
    43 This most critical configuration options 
     53There is already a tool in phpMyAdmin which can help you with deciding what configuration values need to be changed. Go to phpMyAdmin and open "Status" tab, look through this page and pay attention to red values. There is a comment near each value you can use as a guide in changing particular MySQL option. Keep in mind that this is values are collected during some time, so if you change some value it is better to wait at least 1 day until new data is collected for analysis. After MySQL restarted this data is collected from the scratch. 
    4454 
     55This is an example on many queries becomes cached after introducing "query cache": 
    4556 
     57[[Image(hssr-mysql-2-queries-day.png)]] 
     58 
     591 day is showed on the graph, in the beginning cache was disabled then cache was enabled - pink zpne shows SELECT queries which are taken from cache. So, benefits are obvious ! 
 
Below is the legacy version of the Boonex site, maintained for Dolphin.Pro 7.x support.
The new Dolphin solution is powered by UNA Community Management System.
Fork me on GitHub