MySQL Optimizations

I'm running a dedicated server with about 20 websites active. About 6 of them are Magento installations. Running the server as a webserver (web, e-mail, and MySQL ).

This is a preemptive optimization. Currently, I don't seem to have any issues, but I feel that it's good to be proactive and optimize now to avoid any potential problems later.

The server is an 8 core CPU, 8Gb RAM, running CentOS 6. The 8GB of RAM is spread around the entire server, not just the database.
I'm trying to optimize my MySQL installation for the best possible performance and stability. Ran MySQLTuner (1.7.4) and getting these suggestions.

Would appreciate some help in optimizing the settings.

Thank you!

UPDATE: 11/28/2017 08:20PM EST

- Added additional 8GB of RAM.  
 - Latest MySQLTuner results. Global Status, Variables, Innodb Status files updated.

Can view the 3 files requested here.
Latest info below:

```
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 90M (Tables: 300)
[--] Data in InnoDB tables: 923M (Tables: 3591)
[--] Data in MEMORY tables: 3M (Tables: 141)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 13d 17h 16m 32s (29M q [25.279 qps], 860K conn, TX: 43G, RX: 9G)
[--] Reads / Writes: 68% / 32%
[--] Binary logging is disabled
[--] Physical Memory : 7.7G
[--] Max MySQL memory : 5.1G

Your my.cnf includes 4 lines that should be removed, they are

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size

these are all per connection RAM requirements and are driving your RAM footprint much higher than necessary. Let the DEFAULTS work for you to improve response time with room to breathe in RAM.
Consider adjusting the following in your my.cnf, please

key_buffer_size = 512M  # from 1G because 200M is used now
innodb_buffer_pool_size = 4G  # to match RAM reported by MySQLTuner
max_connections = 100  # from 500, until more than 8 are used
thread_cache_size = 100  # from 192 MySQL v 8 recommends CAP of 100 to avoid overload
query_cache_limit     remove to allow default of 1M rather than 32M
query_cache_min_res_unit = 512  # from 4K to conserve qcache RAM used per RESULT
table_open_cache = 10000  # from 2000 to support the 28,000 opened by instance

After the above items have been configured, shutdown/restart have been completed and you have 7 (or more) consecutive days of uptime, please refresh your my.cnf and MySQLTuner report, THEN
for another detailed analysis, add/refresh to OriginalPost the following,
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;
for up to five specific additional cfg recommendations, one per day to be applied, monitor.

StackExchange Database Administrators Q#187505, answer score: 2