Mysql
增加 innodb 緩衝池大小會防止 mysql 崩潰嗎？
我的伺服器一直在用完 mysql 的記憶體。我不斷得到。150708 3:39:09
$$ ERROR $$/usr/sbin/mysqld：記憶體不足（需要 258064 字節） 當我查看 my.cnf 時，我看到 innodb_buffer_pool_size 是 134217728
我假設它以字節為單位，可以轉換為 134MB。我應該改變 innodb_buffer_pool_size=134217728
到 innodb_buffer_pool_size=500MB
當我執行Rolando 的查詢時，我得到了 481M 作為 Recommended_InnoDB_Buffer_Pool_Size：
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM ( SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM ( SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1.25 growth) BBB WHERE ENGINE='InnoDB' ) AA ) A;推薦_InnoDB_Buffer_Pool_Size 481M
以下是 my.cnf 的完整副本
[mysqld] thread_cache_size=6 query_cache_type=2 key_buffer_size=256M open_files_limit=10000 query_cache_size=256M # safe-show-database table_cache=1024 query_cache_limit=1M default-storage-engine=MyISAM innodb_file_per_table=1 innodb_read_io_threads=8 innodb_write_io_threads=8 # Try number of cpus*2 for thread_concurrency thread_concurrency=12 max_allowed_packet=268435456 max_connections=200 query_cache_size=64M thread_cache_size=16 join_buffer_size=256K read_buffer_size=256K sort_buffer_size=256K tmp_table_size=512M max_heap_table_size=512M max_delayed_threads=0 innodb_buffer_pool_size=134217728這是上次崩潰時的回溯，我已經修復了 thecityo_production_wp/wp_wfVulnScanners 錯誤：
我有 10 GB 的記憶體和 180 GB 的磁碟空間以及 6 個共享核心。
key_buffer_size=268435456 read_buffer_size=262144 max_used_connections=0 max_threads=200 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 366904 K bytes of memory Hope thats ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7aabf5] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x686324] /lib64/libpthread.so.0(+0xf710)[0x7fdd49402710] /lib64/libc.so.6(gsignal+0x35)[0x7fdd485c9625] /lib64/libc.so.6(abort+0x175)[0x7fdd485cae05] /usr/sbin/mysqld[0x8fbd91] /usr/sbin/mysqld[0x8b9638] /usr/sbin/mysqld[0x926aa5] /usr/sbin/mysqld[0x91b362] /usr/sbin/mysqld[0x857c7a] /usr/sbin/mysqld[0x84cbc2] /lib64/libpthread.so.0(+0x79d1)[0x7fdd493fa9d1] /lib64/libc.so.6(clone+0x6d)[0x7fdd4867f8fd] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 150708 03:31:13 mysqld_safe mysqld from pid file /var/lib/mysql/mo.serveroflight.com.pid ended 150708 03:36:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150708 3:36:35 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 150708 3:36:35 [Note] Plugin 'FEDERATED' is disabled. 150708 3:36:35 InnoDB: The InnoDB memory heap is disabled 150708 3:36:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150708 3:36:35 InnoDB: Compressed tables use zlib 1.2.3 150708 3:36:35 InnoDB: Using Linux native AIO 150708 3:36:35 InnoDB: Initializing buffer pool, size = 128.0M 150708 3:36:35 InnoDB: Completed initialization of buffer pool 150708 3:36:35 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 48687241371 150708 3:36:35 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 48687308434 150708 3:36:36 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 150708 3:36:36 InnoDB: Error: table 'thecityo_production_wp/wp_wfNet404s' InnoDB: in InnoDB data dictionary has tablespace id 20744, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 150708 3:36:36 InnoDB: Error: table 'thecityo_production_wp/wp_wfVulnScanners' InnoDB: in InnoDB data dictionary has tablespace id 20745, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 150708 3:36:36 InnoDB: Waiting for the background threads to start 150708 3:36:37 InnoDB: 5.5.42 started; log sequence number 48687308434 150708 3:36:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150708 3:36:37 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150708 3:36:37 [Note] Server socket created on IP: '0.0.0.0'. 150708 3:36:37 [Note] Event Scheduler: Loaded 0 events 150708 3:36:37 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.42-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 150708 3:38:40 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:43 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258104 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258640 bytes) 150708 3:38:48 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258104 bytes) 150708 3:38:49 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258104 bytes) 150708 3:38:49 InnoDB: Unable to allocate memory of size 289800. 150708 3:38:51 InnoDB: Assertion failure in thread 139869977511680 in file mem0mem.c line 361 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 07:38:51 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=262144 max_used_connections=181 max_threads=200 thread_count=164 connection_count=164 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 366904 K bytes of memory Hope thats ok; if not, decrease some variables in the equation. Thread pointer: 0x3aef460 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f70e2625e58 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7aabf5] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x686324] /lib64/libpthread.so.0(+0xf710)[0x7f7120768710] /lib64/libc.so.6(gsignal+0x35)[0x7f711f92f625] /lib64/libc.so.6(abort+0x175)[0x7f711f930e05] /usr/sbin/mysqld[0x8fbd91] /usr/sbin/mysqld[0x8fbdee] /usr/sbin/mysqld[0x881fc7] /usr/sbin/mysqld[0x83c104] /usr/sbin/mysqld[0x83efa5] /usr/sbin/mysqld[0x81819b] /usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1f)[0x74de7f] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x79)[0x5ace99] /usr/sbin/mysqld[0x5b16c7] /usr/sbin/mysqld(_ZN4JOIN4execEv+0xc81)[0x5c6f41] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x12c)[0x5c873c] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x165)[0x5c9175] /usr/sbin/mysqld[0x584392] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x144d)[0x5894cd] 150708 3:39:09 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258064 bytes) /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x330)[0x58ce20] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1af3)[0x58f1e3] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xd7)[0x629297] /usr/sbin/mysqld(handle_one_connection+0x51)[0x6293d1] /lib64/libpthread.so.0(+0x79d1)[0x7f71207609d1] /lib64/libc.so.6(clone+0x6d)[0x7f711f9e58fd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f70e40187c0): is an invalid pointer Connection ID (thread ID): 234 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 150708 03:39:11 mysqld_safe Number of processes running now: 0 150708 03:39:11 mysqld_safe mysqld restarted 150708 3:39:11 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 150708 3:39:12 [Note] Plugin 'FEDERATED' is disabled. 150708 3:39:12 InnoDB: The InnoDB memory heap is disabled 150708 3:39:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150708 3:39:12 InnoDB: Compressed tables use zlib 1.2.3 150708 3:39:12 InnoDB: Using Linux native AIO 150708 3:39:12 InnoDB: Initializing buffer pool, size = 128.0M 150708 3:39:12 InnoDB: Completed initialization of buffer pool 150708 3:39:12 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 48687567559 150708 3:39:12 InnoDB: Database was not shut down n
我並不完全相信 MySQL 記憶體不足。原因如下：
方面#1
當您使用 mysqltuner.pl 時，它會計算最壞情況下的總使用量。它通過將 3 個數字相加來實現
key_buffer_size
innodb_buffer_pool_size
thread_memory : max_connections * 的總和
- 排序緩衝區大小
- 加入緩衝區大小
- 讀取緩衝區大小
- read_rnd_buffer_size
在您的情況下，鑑於您目前的設置，這將是
key_buffer_size (256M)
innodb_buffer_pool_size (128M)
thread_memory (200M) : max_connections (200) * 總和
- 排序緩衝區大小（256K）
- join_buffer_size (256K)
- read_buffer_size (256K)
- read_rnd_buffer_size（預設為 256K）
在給定的設置下，MySQL 的絕對最壞情況記憶體消耗為 584M (256M + 128M + 200M)。
如果使用 RIBPS，MySQL 的絕對最壞情況記憶體消耗將變為 939M (256M + 481M + 200M)。
如果使用 500M，那麼 MySQL 的絕對最壞情況記憶體消耗將變為 958M（256M + 481M + 200M）。
因此，在最壞的情況下，MySQL 只會消耗 9.58% 的 RAM。
方面#2
您沒有提到伺服器是專用數據庫伺服器還是執行完整堆棧。如果您正在執行一個完整的堆棧，請降低堆棧其他部分（Varnish（更像是 Vanish）、PHP、Munin、Nagios、Tomcat、Hibernate 等）中的任何記憶體敏感設置。
方面#3
看著凌晨 3 點 36 分左右的第一次崩潰，我看到了
150708 3:36:36 InnoDB: Error: table 'thecityo_production_wp/wp_wfNet404s' InnoDB: in InnoDB data dictionary has tablespace id 20744, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary.此時，MySQL 抱怨數據庫中表的數據字典條目
wp_wfNet404s仍在thecityo_production_wp系統表空間內。接下來，mysqld 重新啟動並準備好進行新連接
150708 3:36:37 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.42-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)一分鐘後，它再次墜毀。第二次崩潰的堆棧跟踪中有什麼？
stack_bottom = 7f70e2625e58 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7aabf5] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x686324] /lib64/libpthread.so.0(+0xf710)[0x7f7120768710] /lib64/libc.so.6(gsignal+0x35)[0x7f711f92f625] /lib64/libc.so.6(abort+0x175)[0x7f711f930e05] /usr/sbin/mysqld[0x8fbd91] /usr/sbin/mysqld[0x8fbdee] /usr/sbin/mysqld[0x881fc7] /usr/sbin/mysqld[0x83c104] /usr/sbin/mysqld[0x83efa5] /usr/sbin/mysqld[0x81819b] /usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1f)[0x74de7f] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x79)[0x5ace99] /usr/sbin/mysqld[0x5b16c7] /usr/sbin/mysqld(_ZN4JOIN4execEv+0xc81)[0x5c6f41] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x12c)[0x5c873c] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x165)[0x5c9175] /usr/sbin/mysqld[0x584392] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x144d)[0x5894cd] 150708 3:39:09 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258064 bytes) /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x330)[0x58ce20] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1af3)[0x58f1e3] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xd7)[0x629297] /usr/sbin/mysqld(handle_one_connection+0x51)[0x6293d1] /lib64/libpthread.so.0(+0x79d1)[0x7f71207609d1] /lib64/libc.so.6(clone+0x6d)[0x7f711f9e58fd]看起來你正在做某種
SELECT ... JOIN查詢。SELECT ... JOIN我的猜測是因為這一行，您正在訪問損壞的表/usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1f)[0x74de7f]這可能是通過某種順序讀取。
如果你繼續看
150708 3:36:36 InnoDB: Error: table 'thecityo_production_wp/wp_wfNet404s'在 mysqld 每次崩潰和重新啟動時，您需要從 ibdata1 中清除該條目。
我不會被愚弄
150708 3:39:09 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258064 bytes)它抱怨沒有分配252K。這只是錯誤的副作用，因為它可以為無法從表中讀取的操作分配所需的記憶體。
結語
請查看刪除數據字典中的 pidgeon 洞。此外，在 DB Server 中查找其他消耗記憶體的組件。