• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

How do I get MYSQL to use all of my systems available memory?

Hello,
I have a new install of mysql 5 on a centos 5 (kernel-pae - hugemem) box. The box has dual xeon procs, 8 gigs of ram, and a massive & fast raid. Here's the deal, I bought this box just for mysql, however, if I try to allocate more than 4gigs of ram to mysql in the my.cnf (buffer pool for innodb), mysql freaks at startup and says it cannot allocate more than 4gigs to a 32bit machine.

So I tried lowering the value from 4G to 3G, still no go, get an error saying malloc cannot allocate xxxxxxxxxxxxxxxxxx.

So then I tried 2G and it works.

How do I get this mysql isntall to USE all or most of the available memory in the system? Its a huge waste if it cannot use it.

Worth 500 points.

Thanks,
Rick
0
richardsimnett
Asked:
richardsimnett
  • 2
  • 2
  • 2
1 Solution
 
hernst42Commented:
32bit progs can't use more than 2GB per one allocated block and AFAIK to a maximum of 4GB per process. Run a 64-bit linux (don't know if you processor support 64bit) with a 64-bit mysql and you can allocate more memory in mysql.
0
 
RurneCommented:
Alternatively, if your hardware supports it, you can use the physical-address extension (PAE) capability to allow you to run multiple to go from 32-bit to 36-bit addressing. PAE will let your 32-bit system access up to 64GB of RAM.  You should have PAE support if you're running an Intel processor, and IIRC, most Athlons made within the last 3 years support it as well.

Rather than building a new system, post your CPU specs.  You may just have to recompile your kernel to enable PAE support.
0
 
richardsimnettAuthor Commented:
Rurne,
I have already installed the PAE kernel. Im running centos, so I installed kernel-PAE in order to get it to recognize more than 4G off boot, so now the OS sees a full 8 gigs of ram, but MYSQL refuses to use it.

Here is some of my cpu info:

[root@localhost ~]# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.40GHz
stepping        : 3
cpu MHz         : 3391.976
cache size      : 2048 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
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 constant_tsc pni monitor ds_cpl est cid cx16 xtpr
bogomips        : 6787.05

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.40GHz
stepping        : 3
cpu MHz         : 3391.976
cache size      : 2048 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
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 constant_tsc pni monitor ds_cpl est cid cx16 xtpr
bogomips        : 6783.45

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.40GHz
stepping        : 3
cpu MHz         : 3391.976
cache size      : 2048 KB
physical id     : 3
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
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 constant_tsc pni monitor ds_cpl est cid cx16 xtpr
bogomips        : 6783.49

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.40GHz
stepping        : 3
cpu MHz         : 3391.976
cache size      : 2048 KB
physical id     : 3
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
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 constant_tsc pni monitor ds_cpl est cid cx16 xtpr
bogomips        : 6783.47

Here is my kernel version:

[root@localhost ~]# cat /proc/version
Linux version 2.6.18-8.1.8.el5PAE (mockbuild@builder4.centos.org) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Tue Jul 10 07:50:36 EDT 2007


Here is my mysql my.cnf:
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
basedir=/usr
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-table-type = InnoDB
transaction-isolation = read-uncommitted
skip-name-resolve
myisam_recover
innodb_file_per_table = 1
set-variable    = innodb_buffer_pool_size=2G
set-variable    = innodb_file_io_threads=4
set-variable    = innodb_thread_concurrency=16
set-variable    = innodb_lock_wait_timeout=360
set-variable    = key_buffer=256M
set-variable    = bulk_insert_buffer_size=128M
set-variable    = max_allowed_packet=16M
set-variable    = table_cache=8192
set-variable    = sort_buffer=16M
set-variable    = myisam_repair_thread=2
set-variable    = read_buffer_size=8M
set-variable    = read_rnd_buffer_size=64M
server-id = 2
max_connections = 500

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Anything else you need to see just let me know.

Thanks!

Rick
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RurneCommented:
After you installed kernel-PAE, did you fix your grub default?
0
 
richardsimnettAuthor Commented:
Rurne,
yes... grub is set to use the PAE kernel as default.

Thanks,
Rick
0
 
hernst42Commented:
AFAIK a 32bit mysql can use more than 2,5GB due the 32-bit limitation. http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/ :
"For 32bit environment you also should keep 32bit limits into account and probably limit mysqld size to about 2.5GB (exact number depends on a lot of factors)"

If you can't install a 64bit linux you will not be able to use the huge amount of memory with mysql. You can run multiple instance of mysql-servers, but each will be limited to its maximum of 2,5GB
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now