Re: InnoDB and Memory Allocation
Johnny Withers wrote: I hope someone can help me out here. I'm having trouble with some new servers and memory allocation. Some basic specs on the servers: 32GB total mem 2GB swap 64-bit RHEL 64-bit mysqld overcommit_memory=2 mysql fails to start with 14GB innodb_buffer_pool_size mysql will start with 12GB buffer pool setting When overcommit_memory is set to 0, mysql starts with 26GB buffer pool. This is due to a system-wide limitation on the amount of memory that can be allocated by processes that the Linux kernel imposes when overcommit is disabled. The limit is (amount of swap) + (percentage of physical memory), and the default percentage is 50. So the limit in your case is 2G + 16G == 18G for all processes together; 14G for innodb_buffer_pool_size is too much. You can increase the amount of swap, or increase the percentage (via /proc/sys/vm/overcommit_ratio, which isn't as well-known, though it is in the kernel documentation). Hope that helps. Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB and Memory Allocation
Wow, I hope this hasn't been hanging out in the mysql list server for a year. I completely forgot about this problem. Thanks for the info though. I've been running this server with overcommit_memory=0 and a 42GB buffer pool for a while now. Thanks again! On Fri, May 4, 2012 at 9:38 AM, Charles Cazabon charlesc-mysql@pyropus.ca wrote: Johnny Withers wrote: I hope someone can help me out here. I'm having trouble with some new servers and memory allocation. Some basic specs on the servers: 32GB total mem 2GB swap 64-bit RHEL 64-bit mysqld overcommit_memory=2 mysql fails to start with 14GB innodb_buffer_pool_size mysql will start with 12GB buffer pool setting When overcommit_memory is set to 0, mysql starts with 26GB buffer pool. This is due to a system-wide limitation on the amount of memory that can be allocated by processes that the Linux kernel imposes when overcommit is disabled. The limit is (amount of swap) + (percentage of physical memory), and the default percentage is 50. So the limit in your case is 2G + 16G == 18G for all processes together; 14G for innodb_buffer_pool_size is too much. You can increase the amount of swap, or increase the percentage (via /proc/sys/vm/overcommit_ratio, which isn't as well-known, though it is in the kernel documentation). Hope that helps. Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - Johnny Withers 601.209.4985 joh...@pixelated.net
InnoDB and Memory Allocation
I hope someone can help me out here. I'm having trouble with some new servers and memory allocation. Some basic specs on the servers: 32GB total mem 2GB swap 64-bit RHEL 64-bit mysqld overcommit_memory=2 mysql fails to start with 14GB innodb_buffer_pool_size mysql will start with 12GB buffer pool setting When overcommit_memory is set to 0, mysql starts with 26GB buffer pool. I'm trying to overcome a problem where running with overcommit_memory=0 causes the oom-killer to eventually take over and start killing things. This usually causes the entire box to become unresponsive and has to be reboot from the console. I've read that setting overcommit_memory=2 will cause malloc() to fail and the offending program to crash. I would rather this happen than the oom-killer take over. Why can't mysql allocate more than ~14GB when this is set to 2 though? Does anyone else have any experience with this? Some more detailed specs about the machine and mysql: free -m reports 28GB free (32 GB +/- buffers) [root@p2383075 ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 270336 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 270336 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited /usr/libexec/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped mysql show variables like '%version%'\G *** 2. row *** Variable_name: version Value: 5.0.77-log *** 4. row *** Variable_name: version_comment Value: Source distribution *** 5. row *** Variable_name: version_compile_machine Value: x86_64 *** 6. row *** Variable_name: version_compile_os Value: redhat-linux-gnu Thanks! -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: InnoDB: wild memory allocation size = server restarted
Yuri, looks like InnoDB was trying to allocate 4 GB + 232 bytes. A major bug in the 64-bit version was fixed in 3.23.52: July 20, 2002: On 64-bit computers updating rows whichcontained the SQL NULL in some column could cause theundo log andthe ordinary log to become corrupt. Fixed in 3.23.52. However, also 32-bit users on Linux have reported somegarbage values sent to the memory allocator. Please upgrade to 3.23.52, and if the bug reappears, compilewith -g -O3, run inside gdb, and when it crashes, do gdb bt full In 3.23.52 an out-of-memory error intentionally generates a segfault so that we can get a stack trace or a core file. Regards, Heikki Copied message: .. I am running version 3.23.51-log (built with -O3) on FreeBSD-5.0-CURRENT on Alpha. InnoDB is on raw disk. After a week of uptime it restarted by itself and the message below was logged. Looks like it was trying to allocate 4GB+0x1E bytes. Machine has 2GB of physical memory + 1GB swap. Database is small: around 1MB. And mysql memory usage was reasonable (150M). 100M was allocated for key_buffer. Could it be related to 64-bits pointer size on this architecture? Any ideas what could have caused the problem? Yuri. my.cnf: snip set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 snip -errorlog /usr/local/libexec/mysqld: ready for connections week passed 020831 11:57:41 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 4294967528 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 33699660 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. 020831 11:57:41 mysqld restarted 020831 11:57:43 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 2705349 InnoDB: Doing recovery: scanned up to log sequence number 0 2705349 InnoDB: Last MySQL binlog file position 0 535499, file name ./s2-bin.101 020831 11:57:43 InnoDB: Flushing modified pages from the buffer pool... 020831 11:57:43 InnoDB: Started - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB: wild memory allocation size = server restarted
I am running version 3.23.51-log (built with -O3) on FreeBSD-5.0-CURRENT on Alpha. InnoDB is on raw disk. After a week of uptime it restarted by itself and the message below was logged. Looks like it was trying to allocate 4GB+0x1E bytes. Machine has 2GB of physical memory + 1GB swap. Database is small: around 1MB. And mysql memory usage was reasonable (150M). 100M was allocated for key_buffer. Could it be related to 64-bits pointer size on this architecture? Any ideas what could have caused the problem? Yuri. my.cnf: snip set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 snip -errorlog /usr/local/libexec/mysqld: ready for connections week passed 020831 11:57:41 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 4294967528 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 33699660 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. 020831 11:57:41 mysqld restarted 020831 11:57:43 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 2705349 InnoDB: Doing recovery: scanned up to log sequence number 0 2705349 InnoDB: Last MySQL binlog file position 0 535499, file name ./s2-bin.101 020831 11:57:43 InnoDB: Flushing modified pages from the buffer pool... 020831 11:57:43 InnoDB: Started - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
