MySQL Tuning -- High Memory Usage

Question

I'm trying to tune a mysql db using mysqltuner. Mysqltuner is advising that I increase the join_buffer_size and the query_cache_size. At the same time, however, it is warning that my max memory usage is high, which it is at 200%+ of installed RAM (which is 2GB). The bind I'm in is of course that if I do what mysqltuner says, the memory usage will shoot up even higher. So what do I do here? Is the problem rather not with mysql but with the apps running on this server that are evidently requring mysql to do a huge amount of cacheing? How would you mysql administrator experts out there proceed from here? See the mysqltuner report below along with my current [mysqld] settings:

MySqlTuner report:

MySQLTuner 1.2.0 - Major Hayden 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.30-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 310M (Tables: 264)
[--] Data in InnoDB tables: 8M (Tables: 365)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 376

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 20m 10s (2M q [35.305 qps], 25K conn, TX: 88B, RX: 2B)
[--] Reads / Writes: 22% / 78%
[--] Total buffers: 480.0M global + 33.5M per thread (110 max threads)
[!!] Maximum possible memory usage: 4.1G (203% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 7% (8/110)
[OK] Key buffer size / total MyISAM indexes: 150.0M/129.5M
[OK] Key buffer hit rate: 100.0% (15M cached / 7K reads)
[OK] Query cache efficiency: 74.3% (762K cached / 1M selects)
[!!] Query cache prunes per day: 4341
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 11K sorts)
[!!] Joins performed without indexes: 3901
[OK] Temporary tables created on disk: 0% (2K on disk / 547K total)
[OK] Thread cache hit rate: 99% (8 created / 25K connections)
[OK] Table cache hit rate: 46% (688 open / 1K opened)
[OK] Open file limit used: 17% (593/3K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 8.9M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
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_size (> 32M)
join_buffer_size (> 32.0M, or always use indexes with joins)

Current mysqld settings in my.cnf:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 150M
max_allowed_packet = 160M
max_connections = 110
wait_timeout = 60
query-cache-type = 1
query-cache-size = 32M
query_cache_limit = 2M
thread_cache_size = 16
tmp_table_size = 32M
max_heap_table_size = 32M
join_buffer_size = 32M
table_open_cache = 128
table_cache = 1600
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 2M
innodb_log_buffer_size = 8M

Answer 1

You've got a rather strange database here - it's doing a lot more writes than reads.

It's really important that you tune your DBMS to run well with Innodb or you tune it to run well with MyISAM - but you seem to have a mixture of both table types. You cannot have a DBMS which is optimal with both. You need to start migrating your data onto a single engine. Given the write heavy nature of the database, I'd recommend Innodb.

Total buffers: 480.0M global + 33.5M per thread (110 max threads)

Currently the risk of running into swap is primarily driven by the per-connection memory usage. but note that this is only a risk just now. You also need to establish whether you need 110 concurrent connections - and preferably reduce that a bit. Usually the sort_buffer_size is the main culprit for using up memory per connection - but yours is a quarter of the default.

If you are consolidating on innodb then you need should probably increase the innodb buffer pool size.

You can play around with different values here.