MySQL Optimization in 16GB RAM Server

Question

I have a CentOS 6.5 VM with 16 GB RAM.

I feel the MySQL quite slow response and I've ran the MySQLTuner script, and from the given recommendations how I can make change to my my.cny config file to improve the performance?

Here are the results 2 days after the last MySQL restart :

[--] 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 & 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.

and this is my my.cny

[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

Thanks !!!

Answer 1

@user3659206 Your configuration requests are quite extreme. innodb_log_files_in_group = 28M should be 2, the default if you will put # in front of the next 4 lines (so the system uses defaults) # sort_buffer_size = 128M # read_buffer_size = 64M # read_rnd_buffer_size = 128M # join_buffer_size = 128M you will be amazed. After shutdown/restart.

Use mysqlcalculator.com to see that your current configuration is prepared to use 82GB with the values you have requested.

Run your mysqltuner again to see the reduced memory footprint.
Please post your new mysqltuner so we all can recognize your progress.