I ran MySQLTuner to verify the configuration of a MySQL database, and it reported this problem:

[!!] Maximum possible memory usage: 40.9G (1022% of installed RAM)

These figures come from computing max_connections multiplied by read_buffer_size + sort_buffer_size + join_buffer_size in the my.cnf file, which effectively gives 40Gb.

However, the server has 128Gb of RAM. It appears that MySQL only sees 4Gb.

At first I thought that this was because a MySQL 32-bit binary on a 64-bit OS can effectively access only 4Gb of RAM. However, both MySQL binary and OS are 64-bit:

root@box# isainfo -v
64-bit amd64 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse 
        fxsr mmx cmov amd_sysc cx8 tsc fpu 
32-bit i386 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse 
        fxsr mmx cmov sep cx8 tsc fpu 

root@box# uname -a
SunOS box 5.11 11.2 i86pc i386 i86pc

root@box# file /usr/local/bin/mysql
/usr/local/bin/mysql:  ELF 64-bit LSB executable AMD64 Version 1 [SSE2 SSE FXSR CMOV FPU], dynamically linked, not stripped

and there is no limit on how much memory a process can access:

root@box# ulimit -a
address space limit (kbytes)   (-M)  unlimited
core file size (blocks)        (-c)  unlimited
cpu time (seconds)             (-t)  unlimited
data size (kbytes)             (-d)  unlimited
file size (blocks)             (-f)  unlimited
locks                          (-x)  not supported
locked address space (kbytes)  (-l)  not supported
message queue size (kbytes)    (-q)  not supported
nice                           (-e)  not supported
nofile                         (-n)  256
nproc                          (-u)  29995
pipe buffer size (bytes)       (-p)  5120
max memory size (kbytes)       (-m)  not supported
rtprio                         (-r)  not supported
socket buffer size (bytes)     (-b)  5120
sigpend                        (-i)  128
stack size (kbytes)            (-s)  8192
swap size (kbytes)             (-w)  not supported
threads                        (-T)  not supported
process size (kbytes)          (-v)  unlimited

Is there another way I can see how much memory MySQL is effectively using?

(In case you ask, shell is tcsh; I cannot tag the post "tcsh" due to insufficient privileges.)

  • 2
    MySQLTuner is written in perl iirc, is it running in 64bit perl environment? – jkavalik Jun 30 '15 at 12:18
  • Well done: root@box# file /usr/bin/perl gives /usr/bin/perl: ELF 32-bit LSB executable 80386 Version 1 [FPU], dynamically linked, not stripped. (Please write this as an answer so I can upvote and mark it as the accepted answer.) – dr_ Jun 30 '15 at 13:07
  • What does top -p<MySQL_PID> give? – Vérace Jun 30 '15 at 13:08
  • top doesn't accept the -p option in this OS. – dr_ Jun 30 '15 at 13:10
  • If you would like more analysis that mysqltuner give you, provide me with SHOW VARIABLES; and SHOW GLOBAL STATUS; (after being up at least a day). – Rick James Jun 30 '15 at 16:27

It is not a MySQL thing - MySQLTuner itself cannot see more memory if it is running with 32bit perl interpreter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.