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 Usage and crashes every few hours

Discussion in 'Workarounds and Optimization' started by Motamedi, Aug 9, 2015.

  1. Motamedi

    Motamedi Well-Known Member

    Joined:
    Mar 14, 2015
    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Iran , Tehran
    cPanel Access Level:
    Root Administrator
    Hello dear,

    MySQL Tunner :

    Code:
     >>  MySQLTuner 1.4.8 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll-lve
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 32G (Tables: 111586)
    [--] Data in InnoDB tables: 31G (Tables: 117684)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in MEMORY tables: 14M (Tables: 2492)
    [!!] Total fragmented tables: 2487
    
    -------- Security Recommendations  -------------------------------------------
    [--] Skipped due to --skippassword option
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 8m 13s (3M q [882.479 qps], 134K conn, TX: 44B, RX: 1B)
    [--] Reads / Writes: 79% / 21%
    [--] Binary logging is disabled
    [--] Total buffers: 67.0G global + 10.6M per thread (1800 max threads)
    [OK] Maximum reached memory usage: 85.7G (68% of installed RAM)
    [OK] Maximum possible memory usage: 85.7G (68.12% of installed RAM)
    [OK] Slow queries: 0% (14/3M)
    [OK] Highest usage of available connections: 3% (69/1800)
    [OK] Aborded connections: 0.18%  (240/134852)
    [!!] Key buffer used: 28.1% (603M used / 2B cache)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/7.0G
    [OK] Read Key buffer hit rate: 99.4% (131M cached / 748K reads)
    [!!] Write Key buffer hit rate: 68.3% (1M cached / 534K writes)
    [OK] Query cache efficiency: 68.6% (1M cached / 2M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (626 temp sorts / 177K sorts)
    [!!] Joins performed without indexes: 1065
    [!!] Temporary tables created on disk: 55% (63K on disk / 113K total)
    [OK] Thread cache hit rate: 99% (69 created / 134K connections)
    [!!] Table cache hit rate: 5% (2K open / 34K opened)
    [OK] Open file limit used: 3% (2K/65K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 32.0G/31.9G
    [!!] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 10.64% (223107 used/ 2097151 total)
    [OK] InnoDB Read buffer efficiency: 99.94% (167914902 hits/ 168016444 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [!!] InnoDB log waits: 0.00% (3 waits / 101717 writes)
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        key_buffer_size (~ 603M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        table_open_cache (> 2000)
        innodb_buffer_pool_instances(=32)
        innodb_log_buffer_size (>= 8M)
    
    and my.cnf configuration :

    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    thread_concurrency              = 10
    
    # MyISAM #
    key-buffer-size                = 2G
    #key_buffer_size=6G
    #myisam-recover                 = FORCE,BACKUP
    join-buffer-size               = 8M
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 100000
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    #expire-logs-days               = 14
    #sync-binlog                    = 1
    
    # WAIT AND TIMEOUT
    #wait_timeout                   = 60
    #interactive_timeout            = 60
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 1G
    max-heap-table-size            = 1G
    query-cache-type               = 1
    query-cache-size               = 32G
    max-connections                = 1800
    thread-cache-size              = 1000
    open-files-limit               = 65535
    #table-definition-cache        = 4096
    table-open-cache               = 2000
    
    # INNODB #
    #innodb-flush-method            = O_DIRECT
    #innodb-log-files-in-group      = 2
    #innodb-flush-log-at-trx-commit = 1
    #innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 32G
    
    # LOGGING #
    #log-queries-not-using-indexes  = 1
    #slow-query-log                 = 1
    #open_files_limit=50000
    innodb_file_per_table=1
    #open_files_limit=50000
    #max_allowed_packet=268435456
    
    Server information :
    128GB Ram
    CPU : 2 x Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz (32core showing in linux).
    Files located in sata disk and mysql databases located in SSD Disk.

    but the query cache size will full and all websites slowly and need to flush query cache size

    aslo after 1hours we need to killall -9 mysqld and restart mysql for solve the crash and flush not working !!!!

    we have 2300 accounts and 6000 domains and 4000 mysql databases on this server

    Please help us

    Thank you.
     
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    First of all I'll suggest you please do not try kill mysql process on live server because some time you will get issues with the innodb files.

    Regarding mysql crash issues, Do you notice any specific error messages in mysql error logs file ?
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,784
    Likes Received:
    665
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. Motamedi

    Motamedi Well-Known Member

    Joined:
    Mar 14, 2015
    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Iran , Tehran
    cPanel Access Level:
    Root Administrator
    Hello dear,

    When crashed i see in emails :

    MySQL authentication failed, and the system could not reset the MySQL root password
    The system was unable to authenticate to the local MySQL server on “localhost”.

    The connection driver reported the following error: Too many connections

    The error reported by the reset attempt was: MySQL failed to start.

    The system was unable to automatically reconnect to the MySQL server.

    You must manually update the password in “/root/.my.cnf” in order to restore normal operations.


    Thank you.
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,784
    Likes Received:
    665
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page