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!

MySQL Load - Failed to establish Database

Discussion in 'General Discussion' started by codegirl42, Feb 29, 2008.

  1. codegirl42

    codegirl42 Well-Known Member

    Joined:
    Mar 9, 2006
    Messages:
    93
    Likes Received:
    0
    Trophy Points:
    156
    Hoping someone can help me analyze this.
    I have one site on the server that has extremely high MYSQL load.
    The site seems to get a lot of "Failed to establish Database connection" errors when people are trying to access all at once. After a minute or two, the site will load again.

    Any help would be greatly appreciated. Thanks so much.

    Here's the output from the tuning primer script:
    ---------------------------------------------------------------------


    /usr$ ./tuning-primer.sh
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    
     -- MYSQL PERFORMANCE TUNING PRIMER --
     - By: Matthew Montgomery -
    
    MySQL Version 4.1.22-standard i686
    
    Uptime = 181 days 20 hrs 37 min 25 sec
    Avg. qps = 14
    Total Questions = 223271032
    Threads Connected = 4
    
    Server has been running for over 48hrs.
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    It should be safe to follow these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10 sec.
    You have 1666 out of 223271055 that take longer than 10 sec. to complete
    Your long_query_time may be too high, I typically set this under 5 sec.
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/4.1/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 8
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    Current threads_cached = 6
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 500
    Current threads_connected = 2
    Historic max_used_connections = 73
    The number of used connections is 14% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    MEMORY USAGE
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    Max Memory Ever Allocated : 758 M
    Configured Max Per-thread Buffers : 2 G
    Configured Max Global Buffers : 426 M
    Configured Max Memory Limit : 2 G
    Physical Memory : 1.94 G
    
    Max memory limit exceeds 90% of physical memory
    
    KEY BUFFER
    Current MyISAM index space = 92 M
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    Current key_buffer_size = 384 M
    Key cache miss rate is 1 : 627
    Key buffer fill ratio = 4.00 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 21 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 68.61 %
    Current query_cache_min_res_unit = 4 K
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    tput: No value for $TERM and no -T specified
    Current join_buffer_size = 132.00 K
    You have had 793425 queries where a join could not use an index properly
    You have had 1292 joins without keys that check for key usage after each row
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
     
  2. troxalias

    troxalias Well-Known Member

    Joined:
    Nov 21, 2001
    Messages:
    96
    Likes Received:
    0
    Trophy Points:
    306
    Location:
    Athens - Greece
    What is the server's overall load ? Could you please post your /etc/my.cnf file as well as an estimation of the concurrent users on the site ?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. codegirl42

    codegirl42 Well-Known Member

    Joined:
    Mar 9, 2006
    Messages:
    93
    Likes Received:
    0
    Trophy Points:
    156
    Hi,

    Here is the etc/my.conf

    [mysqld]
    set-variable = max_connections=1000
    set-variable = max_user_connections=500
    set-variable = interactive_timeout=100
    set-variable = wait_timeout=15
    set-variable = connect_timeout=10
    safe-show-database
    key_buffer = 20M
    max_allowed_packet = 16M
    table_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 3M
    myisam_sort_buffer_size = 64M
    thread_cache = 128
    query_cache_size = 100M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 4
    safe-show-database
     
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