title: MySQL 100% CPU every 12-18 hours
body: <p>I've inherited a Windows Server (2016) and we have a proprietary piece of software not written by me which uses a MySQL db. I am a novice w/ SQL.</p>
  
  <p>There is client software that will connect to the server randomly throughout the day. Anywhere between 10 and 200 different computers will log into this server throughout the day depending on how busy we are.</p>
  
  <p>We use a VPS w/ 4 virtual cores and 8GB of RAM and a 204GB local disk.</p>
  
  <p>It seems like every 12-18 hours we have issues where our software will hang, Task Manager then shows 99-100% CPU usage for mysqld</p>
  
  <p>It doesn't act normally again until I force quit the software and then restart the mysql service.</p>
  
  <p>Here is the settings file I tracked down that was previously set up.</p>
  
  <p>my.ini settings:</p>
  
  <pre><code>[client]
  port=3306
  [mysql]
  no-beep=
  default-character-set=utf8
  [mysqld]
  port=3306
  datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
  character-set-server=utf8
  default-storage-engine=INNODB
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  log-output=FILE
  general-log=0
  general_log_file="xyz.log"
  slow-query-log=1
  slow_query_log_file="xyz-slow.log"
  long_query_time=10
  log-error="xyz.err"
  server-id=1
  secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
  max_connections=151
  table_open_cache=2000
  tmp_table_size=249M
  thread_cache_size=10
  myisam_max_sort_file_size=100G
  myisam_sort_buffer_size=486M
  key_buffer_size=8M
  read_buffer_size=64K
  read_rnd_buffer_size=256K
  innodb_flush_log_at_trx_commit=1
  innodb_log_buffer_size=1M
  innodb_buffer_pool_size=8M
  innodb_log_file_size=48M
  innodb_thread_concurrency=13
  innodb_autoextend_increment=64
  innodb_buffer_pool_instances=8
  innodb_concurrency_tickets=5000
  innodb_old_blocks_time=1000
  innodb_open_files=300
  innodb_stats_on_metadata=0
  innodb_file_per_table=1
  innodb_checksum_algorithm=0
  back_log=80
  flush_time=0
  join_buffer_size=256K
  max_allowed_packet=4M
  max_connect_errors=100
  open_files_limit=4161
  sort_buffer_size=256K
  table_definition_cache=1400
  binlog_row_event_max_size=8K
  sync_master_info=10000
  sync_relay_log=10000
  sync_relay_log_info=10000
  </code></pre>
  
  <p>Here's a pic of MySQLTuner for Windows and it's results:
  <a href="https://i.stack.imgur.com/p6j6N.png" rel="nofollow noreferrer">mysql_tuner</a></p>
  
  <p>Just looking for some insight into what we can do here to help alleviate this issue. It appears we need to run OPTIMIZE to fix the fragmentation of the tables.</p>
  
  <p>It also shows 23M selects with 0% caching.</p>
  
  <p>The innodb data size / buffer pool is 687M/8M</p>
  
  <p>Any help would be appreciated.</p>
  
  <p>[EDIT]</p>
  
  <p>I just ran the following command that I found in another thread:</p>
  
  <pre><code>SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
  (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
  FROM information_schema.tables WHERE engine='InnoDB') A;
  </code></pre>
  
  <p>I got a value of 2 back, so I should probably set <code>innodb_buffer_pool_size=2G</code>?</p>
  
  <p><code>SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;</code><br>
  will be fine.  You have 8GB machine.  I will take a while for MySQL to dynamically get this in place.  Check in 30 minutes with <code>SELECT @@innodb_buffer_pool_size;</code>   to see if it has accomplished your dynamic request. </p>
  
  <p>Here's what SHOW CREATE TABLE recipes looks like:</p>
  
  <pre><code>mysql&gt; USE xyz; SHOW CREATE TABLE recipes;
  Database changed
  +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | recipes | CREATE TABLE `recipes` (
    `id` int(11) NOT NULL,
    `name` varchar(64) NOT NULL,
    `dname` varchar(64) NOT NULL DEFAULT '',
    `descrpara` text NOT NULL,
    `category` varchar(32) NOT NULL,
    `costperserving` float NOT NULL DEFAULT '0',
    `batchsize` int(11) NOT NULL DEFAULT '1',
    `servname` varchar(32) NOT NULL DEFAULT 'Serving',
    `rectype` tinyint(4) NOT NULL DEFAULT '0',
    `servweight` float NOT NULL DEFAULT '0',
    `servflvol` float NOT NULL DEFAULT '0',
    `flags` tinyint(4) NOT NULL DEFAULT '0',
    `isapproved` tinyint(4) NOT NULL DEFAULT '0',
    `isapproved2` tinyint(4) NOT NULL DEFAULT '0',
    `tags` varchar(128) NOT NULL DEFAULT '',
    `allrestr` tinyint(4) NOT NULL DEFAULT '0',
    `numsides` tinyint(4) NOT NULL DEFAULT '0',
    `utensils` varchar(64) NOT NULL DEFAULT '',
    `active` tinyint(4) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Again, I'm a super novice but could there be something where our my.ini file shows a default character set of utf8, but in this, it shows latin1?</p>
  
  <p>I'll update these again after 24h</p>
  
  <p>PasteBin links for</p>
  
  <p><a href="https://pastebin.com/gam4QmKq" rel="nofollow noreferrer">SHOW GLOBAL STATUS</a></p>
  
  <p><a href="https://pastebin.com/htzqcK3G" rel="nofollow noreferrer">SHOW GLOBAL VARIABLES</a></p>
  
  <p><a href="https://pastebin.com/tBZzDcXm" rel="nofollow noreferrer">SHOW ENGINE INNODB STATUS</a></p>
  
  <hr>
  
  <p>MySQLTuner after >1 day uptime. This is also after changing innodb_buffer_pool_size=8M to 1G. Total RAM usage for mysqld has been about 1.25G since then.</p>
  
  <pre><code>MySQL Tuner 0.8.3 - Peter Chapman &lt;peter@conglomo.co.nz&gt;
  Performing tests on localhost:3306 at Friday, 16 November 2018 6:13:41 AM
  Currently running supported MySQL version 5.7.24-log
  Archive Engine Installed
  Berkeley DB Engine Not Installed
  Federated Engine Not Installed
  InnoDB Engine Installed
  ISAM Engine Not Installed
  NDBCLUSTER Engine Not Installed
  Data in InnoDB tables: 687M (Tables: 257)
  Data in MyISAM tables: 6M (Tables: 32)
  Total fragmented tables: 67
  All database users have passwords assigned
  Up for: 1d 10h 33m 15s (49M q [397.000 qps], 38K conn, TX: 43G, RX: 3G)
  Reads / Writes: 99% / 1%
  Total buffers: 1.0G global + 1.1M per thread (151 max threads)
  Maximum possible memory usage: 1.2G (14% of installed RAM)
  Slow queries: 1% (4/49M)
  Highest usage of available connections: 26% (38/151)
  Key buffer size / total MyISAM indexes: 8.0M/1.0M
  Key buffer hit rate: 98% (630 cached / 9 reads)
  Query cache efficiency: 0% (0 cached / 47M selects)
  Query cache prunes per day: 0
  Sorts requiring temporary tables: 1% (2K temp sorts / 377K sorts)
  Joins performed without indexes: 13254
  Temporary tables created on disk: 3% (2K on disk / 79K total)
  Thread cache hit rate: 99% (41 created / 38K connections)
  Table cache hit rate: 26% (2K open / 7K opened)
  Open file limit used: 0% (10/6K)
  Table locks acquired immediately: 100% (682 immediate / 682 locks)
  InnoDB data size / buffer pool: 687.0M/1.0G
  Run OPTIMIZE TABLE to defragment tables for better performance
  Adjust your join queries to always utilize indexes
  query_cache_limit (&gt; 1M, or use smaller result sets)
  join_buffer_size (&gt; 256.0K, or always use indexes with joins)
  Scan Completed at Friday, 16 November 2018 6:13:44 AM
  </code></pre>
  
  <p><a href="https://pastebin.com/Y1xU561J" rel="nofollow noreferrer">SHOW GLOBAL STATUS - > 1 day uptime</a></p>
  
  <p><a href="https://pastebin.com/SYVNMBJf" rel="nofollow noreferrer">SHOW GLOBAL VARIABLES - > 1 day uptime</a></p>