The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL-WHM Optimization for high traffic sites?

Discussion in 'Workarounds and Optimization' started by sOliver, Sep 27, 2011.

  1. sOliver

    sOliver Active Member

    Joined:
    Oct 25, 2010
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    Hi,


    what do you think is the most critical SQL setting for high traffic sites (with database caches)?

    I was having slow DNS lookups so I added skip-name-resolve. Seems to be running much smoother now. I am currently considering adding skip-networking, but I already blocked the port anyway.

    A lot of my tables are created on disk although I have plenty of RAM, probably because of mostly TEXT colums.


    Let's say you have a lot of spare RAM, what would you increase?


    My.cnf:

    Code:
    [mysqld]
    socket=/###/mysql/mysql.sock
    local-infile = 0
    skip-innodb
    skip-name-resolve
    max_allowed_packet = 32M
    log-warnings
    
    # MySQL 4.x has query caching available.
    # Enable it for vast improvement and it may be all you need to tweak.
    query_cache_type=1
    query_cache_limit=4M
    query_cache_size=128M
    
    
    # Reduced to 200 as memory will not be enough for 500 connections.
    # memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
    # which is now: 64 + (1 + 1) * 200 = 464 MB
    # max_connections = approx. MaxClients setting in httpd.conf file
    # Default set to 100.
    max_connections=300
    
    
    ##temporary tables##
    ##tmp_table_size 32
    ##max_heap_table_size 16
    max_heap_table_size = 90M
    tmp_table_size = 90M
    
    
    # Checked opened tables and adjusted accordingly after running for a while.
    table_cache=1024
    thread_cache_size=50
    
    
    
    #####LOGS#######
    # log slow queries is a must. Many queries that take more than 2 seconds.
    # If so, then your tables need enhancement.
    log_slow_queries=/var/log/slow.log
    long_query_time=1
    #log=/var/log/mysqld.log
    log_long_format
    slow_launch_time=1
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
     
  2. jdarow

    jdarow Well-Known Member
    PartnerNOC

    Joined:
    May 30, 2003
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Michigan, US
    cPanel Access Level:
    DataCenter Provider
    If I had to pick one more, it would be key_buffer.
     
Loading...

Share This Page