; MySQL Server Instance Configuration File
; ----------------------------------------------------------------------
; Generated by the MySQL Server Instance Configuration Wizard
;
;
; Installation Instructions
; ----------------------------------------------------------------------
;
; On Linux you can copy this file to /etc/my.cnf to set global options,
; mysql-data-dir/my.cnf to set server-specific options
; (@localstatedir@ for this installation) or to
; ~/.my.cnf to set user-specific options.
;
; On Windows you should keep this file in the installation directory
; of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
; make sure the server reads the config file use the startup option
; "--defaults-file".
;
; To run the server from the command line, execute this in a
; command line shell, e.g.
; mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
;
; To install the server as a Windows service manually, execute this in a
; command line shell, e.g.
; mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
;
; And then execute this in a command line shell to start the server, e.g.
; net start MySQLXY
;
;
; Guidelines for editing this file
; ----------------------------------------------------------------------
;
; In this file, you can use all long options that the program supports.
; If you want to know the options a program supports, start the program
; with the "--help" option.
;
; More detailed information about the individual options can also be
; found in the manual.
;
; For advice on how to change settings please see
; https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
;
;
; CLIENT SECTION
; ----------------------------------------------------------------------
;
; The following options will be read by MySQL client applications.
; Note that only client applications shipped by MySQL are guaranteed
; to read this section. If you want your own MySQL client program to
; honor these values, you need to specify it as an option during the
; MySQL client library initialization.
;
[client]

; pipe=

; socket=MYSQL

port=3306

[mysql]
no-beep

; default-character-set=

; SERVER SECTION
; ----------------------------------------------------------------------
;
; The following options will be read by the MySQL Server. Make sure that
; you have installed the server correctly (see above) so it reads this
; file.
;
; server_type=3
[mysqld]

; The next three options are mutually exclusive to SERVER_PORT below.
; skip-networking
; enable-named-pipe
; shared-memory

; shared-memory-base-name=MYSQL

; The Pipe the MySQL Server will use
; socket=MYSQL

; The TCP/IP Port the MySQL Server will listen on
port=3306

; Path to installation directory. All paths are usually resolved relative to this.
; basedir="C:/Program Files/MySQL/MySQL Server 8.0/"

; Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
tmpdir=c:/windows/temp

; The default character set that will be used when a new schema or table is
; created and no character set is defined
; character-set-server=

; The default authentication plugin to be used when connecting to the server
default_authentication_plugin=caching_sha2_password

; The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

; Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

; General and Slow logging.
log-output=FILE

general-log=0

general_log_file="LACDTL03WL8573.log"

slow-query-log=1

slow_query_log_file="LACDTL03WL8573-slow.log"

long_query_time=30

; Error Logging.
log-error="LACDTL03WL8573.err"

; ***** Group Replication Related *****
; Specifies the base name to use for binary log files. With binary logging
; enabled, the server logs all statements that change data to the binary
; log, which is used for backup and replication.
log-bin="LACDTL03WL8573-bin"

; ***** Group Replication Related *****
; Specifies the server ID. For servers that are used in a replication topology,
; you must specify a unique server ID for each replication server, in the
; range from 1 to 2^32 − 1. “Unique” means that each ID must be different
; from every other ID in use by any other source or replica.
server-id=3

; ***** Group Replication Related *****
; The host name or IP address of the replica to be reported to the source
; during replica registration. This value appears in the output of SHOW REPLICAS
; on the source server. Leave the value unset if you do not want the replica to
; register itself with the source.
; report_host=0.0

; NOTE: Modify this value after Server initialization won't take effect.
lower_case_table_names=1

; Secure File Priv.
secure-file-priv=""

; The maximum amount of concurrent sessions the MySQL server will
; allow. One of these connections will be reserved for a user with
; SUPER privileges to allow the administrator to login even if the
; connection limit has been reached.
max_connections=151

; The number of open tables for all threads. Increasing this value
; increases the number of file descriptors that mysqld requires.
; Therefore you have to make sure to set the amount of open files
; allowed to at least 4096 in the variable "open-files-limit" in
; section [mysqld_safe]
table_open_cache=2000

; Maximum size for internal (in-memory) temporary tables. If a table
; grows larger than this value, it is automatically converted to disk
; based table This limitation is for a single table. There can be many
; of them.
;tmp_table_size=49M
tmp_table_size=256M

; How many threads we should keep in a cache for reuse. When a client
; disconnects, the client's threads are put in the cache if there aren't
; more than thread_cache_size threads from before.  This greatly reduces
; the amount of thread creations needed if you have a lot of new
; connections. (Normally this doesn't give a notable performance
; improvement if you have a good thread implementation.)
thread_cache_size=30

;*** MyISAM Specific options
; The maximum size of the temporary file MySQL is allowed to use while
; recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
; If the file-size would be bigger than this, the index will be created
; through the key cache (which is slower).
myisam_max_sort_file_size=100G

; The size of the buffer that is allocated when sorting MyISAM indexes
; during a REPAIR TABLE or when creating indexes with CREATE INDEX
; or ALTER TABLE.
myisam_sort_buffer_size=89M

; Size of the Key Buffer, used to cache index blocks for MyISAM tables.
; Do not set it larger than 30% of your available memory, as some memory
; is also required by the OS to cache rows. Even if you're not using
; MyISAM tables, you should still set it to 8-64M as it will also be
; used for internal temporary disk tables.
key_buffer_size=8M

; Size of the buffer used for doing full table scans of MyISAM tables.
; Allocated per thread, if a full scan is needed.
read_buffer_size=64K

read_rnd_buffer_size=256K

;*** INNODB Specific options ***
; innodb_data_home_dir=

; Use this option if you have a MySQL server with InnoDB support enabled
; but you do not plan to use it. This will save memory and disk space
; and speed up some things.
; skip-innodb

; If set to 1, InnoDB will flush (fsync) the transaction logs to the
; disk at each commit, which offers full ACID behavior. If you are
; willing to compromise this safety, and you are running small
; transactions, you may set this to 0 or 2 to reduce disk I/O to the
; logs. Value 0 means that the log is only written to the log file and
; the log file flushed to disk approximately once per second. Value 2
; means the log is written to the log file at each commit, but the log
; file is only flushed to disk approximately once per second.
;innodb_flush_log_at_trx_commit=1
innodb_flush_log_at_trx_commit=0

; The size of the buffer InnoDB uses for buffering log data. As soon as
; it is full, InnoDB will have to flush it to disk. As it is flushed
; once per second anyway, it does not make sense to have it very large
; (even with long transactions).
innodb_log_buffer_size=256K

; InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
; row data. The bigger you set this the less disk I/O is needed to
; access data in tables. On a dedicated database server you may set this
; parameter up to 80% of the machine physical memory size. Do not set it
; too large, though, because competition of the physical memory may
; cause paging in the operating system.  Note that on 32bit systems you
; might be limited to 2-3.5G of user level memory per process, so do not
; set it too high.
innodb_buffer_pool_size=12G

; Size of each log file in a log group. You should set the combined size
; of log files to about 25%-100% of your buffer pool size to avoid
; unneeded buffer pool flush activity on log file overwrite. However,
; note that a larger logfile size will increase the time needed for the
; recovery process.
;innodb_log_file_size=48M
innodb_log_file_size=3G

; Number of threads allowed inside the InnoDB kernel. The optimal value
; depends highly on the application, hardware as well as the OS
; scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=25

; The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

; The number of regions that the InnoDB buffer pool is divided into.
; For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
; by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=6

; Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

; Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
; it can be moved to the new sublist.
innodb_old_blocks_time=1000

; It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

; When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

; When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
; in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

; Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

; The number of outstanding connection requests MySQL can have.
; This option is useful when the main MySQL thread gets many connection requests in a very short time.
; It then takes some time (although very little) for the main thread to check the connection and start a new thread.
; The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
; stops answering new requests.
; You need to increase this only if you expect a large number of connections in a short period of time.
back_log=80

; If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
; synchronize unflushed data to disk.
; This option is best used only on systems with minimal resources.
flush_time=0

; The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
; indexes and thus perform full table scans.
join_buffer_size=256K

; The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
; mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

; If more than this many successive connection requests from a host are interrupted without a successful connection,
; the server blocks that host from performing further connections.
max_connect_errors=100

; Changes the number of file descriptors available to mysqld.
; You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161

; If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
; sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
; or improved indexing.
sort_buffer_size=256K

; The number of table definitions (from .frm files) that can be stored in the definition cache.
; If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
; The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
; The minimum and default values are both 400.
table_definition_cache=1400

; Specify the maximum size of a row-based binary log event, in bytes.
; Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

; If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.
; (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

; If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
; (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

; If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
; (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

; Load mysql plugins at start."plugin_x ; plugin_y".
; plugin_load

; The TCP/IP Port the MySQL Server X Protocol will listen on.
loose_mysqlx_port=33060

; wsl 20200806

innodb-doublewrite=0
;innodb_flush_log_at_timeout=60

net-read-timeout = 600
net-write-timeout = 600
connect-timeout = 3600
mysqlx_write_timeout = 600


;wsl 20210203
;https://stackoverflow.com/questions/63432924/very-slow-writes-on-mysql-8-waiting-for-handler-commit
; innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
; innodb_io_capacity=1900  # from 200 to allow more IOPSecond to your storage device
innodb_flush_neighbors=0  # from 0 to expedite writing to current extent

; innodb_innodb_max_dirty_pages_pct_lwm=10  # from 10 percent to expedite writes
; innodb_innodb_max_dirty_pages_pct=25  # from 90 percent to reduce innodb_buffer_pool_pages_dirty count
; innodb_change_buffer_max_size=50  # from 25 percent to expedite your high volume activity

; https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_neighbors
innodb_flush_neighbors=0
innodb_io_capacity_max=5000
innodb_io_capacity=2500
innodb_log_compressed_pages=OFF


; https://dba.stackexchange.com/questions/232365/mysql-8-0-updates-are-sometimes-stalling
innodb_flush_log_at_trx_commit = 2
; range_optimizer_max_mem_size = 0


; https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html
 ; innodb_log_compressed_pages=0


; To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup.
; If either of these options is specified and --log-bin is also specified, the option specified later takes precedence.

skip-log-bin = 1


innodb_buffer_pool_dump_at_shutdown = on