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 <[email protected]>
 >>  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)
2
  • Which particular bits don't you understand?
    – user9517
    Nov 11, 2015 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, 2015 at 7:30

0

You must log in to answer this question.

Browse other questions tagged .