MySQL Usage and crashes every few hours

[Motamedi]

Hello dear,

MySQL Tunner :

 >>  MySQLTuner 1.4.8 - Major Hayden <[email protected]>
>>  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 :

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

 

[24x7server]

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 ?

 

[cPanelMichael]

Hello

Do you notice any specific error messages in the /var/lib/mysql/$hostname.err file when MySQL fails?

Thank you.

 

[Motamedi]

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.

 

[cPanelMichael]

Could you review /var/lib/mysql/$hostname.err and let us know the entries that appear at the time of the failure?

Thank you.