Answers.Body: <p>First you should fix "<strong><em>Total fragmented tables: 868</em></strong>".</p>
  
  <p>And it does seem your amount of RAM is too small.
  But you could first increase </p>
  
  <pre><code> innodb_buffer_pool_size to 2.5GB
  </code></pre>
  
  <p>and</p>
  
  <pre><code> join_buffer_size to 16M
  </code></pre>
  
  <p>Maybe then MySQL can run a bit longer, so  you get a better quality output of mysqltuner.</p>
  
  <p>Personally I would say all of the following are on the low side:</p>
  
  <pre><code>max_allowed_packet=512M,
  query_cache_limit=256M,
  tmp_table_size=256M,
  max_heap_table_size=256M,
  query_cache_size=128M
  </code></pre>
  
  <p>But would have expected mysqltuner to advise that too.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2016-08-26T06:13:50.607Z
Answers.LastActivityDate: 2018-07-20T19:03:14.773Z
Answers.LastEditDate: 2018-07-20T19:03:14.773Z
Answers.UserDisplayName: Catorghans
Body: <p>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:</p>
  
  <p><code>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</code></p>
  
  <p>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.</p>
  
  <p>Edit: mysqltuner output added: </p>
  
  <pre><code> &gt;&gt;  MySQLTuner 1.6.4 - Major Hayden &lt;major@mhtx.net&gt;
   &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
   &gt;&gt;  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 (&gt; 16.0M, or always use indexes with joins)
      innodb_buffer_pool_size (&gt;= 6G) if possible.
  </code></pre>
  
  <p>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.</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-24T18:30:06.993Z
Comments.Text: hey Eric, what does mysqltuner say now, when 4.7.10 is running for a while?
Comments.UserDisplayName: Catorghans
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-24T23:28:20.547Z
Comments.Text: hesitated adding it due to frequent crashes making it less accurate. but here we go.
Comments.UserDisplayName: Eric H
Tags: <mysql><performance>
Title: Tweaking my.cnf for performance
ThreadText: # Tweaking my.cnf for performance
  
  (Asked by: username_0 on 2016-08-24)
  
  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.
  
  **Tags:** mysql, performance
  
  > **Comments:**
  > **username_1**: hey username_0, what does mysqltuner say now, when 4.7.10 is running for a while?  
  > **username_0**: hesitated adding it due to frequent crashes making it less accurate. but here we go.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-08-26. Score: 2
  
  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.
  
  
  ---
  
  