0

Newbie as far as InnoDB goes, but I installed MySQLTuner awhile back and according to this it looks like I need to adjust my settings, but can someone chime in with some deeper understanding of all this?

I have created two db's both of which are InnoDB. There are system created dbs/tables and what not using MyISAM.

 >>  MySQLTuner 1.6.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
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.21-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 879K (Tables: 17)
[--] Data in InnoDB tables: 1G (Tables: 52)
[!!] Total fragmented tables: 14

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'someuser@%' hasn't specific host restriction.
[!!] User 'someuser2@%' hasn't specific host restriction.
[!!] There is no basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 26d 8h 50m 48s (120M q [53.014 qps], 24M conn, TX: 34B, RX: 25B)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Total buffers: 3.3G global + 34.2M per thread (150 max threads)
[!!] Maximum reached memory usage: 8.4G (223.62% of installed RAM)
[!!] Maximum possible memory usage: 8.3G (222.72% of installed RAM)
[OK] Slow queries: 0% (580/120M)
[!!] Highest connection usage: 100%  (151/150)
[OK] Aborted connections: 0.00%  (511/24377271)
[OK] Query cache efficiency: 47.6% (75M cached / 158M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (46 temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 0% (9K on disk / 1M total)
[OK] Thread cache hit rate: 99% (116K created / 24M connections)
[OK] Table cache hit rate: 71% (463 open / 652 opened)
[OK] Open file limit used: 1% (109/8K)
[OK] Table locks acquired immediately: 100% (35M immediate / 35M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.7% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/350.0K
[OK] Read Key buffer hit rate: 99.1% (297K cached / 2K reads)
[!!] Write Key buffer hit rate: 17.1% (40K cached / 33K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 3.0G/1.2G
[!!] InnoDB buffer pool instances: 8
[!!] InnoDB Used buffer: 47.34% (93075 used/ 196600 total)
[OK] InnoDB Read buffer efficiency: 100.00% (3368470582 hits/ 3368515922 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 14186703 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce your overall MySQL memory footprint for system stability
    Reduce or eliminate persistent connections to reduce connection usage
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 150)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    innodb_buffer_pool_instances(=3)
  • Which particular bits don't you understand? – Iain Nov 11 '15 at 7:03
  • [--] Total buffers: 3.3G global + 34.2M per thread (150 max threads) [!!] Maximum reached memory usage: 8.4G (223.62% of installed RAM) [!!] Maximum possible memory usage: 8.3G (222.72% of installed RAM) stick out the most to me... I'm not sure how I am getting 34MB buffer per thread as that seems awfully high right? Upping connections is no problem at all. It says to use 3 instances and I have 8 (I believe this was the default or recommended somewhere), etc – user756659 Nov 11 '15 at 7:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.