Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB?

# ./mysqltuner.pl

 >>  MySQLTuner 1.2.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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 457M (Tables: 1361)
[--] Data in MRG_MYISAM tables: 53M (Tables: 36)
[--] Data in InnoDB tables: 792M (Tables: 1133)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 175

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54s (2K q [37.537 qps], 325 conn, TX: 1M, RX: 354K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 16.2G (874% of installed RAM)
[OK] Slow queries: 0% (9/2K)
[OK] Highest usage of available connections: 1% (13/800)
[OK] Key buffer size / total MyISAM indexes: 128.0M/78.9M
[!!] Key buffer hit rate: 73.9% (1K cached / 297 reads)
[OK] Query cache efficiency: 42.4% (388 cached / 915 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 148 sorts)
[OK] Temporary tables created on disk: 21% (114 on disk / 537 total)
[OK] Thread cache hit rate: 94% (17 created / 325 connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 1% (2K/200K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 792.7M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

And buffer related settings

mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| bulk_insert_buffer_size      | 8388608    |
| innodb_buffer_pool_instances | 1          |
| innodb_buffer_pool_size      | 1073741824 |
| innodb_change_buffering      | all        |
| innodb_log_buffer_size       | 8388608    |
| join_buffer_size             | 16777216   |
| key_buffer_size              | 134217728  |
| myisam_sort_buffer_size      | 8388608    |
| net_buffer_length            | 16384      |
| preload_buffer_size          | 32768      |
| read_buffer_size             | 131072     |
| read_rnd_buffer_size         | 262144     |
| sort_buffer_size             | 2097152    |
| sql_buffer_result            | OFF        |
+------------------------------+------------+
14 rows in set (0.00 sec)
share|improve this question
    
Whoever configured your mysql server appears to have messed up and set the ceiling for RAM mysql can have way over the actual physical limit. I would recommend lowering this bar considerably. –  Zane Dec 23 '13 at 18:07

1 Answer 1

Staring at the mysqltuner output, I see something disturbing

[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)

What is 800 * 18M ?

mysql> select 18 * 1024 * 1024 * 800 / power(1024,3);
+----------------------------------------+
| 18 * 1024 * 1024 * 800 / power(1024,3) |
+----------------------------------------+
|                                14.0625 |
+----------------------------------------+
1 row in set (0.00 sec)

That's 14G your DB Server cannot handle.

RECOMMENDATION

Add this to my.cnf

[mysqld]
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
max_connections = 100

then restart mysql and run mysqltuner.pl again. You will see lower numbers.

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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