最大可能記憶體使用量：3.2G（已安裝 RAM 的 163.34%）

我經常遇到數據庫崩潰……這是可以解釋的，因為 mysqltuner 告訴我 mysql 對記憶體有胃口。由於我不是數據庫專家，我希望您能指出我在哪裡查看和編輯什麼的正確方向。

我在跑：

• Ubuntu 16.04.6 64 位
• Intel(R) Xeon(R) CPU E5-2630L v2 @ 2.40GHz
• 2GB 記憶體
• MySQL 版本 5.7.26
• 由 Virtualmin 管理

我將 Speedemy 設置添加到 my.cnf 以查看它是否可以解決我的問題。伺服器仍然記憶體不足。

---

mysqltuner 輸出

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 147M (Tables: 203)
[!!] Total fragmented tables: 14

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 0m 2s (7K q [2.004 qps], 894 conn, TX: 22M, RX: 994K)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 592.0M global + 12.5M per thread (214 max threads)
[OK] Maximum reached memory usage: 642.0M (32.10% of installed RAM)
[!!] Maximum possible memory usage: 3.2G (163.34% of installed RAM)
[OK] Slow queries: 0% (0/7K)
[OK] Highest usage of available connections: 1% (4/214)
[OK] Aborted connections: 0.00%  (0/894)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 355 sorts)
[OK] Temporary tables created on disk: 15% (262 on disk / 1K total)
[OK] Thread cache hit rate: 99% (4 created / 894 connections)
[!!] Table cache hit rate: 14% (383 open / 2K opened)
[OK] Open file limit used: 2% (22/1K)
[OK] Table locks acquired immediately: 100% (164 immediate / 164 locks)
[OK] Binlog cache memory access: 100.00% ( 153 Memory / 153 Total)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/43.0K
[!!] Read Key buffer hit rate: 94.3% (122 cached / 7 reads)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/147.8M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.12% (1648 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 98.95% (142629 hits/ 144137 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 189 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- 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
   Reduce your overall MySQL memory footprint for system stability
   Increase table_open_cache gradually to avoid file descriptor limits
   Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
   Beware that open_files_limit (1024) variable 
   should be greater than table_open_cache ( 400)
Variables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
   query_cache_size (>= 8M)
   table_open_cache (> 400)
   innodb_buffer_pool_size (>= 147M) if possible.

---

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 402653184      |
| myisam_sort_buffer_size             | 67108864       |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 2097152        |
| read_rnd_buffer_size                | 8388608        |
| sort_buffer_size                    | 2097152        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
24 rows in set (0.00 sec)

---

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

key_buffer_size = 384M
max_allowed_packet = 1M
thread_cache_size = 8

myisam-recover-options  = BACKUP

query_cache_limit   = 1M
query_cache_size = 32M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size   = 100M
innodb_file_per_table = 1
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_open_cache = 512

---

my.cnf

（我將 speedemy 設置添加到“空”預設設置中）

### This my.cnf file is based on the following speedemy.com blog post:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof

[client]

###
### client section - used for "mysql", "mysqladmin" and similar command line
### clients.
###

#port = 3306
#socket = /tmp/mysql.sock
### Just in case your current configuration is not using default values.

[mysqld]

###
### mysqld section - used by MySQL Server (also applies to Percona Server,
### MariaDB etc.)
###

#port = 3306
#socket = /tmp/mysql.sock
#user = mysql
### Just in case your current configuration is not using default values.

datadir = /var/lib/mysql
### This must point to the main MySQL data directory.


###
### General Server Options:
###

max_allowed_packet = 32M
### Default packet limit is almost always too small.

max_connections = 2000
### Max connections as well (sleeping threads are okay to have)

#table_open_cache = 2000
### Table open cache - defaults only good in MySQL 5.7 and 5.6
### On 5.1 or 5.5 set the above.

#table_open_cache_instances = 16
### Table open cache instances - be sure to set this on MySQL 5.6.

open_files_limit = 10000
### Increase the number of file descriptors, we're not in stone age now.

tmp_table_size = 64M
max_heap_table_size = 64M
### Incease limits for implicit and explicit temporary tables.

tmpdir = /tmp
### Don't use tmpfs for tmpdir, or AIO will be disabled. And make sure
### There's enough room for temporary tables and files (usually 2-5GB)

#thread_cache_size = 100
### Thread cache - this is now autosized in MySQL 5.6 and 5.7
### But on MySQL 5.1 and 5.5 do set the above

default_storage_engine = InnoDB
### Default storage engine in most cases should be InnoDB.

skip_name_resolve
### Disabling DNS resolution

query_cache_type=0
query_cache_size=0

#back_log = 512
### Consider increasing this if you have a high number of new connections
### (> 1000/sec) and you are running on MySQL 5.6 or older

#thread_concurrency = 0
### Do not tune this. This does nothing. And I have included it here only
### because I've seen too many people obsess over it.

#join_buffer_size = 256k
#sort_buffer_size = 256k
#read_buffer_size = 128k
#read_rnd_buffer_size = 256k
### Leave these at their defaults, do not change server-wide settings for them
### Instead, use session variable when you really need it like that:
### mysql> SET session read_buffer_size = 2 * 1024 * 1024;
### mysql> RUN YOUR QUERY;


###
### Binary logging section
###

log_bin
server_id = 1
### Enabling binlog as well as unique server_id for
### point in time recovery and, potentially, replication.

max_binlog_size = 100M
### Don't have large binary logs, with file systems like ext3 this could
### cause stalls.

#master_info_repository = TABLE
#relay_log_info_repository = TABLE
### If you're on MySQL 5.6 or MySQL 5.7, store the binlog position
### to TABLE rather than FILE (that way position is in sync with db)

expire_logs_days = 7
### Control the amount of binary logs server is allowed to keep (in days)

sync_binlog = 0
### Disabling sync-binlog for better performance, but do consider the
### durability issues

binlog_format = MIXED
### For the binary log format, I recommend MIXED, but it's up to you.

###
### MyISAM Specific Options:
###

### Assuming MyISAM is not going to be used, therefore defaults used for MyISAM
### Still if you do plan to use it, this is probably the only variable you
### want to tune:
#key_buffer_size = 128M


###
### InnoDB Specific Options:
###

#innodb_buffer_pool_size = 96G
### Set the innodb buffer pool size to 75-80% of total memory (dedicated)
### The example above would be used on a dedicated 128GB MySQL server.

#innodb_log_file_size = 2047M
### Allow 1-2h worth of writes to be buffered in transaction logs:
### Helper command:
### mysql> pager grep seq
### mysql> show engine innodb status\G select sleep(60); show engine innodb status\G

innodb_log_buffer_size = 16M
### Don't sweat about about this, just set it to 16M.

innodb_flush_log_at_trx_commit = 0
### This, OTOH is really important.

innodb_flush_method = O_DIRECT
### On Linux, just leave it set to O_DIRECT.

#innodb_buffer_pool_instances = 4
### Set this depending on MySQL version. Available since 5.6
### In 5.6, set to 4
### In 5.7, set to 8

innodb_thread_concurrency = 8
### Yes, this is not an error. Use 0 only for benchmarks and if you're nowhere
### near saturating your server.

#innodb_io_capacity = 1000
#innodb_io_capacity_max = 3000
### Base these on your server radom write IO capabilities

innodb_stats_on_metadata = 0
### On 5.6 and 5.7 this is already turned off by default.

#innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
#innodb_buffer_pool_load_at_startup  = 1 # MySQL 5.6+
#innodb_buffer_pool_dump_pct = 75        # MySQL 5.7 only
### Enable these for faster warm-up

#innodb_adaptive_hash_index_parts = 16      # MySQL 5.7 only
#innodb_adaptive_hash_index_partitions = 16 # Percona Server only
### AHI is a common bottle-neck, however few versions of MySQL

#innodb_checksum_algorithm = crc32     # MySQL 5.6 or newer
### Hardware acceleratorion for checksums

innodb_read_io_threads = 16
innodb_write_io_threads = 16
### This isn't super important to fine tune, but it's good to aling
### it to number of availble read / write spindles

innodb_file_per_table
### There's a number of reasons to use file per table, but beware that
### it doesn't convert tables automatically, and ibdata1 will never shrink anyways

#innodb_open_files = 3000
### Set this on MySQL 5.1 and 5.5. On 5.6 and beyond it's autotuned
### specifies max number of .ibd files that MySQL can keep open at one time

#innodb_flush_neighbors = 0
### Set to 0 if you're using SSD. For magnetic drives, keep it at 1
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

#innodb_flushing_avg_loops = 100
### Use this if you don't like how flushing activity behaves
### (e.g. if you get stalls)
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flushing_avg_loops

#innodb_page_cleaners = 8 # MySQL 5.7 only
### On a write-heavy environment, use this to gain even more control
### over the flushing activity. This is not yet tested too wildly.
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners

#innodb_old_blocks_time = 1000
### Use this only on 5.1 and 5.5 since 5.6 and 5.7 already have 1000 as a default
### This basically protects your buffer pool from occasional scans
### Although the way it works is quite complex, definitely not for my.cnf

#innodb_max_dirty_pages_pct
### If you're in a write-heavy environment, but want to limit how much of it
### you use for dirty pages, this is the variable to configure. Defaults are
### version specific, but range in 75-90 %


###
### Slow query log (mostly Percona XtraDB Specific)
###

long_query_time = 1.0
### Log queries that take > 1s to execute

slow_query_log = 1
### Enable the slow query logging

### Only to be used on Percona Server:

#log_slow_slave_statements = ON
#log_slow_verbosity = full
#slow_query_log_timestamp_always = ON
#slow_query_log_timestamp_precision = microsecond
#slow_query_log_use_global_control = all
### See here for more information:
### https://www.percona.com/doc/percona-server/5.6/diagnostics/slow_extended.html

---

我跑了ulimit -n 10000並添加[Service] LimitNOFILE=infinity LimitMEMLOCK=infinity到/etc/systemd/system/mysql.service

它仍然在我身上崩潰。這是日誌：

2019-05-09T15:10:39.704727Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-09T15:10:39.711331Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1-log) starting as process 18050 ...
2019-05-09T15:10:39.716998Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=server3-bin' to avoid this problem.
2019-05-09T15:10:39.745721Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-09T15:10:39.745762Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-09T15:10:39.745770Z 0 [Note] InnoDB: Uses event mutexes
2019-05-09T15:10:39.745776Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-09T15:10:39.745786Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-05-09T15:10:39.745793Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-09T15:10:39.750629Z 0 [Note] InnoDB: Number of pools: 1
2019-05-09T15:10:39.758809Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-09T15:10:39.765671Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-05-09T15:10:39.766744Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-05-09T15:10:39.766761Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-05-09T15:10:39.766769Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-05-09T15:10:39.766782Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-05-09T15:10:39.766789Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-05-09T15:10:39.766796Z 0 [ERROR] Failed to initialize builtin plugins.
2019-05-09T15:10:39.766801Z 0 [ERROR] Aborting

2019-05-09T15:10:39.767196Z 0 [Note] Binlog end
2019-05-09T15:10:39.767262Z 0 [Note] Shutting down plugin 'CSV'
2019-05-09T15:10:39.769116Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2019-05-09T15:11:21.632996Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-09T15:11:21.643635Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1-log) starting as process 1848 ...
2019-05-09T15:11:21.675629Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=server3-bin' to avoid this problem.
2019-05-09T15:11:22.173868Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-09T15:11:22.173905Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-09T15:11:22.173922Z 0 [Note] InnoDB: Uses event mutexes
2019-05-09T15:11:22.173929Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-09T15:11:22.173935Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-05-09T15:11:22.173941Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-09T15:11:22.194558Z 0 [Note] InnoDB: Number of pools: 1
2019-05-09T15:11:22.197089Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-09T15:11:22.215371Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-05-09T15:11:22.309717Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-05-09T15:11:22.341933Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-05-09T15:11:22.372313Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-05-09T15:11:22.387121Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2329544419
2019-05-09T15:11:22.387148Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2329544428
2019-05-09T15:11:22.387926Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-05-09T15:11:22.387933Z 0 [Note] InnoDB: Starting crash recovery.
2019-05-09T15:11:22.536639Z 0 [Note] InnoDB: Last MySQL binlog file position 0 3849, file name server3-bin.000038
2019-05-09T15:11:23.145013Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-05-09T15:11:23.145044Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-05-09T15:11:23.145092Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-05-09T15:11:23.161018Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-05-09T15:11:23.161941Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-05-09T15:11:23.161956Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-05-09T15:11:23.162243Z 0 [Note] InnoDB: Waiting for purge to start
2019-05-09T15:11:23.212875Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2329544428
2019-05-09T15:11:23.213350Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-05-09T15:11:23.213959Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-05-09T15:11:23.273185Z 0 [Note] Recovering after a crash using server3-bin
2019-05-09T15:11:23.273251Z 0 [Note] Starting crash recovery...
2019-05-09T15:11:23.273283Z 0 [Note] Crash recovery finished.
2019-05-09T15:11:23.298008Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-05-09T15:11:23.298088Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2019-05-09T15:11:23.298102Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2019-05-09T15:11:23.298134Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2019-05-09T15:11:23.347833Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190509 17:11:23
2019-05-09T15:11:23.366913Z 0 [Note] Failed to start slave threads for channel ''
2019-05-09T15:11:23.443956Z 0 [Note] Event Scheduler: Loaded 0 events
2019-05-09T15:11:23.444130Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.26-0ubuntu0.16.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

---

$$ EDIT $$ 我聽取了 Rick James 和 Wilson Hauck 的建議。Max Memory 現在玩得很好。也許有點保守，但讓我們看看它是如何成立的。

[OK] Maximum reached memory usage: 419.2M (20.96% of installed RAM)
[OK] Maximum possible memory usage: 481.0M (24.05% of installed RAM)

key_buffer_size = 384M  --> 80M
max_connections         --> 20

如果這些變化不足以讓事情順利進行，我們將需要減少更多的東西。今天 2GB 的 RAM “很小”。

每秒速率=RPS - 為您的 my.cnf 考慮的建議

$$ mysqld $$部分

read_rnd_buffer_size=256K  # from 8M to conserve RAM and reduce handler_read_rnd_next
read_buffer_size=512K  # from 2M to conserve RAM and reduce handler_read_next
key_buffer_size=80M  # from 402M to conserve RAM
innodb_buffer_pool_size=256M  # from default 128M to allow growth
max_connnections=20  # from 214 until you need more
table_open_cache=800  # from 400 to reduce opened_tables count
query_cache_type=OFF  # from ON to conserve RAM and CPU cycles
query_cache_size=0  # from 8M to conserve RAM and CPU cycles

免責聲明：我是我的個人資料、網路個人資料中的網頁內容的作者，可以提供額外的建議和免費的實用程序腳本。

引用自：https://dba.stackexchange.com/questions/237727