Help with mysqltuner.pl results

[karll]

I've just run the mysqltuner.pl script for the first time. Here's the whole thing:

>> 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
Please enter your MySQL administrative login:
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[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: 34M (Tables: 9)
[--] Data in InnoDB tables: 296M (Tables: 173)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 378K (Tables: 5)
[!!] Total fragmented tables: 176

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 8h 7m 16s (4M q [6.578 qps], 225K conn, TX: 363B, RX: 1B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 597.8M (120% of installed RAM)
[OK] Slow queries: 0% (3/4M)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/53.8M
[OK] Key buffer hit rate: 99.9% (22M cached / 12K reads)
[OK] Query cache efficiency: 54.8% (1M cached / 2M selects)
[!!] Query cache prunes per day: 17774
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 156K sorts)
[!!] Temporary tables created on disk: 44% (12K on disk / 29K total)
[OK] Thread cache hit rate: 99% (233 created / 225K connections)
[!!] Table cache hit rate: 7% (302 open / 3K opened)
[OK] Open file limit used: 7% (72/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[!!] InnoDB data size / buffer pool: 296.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 400)
innodb_buffer_pool_size (>= 296M)

So yes, this is a tiny VPS, but this is what I have to work with.
Based on this, what would be the most important improvements I could do?
Also, what I'd like to know is, when it lists the "variables to adjust" at the end, does it mean that I should be setting them to the ranges listed, or that they are already in those ranges? (E.g. it says "query_cache_size (> 16M)", so does that mean query_cache_size is > 16M or that it should be > 16M? The my.cnf file says "query_cache_size = 16M" ...)
Anyway, any and all optimization recommendations are welcome.
By the way, is there a way to run one single command to run "optimize table" on all the tables?

 

[hellreturn]

tmp_table_size increase that to 32M and same for max_heap_table_size

Change u max connections from 150 to 75.
max_connections = 75

Now another question is.. how much extra RAM you have on sever/VPS?

 

[karll]

tmp_table_size increase that to 32M and same for max_heap_table_size

Change u max connections from 150 to 75.
max_connections = 75

Thank you for these ideas.

Now another question is.. how much extra RAM you have on sever/VPS?

You mean how much free memory? Not much, total RAM is 512MB, unused RAM is only 64KB or so. I can get 90MB extra for $5 per month, but can't really afford / justify the expense for the time being.

 

[hellreturn]

If you are on 512Mb RAM and having no free memory then you are on tight plan. I believe 512MB should work just fine for 50 users/sec but that's just my thought.

How much is price for your current plan?

 

[karll]

If you are on 512Mb RAM and having no free memory then you are on tight plan. I believe 512MB should work just fine for 50 users/sec but that's just my thought.

How much is price for your current plan?

I'm paying $19.99 per month. This is the 512MB plan with linode.com.

I can't guarantee that I was reading the amount of unused memory correctly, but I got it from the "free" command (oh, now it's even less):
$ free
total used free shared buffers cached
Mem: 508252 495744 12508 0 20212 143008
-/+ buffers/cache: 332524 175728
Swap: 262140 127848 134292

Maybe I'm running unnecessary processes or something.

 

[CTXMedia]

Just run #top - it should report total mem in the summary at the top of the page (press 'q' to quit).

 

[karll]

Ah, I see now. Both programs (free, top) are by default reporting in kilobytes. Right now it's ~68000KB free, i.e. 68MB.

 

[shawn]

I wouldn't worry about the query_cache_size and tmp_table_size comments. I've messed around with those in the past, but increasing them didn't satisfy the script. I think it's going strictly off of the prunes number and the temp tables on disc numbers... which really probably has more to do with what web software you're running (a forum) and how it's being used (lots of unique queries).

 

[hellreturn]

Install htop it's best.

32M for temp tables is pretty low anyways. I use 96M for my forums and temp tables created on disk are way less. But i have 4GB RAM

 

[ARealRiley]

I'm paying $19.99 per month. This is the 512MB plan with linode.com.

I can't guarantee that I was reading the amount of unused memory correctly, but I got it from the "free" command (oh, now it's even less):
$ free
total used free shared buffers cached
Mem: 508252 495744 12508 0 20212 143008
-/+ buffers/cache: 332524 175728
Swap: 262140 127848 134292

Maybe I'm running unnecessary processes or something.

http://www.linuxatemyram.com/

 

[karll]

http://www.linuxatemyram.com/

Thank you! That explains a lot!