myql5.7.7 Optimizing configuration Parameters

# Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# on Linux can copy this file to/etc/my.cnf to set global options,
# MYSQL-DATA-DIR/MY.CNF to set server-specific options
# (@[email protected] for the installation) or to
# ~/.MY.CNF to set user-specific options.
#
# on Windows 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 with the startup option
# "--defaults-file".
#
# to run 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 the ' command line Shell ' to start the server, e.g.
# net start Mysqlxy
#
#
# Guildlines for editing the This file
# ----------------------------------------------------------------------
#
# In this file, you can use the ' all long options ' and the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information on the individual options can also be
# found in the manual.
#
# for advice The change settings
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# CLIENT Section
# ----------------------------------------------------------------------
#
# The following options would be a read by MySQL client applications.
# Note that only client applications shipped by MySQL is guaranteed
# to read the 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]
No-beep

# pipe
# socket=0.0
port=3306

[MySQL]

Default-character-set=utf8

# SERVER Section
# ----------------------------------------------------------------------
#
# The following options would be read by the MySQL Server. Make sure
# You are installed the server correctly (see above) so it reads this
# file.
#
# server_type=3
[Mysqld]

# The next three options is mutually exclusive to Server_port below.
# skip-networking

# Enable-named-pipe

# shared-memory

# Shared-memory-base-name=mysql

# The Pipe the MySQL Server would use
# Socket=mysql

# The TCP/IP Port the MySQL Server would listen on
port=3306

# Path to installation directory. All paths is usually resolved relative to this.
# basedir= "C:/Program files/mysql/mysql Server 5.7/"
Basedir = "C:\ProgramData\MySQL\MySQL Server 5.7"
# Path to the database root
Datadir= "C:\ProgramData\MySQL\MySQL Server 5.7\data"
Tmpdir = "C:\ProgramData\MySQL\MySQL Server 5.7\data"
Socket = "C:\ProgramData\MySQL\MySQL Server 5.7\data\mysql.sock"

# The default character set that would be used if a new schema or table is
# created and no character set is defined
Character-set-server=utf8

# The default storage engine that would be used when create new tables when
Default-storage-engine=innodb

Explicit_defaults_for_timestamp = True
# Set The SQL mode to strict
Sql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution"

# Enable Windows Authentication
# Plugin-load=authentication_windows.dll

# General and Slow logging.
Log-output=file
General-log=1
General_log_file= "Ms-20150605jtjq.log"
Slow-query-log=1
Slow_query_log_file= "Ms-20150605jtjq-slow.log"
long_query_time=10

# Binary Logging.
Log-bin= "Ms-20150605jtjq-bin"

# Error Logging.
Log-error= "Ms-20150605jtjq.err"

# Server Id.
Server-id=1

# The maximum amount of concurrent sessions the MySQL server would
# Allow. One of these connections'll be reserved-a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=2000

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# has a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# note:in Case your tables change very often or if your queries is
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
Query_cache_size=0

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore sure to set the amount of open files
# allowed to @ 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's automatically converted to disk
# based table This limitation are for a single table. There can many
# of them.
tmp_table_size=246m

# How many threads we should keep in a cache for reuse. When a client
# Disconnects, the client ' s threads is 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 had a lot of new
# connections. (Normally this doesn ' t give a notable performance
# Improvement If you have a good thread implementation.)
thread_cache_size=300

#限定用于每个数据库线程的栈大小. The default setting is sufficient to meet most applications
Thread_stack = 192k
#*** MyISAM Specific Options
# The maximum size of the temporary file MySQL is allowed
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If The file-size would is bigger than this, the index would be created
# through the key cache (which is slower).
#myisam_max_sort_file_size =100g

# If The temporary file used for fast index creation would is bigger
# than using the key cache by the amount specified here and then prefer the
# Key Cache method. This is mainly used to force long character keys in
# Large tables to use the slower key, cache method to create the index.
#myisam_sort_buffer_size =37m

# 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'll also be
# used for internal temporary disk tables.
key_buffer_size=512m

# 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=4m
read_rnd_buffer_size=32m

#*** INNODB Specific Options * * *
# innodb_data_home_dir=0.0

# Use the This option if you had a MySQL server with InnoDB support enabled
# but don't plan to use it. This would save memory and disk space
# and speed up some things.
# Skip-innodb

Innodb_data_home_dir = "C:\ProgramData\MySQL\MySQL Server 5.7\data\"
# If set to 1, InnoDB'll 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 is running small
# transactions, you could set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log was 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 a flushed to disk approximately once per second.
Innodb_flush_log_at_trx_commit=0

# The size of the buffer InnoDB uses for buffering log data. As soon as
# It's full, InnoDB'll has to flush it to disk. As it is flushed
# once per second anyway, it does not do sense to has it very large
# (even with long transactions).
innodb_log_buffer_size=16m

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this and the less disk I/O are needed to
# Access data in tables. On a dedicated database server if 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
# might be limited to 2-3.5g of the user level memory per process, so does not
# Set it too high.
innodb_buffer_pool_size=256m

# 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 would increase the time needed for the
# recovery process.
innodb_log_file_size=128m

# 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 leads to thread thrashing.
innodb_thread_concurrency=128

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

# The number of regions 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=8

# determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted to the old sublist must stay there after its first access BEF Ore
# It can be moved to the new sublist.
innodb_old_blocks_time=1000

# IT Specifies the maximum number of. ibd files, 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 was enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each new Ly 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 was 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 is stacked during this short time before MySQL momentarily
# stops answering new requests.
# need to increase the if you expect a large number of connections in a short period of time.
Back_log=80

# If This was set to a nonzero value, all tables be closed every flush_time seconds to free up resources and
# Synchronize unflushed data to disk.
# This option is the best used only on systems with minimal resources.
Flush_time=0

# The minimum size of the buffer that's used for plain index scans, range index scans, and joins
# indexes and thus perform full table scans.
join_buffer_size=128m

# 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=1024m

# If More than this many successive connection requests from a host is interrupted without a successful connection,
# The server blocks this host from performing further connections.
max_connect_errors=2000

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

# Set the query cache type. 0 for OFF, 1 for on and 2 for DEMAND.
Query_cache_type=0

# 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, cannot is improved with query optimization
# or improved indexing.
sort_buffer_size=32m

# The number of the table definitions (from. frm files), that can is stored in the definition cache.
# If You use a large number of tables, you can create a large table definition cache to the speed up opening of tables.
# The table definition cache takes less space and does is not a 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 is 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 replication slave synchronizes it master.info file to disk.
# (using Fdatasync ()) after every Sync_master_info events.
sync_master_info=10000

# If The value of this variable are greater than 0, the MySQL server synchronizes its relay logs 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 replication slave synchronizes it relay-log.info file to disk.
# (using Fdatasync ()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

#批量插入数据缓存大小, can effectively improve the efficiency of insertion, the default is 8M
Bulk_insert_buffer_size = 64M

Interactive_timeout = 120
Wait_timeout = 120
Log-bin-trust-function-creators=1

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

myql5.7.7 Optimizing configuration Parameters