asked on

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

hernst42

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.

Rurne

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.

richardsimnett

ASKER

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

Rurne

After you installed kernel-PAE, did you fix your grub default?

richardsimnett

ASKER

Rurne,
yes... grub is set to use the PAE kernel as default.

Thanks,
Rick

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial