mysqltuner.pl result about Memory Usage

T

thunn

Verified User
Joined
Mar 13, 2012
Messages
166
Dear Supporter,
I run mysqltuner.pl (http://mysqltuner.pl/mysqltuner.pl)
It said like this:
[!!] Maximum possible memory usage: 3.3G (148% of installed RAM)

But I don't know how it's calculated?

Thanks for your help. Here is the full test result:
Code: 
# ./mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-community-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3G (Tables: 3489)
[--] Data in InnoDB tables: 43M (Tables: 1089)
[--] Data in MEMORY tables: 3M (Tables: 190)
[--] Total fragmented tables: 10

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 33m 5s (3M q [76.838 qps], 163K conn, TX: 1B, RX: 899M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 1.6G global + 13.1M per thread (130 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[B][!!] Maximum possible memory usage: 3.3G (148% of installed RAM)[/B]
[OK] Slow queries: 0% (1K/3M)
[OK] Highest usage of available connections: 73% (95/130)
[OK] Key buffer size / total MyISAM indexes: 500.0M/1.1G
[OK] Key buffer hit rate: 99.9% (124M cached / 64K reads)
[OK] Query cache efficiency: 55.4% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5K temp sorts / 573K sorts)
[!!] Joins performed without indexes: 5309
[!!] Temporary tables created on disk: 46% (395K on disk / 858K total)
[OK] Thread cache hit rate: 99% (95 created / 163K connections)
[OK] Table cache hit rate: 87% (1K open / 1K opened)
[OK] Open file limit used: 7% (1K/17K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 43.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 3.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 43M)
 
It depends on a) the size of your global buffers and b) the size of your connection specific buffers multiplied by your number of max_connections.
 
Total buffers: 1.6G global + 13.1M per thread (130 max threads)

What is your /etc/my.cnf looks like?

Code: 
cat /etc/my.cnf
 
Yes, it looks like this:
Code: 
[root@ns ~]# cat /etc/my.cnf
[mysqld]
local-infile=0
bind-address = 127.0.0.1
skip-locking
skip-networking
skip-name-resolve

#Additional Setting
log-slow-queries=/home/log-query.log
long_query_time=2

server-id=1



max_connections=130
key_buffer_size=500M
max_allowed_packet=32M
table_cache=8892
sort_buffer_size=3M
read_buffer_size=2M
#join_buffer_size=2M
join_buffer_size=3M

#Additional configuration:
wait_timeout=15                   #The number of seconds the server waits for activity on a noninteractive connection before closing it.
connect_timeout=30                #The number of seconds that the mysqld server waits for a connect packet before responding with Bad, default is 10 second
max_user_connections = 100
low_priority_updates=1

# if you are performing GROUP BY or ORDER BY queries on tables that
# are much larger than your available memory, you should increase
# the value of read_rnd_buffer_size to speed up the reading of rows
# following sorting operations. 
# but: change the session variable only from within those clients 
# that need to run large queries
read_rnd_buffer_size=5M

max_heap_table_size=768M
tmp_table_size=768M

myisam_sort_buffer_size=64M

# increase until threads_created doesnt grow anymore
thread_cache=256

query_cache_type=1
#query_cache_limit=1M
query_cache_limit=8M
#query_cache_size=32M
query_cache_size=400M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4


[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=32M
write_buffer=32M

[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=32M
write_buffer=32M

[mysqlhotcopy]
interactive-timeout

Thanks.
 
So, your /etc/my.cnf is not empty and is not default either. So you need either buy extra RAM and switch to 64bit, or lower values for buffers. Note, I don't know your specification and I won't give any direct numbers, so you are mostly on your own here, unless you hire me.

You might want to start from scratch with one of these files:

/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf

and update values for your requirements.
 
zEitEr said:
So, your /etc/my.cnf is not empty and is not default either. So you need either buy extra RAM and switch to 64bit, or lower values for buffers. Note, I don't know your specification and I won't give any direct numbers, so you are mostly on your own here, unless you hire me.

You might want to start from scratch with one of these files:

/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf

and update values for your requirements.
Click to expand...

Yes, thanks for your info.
I'm currently a small company so I'll try to do all my self and ask community. Later time when I have many hosting client then it's great to work with you.
 
Yes, it would be great. Did you try any of these files

/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf

How are the results?
 
I did not.
But I'm not sure I use my-large.cnf or my-huge.cnf?
In file my-huge.cnf says "This is for a large system with memory of 1G-2G where the system runs mainly # MySQL." so I'm not sure % of server resource for MySQL Server.
My VPS server is 2GB of RAM, 2 CPU E5620 @ 2.40GHz. It runs websites with PHP + mysql only.

Thanks.
 
Last edited:
Note, you won't find my-huge.cnf in my list. So you might want to try my-medium.cnf.
By the way is that you who updated /etc/my.cnf to the current values? If so, what did you do it for?
 
zEitEr said:
Note, you won't find my-huge.cnf in my list. So you might want to try my-medium.cnf.
By the way is that you who updated /etc/my.cnf to the current values? If so, what did you do it for?
Click to expand...

Yes, thanks.
For updated of /etc/my.cnf, I did many of changes and see the system's performance with a lot of reading in internet about mysql performance. I do changes + track server's performance.
 
Once done it, you already know what to do. Sometimes it's easier to start from a scratch and enlarge the values... anyway you might want to do your own way.
 
Yes, I think I'll try with your suggested file to see how system work. Hope it works good so I don't get any headache with this :)
 
Application slowness issue

zEitEr said:
Total buffers: 1.6G global + 13.1M per thread (130 max threads)

What is your /etc/my.cnf looks like?

Code: 
cat /etc/my.cnf
Click to expand...

Hi,

I'm using 16G RAM with minimum threads of 300,

I have tried /etc/my.cnf, the result is

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

Suggest me how can I allocate RAM in my.cnf
 
You must log in or register to reply here.
Back
Top