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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Database crashed/CPU higher on server to down

Discussion in 'Workarounds and Optimization' started by Sametto Chan, Oct 21, 2017.

  1. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:
    Dear,


    I think MySQL maybe problem in /etc/my.cnf

    We running on latest of MariaDB.

    My server is VPS - SSHD - SSD-Boosted,

    30GB RAM - DDR3
    6x Cores - CPU
    2TB SSHD

    my.cnf there, If a line has bad.
    Code:
    [mysqld]
    log-error=/var/lib/mysql/somenme.example.net.err
    default-storage-engine = MyISAM
    innodb_file_per_table = 1
    performance-schema = 0
    max_allowed_packet = 268435456
    local-infile = 0
    open_files_limit = 10192
    table_open_cache = 24000
    table_definition_cache = 1024
    max_connections = 500
    query_cache_size = 8M
    join_buffer_size = 128K
    thread_cache_size = 4
    table_cache = 1024
    
     
    #1 Sametto Chan, Oct 21, 2017
    Last edited by a moderator: Oct 21, 2017
  2. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    575
    Likes Received:
    175
    Trophy Points:
    43
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Run the following in a terminal as root and carefully read the output
    Code:
    mysqld --help --verbose
    If there is anything not right, it should error out with an explanation.
     
  3. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:
    I can't copy & paste, SSH command is limited. This line is too many. How I do full copy the verbose?
     
  4. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,669
    Likes Received:
    73
    Trophy Points:
    28
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi,

    You got to analyse the database configuration and then decide. You can use mysqltuner and/or mysql primer to get started with. If you have good hardware and have MySQL/Mariadb not properly optimized, then you are still going to face issue.
     
  5. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:
    Solved my problem scrollback 500 changed to 5000 from SecureCRT fixed now.

    There,
    verbose.txt uploaded for file attach.

    My VPS Server is unmanaged server. I can't buy a managed server is expensive. I have no enough money. Only limit $30 EURO monthly.

    1. Which I have to use InnoDB or MyISAM?

    2. Host support said no issue my hardware. I think my.cnf is a problem. I saw CPU is higher of MySQL only.
    Code:
    Load Averages: 22.47 10.36 4.46
    
    3. Where can I find error logs for MySQL and hardware failure, site error on SFTP?

    Code:
    [root@unknownxanime src]# perl mysqltuner.pl
     >>  MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.2.9-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Log file Recommendations ------------------------------------------------------------------
    [--] Log file: /var/lib/mysql/animexunknown.unknown.net.err(1M)
    [OK] Log file /var/lib/mysql/animexunknown.unknown.net.err exists
    [OK] Log file /var/lib/mysql/animexunknown.unknown.net.err is readable.
    [OK] Log file /var/lib/mysql/animexunknown.unknown.net.err is not empty
    [OK] Log file /var/lib/mysql/animexunknown.unknown.net.err is smaller than 32 Mb
    [!!] /var/lib/mysql/animexunknown.unknown.net.err contains 2391 warning(s).
    [!!] /var/lib/mysql/animexunknown.unknown.net.err contains 2293 error(s).
    [--] 92 start(s) detected in /var/lib/mysql/animexunknown.unknown.net.err
    [--] 1) 2017-10-21  3:18:26 140421808523392 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 2) 2017-10-21  3:08:04 140085739542656 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 3) 2017-10-21  2:08:28 140208588351616 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 4) 2017-10-19  2:36:44 140705789565056 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 5) 2017-10-19  2:36:33 140568863770752 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 6) 2017-10-17 19:30:14 140586253817984 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 7) 2017-10-17 19:30:04 139620718205056 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 8) 2017-10-14 19:51:50 140700734711936 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 9) 2017-10-14 19:51:40 139751456983168 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 10) 2017-10-04 16:23:04 139651866327168 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 91 shutdown(s) detected in /var/lib/mysql/animexunknown.unknown.net.err
    [--] 1) 2017-10-21  3:08:06 140085462673152 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 2) 2017-10-21  3:03:06 140207802783488 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 3) 2017-10-21  2:07:00 140705346029312 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 4) 2017-10-19  2:36:35 140568586901248 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 5) 2017-10-19  2:34:13 140585760294656 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 6) 2017-10-17 19:30:06 139620597221120 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 7) 2017-10-17 19:27:59 140700247639808 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 8) 2017-10-14 19:51:41 139751017780992 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 9) 2017-10-14 19:49:50 139651063711488 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 10) 2017-10-04 16:22:56 140438602782464 [Note] /usr/sbin/mysqld: Shutdown complete
    
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
    [--] Data in MyISAM tables: 472M (Tables: 89)
    [--] Data in InnoDB tables: 4G (Tables: 1295)
    [--] Data in MEMORY tables: 2M (Tables: 24)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [--] There are 612 basic passwords in the list.
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 13h 17m 8s (6M q [132.711 qps], 293K conn, TX: 269G, RX: 2G)
    [--] Reads / Writes: 87% / 13%
    [--] Binary logging is disabled
    [--] Physical Memory     : 29.3G
    [--] Max MySQL memory    : 1.8G
    [--] Other process memory: 1.0G
    [--] Total buffers: 417.0M global + 2.9M per thread (500 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 446.1M (1.49% of installed RAM)
    [OK] Maximum possible memory usage: 1.8G (6.24% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/6M)
    [OK] Highest usage of available connections: 2% (10/500)
    [OK] Aborted connections: 0.04%  (104/293007)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [!!] Query cache efficiency: 0.0% (0 cached / 4M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 320K sorts)
    [!!] Joins performed without indexes: 506
    [!!] Temporary tables created on disk: 98% (218K on disk / 221K total)
    [OK] Thread cache hit rate: 99% (10 created / 293K connections)
    [OK] Table cache hit rate: 82% (1K open / 1K opened)
    [OK] Open file limit used: 0% (107/240K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    [--] Memory used by P_S: 0B
    [--] Sys schema isn't installed.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 6 thread(s).
    [--] Using default value is good enough for your version (10.2.9-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 31.4% (42M used / 134M cache)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/261.0M
    [OK] Read Key buffer hit rate: 99.6% (4M cached / 15K reads)
    [!!] Write Key buffer hit rate: 56.4% (659K cached / 371K writes)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [!!] InnoDB buffer pool / data size: 128.0M/4.1G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
    [OK] InnoDB buffer pool instances: 1
    [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
    [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
    [OK] InnoDB Read buffer efficiency: 99.96% (361634378 hits/ 361761376 total)
    [OK] InnoDB Write log efficiency: 91.95% (1527951 hits/ 1661676 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 133725 writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 98.9% (20M cached / 218K reads)
    
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- XtraDB Metrics ----------------------------------------------------------------------------
    [--] XtraDB is disabled.
    
    -------- RocksDB Metrics ---------------------------------------------------------------------------
    [--] RocksDB is disabled.
    
    -------- Spider Metrics ----------------------------------------------------------------------------
    [--] Spider is disabled.
    
    -------- Connect Metrics ---------------------------------------------------------------------------
    [--] Connect is disabled.
    
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Control warning line(s) into /var/lib/mysql/animexunknown.unknown.net.err file
        Control error line(s) into /var/lib/mysql/animexunknown.unknown.net.err file
        MySQL started within last 24 hours - recommendations may be inaccurate
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
        Performance should be activated for better diagnostics
        Consider installing Sys schema from https://github.com/mysql/mysql-sys
        Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: bit.ly/2wgkDvS
    Variables to adjust:
        query_cache_size (=0)
        query_cache_type (=0)
        query_cache_limit (> 1M, or use smaller result sets)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        performance_schema = ON enable PFS
        innodb_buffer_pool_size (>= 4G) if possible.
        innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    
     
    #5 Sametto Chan, Oct 21, 2017
    Last edited: Oct 21, 2017
  6. sktest123

    sktest123 Well-Known Member

    Joined:
    Jan 31, 2017
    Messages:
    86
    Likes Received:
    5
    Trophy Points:
    8
    Location:
    kochin
    cPanel Access Level:
    Root Administrator
    You have got enough ram , so adjust innodb parameters , the error log is the one you editd and pasted previously :)

    logerror=/var/lib/mysql/somenme.example.net.err
    Check command mysqladmin proc.
     
  7. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:
    Code:
    [root@unknown ~]# mysqladmin proc
    +---------+---------------------+-----------+-------------------------------------+----------------+------+--------------------------+--------------------------------------------------------------------------+----------+
    | Id      | User                | Host      | db                                  | Command        | Time | State                    | Info                                                                     | Progress |
    +---------+---------------------+-----------+-------------------------------------+----------------+------+--------------------------+--------------------------------------------------------------------------+----------+
    | 2       | system user         |           |                                     | Daemon         |      | InnoDB purge worker      |                                                                          | 0.000    |
    | 1       | system user         |           |                                     | Daemon         |      | InnoDB purge coordinator |                                                                          | 0.000    |
    | 3       | system user         |           |                                     | Daemon         |      | InnoDB purge worker      |                                                                          | 0.000    |
    | 4       | system user         |           |                                     | Daemon         |      | InnoDB purge worker      |                                                                          | 0.000    |
    | 5       | system user         |           |                                     | Daemon         |      | InnoDB shutdown handler  |                                                                          | 0.000    |
    | 973217  | DELAYED             | localhost | unknown               | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1029851 | DELAYED             | localhost | unknown               | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1050799 | DELAYED             | localhost | unknown2 | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1051663 | DELAYED             | localhost | unknown               | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1052290 | DELAYED             | localhost | unknown2 | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1052675 | DELAYED             | localhost | unknown2 | Delayed insert |      | Waiting for INSERT       |                                                                          | 0.000    |
    | 1053342 | unknown | localhost | unknown               | Prepare        | 0    | closing tables           | SELECT user.*
    
                            FROM xf_user AS user
    
                            WHERE user.user_id = ? | 0.000    |
    | 1053343 | unknown | localhost | unknown               | Sleep          | 0    |                          |                                                                          | 0.000    |
    | 1053345 | unknown | localhost | unknown               | Sleep          | 0    |                          |                                                                          | 0.000    |
    | 1053346 | root                | localhost |                                     | Query          | 0    | init                     | show processlist                                                         | 0.000    |
    +---------+---------------------+-----------+-------------------------------------+----------------+------+--------------------------+--------------------------------------------------------------------------+----------+
    
    Adjust edited to improvements my.cnf by mysqltuner.
    Code:
    [mysqld]
    log-error=/var/lib/mysql/anime.unknown.net.err
    default-storage-engine = MyISAM
    innodb_file_per_table = 1
    performance-schema = 1
    performance_schema_events_waits_history_size = 20
    performance_schema_events_waits_history_long_size = 15000
    max_allowed_packet = 268435456
    local-infile = 0
    open_files_limit = 10192
    table_open_cache = 24000
    table_definition_cache = 1024
    max_connections = 500
    query_cache_size = 0
    query_cache_type = 0
    query_cache_limit = 1M
    join_buffer_size = 128
    thread_cache_size = 8
    table_cache = 8192
    tmp_table_size = 16M
    max_heap_table_size = 16M
    innodb_buffer_pool_size = 4G
    innodb_log_file_size = 512M
    thread_stack = 128K
    key_buffer_size = 1280M
    innodb_buffer_pool_instances = 4
    
    It's all good?
     
    #7 Sametto Chan, Oct 22, 2017
    Last edited: Oct 22, 2017
    cesarlopes likes this.
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,396
    Likes Received:
    1,605
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Let us know if you notice any improvement after modifying your /etc/my.cnf values.

    Thank you.
     
  9. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:

    There, my.cnf
    Code:
    [mysqld]
    log-error=/var/lib/mysql/hero.unknown.net.err
    default-storage-engine = MyISAM
    innodb_file_per_table = 1
    performance-schema = 1
    performance_schema_events_waits_history_size = 20
    performance_schema_events_waits_history_long_size = 15000
    max_allowed_packet = 268435456
    local-infile = 0
    open_files_limit = 10192
    table_open_cache = 24000
    table_definition_cache = 1024
    max_connections = 500
    query_cache_size = 0
    query_cache_type = 0
    query_cache_limit = 1M
    join_buffer_size = 128
    thread_cache_size = 8
    table_cache = 8192
    tmp_table_size = 16M
    max_heap_table_size = 16M
    innodb_buffer_pool_size = 4G
    innodb_log_file_size = 512M
    thread_stack = 128K
    key_buffer_size = 1280M
    innodb_buffer_pool_instances = 4
    
    If they are wrong or not. Let me know. :)
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,396
    Likes Received:
    1,605
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    You'd need to consult with a system administrator if you'd like direct help tuning your MySQL configuration, as that's outside the scope of support we offer. You can find a list of companies offering system admin services at:

    System Administration Services | cPanel Forums

    Thank you.
     
  11. Sametto Chan

    Sametto Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    280
    Likes Received:
    25
    Trophy Points:
    103
    cPanel Access Level:
    Root Administrator
    Twitter:
    Thank you, I don't like a managed server is expensive.

    I gave up details has no issue for 4 days right now. Without CPU higher and crash MySQL.

    Thanks to mysqltuner fixed my server for my.cnf!
     
Loading...

Share This Page