0

I have a virtualized 32bits Server for Mysql (RAM : 115 Go).

I am trying to optimize with mysqltuner and I get this :

>>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1-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: 6G (Tables: 293)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23m 4s (55K q [39.949 qps], 8K conn, TX: 72M, RX: 9M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 2.7G global + 35.6M per thread (600 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 23.6G (785% of installed RAM)
[OK] Slow queries: 0% (35/55K)
[OK] Highest usage of available connections: 8% (53/600)
[OK] Key buffer size / total MyISAM indexes: 1.5G/1.1G
[OK] Key buffer hit rate: 99.6% (85M cached / 327K reads)
[!!] Query cache efficiency: 10.5% (2K cached / 22K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (43 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 68
[OK] Temporary tables created on disk: 19% (645 on disk / 3K total)
[OK] Thread cache hit rate: 97% (202 created / 8K connections)
[OK] Table cache hit rate: 34% (542 open / 1K opened)
[OK] Open file limit used: 28% (843/3K)
[OK] Table locks acquired immediately: 97% (47K immediate / 48K locks)

-------- 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
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 5M, or use smaller result sets)
    join_buffer_size (> 20.0M, or always use indexes with joins) 

my.cnf :

key_buffer              = 1500M
key_buffer_size         = 1500M
tmp_table_size          = 800M
max_heap_table_size     = 800M
sort_buffer_size        = 15M
join_buffer_size        = 20M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 600
table_cache            = 1200
open_files_limit       = 2400
#thread_concurrency     = 10
query_cache_limit       = 5M
query_cache_size        = 500M

Does it mean that I can use only 23,6 Go of the RAM ? Do you think it would be important to migrate to a 64bits container ? The Mysql performance are not so good that I could imagine with a such server...

  • Why are you still using a 32-bit server? – BuahahaXD Mar 23 '15 at 18:27
  • I use a virtual server with proxmox and I unfortunally taked the 32 bits template. Proxmox recomend 32 bits containers... But in my case, it seems to be a mistake... – Galupo Mar 24 '15 at 20:32
1

I wish the creator of that tool would toss that line. It unnecessarily scares people. And the number it gives is both too small and too large.

But, in your case, it there is a real danger. If you had just 3 queries running that were nasty enough, they could be asking for 2.4GB -- more than your RAM!! This is because tmp_table_size and max_heap_table_size are set to 800M. Change them to, say, 20M. Queries will still work, but they may spill to disk more often. More importantly, you want swap or crash.

Fragmented tables -- bogus. Virtually all tables are fragmented. OPTIMIZE TABLE is almost never worth doing.

But...

key_buffer_size of 1.5G in 2GB of RAM is really bad. 300M is about right if you are using only MyISAM. The key_buffer is for MyISAM indexes. You need to leave room for data, which is cached by the Operating System.

Since the Query cache hit rate is so low and the size is dangerously high, you are better off turning it off and using the RAM for other stuff. query_cache_type = OFF and query_cache_size = 0.

Since you have a "tiny" amount if RAM (2GB), you should probably lower max_connections. Look at the STATUS value of Max_used_connections to see the high water mark since last restart.

Edit

  1. Upgrade to a 64-bit MySQL
  2. Configure key_buffer_size and innodb_buffer_pool_size in my.cnf to the amount of RAM you have. Guidelines: http://mysql.rjweb.org/doc.php/memory
  3. See how things are going; come back to us for more help (and/or abuse) if needed.
  • Thank you for your answer. My RAM is up to 115Go : total used free shared buffers cached Mem: 120586240 22251744 98334496 0 0 21338844 -/+ buffers/cache: 912900 119673340 Swap: 121634816 3356 121631460 So, i don't understand why mysql doesn't seem to work properly with it... Maybe because of the 32 bits limitations... – Galupo Mar 24 '15 at 20:34
  • 1
    (1) NO 32bit process can access more than 4GB. (2) MySQL must be configured to use the "right" amount of RAM. – Rick James Mar 24 '15 at 20:49
  • I upgrade to a 64 bits server and everything is working fine now ! All my RAM is now recognized. Thank you for your help ! – Galupo Mar 26 '15 at 12:39

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.