Tweaking my.cnf for performance

Question

Over the last year, with the help of mysqltuner, I've adjusted my.cnf to improve the performance of the 7 CiviCRM installations on my VPS. But in the last few days, as I upgraded my sites to 4.7.10, MySQL has been repeatedly terminated due to low system memory and I'm wondering if a settings tweak could help avoid my having to pony up the dough for more RAM on my VPS. I currently have 3GB RAM, and now around 2.5GB is routinely in use. Running CentOS, Drupal 7.50, CiviCRM 4.7.10. Here's my current my.cnf:

performance-schema=0 default-storage-engine=MyISAM innodb_file_per_table=1 max_allowed_packet=512M open_files_limit=50000 query_cache_type=1 query_cache_limit=256M tmp_table_size=256M max_heap_table_size=256M innodb_buffer_pool_instances=1 join_buffer_size=16M query_cache_size=128M table_open_cache=10000 innodb_buffer_pool_size=750M

Any glaring crazy settings there? I do have the option of having cPanel determine open_files_limit max_allowed_packets, and innodb_buffer_pool_size if those would be beneficial.

Edit: mysqltuner output added:

 >>  MySQLTuner 1.6.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
[OK] Currently running supported MySQL version 5.6.31
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------    --
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 20M (Tables: 47)
[--] Data in InnoDB tables: 6G (Tables: 10176)
[!!] Total fragmented tables: 868

-------- Security Recommendations  -----------------------------------------    --
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  -------------------------------------    --
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 20m 23s (132K q [27.574 qps], 1K conn, TX: 249M, RX: 32M)
[--] Reads / Writes: 64% / 36%
[--] Binary logging is disabled
[--] Total buffers: 1.1G global + 16.9M per thread (151 max threads)
[OK] Maximum reached memory usage: 1.5G (52.60% of installed RAM)
[!!] Maximum possible memory usage: 3.6G (128.91% of installed RAM)
[OK] Slow queries: 0% (26/132K)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Aborted connections: 0.15%  (2/1312)
[OK] Query cache efficiency: 84.1% (78K cached / 93K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 189
[!!] Temporary tables created on disk: 30% (1K on disk / 3K total)
[OK] Thread cache hit rate: 97% (28 created / 1K connections)
[OK] Table cache hit rate: 21% (9K open / 45K opened)
[OK] Open file limit used: 0% (97/50K)
[OK] Table locks acquired immediately: 99% (26K immediate / 26K locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 19.0% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
[OK] Read Key buffer hit rate: 99.4% (118K cached / 709 reads)
[OK] Write Key buffer hit rate: 97.9% (15K cached / 322 writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 750.0M/6.4G
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 97.90% (46993 used/ 47999 total)
[OK] InnoDB Read buffer efficiency: 99.86% (29679387 hits/ 29721392 total)
[OK] InnoDB Write log efficiency: 97.49% (404397 hits/ 414797 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10400 writes)

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

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

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

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

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

-------- 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 16.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 6G) if possible.

In the past, I found that putting in a large innodb_buffer_pool_size seems to contribute to the crashes, so I usually ignore that suggestion.

Answer 1

First you should fix "Total fragmented tables: 868".

And it does seem your amount of RAM is too small. But you could first increase

 innodb_buffer_pool_size to 2.5GB

and

 join_buffer_size to 16M

Maybe then MySQL can run a bit longer, so you get a better quality output of mysqltuner.

Personally I would say all of the following are on the low side:

max_allowed_packet=512M,
query_cache_limit=256M,
tmp_table_size=256M,
max_heap_table_size=256M,
query_cache_size=128M

But would have expected mysqltuner to advise that too.