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.

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:
    6
    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:
    6
    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 ?
     
  3. codegirl42

    codegirl42 Well-Known Member

    Joined:
    Mar 9, 2006
    Messages:
    93
    Likes Received:
    0
    Trophy Points:
    6
    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