Bug #37643 | mysqld: Out of memory (Needed 776880 bytes) | ||
---|---|---|---|
Submitted: | 25 Jun 2008 20:06 | Modified: | 9 Dec 2011 5:15 |
Reporter: | Joel Shapiro | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Errors | Severity: | S2 (Serious) |
Version: | 5.0.51a | OS: | Linux (2.6.18-53.1.14.el5PAE #1 SMP i686 i686 i386 GNU/Linux) |
Assigned to: | CPU Architecture: | Any | |
Tags: | crash, innodb, innodb_buffer_pool_size, out of memor |
[25 Jun 2008 20:06]
Joel Shapiro
[25 Jun 2008 20:09]
Joel Shapiro
I forgot to add that we have many other instances running with these same larger innodb settings and none of them have crashed. They are running on different hosts but identical hardware. They have very similar patterns of queries and data but are not identical in their schema (i.e., a different set of tables).
[26 Jun 2008 5:21]
Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf file content and SHOW GLOBAL STATUS results.
[26 Jun 2008 10:51]
Joel Shapiro
I listed the entire contents of our my.cnf in the original posting above . . . But I can repeat it here for convenience: # BEGIN my.cnf [mysqld] character_set_server = utf8 collation_server = utf8_bin init-connect='SET NAMES utf8 COLLATE utf8_bin' socket = /fs-a01-a/mysql1/mysql.sock port = 3307 pid-file = /fs-a01-a/mysql1/mysql.pid datadir = /fs-a01-a/mysql1/data server-id = 110601 log-bin = /fs-a01-a/mysql1/log/log-update log-error = /fs-a01-a/mysql1/log/log-error tmpdir = /fs-a01-a/mysql1/tmp max_connections=2000 innodb_data_file_path = ibdata1:200G;ibdata2:100M:autoextend innodb_buffer_pool_size=1000M innodb_additional_mem_pool_size=32M innodb_log_file_size=500M innodb_log_buffer_size=8M sort_buffer_size=262144 # END my.cnf Here is the show global status: Variable_name Value Aborted_clients 167335 Aborted_connects 1 Binlog_cache_disk_use 0 Binlog_cache_use 261346 Bytes_received 2205608599 Bytes_sent 715044306 Com_admin_commands 1 Com_alter_db 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_call_procedure 0 Com_change_db 0 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_create_user 0 Com_dealloc_sql 0 Com_delete 1 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_drop_user 0 Com_execute_sql 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 13910 Com_insert_select 0 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 4 Com_purge_before_date 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_savepoint 0 Com_select 24172509 Com_set_option 468948 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 93788 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 1 Com_show_databases 3 Com_show_errors 0 Com_show_fields 5947 Com_show_grants 0 Com_show_innodb_status 1 Com_show_keys 1076 Com_show_logs 0 Com_show_master_status 0 Com_show_ndb_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 0 Com_show_slave_hosts 1 Com_show_slave_status 0 Com_show_status 613 Com_show_storage_engines 0 Com_show_tables 87564 Com_show_triggers 0 Com_show_variables 187579 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 24507008 Com_stmt_execute 24507013 Com_stmt_fetch 0 Com_stmt_prepare 24507013 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_unlock_tables 0 Com_update 260173 Com_update_multi 0 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 94184 Created_tmp_disk_tables 5902 Created_tmp_files 5 Created_tmp_tables 459535 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 522692 Handler_delete 0 Handler_discover 0 Handler_prepare 522692 Handler_read_first 1029 Handler_read_key 72531498 Handler_read_next 338032621 Handler_read_prev 0 Handler_read_rnd 50186 Handler_read_rnd_next 35436127 Handler_rollback 12738 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 35043089 Innodb_buffer_pool_pages_data 61897 Innodb_buffer_pool_pages_dirty 108 Innodb_buffer_pool_pages_flushed 194169 Innodb_buffer_pool_pages_free 0 Innodb_buffer_pool_pages_latched 0 Innodb_buffer_pool_pages_misc 2103 Innodb_buffer_pool_pages_total 64000 Innodb_buffer_pool_read_ahead_rnd 149132 Innodb_buffer_pool_read_ahead_seq 420142 Innodb_buffer_pool_read_requests 1550523647 Innodb_buffer_pool_reads 46874197 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 1746617 Innodb_data_fsyncs 500046 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 672944128 Innodb_data_reads 52772914 Innodb_data_writes 679363 Innodb_data_written 2423008256 Innodb_dblwr_pages_written 194169 Innodb_dblwr_writes 7738 Innodb_log_waits 0 Innodb_log_write_requests 212192 Innodb_log_writes 477421 Innodb_os_log_fsyncs 484193 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 352030208 Innodb_page_size 16384 Innodb_pages_created 388 Innodb_pages_read 61382513 Innodb_pages_written 194169 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 39 Innodb_rows_inserted 1172 Innodb_rows_read 338653482 Innodb_rows_updated 219547 Key_blocks_not_flushed 0 Key_blocks_unused 7248 Key_blocks_used 0 Key_read_requests 0 Key_reads 0 Key_write_requests 0 Key_writes 0 Last_query_cost 0.000000 Max_used_connections 66 Ndb_cluster_node_id 0 Ndb_config_from_host Ndb_config_from_port 0 Ndb_number_of_data_nodes 0 Not_flushed_delayed_rows 0 Open_files 16 Open_streams 0 Open_tables 53 Opened_tables 59 Prepared_stmt_count 3 Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0 Questions 74316229 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 25296 Select_range_check 0 Select_scan 377176 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 6 Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 Ssl_accept_renegotiates 0 Ssl_accepts 0 Ssl_callback_cache_hits 0 Ssl_cipher Ssl_cipher_list Ssl_client_connects 0 Ssl_connect_renegotiates 0 Ssl_ctx_verify_depth 0 Ssl_ctx_verify_mode 0 Ssl_default_timeout 0 Ssl_finished_accepts 0 Ssl_finished_connects 0 Ssl_session_cache_hits 0 Ssl_session_cache_misses 0 Ssl_session_cache_mode NONE Ssl_session_cache_overflows 0 Ssl_session_cache_size 0 Ssl_session_cache_timeouts 0 Ssl_sessions_reused 0 Ssl_used_session_cache_entries 0 Ssl_verify_depth 0 Ssl_verify_mode 0 Ssl_version Table_locks_immediate 48832177 Table_locks_waited 0 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 38 Threads_created 94183 Threads_running 4 Uptime 66859 Uptime_since_flush_status 66859
[27 Jun 2008 5:12]
Joel Shapiro
Hi Valeriy, I attached the info you requested . . . Any progress on this bug? Thanks, -Joel
[30 Jun 2008 10:34]
Susanne Ebrecht
InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. You have a given limit from 4 GB. This is splitted in kernel space and user space. In older linux and also windows kernel this was splitted 50:50 percent. By using kernel linux 2.6 you can configure this during kernel installation. Look to Linux 64bit: $ ulimit -a max memory size (kbytes, -m) unlimited But this also is only theoretically. In practice this will end at 16 Exabyte (16*10^18). Will this already answering your question?
[1 Jul 2008 23:59]
Joel Shapiro
Hi, Thanks for your response. But no, this does *not* answer my question. The problem has nothing to do with my code or my kernel. Here's why: Before: mysql 5.0.45 our code and our queries our my.cnf Linux kernel X MySQL runs fine After: mysql 5.0.51 our code and our queries are unchanged our my.cnf is unchanged Same linux kernel X MySQL crashes all the time with out of memory issues The *only* thing that changed here is that we moved from mysql 5.0.45 to 5.0.51. Our code and queries did not change. Our kernel did not change. Giving me advice about how to expand memory available to processes doesn't help, although I appreciate the help. I'm not trying to be rude, I understand you are busy and I appreciate your responses . . . How could the same code, queries and kernel that worked fine in mysql 5.0.45 now all of sudden cause mysql 5.0.51 to crash all the time with out of memory errors?!? Can you please explain that? My kernel already provides tons of memory to processes, so asking me to tune my kernel does not help. The problem is in mysql I think . . .
[2 Jul 2008 0:02]
Joel Shapiro
Just to clarify my previous response, it is *mysql* that is running out of memory, *not* my kernel. The host has 16 GB of RAM available in 32-bit PAE which means that 4 GB is available to a given process as well as 4 GB to the kernel. It is mysql's memory management that is causing the crash, not the kernel's . . .
[2 Jul 2008 11:48]
Susanne Ebrecht
Ok, three further questions: 1) What is the main query that happens on the system? I mean is it insert/delete/update or is it most times select? 2) How did you upgrade to MySQL 5.0.51a? Which packages did you use. Did you make mysql_upgrade? 3) Sorry, but according to your description, did you ever check the disk? Maybe this is the first warning, that your disk is failing.
[21 Jul 2008 5:20]
Joel Shapiro
Hi, Can somebody please take a look at this! I haven't heard anything from mysql in weeks and this bug is still a big issue for us. I know you guys are very busy, but any help would be very much appreciated! Thanks, -Joel
[21 Jul 2008 5:22]
Joel Shapiro
Hi Susanne, Sorry, I didn't see your quick response on the 2nd of July. My apologies. To answer your questions: 1) The DB does about 85% selects, 10% updates and 5% inserts with very few deletes. 2
[21 Jul 2008 5:23]
Joel Shapiro
Hi Susanne, Sorry, I didn't see your quick response on the 2nd of July. My apologies. To answer your questions: 1) The DB does about 85% selects, 10% updates and 5% inserts with very few deletes. 2) We did a clean update, i.e., dump the date from 5.0.45, re-install 5.0.51 from a clean binary download from mysql.com. The mysqlimport the data. 3) We have done detailed analysis of the disks and they are fine, no problems with the disks. Thanks, -Joel
[6 Aug 2008 9:24]
Susanne Ebrecht
You only explained how you backup/restore the data. But how did you backup the database schema? Did you use the old data directory? Did you run mysql_upgrade scripts?
[8 Aug 2008 1:19]
Joel Shapiro
Hi, We did a completely new install when moving to 5.0.51 from 5.0.45. In other words: 1) We dumped the data and schema from 5.0.45 using mysqldump to produce TableName.txt and TableName.sql files. 2) We then completely deleted the 5.0.45 install and performed a clean install of 5.0.51. At this point, mysql 5.0.45 is totally gone and no trace of it remains. 3) We then re-created the schema in 5.0.51 that we dumped from 5.0.45 using the TableName.sql files from step 1) as in: cat TableName.sql | mysql DatabaseName 4) We then imported the data from 5.0.45 into 5.0.51 using mysqlimport on the TableName.txt data files. When we moved to 5.0.51 we totally deleted 5.0.45 and we did not re-use anything from 5.0.45, not the data files, not the binaries, nothing (except for importing the TableName.sql and .txt files with the data and schema). The 5.0.51 install was totally fresh. The bug is not in how we did our install, the bug is in mysql itself . . . Can you please help us resolve this? Thanks!
[8 Aug 2008 6:53]
Susanne Ebrecht
Please show ulimit -a from mysql user. I need to know "max memory size" and "open files"
[8 Aug 2008 19:31]
Joel Shapiro
Hi Susanne, I gave the results of "ulimit -a" in the original bug report on June 25th . . . Did you not see it in the original bug report, submitted over 6 weeks ago? I don't mean to sound rude, but it's puzzling me why it is taking mysql so long to investigate this! I know you are very busy and that you work hard, but again, it seems like you just keep asking me questions and there does not seem to be any progress in resolving this bug. Again, I'm not trying to be rude, but I am trying to convey my frustration!!! I do thank you for your efforts. Can you please just ask me all potential questions all at once right now so I can answer them all and we can move on more quickly? As I said, the results of ulimit -a were included in the original bug report, but I'll repeat them below for convenience: ulimit -a: -t: cpu time (seconds) unlimited -f: file size (blocks) unlimited -d: data seg size (kbytes) unlimited -s: stack size (kbytes) 10240 -c: core file size (blocks) 0 -m: resident set size (kbytes) unlimited -u: processes 274431 -n: file descriptors 65535 -l: locked-in-memory size (kb) 32 -v: address space (kb) unlimited -x: file locks unlimited -i: pending signals 274431 -q: bytes in POSIX msg queues 819200 -N 13: 0 -N 14: 0
[19 Aug 2008 0:47]
Joel Shapiro
Hi, Has there been any progress on this bug? Thanks, -Joel
[9 Sep 2008 5:51]
Joel Shapiro
OK, I didn't mean to sound rude in my earlier posts, I was just frustrated, sorry. It's been over a month since MySQL last responded to me about this bug. Can somebody please look at it? I'm beginning to think you don't love me anymore . . . :-) Ha ha ha. Thanks!
[8 Oct 2008 8:14]
MySQL Verification Team
Joel, this is not necessarily a bug in mysql. If innodb could not allocate memory *for whatever reason*, it volunteered to shutdown, i.e. crash. In this case, the OS refused to satisfy a request for memory. So far you haven't given any evidence that would point to a memory leak within mysqld. What is the memory usage of mysqld just before a crash? Please, monitor it. Last of all, if you put the same my.cnf with 5.0.45 (your old version), I'm sure you'll see exact same problems. Immediate solution will be to set innodb_buffer_pool_size=300M in my.cnf.
[1 Nov 2008 21:23]
Joel Shapiro
Hi Shane, Thanks for your response. mysql memory usage just before the crash is fine --- under 2 GB, and since we are running a 32-bit PAE kernel, there is fully 3 GB available to mysql. This is on a machine with 16 GB of RAM and very few other processes running. So when mysql crashes, there is (provably) at least 1 GB of free memory available for it. The queries that are crashing mysql are not large and it is not possible that they are requiring a full 1 GB . . . Also, as per the original posting, these types of crashes did *not* occur in 5.0.45. In 5.0.45 with inno_db_buffer_pool_size of 1000m, we saw no crashes. Now, all of a sudden, after moving to 5.0.51, we see crashes, with the same my.cnf as from 5.0.45. So why would 5.0.51 crash with a inno_db_buffer_pool_size that did not cause 5.0.45 to crash? Any ideas? Thanks.
[10 Nov 2008 12:16]
Dario Rigolin
Very frequent Out of memory in mysql after upgrade to 5.0.51a (Ubuntu 8.04 standard). never seen before. It seems related to mysqldump command or to temporary table creation. We use only myisam no innodb. Never seen in previous version of mysql available in Ubuntu 7.10. No replication and no cluster. Simple myisam tables.
[3 Jan 2009 11:35]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.67 at least (MySQL binaries), and inform about the results.
[4 Feb 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[9 Nov 2009 5:42]
Ashokkumar S
What is the solution for this bug?
[16 Nov 2009 17:01]
Ralf Hauser
Under debian stable (5.0.51a-24+lenny2) we also get << mysqldump: Out of memory (Needed 3061500 bytes) >> despite setting http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_skip-opt and http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_quick i.e. the combination <<--quick --skip-opt --add-drop-table --add-drop-database>> failed. Once using http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert it apparently worked again i.e. the combination <<--opt --skip-extended-insert --add-drop-table --add-drop-database>>
[16 Nov 2009 17:22]
Valeriy Kravchuk
If anybody can repeat this bug with recent MySQL 5.0.x (that is, 5.0.86 or newer), please, say so and upload your my.cnf file used.
[17 Dec 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[26 Jul 2010 10:24]
vinhdo the
Just change this key_buffer_size = 2048M
[27 Jul 2010 8:56]
Sveta Smirnova
vinhdo, thank you for the feedback. But I don't understand your comment. If you mean to repeat error we just need to change this value this means you experience not a bug, but have not enough memory for such huge setting. If you meant something else please explain.
[27 Aug 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".