MySQL Tuner on server with lots of memory

[mkevenaar]

Hi,

our server has 64G of memory installed. When I run mysqltuner I get the following message:

[!!] Maximum reached memory usage: 16.5G (262.57% of installed RAM)
[!!] Maximum possible memory usage: 22.4G (357.46% of installed RAM)

free -b:

             total       used       free     shared    buffers     cached
Mem:    6742351462 6712542003  298094592    2928640  474599424 4422631014
-/+ buffers/cache: 2242451046 4499900416
Swap:   2147479552          0 2147479552

free -m:

             total       used       free     shared    buffers     cached
Mem:         64300      64029        270          2        452      42190
-/+ buffers/cache:      21386      42913
Swap:         2047          0       2047

[jmrenouard]

Can you use the last version in github please ?

Can you give us the output with --debug option for your server ?

Thanks by advance,
JM

[Jugosloven]

Hello,

I am seeeing the same output;

Running the latest version from Git;

MySQLTuner 1.5.0 - Major Hayden major@mhtx.net
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.6.23
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 166M (Tables: 739)
[--] Data in InnoDB tables: 4G (Tables: 1570)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 74

-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'munin@localhost' has user name as password.
[!!] There is not basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 12m 21s (1M q [26.963 qps], 100K conn, TX: 10B, RX: 410M)
[--] Reads / Writes: 58% / 42%
[--] Binary logging is disabled
[--] Total buffers: 11.5G global + 29.0M per thread (150 max threads)
[!!] Maximum reached memory usage: 12.1G (388.44% of installed RAM)
[!!] Maximum possible memory usage: 15.8G (505.32% of installed RAM)

         total       used       free     shared    buffers     cached

Mem: 32007 13616 18391 1309 160 3197
-/+ buffers/cache: 10258 21749
Swap: 8189 137 8052

Is there any way to provide --debug info (privetly) as it includes a lot of inofmrations (db's, etc)

Thanks

[kn007]

Please using the last version in github, and using

mysqltuner.pl --debug

JM want to see like

[DG] Physical Memory: 4016467968
[DG] Swap Memory: 0

Thank you

[Jugosloven]

Here it is, that part:

[^[[0;31mDG^[[0m] /usr/bin/mysqladmin ping 2>&1 >> MySQLTuner 1.5.0 - Major Hayden major@mhtx.net

Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering

[^[[0;31mDG^[[0m] Physical Memory: 3356239872
[^[[0;31mDG^[[0m] Swap Memory: 8587829248
[^[[0;31mDG^[[0m] PERFORM: SELECT VERSION()
[^[[0;31mDG^[[0m] VERSION: 5.6.23

If this is what you meant.

Thanks

[kn007]

Could you show your environment please?

In shell, using

uname

---

you can see that
Physical Memory: 3356239872 = 3200.7597656 MB
Swap Memory: 8587829248 = 8189.9921875 MB

[Jugosloven]

Why is it showing only 3200+- MB memory?

uname: (just removed server name)

This server is running latest CentOS 6, 64bit

Linux * 2.6.32-573.1.1.el6.x86_64 #1 SMP Sat Jul 25 17:05:50 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

[OK] Currently running supported MySQL version 5.6.23
[OK] Operating on 64-bit architecture

Thanks

[kn007]

Sorry, i don't know why.

I am using CentOS 6.7 too.

Could you show the following command please?

LANG=en free -b

LANG=en free -b | grep Mem | awk '{print $2}'

---

Hey, JM. i found the problem.

You see the comment-1 mkevenaar said

free -b:

Mem: 6742351462 = 6430.0074215 MB

maybe the problem on command ‘free -b’

[kn007]

To JM:
Maybe 'free -b' each column just can only show 10 digits!?

[Jugosloven]

Sure i can, anything:

1)LANG=en free -b

Mem: 3356239872 1465691750 1890548121 1370304512 179990528 3404886016
-/+ buffers/cache: 1107204096 2249035776
Swap: 8587829248 144080896 8443748352

2)LANG=en free -b | grep Mem | awk '{print $2}'

3356239872

[Jugosloven]

Different server, latest CentOS, 64bit as well

128GB DDR4

[!!] Maximum possible memory usage: 29.2G (2317% of installed RAM)

Mem: 128955 58575 70379 1625 9809 28407
-/+ buffers/cache: 20358 108596
Swap: 8190 138 8052

1. free -b:
   Mem: 1352191344 6154761830 7367151616 1704292352 1028596531 2978836070
   -/+ buffers/cache: 2147329228 1137458421
   Swap: 8588881920 145432576 8443449344

2. LANG=en free -b | grep Mem | awk '{print $2}'
   1352191344

[kn007]

Thank you very much.
I think the problem on 'free -b', each column just can only show 10 digits!

Waiting JM have any idea to fix it.
I am going to bed. 3:48AM here.

[Jugosloven]

Different server, latest CentOS, 64bit as well

64GB DDR4

[!!] Maximum reached memory usage: 27.4G (435.61% of installed RAM)
[!!] Maximum possible memory usage: 29.5G (469.89% of installed RAM)

Mem: 64321 50734 13586 2332 9968 16649
-/+ buffers/cache: 24116 40205
Swap: 8191 1506 6685

1. free -b
   Mem: 6744579276 5318411878 1426167398 2446032896 1045257011 1745901158
   -/+ buffers/cache: 2527253708 4217325568
   Swap: 8589930496 1579802624 7010127872

2. LANG=en free -b | grep Mem | awk '{print $2}'
   6744579276

I can even show my.cnf if needed.

Big thanks for help.

[kn007]

Same problem:
1352191344 = 1289.5501556 MB
6744579276 = 6432.1320305 MB

[Jugosloven]

kn007, perfect, hopefully only this is the issue, but not something related to my.cnf

Thanks again, and good night :)

[jmrenouard]

Thanks for your feedback :)

But do you have an idea on the right way to do it ?

I propose to use directly /proc/meminfo information:

    if ($os =~ /Linux/) {
        $physical_memory = `LANG=en grep -i memtotal: /proc/meminfo | awk '{print \$2}'` or memerror;
        $physical_memory*=1024;

        $swap_memory = `LANG=en grep -i swaptotal: /proc/meminfo | awk '{print \$2}'` or memerror;
        $swap_memory*=1024;

[jmrenouard]

Can you tell me if 1.5.1 version is solving this issue ?

[kn007]

Looking great, thanks!
This issue should have been resolved, i saw you using '/proc/meminfo' now.

Maybe NEK4TE can give more information for this issue. :)

[Jugosloven]

Going to test it shortly, and will update guys.

Thank you both!

[Jugosloven]

I would say it looks good now, very good! :)

[OK] Maximum reached memory usage: 12.1G (38.84% of installed RAM)
[OK] Maximum possible memory usage: 15.8G (50.53% of installed RAM)

I was afraid that my.cnf was wrongly adjusted - and server could go down because of this.

[jmrenouard]

Nice think :)

Can I close this issue ?

[Jugosloven]

In my opinion this has been resolved - not sure if anyone else have to add anything, but, for me its perfect again, i love this software, very usefel.

Thank you VERY much for not ignoring the issue and taking care of it.

I am sure many appriciate this - as this was discussed in other forums as well, cpanel for example, and i let the people know that you implementd the fix and new version for download is up.

have a wonderful day sir!

[jmrenouard]

Thanks for reporting this issue :)