MySQL ушла и настройки базы данных MySQL
у меня есть база данных mysql, которая вылетает примерно 1 раз в день с ошибкой Сервер MySQL ушел. Тайм-аут составляет 28800 секунд, и я установил max_allowed_packet на 1024M, но у меня все еще есть проблема.
В другом ответе я прочитал, что я должен проверить память, чтобы запустить mysqltuner и получить эти отчеты. Я последовал некоторому совету, но я немного запутался … Я установил размер журнала буфера r innodb на 1,1 ГБ, но в отчете говорится, что у меня есть опасное использование оперативной памяти … любая помощь?
Спасибо
>> 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 5.6.38
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/mysql-error.log(8M)
[OK] Log file /var/lib/mysql/mysql-error.log exists
[OK] Log file /var/lib/mysql/mysql-error.log is readable.
[OK] Log file /var/lib/mysql/mysql-error.log is not empty
[OK] Log file /var/lib/mysql/mysql-error.log is smaller than 32 Mb
[!!] /var/lib/mysql/mysql-error.log contains 18740 warning(s).
[!!] /var/lib/mysql/mysql-error.log contains 2505 error(s).
[--] 83 start(s) detected in /var/lib/mysql/mysql-error.log
[--] 1) 2017-12-10 17:13:50 31080 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-12-10 17:06:23 27549 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-12-10 17:03:47 24587 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-12-10 16:40:28 15588 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2017-12-10 15:28:48 3726 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2017-12-10 15:28:43 3123 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2017-12-10 15:03:51 31716 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2017-12-10 14:09:35 22358 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2017-12-10 12:16:42 4590 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2017-12-10 10:52:36 23424 [Note] /usr/sbin/mysqld: ready for connections.
[--] 34 shutdown(s) detected in /var/lib/mysql/mysql-error.log
[--] 1) 2017-12-10 17:13:45 27549 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-12-10 17:06:20 24587 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-12-10 17:03:43 15588 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-12-10 16:40:26 3726 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2017-12-10 15:28:45 3123 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2017-12-10 10:52:33 12404 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2017-12-10 10:09:13 28482 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2017-12-06 13:56:15 13476 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2017-12-06 13:55:13 11594 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2017-12-06 13:52:50 8372 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 674M (Tables: 184)
[--] Data in InnoDB tables: 1G (Tables: 281)
[--] Data in MEMORY tables: 0B (Tables: 1)
[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: 6s (474 q [79.000 qps], 17 conn, TX: 603K, RX: 48K)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 1.8G
[--] Max MySQL memory : 1.6G
[--] Other process memory: 392.4M
[--] Total buffers: 1.2G global + 1.1M per thread (50 max threads)
[--] P_S Max memory usage: 438M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 1.6G (88.24% of installed RAM)
[!!] Maximum possible memory usage: 1.6G (91.06% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/474)
[OK] Highest usage of available connections: 8% (4/50)
[OK] Aborted connections: 0.00% (0/17)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 77 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 92% (72 on disk / 78 total)
[OK] Thread cache hit rate: 76% (4 created / 17 connections)
[OK] Table cache hit rate: 92% (84 open / 91 opened)
[OK] Open file limit used: 0% (20/10K)
[OK] Table locks acquired immediately: 100% (391 immediate / 391 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 438.5M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (6M used / 33M cache)
[!!] Key buffer size / total MyISAM indexes: 32.0M/103.3M
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.1G/1.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 137.0M * 2/1.1G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 96.97% (54277 hits/ 55974 total)
[!!] InnoDB Write Log efficiency: 0% (6 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 6 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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/mysql-error.log file
Control error line(s) into /var/lib/mysql/mysql-error.log file
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
key_buffer_size (> 103.3M)
innodb_buffer_pool_size (>= 1G) if possible.
innodb_buffer_pool_instances(=1)
Это резюме БД:
# Percona Toolkit MySQL Summary Report #######################
System time | 2017-12-10 17:53:01 UTC (local TZ: UTC +0000)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
/var/lib/mysql 0 0 /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/sbin/mysqld
Has symbols | Yes
# Slave Hosts ################################################
No slaves found
# Report On Port 3306 ########################################
User | root@localhost
Time | 2017-12-10 17:53:01 (UTC)
Hostname | sun-server
Version | 5.6.38 MySQL Community Server (GPL)
Built On | Linux x86_64
Started | 2017-12-10 17:13 (up 0+00:39:12)
Databases | 11
Datadir | /var/lib/mysql/
Processes | 2 connected, 2 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /var/lib/mysql/mysql.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
Sleep 3 0 1 1
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
xxx 3 0 0 0
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 4 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
xxx 3 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
3 0 0 0
init 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 11 secs
Aborted_clients 35
Bytes_received 1000000000 12500 15000
Bytes_sent 25000000000 300000 900000
Com_admin_commands 250
Com_begin 50000
Com_change_db 15000
Com_commit 60000 1
Com_delete 8000
Com_insert 50000 1
Com_replace 1250
Com_select 7000000 80 100
Com_set_option 350000 4 5
Com_show_databases 80
Com_show_engine_status 150
Com_show_fields 1000000 10 20
Com_show_processlist 300
Com_show_slave_hosts 40
Com_show_slave_status 40
Com_show_status 25000
Com_show_storage_engines 40
Com_show_table_status 400
Com_show_tables 350
Com_show_variables 150
Com_stmt_close 3000
Com_stmt_execute 3000
Com_stmt_prepare 3000
Com_update 175000 1 1
Connections 200000 2 3
Created_tmp_disk_tables 1000000 10 20
Created_tmp_files 4500
Created_tmp_tables 1000000 10 25
Flush_commands 40
Handler_commit 6000000 70 100
Handler_delete 8000
Handler_external_lock 15000000 175 225
Handler_read_first 400000 4 5
Handler_read_key 175000000 2000 800
Handler_read_last 6000
Handler_read_next 1000000000 12500 8000
Handler_read_prev 22500000 250 4500
Handler_read_rnd 15000000 175 50
Handler_read_rnd_next 2000000000 22500 17500
Handler_rollback 40
Handler_update 1000000 10 3
Handler_write 22500000 250 450
Innodb_buffer_pool_bytes_data 20000000000 225000 7000
Innodb_buffer_pool_bytes_dirty 70000000 800 -86835
Innodb_buffer_pool_pages_flushed 600000 6 15
Innodb_buffer_pool_read_ahead 150000 1
Innodb_buffer_pool_read_requests 2000000000 22500 10000
Innodb_buffer_pool_reads 1000000 10
Innodb_buffer_pool_write_requests 1500000 15 15
Innodb_data_fsyncs 400000 4 6
Innodb_data_read 20000000000 225000 7000
Innodb_data_reads 1250000 15
Innodb_data_writes 900000 10 15
Innodb_data_written 17500000000 225000 450000
Innodb_dblwr_pages_written 600000 6 15
Innodb_dblwr_writes 100000 1 1
Innodb_log_write_requests 200000 2 1
Innodb_log_writes 200000 2 1
Innodb_os_log_fsyncs 70000
Innodb_os_log_written 175000000 2250 1750
Innodb_pages_created 3500
Innodb_pages_read 1250000 15
Innodb_pages_written 600000 6 15
Innodb_rows_deleted 7000
Innodb_rows_inserted 50000 1
Innodb_rows_read 3000000000 35000 30000
Innodb_rows_updated 200000 2 1
Innodb_num_open_files 10000
Innodb_available_undo_logs 5000
Key_read_requests 15000000 175 1
Key_reads 70000
Key_write_requests 15000
Key_writes 10000
Open_table_definitions 20000
Opened_files 4000000 50 90
Opened_table_definitions 20000
Opened_tables 22500
Queries 9000000 100 150
Questions 9000000 100 150
Select_full_join 5000
Select_full_range_join 40
Select_range 80000
Select_scan 1500000 15 25
Slow_queries 80
Sort_merge_passes 2250
Sort_range 1250000 15 20
Sort_rows 12500000 150 50
Sort_scan 125000 1 1
Table_locks_immediate 7000000 80 100
Table_locks_waited 40
Table_open_cache_hits 8000000 100 125
Table_open_cache_misses 22500
Threads_created 800
Uptime 90000 1 1
# Table cache ################################################
Size | 4096
Usage | 15%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.6.38
Buffer Pool Size | 1.1G
Buffer Pool Fill | 45%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 137.0M = 274.0M
Log Buffer Size | 8M
Flush Method | O_DIRECT
Flush Log At Commit | 2
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 13k
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 2400
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 1xnot started
# MyISAM #####################################################
Key Cache | 32.0M
Pct Used | 25%
Unflushed | 0%
# Security ###################################################
Users | 60 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Binary Logging #############################################
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | NO_ENGINE_SUBSTITUTION
join_buffer_size | 256k
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 32M
tmp_table_size | 32M
max_allowed_packet | 1G
thread_stack | 256k
log |
log_error | /var/lib/mysql/mysql-error.log
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
# Configuration File #########################################
Config File | /etc/my.cnf
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
performance-schema = 0
performance-schema = 0
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
performance-schema = 0
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
performance-schema = 0
max_allowed_packet = 1024M
max-connect-errors = 1000000
performance-schema = 0
datadir = /var/lib/mysql/
performance-schema = 0
performance-schema = 0
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 50
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
performance-schema = 0
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 137M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 1100M
performance-schema = 0
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 0
long_query_time = 2
slow-query-log-file = /var/lib/mysql/mysql-slow.log
performance-schema = ON
# Memory management library ##################################
jemalloc is not enabled in MySQL config for process with ID 31080
# The End ####################################################
Решение
Ваш сервер имеет менее 2 ГБ оперативной памяти.
my.cnf / ini что делать в секции [mysqld]
#max_allowed_packet=1024M # to allow default to work for you.
#innodb_log_file_size=1.1G # allow default to work for you.
Наблюдение, ваш innodb_buffer_pool_size 1.1G имеет эффективный размер ~ 800M после того, как вы считаете, что для innodb_change_buffer_max_size по умолчанию установлено значение 25%. Доступ к 4G RAM и увеличение вашего innodb_buffer_pool_size до 2G позволят innodb передохнуть.
Другие решения
max_connect_errors при 1000000 ПОЗВОЛИТ хакеру / взломщику 1 миллион попыток «угадать» имя пользователя / пароль для доступа к вашей системе. Если вы действительно не имеете это в виду, установите 10 (и тогда у вас будет разумная мера защиты). Да, я знаю, 1 миллион по умолчанию.