Vfrdtyky

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.

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.

We use a VPS w/ 4 virtual cores and 8GB of RAM and a 204GB local disk.

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

It doesn't act normally again until I force quit the software and then restart the mysql service.

Here is the settings file I tracked down that was previously set up.

my.ini settings:

[client]

port=3306

[mysql]

no-beep=

default-character-set=utf8

[mysqld]

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7Data

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

Here's a pic of MySQLTuner for Windows and it's results:
mysql_tuner

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.

It also shows 23M selects with 0% caching.

The innodb data size / buffer pool is 687M/8M

Any help would be appreciated.

[EDIT]

I just ran the following command that I found in another thread:

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;

I got a value of 2 back, so I should probably set innodb_buffer_pool_size=2G?

SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;

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 SELECT @@innodb_buffer_pool_size; to see if it has accomplished your dynamic request.

Here's what SHOW CREATE TABLE recipes looks like:

mysql> 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)

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?

I'll update these again after 24h

PasteBin links for

SHOW GLOBAL STATUS

SHOW GLOBAL VARIABLES

SHOW ENGINE INNODB STATUS

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.

MySQL Tuner 0.8.3 - Peter Chapman <peter@conglomo.co.nz>

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 (> 1M, or use smaller result sets)

join_buffer_size (> 256.0K, or always use indexes with joins)

Scan Completed at Friday, 16 November 2018 6:13:44 AM

SHOW GLOBAL STATUS - > 1 day uptime

SHOW GLOBAL VARIABLES - > 1 day uptime

Suggestion to consider for immediate relief

SET GLOBAL innodb_buffer_pool_size=1*1024*1024*1024

from your MySQL command prompt

will make a world of difference for you and possibly let you survive while other details are sorted out.

Change your my.ini [mysqld] section to 1G for your next stop/start services.

Additional suggestions to consider for your my.ini [mysqld] section
Rate Per Second=RPS

innodb_buffer_pool_size=2G  # from 1G reported in SHOW GLOBAL STATUS of 11/16

innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every SECOND

thread_cache_size=100  # from 10 to increase threads_cached for increased performance

innodb_io_capacity=1600  # from 200 to allow higher IOPS 

read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_next RPS of 63407

innodb_log_buffer_size=16M  # from 1M for ~ 30 minutes in RAM before WD

open_files_limit=8192  # from 6209 to reduce opened_files RPHour of 152

table_open_cache=3000  # from 2000 to reduce opened_tables RPHour of 258

query_cache_size=0  # from 1M to conserve RAM since QC is OFF

For additional assistance please view my profile, Network profile for contact info.

If you add a comment, please start with @wilsonhauck for me to be alerted by SO automatically.