join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tuning

[syndicated]

I have been tuning MYSQL for a few days now, it's using some huge cache's

key_buffer_size = 128M
sort_buffer_size = 1M
read_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 50
table_open_cache = 1500
query_cache_size= 164M
query_cache_type = 1
query_cache_limit = 1M
max_connections = 200
tmp_table_size = 256M
max_heap_table_size = 256M

The tmp table sizes seem to be pretty huge, but that's what the tuner wanted

So the results are:

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 (> 1.0M, or always use indexes with joins)

This will ask me to up JOIN BUFFER to over 6M, and keeps asking for more. Join Buffer adds to the PER CONNECTION ram used, so adding 4M x 200 connections = 800 megs more ram. I read that some files are always going to cause this to fire off, so what is a reasonable amount to put this at. I have it set at 1M already up from I think 128k or whatever the default it.

Also is 256m of temp table too much even tho the tuner set it there?

Thanks

[syndicated]

Here is the full post

------- General Statistics --------------------------------------------------
--] Skipped version check for MySQLTuner script
OK] Currently running supported MySQL version 5.1.63-cll
OK] Operating on 64-bit architecture

------- Storage Engine Statistics -------------------------------------------
--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
--] Data in MyISAM tables: 450M (Tables: 680)
--] Data in InnoDB tables: 8M (Tables: 147)
--] Data in MEMORY tables: 0B (Tables: 4)
!!] Total fragmented tables: 39

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

------- Performance Metrics -------------------------------------------------
--] Up for: 14h 3m 49s (10M q [215.581 qps], 36K conn, TX: 76B, RX: 2B)
--] Reads / Writes: 83% / 17%
--] Total buffers: 559.0M global + 7.2M per thread (200 max threads)
!!] Maximum possible memory usage: 2.0G (98% of installed RAM)
OK] Slow queries: 0% (218/10M)
OK] Highest usage of available connections: 34% (69/200)
OK] Key buffer size / total MyISAM indexes: 128.0M/147.6M
OK] Key buffer hit rate: 100.0% (2B cached / 17K reads)
OK] Query cache efficiency: 94.0% (9M cached / 10M selects)
OK] Query cache prunes per day: 0
OK] Sorts requiring temporary tables: 0% (13 temp sorts / 98K sorts)
!!] Joins performed without indexes: 5561
OK] Temporary tables created on disk: 23% (25K on disk / 111K total)
OK] Thread cache hit rate: 99% (113 created / 36K connections)
OK] Table cache hit rate: 96% (1K open / 1K opened)
OK] Open file limit used: 45% (1K/4K)
OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)
OK] InnoDB data size / buffer pool: 8.5M/9.0M

------- Recommendations -----------------------------------------------------
eneral 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
ariables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
   join_buffer_size (> 1.0M, or always use indexes with joins)

[Eric]

Howdy,

I run this in a cron each morning after I optimize every table. Some of this you're just to have to accept as the most you're willing to offer MySQL. Also some apps are not well written with MySQL so they never use indexes with their joins. I run this right after I Optimize ever table but still it insists there are some that are not correct. It's a since of /good enough/ and if your MySQL server is performing well for your users.

Thanks!

[syndicated]

So is it safe to run

mysqlcheck -o -A

in a Cron every night?

Thanks

[johnburk]

Yes you can run a cron, but it's not going to do much. Check the nr of fragmented tables. Run mysqlcheck -o -A, wait 12 hours and check the nr of fragmented tables. I don't think that nr is going to decrease that much.

"Maximum possible memory usage: 2.0G (98% of installed RAM)"

Using 98% of your memory is not good! You need to reduce global usage, per thread use, max connections or increase your ram. 2GB is really not that much. Memory is not that expensive.

Also you might want to increase your innodb from 8mb to 16mb.