title: MySQL Optimization in 16GB RAM Server
body: <p>I have a <strong>CentOS</strong> 6.5 VM with 16 GB RAM.</p>
  
  <p>I feel the <strong>MySQL</strong> quite slow response  and I've ran the <strong>MySQLTuner</strong> script, and from the given recommendations how I can make change to my my.cny config file to improve the performance?</p>
  
  <p>Here are the results 2 days after the last MySQL restart : </p>
  
  <pre><code>[--] Skipped version check for MySQLTuner script
  running supported MySQL version 5.7.17-log
  [OK] Operating on 64-bit architecture
  
  -------- Storage Engine Statistics -----------------------------------------------------------------
  [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
  [--] Data in InnoDB tables: 111M (Tables: 88)
  [OK] Total fragmented tables: 0
  
  -------- Performance Metrics -----------------------------------------------------------------------
  [--] Up for: 1d 20h 44m 5s (178K q [1.111 qps], 20K conn, TX: 260M, RX: 24M)
  [--] Reads / Writes: 84% / 16%
  [--] Binary logging is enabled (GTID MODE: OFF)
  [--] Physical Memory     : 15.5G
  [--] Max MySQL memory    : 76.2G
  [--] Other process memory: 40.8M
  [--] Total buffers: 10.1G global + 448.2M per thread (151 max threads)
  [--] P_S Max memory usage: 72B
  [--] Galera GCache Max memory usage: 0B
  [!!] Maximum reached memory usage: 19.3G (124.21% of installed RAM)
  [!!] Maximum possible memory usage: 76.2G (490.99% of installed RAM)
  [!!] Overall possible memory usage with other process exceeded memory
  [OK] Slow queries: 0% (0/178K)
  [OK] Highest usage of available connections: 13% (21/151)
  [OK] Aborted connections: 0.04%  (8/20336)
  [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
  [OK] No joins without indexes
  [OK] Temporary tables created on disk: 24% (420 on disk / 1K total)
  [OK] Thread cache hit rate: 93% (1K created / 20K connections)
  [OK] Table cache hit rate: 21% (416 open / 1K opened)
  [OK] Open file limit used: 0% (3/1K)
  [OK] Table locks acquired immediately: 100% (167 immediate / 167 locks)
  [OK] Binlog cache memory access: 100.00% (4605 Memory / 4605 Total)
  
  -------- Performance schema ------------------------------------------------------------------------
  [--] Memory used by P_S: 72B
  [--] Sys schema is installed.
  
  -------- MyISAM Metrics ----------------------------------------------------------------------------
  [!!] Key buffer used: 18.2% (3M used / 16M cache)
  [OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
  [!!] Read Key buffer hit rate: 85.5% (62 cached / 9 reads)
  
  -------- InnoDB Metrics ----------------------------------------------------------------------------
  [--] InnoDB is enabled.
  [--] InnoDB Thread Concurrency: 0
  [OK] InnoDB File per table is activated
  [OK] InnoDB buffer pool / data size: 10.0G/111.5M
  [OK] InnoDB log file size / InnoDB Buffer pool size: 28.0M * 100/10.0G should be equal 25%
  [OK] InnoDB buffer pool instances: 10
  [--] Number of InnoDB Buffer Pool Chunk : 80 for 10 Buffer Pool Instance(s)
  [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size &amp; Innodb_buffer_pool_instances
  [OK] InnoDB Read buffer efficiency: 99.99% (41669876 hits/ 41674787 total)
  [!!] InnoDB Write Log efficiency: 34.26% (17312 hits/ 50528 total)
  [OK] InnoDB log waits: 0.00% (0 waits / 33216 writes)
  
  -------- Replication Metrics -----------------------------------------------------------------------
  [--] Galera Synchronous replication: NO
  [--] This server is acting as master for 1 server(s).
  [--] No replication setup for this server.
  
  -------- Recommendations ---------------------------------------------------------------------------
  General recommendations:
  Control warning line(s) into /var/log/mysqld-error.log file
  Control error line(s) into /var/log/mysqld-error.log file
  Reduce your overall MySQL memory footprint for system stability
  Dedicate this server to your database for highest performance.
  </code></pre>
  
  <p>and this is my my.cny</p>
  
  <pre><code>[mysqld]
  innodb_buffer_pool_size = 10G
  innodb_log_file_size = 28M
  innodb_log_files_in_group = 28M
  innodb_buffer_pool_instances=10
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  binlog_cache_size = 32M
  key_buffer_size = 16M
  table_open_cache = 64M
  sort_buffer_size = 128M
  read_buffer_size = 64M
  read_rnd_buffer_size = 128M
  join_buffer_size = 128M
  myisam_sort_buffer_size = 32M
  thread_cache_size = 4
  query_cache_size= 32M
  query_cache_limit = 1M
  log-error=/var/log/mysqld-error.log
  pid-file=/var/run/mysqld/mysqld.pid
  skip-name-resolve
  symbolic-links=0
  </code></pre>
  
  <p>Thanks !!!</p>