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 Database crashed/CPU higher on server to down

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

  1. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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 Samet Chan, Oct 21, 2017
    Last edited by a moderator: Oct 21, 2017
  2. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    776
    Likes Received:
    268
    Trophy Points:
    113
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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,858
    Likes Received:
    89
    Trophy Points:
    78
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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 Samet Chan, Oct 21, 2017
    Last edited: Oct 21, 2017
  6. sktest123

    sktest123 Well-Known Member

    Joined:
    Jan 31, 2017
    Messages:
    99
    Likes Received:
    6
    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. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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 Samet Chan, Oct 22, 2017
    Last edited: Oct 22, 2017
    cesarlopes likes this.
  8. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,720
    Likes Received:
    1,883
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,720
    Likes Received:
    1,883
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  11. Samet Chan

    Samet Chan Well-Known Member

    Joined:
    Jun 24, 2016
    Messages:
    350
    Likes Received:
    30
    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

  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