Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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:
    33
    Likes Received:
    0
    Trophy Points:
    56
    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:
    156
    Location:
    Michigan, US
    cPanel Access Level:
    DataCenter Provider
    If I had to pick one more, it would be key_buffer.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice