MySQL Database crashed/CPU higher on server to down

[Samet Chan]

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.

[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

 

[rpvw]

Run the following in a terminal as root and carefully read the output

mysqld --help --verbose

If there is anything not right, it should error out with an explanation.

 

[Samet Chan]

Run the following in a terminal as root and carefully read the output

mysqld --help --verbose

If there is anything not right, it should error out with an explanation.

I can't copy & paste, SSH command is limited. This line is too many. How I do full copy the verbose?

 

[24x7server]

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.

 

[Samet Chan]

Run the following in a terminal as root and carefully read the output

mysqld --help --verbose

If there is anything not right, it should error out with an explanation.

Solved my problem scrollback 500 changed to 5000 from SecureCRT fixed now.

There,
verbose.txt uploaded for file attach.

You got to analyse the database configuration and then decide. You can use mysqltuner and/or mysql primer to get started with.

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.

Load Averages: 22.47 10.36 4.46

3. Where can I find error logs for MySQL and hardware failure, site error on SFTP?

[[email protected] src]# perl mysqltuner.pl
 >>  MySQLTuner 1.7.4 - Major Hayden <[email protected]>
 >>  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.

 

[sktest123]

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.

 

[Samet Chan]

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.

[[email protected] ~]# 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.

[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?

 

[cPanelMichael]

Hello,

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

Thank you.

 

[Samet Chan]

Hello,

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

Thank you.

There, my.cnf

[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.

 

[cPanelMichael]

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.

 

[Samet Chan]

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.

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!