MySQL Tuning and optimization How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action on master, when performing this action on master slave will become master.

Both on Centos 6.7
Master Version MySQL-server-5.5.41-1.el6.x86_64
Slave Version mysql-community-server-5.6.28-2.el6.x86_64  

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

-------- Storage Engine Statistics ------------------------------                                                                      -------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be in                                                                      secure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 335M (Tables: 62)
[--] Data in InnoDB tables: 107G (Tables: 1670)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 551

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 21m 48s (3K q [0.071 qps], 1K conn, TX: 8M, RX: 270M)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 109.1G global + 29.2M per thread (600 max threads)
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 0% (2/600)
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M
[OK] Key buffer hit rate: 100.0% (7M cached / 2K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)
[!!] Joins performed without indexes: 252
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)
[OK] Thread cache hit rate: 99% (2 created / 1K connections)
[OK] Table cache hit rate: 23% (309 open / 1K opened)
[OK] Open file limit used: 0% (48/20K)
[OK] Table locks acquired immediately: 99% (76K immediate / 76K locks)
[!!] Connections aborted: 20%
[!!] InnoDB data size / buffer pool: 108.0G/107.0G


How do we work on join queries Run OPTIMIZE TABLE closing MySQL connections and footprint for system stability.    

-------- 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
        Your applications are not c properly
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (&gt; 1M, or use smaller result sets)
        join_buffer_size (&gt; 3.0M, or always use indexes with joins)
        innodb_buffer_pool_size (&gt;= 107G)
[!!] Total fragmented tables: 551  -- bogus, ignore it
[0.071 qps],  -- very lightly loaded
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)  -- bogus, ignore it
[OK] Highest usage of available connections: 0% (2/600)  -- consider lowering max_connections
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M  -- lower key_buffer_size
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)  -- Turn the QC off
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)  -- Let's see some slow queries and help you optimize them
[!!] Joins performed without indexes: 252  -- ditto
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)  -- ditto
[OK] Open file limit used: 0% (48/20K)  -- 20K?
[!!] InnoDB data size / buffer pool: 108.0G/107.0G  -- bogus; it's a cache!

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance  -- NO!
    MySQL started within last 24 hours - recommendations may be inaccurate  -- true
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***  -- bogus
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (&gt; 1M, or use smaller result sets)  -- no, set to 0
    innodb_buffer_pool_size (&gt;= 107G)  -- No, it is dangerously high now (assuming you have 128GB of RAM)


For more analysis, provide SHOW VARIABLES and SHOW GLOBAL STATUS, plus RAM size.  (Due to limitations here, you may need a postit site.)

The biggest problem seems to be poorly designed/indexed queries.  Those cannot be fixed by tuning.