Re: run out of memory
As most 64 bit libraries declare their 64-bitness clearly int heir names, I think that there is a very high chance that you have built yourself a 32-bit database in which case 4G is the limit of the known universe. On 8/14/07, Gu Lei(Tech) [EMAIL PROTECTED] wrote: try: uname -a to see if your OS is 64bit or not. Gu Lei -原始邮件- 发件人: Jen mlists [mailto:[EMAIL PROTECTED] 发送时间: 2007年8月15日 9:46 收件人: mysql@lists.mysql.com 主题: Re: run out of memory 2007/8/15, B. Keith Murphy [EMAIL PROTECTED]: What operating system are you running and is it 32 or 64 bit? Thanks for the reply. Here is my OS info: $ uname -r 2.6.9-42.ELsmp $ cat /etc/redhat-release Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Yes I think it's 64bit OS. The CPU is something like: $ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5345 @ 2.33GHz stepping: 7 cpu MHz : 2327.567 cache size : 4096 KB physical id : 0 siblings: 4 core id : 0 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est tm2 xtpr bogomips: 4657.86 I don't know whether it's 64bit or not.Do you mean my CPU have to be 64bit too? I built Mysql from source (mysql-5.0.45.tar.gz). Thanks again. --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful.
Re: run out of memory
Michael Dykman wrote: As most 64 bit libraries declare their 64-bitness clearly int heir names, I think that there is a very high chance that you have built yourself a 32-bit database in which case 4G is the limit of the known universe. On 8/14/07, Gu Lei(Tech) [EMAIL PROTECTED] wrote: try: uname -a to see if your OS is 64bit or not. Regardless of whether it is a 64bit or 32bit OS, if the binary is built as 32bit, it will still be limited. 'file mysqld' will tell you whether it is 32bit or 64bit. And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
So I stand corrected :) Sorry for the mis-information. Thanks Mark!! Keith - Original Message - From: Mark Leith [EMAIL PROTECTED] To: Baron Schwartz [EMAIL PROTECTED] Cc: Michael Dykman [EMAIL PROTECTED], Gu Lei(Tech) [EMAIL PROTECTED], Jen mlists [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:51:19 PM (GMT-0500) America/New_York Subject: Re: run out of memory Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
I have had the same type of problems as this user when unknowing using 32-bit code. That was why I was asking about what distro he was using. As for your question Baron - I don't think that limit is true (anymore). I am fairly certain that it use to be, but has been corrected. If everyone is really curious I can dig around and even test it if need be. Keith - Original Message - From: Baron Schwartz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Michael Dykman [EMAIL PROTECTED], Gu Lei(Tech) [EMAIL PROTECTED], Jen mlists [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:27:14 PM (GMT-0500) America/New_York Subject: Re: run out of memory Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
What operating system are you running and is it 32 or 64 bit? Keith - Original Message - From: Jen mlists [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 2:35:34 AM (GMT-0500) America/New_York Subject: run out of memory Hello, My server box has 8G memory and 8CPU (DELL Standard Server),when I configure Mysql server (5.0.45) using large memory,mysqld would say it run out of memory. For example,when this line appear in my.cnf, key_buffer = 4000M mysqld can't startup.When I change it to, key_buffer = 2560M mysqld startup successfully. Why this mysqld can't support large memory usage?How to improve it? Thanks! --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
2007/8/15, B. Keith Murphy [EMAIL PROTECTED]: What operating system are you running and is it 32 or 64 bit? Thanks for the reply. Here is my OS info: $ uname -r 2.6.9-42.ELsmp $ cat /etc/redhat-release Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Yes I think it's 64bit OS. The CPU is something like: $ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5345 @ 2.33GHz stepping: 7 cpu MHz : 2327.567 cache size : 4096 KB physical id : 0 siblings: 4 core id : 0 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est tm2 xtpr bogomips: 4657.86 I don't know whether it's 64bit or not.Do you mean my CPU have to be 64bit too? I built Mysql from source (mysql-5.0.45.tar.gz). Thanks again. --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: run out of memory
try: uname -a to see if your OS is 64bit or not. Gu Lei -原始邮件- 发件人: Jen mlists [mailto:[EMAIL PROTECTED] 发送时间: 2007年8月15日 9:46 收件人: mysql@lists.mysql.com 主题: Re: run out of memory 2007/8/15, B. Keith Murphy [EMAIL PROTECTED]: What operating system are you running and is it 32 or 64 bit? Thanks for the reply. Here is my OS info: $ uname -r 2.6.9-42.ELsmp $ cat /etc/redhat-release Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Yes I think it's 64bit OS. The CPU is something like: $ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5345 @ 2.33GHz stepping: 7 cpu MHz : 2327.567 cache size : 4096 KB physical id : 0 siblings: 4 core id : 0 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est tm2 xtpr bogomips: 4657.86 I don't know whether it's 64bit or not.Do you mean my CPU have to be 64bit too? I built Mysql from source (mysql-5.0.45.tar.gz). Thanks again. --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
