Optimize Mysql & My.cnf to 16g Ram & 8 Core CPU? [30-80 concurrent users, 200-300 at peak]

Question

I haven't optimized my MY.cnf file since I purchased the server which was a 16GB ram & 1 Core Server.

I don't know what to do because all this optimization stuff is new to me. Server has around 60-80 concurrent users on normal hours and 20-40 at bad hours and then sometimes peaks around 200-300 at different occasions.

What should I do?

Here is my mysqltuner.pl:

 >>  MySQLTuner 1.7.4 - 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
Please enter your MySQL administrative login: forge
Please enter your MySQL administrative password: [OK] Currently running supporte                                                                                                                                                             d MySQL version 5.7.20-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------                                                                                                                                                             --------------------
[--] Log file: /var/lib/mysql/crypto-boi.err(0B)
[!!] Log file /var/lib/mysql/crypto-boi.err doesn't exist
[!!] Log file /var/lib/mysql/crypto-boi.err isn't readable.

-------- Storage Engine Statistics ---------------------------------------------                                                                                                                                                             --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                                                                                             ISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 514M (Tables: 66)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                                                                                             --------------------


-------- CVE Security Recommendations ------------------------------------------                                                                                                                                                             --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics ---------------------------------------------------                                                                                                                                                             --------------------
[--] Up for: 4m 0s (49K q [205.421 qps], 286 conn, TX: 38M, RX: 7M)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 2.0G
[--] Other process memory: 1.6G
[--] Total buffers: 1.5G global + 2.0M per thread (214 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (9.95% of installed RAM)
[OK] Maximum possible memory usage: 2.0G (12.54% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/49K)
[OK] Highest usage of available connections: 2% (6/214)
[OK] Aborted connections: 0.35%  (1/286)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 37K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 283 sorts)
[!!] Joins performed without indexes: 187
[OK] Temporary tables created on disk: 4% (10 on disk / 227 total)
[OK] Thread cache hit rate: 97% (6 created / 286 connections)
[OK] Table cache hit rate: 75% (340 open / 453 opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (48M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/514.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 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.82% (11564032 hits/ 11585189 total)
[!!] InnoDB Write Log efficiency: 54.07% (3734 hits/ 6906 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10640 writes)

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

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

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

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

Here is my My.CNF

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = *
#
max_connections         = 300
key_buffer_size         = 256M
read_buffer_size = 1M
table_open_cache = 15000
thread_cache_size = 384
connect_timeout = 10
interactive_timeout = 7000
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 0
query_cache_type = 0
innodb_buffer_pool_size = 1G

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 32M
#
#
expire_logs_days        = 10
max_binlog_size   = 100M
default_password_lifetime = 0

Answer 1

You have 514M of innodb data. Leave it alone until you have growth of your innodb storage requirements.

Your my.cnf/ini should be changed to:

#read_buffer_size=1M  # disabled to allow default to serve your needs
thread_cache_size=100 # from 384 to avoid OOM per V8 CAP at 100 threads_cached.

Shutdown/restart.

Repost current MySQLTuner report when you have been busy for at least 24 hours.

In 4 minutes your information is nothing compared to what we will see after a full working day or a week.

Answer 2

I think your mysql database engine is innodb. From my experience, increase innodb_buffer_pool_size will improve mysql performance so much, especially you have 16G Ram. If your server is dedicated for mysql you can set this value up to 12G

Here are some useful optimization guidelines I often follow https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/