Detailed explanation of the my.ini Chinese configuration scheme for MySql optimization: InnoDB, 4GB memory, and multiple queries

This article is a MySQL configuration file solution for a 4G memory system (mainly running MySQL with only InnoDB tables and using several connections to perform complex queries)

#Start configuration information
#Description: 4GB memory, only InnoDB, ACID, a few connections, heavy queries
# Type: System
#End configuration information

# You can copy this file to /etc/my.cnf to set global options, to mysql-data-dir/my.cnf to set server-specific options (in this installation, the directory is C:mysqldata), or to ~/.my.cnf to set user-specific options.
#
# In this file you can use all permanent options a program supports.
# If you want to know which options the program supports, use the "--help" option when running the program.
#
# More detailed information about individual options can also be found in the manual.

# The following options will be read by MySQL client applications.
# Note that only the MySQL standard client application is guaranteed to be able 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]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** APPLICATION SPECIFIC OPTIONS BELOW ***

# MySQL server.
[mysqld]

# Common configuration options
port = 3306
socket = /tmp/mysql.sock

# back_log refers to the number of connections held in the operating system's listen queue, before the MySQL connection manager thread processes them.
# If you have a very high connection rate and are seeing "connection refused" errors, you may want to increase this value.
# Check your system documentation for the maximum value for this parameter.
# Trying to set back_log higher than your OS's limit will have no effect.
back_log = 50

# Don't need to listen on a TCP/IP port at all.
# This may be a security enhancement if all processes running on the same host need to connect to mysqld.
# All interaction with mysqld must be done through Unix sockets or named pipes.
# Note that using this option on Windows without enabling named pipes (via the "enable-named-pipe" option) will render mysqld useless.
#
#skip-networking

# The maximum number of concurrent sessions MySQL allows.
# One of the connections will be reserved for users with the SUPER privilege, allowing administrators to log in even if the connection limit has been reached.
max_connections = 100

# Maximum number of errors allowed per host.
# If this limit is reached, the host will block connections to the MySQL server until "FLUSH HOSTS" is run or the server is restarted.
# Invalid passwords and other errors during the connection phase will cause this value to be increased.
# See the "Aborted_connects" status variable of the global counter.
max_connect_errors = 10

# Number of open tables by all threads.
# Increasing this value will increase the number of file descriptors mysqld requires.
# Therefore, you must be sure to set the number of open files to at least 4096 in the "open-file-limit" variable in the "mysqld security" section.
table_open_cache = 2048

# Enable external file-level locking.
# Enabling file locking will have a negative performance impact, so only use it if you have multiple database instances running on the same files (note that some restrictions still apply) or if you use other software that relies on locking MyISAM tables at the file level.
#external-locking

# The maximum size of a query packet that the server can handle, and the maximum query size that the server can handle (important when working with large BLOB fields).
# Dynamically expand for each connection.
max_allowed_packet = 16M

# The size of the cache that can be maintained for binary log SQL statements in a transaction.
# If you frequently use large, multi-statement transactions, you can increase this value to get better performance.
# All statements from a transaction are buffered in the binary log cache and written to the binary log immediately after a COMMIT.
# If the transaction is larger than this value, a temporary file on disk will be used instead.
# This buffer is allocated to each connection on the first update statement in a transaction.
binlog_cache_size = 1M

# Maximum allowed size of a single HEAP (in-memory) table.
# This option protects against accidentally creating a very large HEAP table that would otherwise use up all memory resources.
max_heap_table_size = 64M

# The sort buffer is used to perform sorting for some ORDER BY and GROUP BY queries.
# If sorted data does not fit into the sort buffer, a disk-based merge sort will be used instead - see the "Sort_merge_passes" status variable.
# If sorting is needed, it will be assigned to each thread.
sort_buffer_size = 8M

# This buffer is used to optimize FULL JOIN (JOIN without index).
# Anyway, this JOIN is very bad for performance in most cases, but setting this variable to a large value will reduce the performance impact.
# See the "Select_full_join" status variable for a certain number of FULL JOINs.
# If FULL JOIN is found, it will be assigned to each thread.
join_buffer_size = 8M

# We keep a lot of reusable threads in a cache.
# When a client disconnects, if the number of threads before this does not exceed thread_cache_size, the client's threads will be placed in the cache.
# If you have a lot of new connections, this will drastically reduce the number of threads needed to be created.
# (If you have a good threading implementation, this usually won't give a noticeable performance improvement.)
thread_cache_size = 8

# This allows an application to give the thread system a hint about the desired number of threads to run at the same time.
# This value is only meaningful on systems that support the thread_concurrency() function call (such as Sun Solaris).
# You should try 2/4/6/... times the number of CPUs for thread_concurrency.
thread_concurrency = 8

# The query cache is used to cache SELECT results and return them later without actually executing the same query again.
# If you have many of the same queries and rarely change tables, enabling the query cache will result in significant speed improvements.
# Please look at the "Qcache_lowmem_prunes" status variable to check if the current value is high enough for your load.
# NOTE: If your tables change frequently, or if your queries are of different nature each time, then query caching will result in slowdowns, replacing performance improvements.
query_cache_size = 64M

# Only cached result sets are smaller than this limit.
# This can prevent a query cache with a very large result set from overwriting all other query results.
query_cache_limit = 2M

# Minimum word length for full-text search indexing.
# If you need to retrieve shorter words, you may want to reduce this.
# Note that after you modify this value, you need to rebuild your FULLINDEX index.
ft_min_word_len = 4

# If your system supports the memlock() function call, you may want to enable that option (to run MySQL to keep it locked into memory and avoid potential swapping out in cases of high memory pressure).
# This is very good for performance.
#memlock

# The default table type to use when creating a new table if no different is specified during the CREATE TABLE statement.
default-storage-engine = MYISAM

# The thread stack size to use.
# This amount of memory is always reserved at connect time.
# MySQL itself usually requires no more than 64K of memory, however if you are using your own stack UDF functions or your system requires more stack for certain operations, you may want to set this value to a higher value.
thread_stack = 192K

# Set the default transaction isolation level.
# Available levels are: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
transaction_isolation = REPEATABLE-READ

# Maximum size of internal (in-memory) temporary tables.
# If a table grows beyond this value, it will automatically be converted to a disk-based table.
# This restriction is for a single table, but there can be many such tables.
tmp_table_size = 64M

# Enable binary logging.
# This is necessary to act as a MASTER in a replication configuration.
# If you need to be able to restore from your last backup point in time, you also need binary logging.
log-bin = mysql-bin

# Recommended binary log format - mixed.
binlog_format = mixed

# If you are using chained slave servers (A->B->C) for replication, you need to enable this option on server B.
# It allows log updates to be achieved by recording logs to the binary log of the slave server through the slave server thread.
#log_slave_updates

# Enable full query logging. Every query received by the server (even syntactically incorrect ones) will be logged.
# This is useful for debugging, it is usually disabled for production use.
#log

# Print warnings to the error log file.
# If you have any problems with MySQL, you should enable warning logging and check the error log for possible explanations.
#log_warnings

# Record slow queries.
# Slow queries are queries that take longer than the total time defined in "long_query_time", or queries that do not use indexes if log_short_format is not enabled.
# If you frequently add new queries to the system, it is a good idea to turn this on.
slow_query_log

# All queries that take longer than this total time will be considered slow.
# Do not use a value of "1" here, as this will cause even very fast queries to be logged from time to time (MySQL currently measures time only to the nearest second).
long_query_time = 2

# Directory used by MySQL to store temporary files.
# For example, it is used to perform large disk-based sorts, as well as internal and explicit temporary tables.
# If you don't create a very large temporary file, it is beneficial to put it in a swapfs/tmpfs filesystem.
# Alternatively, you can put it on a dedicated disk.
# You can specify multiple paths separated by ";" - they will be used later in a loop.
#tmpdir = /tmp

# *** Settings related to replication

# A unique server identification number between 1 and 2^32-1.
# This value is required for both the master and slave servers.
# If "master-host" is not set it defaults to 1, but if omitted MySQL will not function as a master server.
server-id = 1

# Replication slave (comment out the master section to use this).
#
# To configure this host as a replication slave, you can choose between the following two methods:
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) - its syntax is:
#
# CHANGE MASTER TO MASTER_HOST = 〈host〉, MASTER_PORT = 〈port〉, MASTER_USER = 〈user〉, MASTER_PASSWORD = 〈password〉;
#
# Replace 〈host〉, 〈user〉, 〈password〉 with a quoted string, and 〈port〉 is the port number of the master server (default is 3306).
#
# example:
#
# CHANGE MASTER TO MASTER_HOST = '125.564.12.1', MASTER_PORT = 3306, MASTER_USER = 'joe', MASTER_PASSWORD = 'secret';
#
# or
#
# 2) Set the following variables. However, if you choose this method, start replication the first time (even if it is unsuccessful, for example if you did not type a password in MASTER_PASSWORD and the slave connection fails), the slave will create a master.info file. Any later changes to the values ​​of the following variables in that file will be ignored and overwritten by the connections in the master.info file unless you shut down the slave, delete master.info and restart the slave.
# For this reason, you may want to leave the following lines untouched (commented) and use CHANGE MASTER TO (see above) instead.
#
# Requires a unique id between 2 and 2^32-1 (different from the primary server).
# If "master-host" is set, it defaults to 2.
# But if omitted, it will not function as a slave server.
#server-id = 2
#
# The replication master for this slave - required.
#master-host = 〈hostname〉
#
# Username the slave will use to authenticate when connecting to the master - required.
#master-user = 〈username〉
#
# Password the slave will use to authenticate when connecting to the master - required.
#master-password = 〈password〉
#
# Port that the master server is listening on.
# Optional - Defaults to 3306.
#master-port = 〈port〉

# Make the slave server read-only.
# Only users with the SUPER privilege and replication slave server threads can modify its data.
# You can use this to ensure that no application accidentally modifies data on the slave instead of the master.
#read_only

#*** MyISAM-specific options

# The size of the key buffer, used to cache index blocks for MyISAM tables.
# Don't set it more than 30% of your available memory, because the OS also needs some memory to cache lines.
# Even if you don't use MyISAM tables, you should still set it to 8-64M, because it is also used for internal temporary disk tables.
key_buffer_size = 32M

# The size of the buffer used for full table scans of MyISAM tables.
# If a full table scan is needed, it will be assigned to each thread.
read_buffer_size = 2M

# When reading rows in an ordered sort, the rows can be read through this buffer to avoid disk seeks.
# If you set this value to a very high value, you can significantly improve the performance of ORDER BY.
# Allocate to each thread when needed.
read_rnd_buffer_size = 16M

# MyISAM uses a special tree-like cache to make bulk inserts (i.e., INSERT ... SELECT, INSERT ... VALUES(...), and LOAD DATA INFILE) operations faster.
# This variable limits the size in bytes of the cache tree for each thread.
# Setting it to 0 will disable this optimization.
# To optimize performance, do not set it larger than "key_buffer_size".
# This buffer is allocated when a large number of inserts are detected.
bulk_insert_buffer_size = 64M

# This buffer is allocated when MySQL needs to rebuild an index through REPAIR, OPTIMIZE, ALTER table statements, and LOAD DATA INFILE to an empty table.
# It is allocated per thread, so be careful with large settings.
myisam_sort_buffer_size = 128M

# The maximum size of the temporary file that MySQL allows to be used when rebuilding an index (during REPAIR, ALTER TABLE, or LOAD DATA INFILE).
# If 'file-size' is larger than this value, the index will be created via the key cache (which is slower).
myisam_max_sort_file_size = 10G

# If a table has more than one index, MyISAM can use more than one thread to repair them in parallel when sorting.
# This makes sense if you have multiple CPUs and enough memory.
myisam_repair_threads = 1

# Automatically check and repair MyISAM tables that were not closed properly.
myisam_recover

# *** INNODB-SPECIFIC OPTIONS ***

# Use this option if you have a MySQL server with InnoDB enabled and you do not plan to use it.
# This can save some memory and disk space, and increase speed.
#skip-innodb

# Additional memory pool that InnoDB uses to store metadata information.
# If InnoDB needs more memory for this purpose, it will start allocating it from the operating system.
# Since this is fast enough on most recent operating systems, you usually don't need to change this value.
# SHOW INNODB STATUS will show the current total usage.
innodb_additional_mem_pool_size = 16M

# InnoDB, unlike MyISAM, uses a buffer pool to cache indexes and row data.
# The larger you set this value, the less disk I/O is required when accessing the required data in the table.
# On a dedicated database server, you can set this parameter to 80% of the machine's physical memory size.
# Do not set this too large, as contention for physical memory may cause paging in the OS.
# Note that on 32-bit systems you may be limited to 2-3.5G of user-level memory per processor, so don't set this too high.
innodb_buffer_pool_size = 2G

# InnoDB stores data in one or more data files to form a tablespace.
# If you have a single physical device for your data, then a single auto-extending file is sufficient.
# In other cases, a single file per device is a very good choice.
# You can also configure InnoDB to use raw disk partitions - see the manual for more information on this.
innodb_data_file_path = ibdata1:10M:autoextend

# If you want InnoDB tablespace files to be stored somewhere else, set this option.
# The default is the MySQL data directory.
#innodb_data_home_dir = 〈directory〉

# The number of IO threads used for asynchronous IO operations.
# This value is hard-coded to 4 on Unix systems, but on Windows, disk I/O may benefit from a larger number.
innodb_file_io_threads = 4

# If you encounter InnoDB tablespace corruption, setting this value to a non-zero value will help you export your tables easily.
# Start with a value of 1 and increase it until you can successfully export the table.
#innodb_force_recovery=1

# The number of threads allowed in the InnoDB kernel.
# The optimal value is highly dependent on the application, the hardware, and the scheduling properties of the operating system.
# A value that is too high may cause thread thrashing.
innodb_thread_concurrency = 16

# If set to 1, InnoDB flushes the transaction log to disk on every commit (providing full ACID behavior).
# If you want to compromise on safety, and you are running small transactions, you can use 0 or 2 to reduce the disk I/O for the log.
# A value of 0 means that logs are written only to the log file, and the log file is flushed to disk approximately once a second.
# A value of 2 means that logs are written to the log file on every commit, but the log file is only flushed to disk approximately once a second.
innodb_flush_log_at_trx_commit = 1

# Speed ​​up the shutdown of InnoDB.
# This will disable InnoDB from doing a full purge and insert buffer merge when turned off.
# It may increase shutdown time quite a bit, but instead InnoDB will do it on next startup.
#innodb_fast_shutdown

# The size of the buffer used by InnoDB to buffer log data.
# Once it is full, InnoDB will flush it to disk.
# Since it's refreshed every second anyway, there's no need to make it very large (or even very long transactions).
innodb_log_buffer_size = 8M

# The size of each log file in a log group.
# You can set the combined size of the log files to 25%-100% of your buffer pool size to avoid unnecessary buffer pool dynamic flushing and rewriting of log files.
# However, note that a larger log file size will increase the time required for the recovery process.
innodb_log_file_size = 256M

# The total number of files in the log group.
# Usually a value of 2-3 is sufficient.
innodb_log_files_in_group = 3

# Location of the InnoDB log files.
# Defaults to MySQL's data directory.
# You may wish to assign this to a dedicated hard drive or a RAID1 volume to improve performance.
#innodb_log_group_home_dir

# Maximum percentage of dirty pages allowed in the InnoDB buffer pool.
# If it arrives, InnoDB will start aggressively cleaning them to avoid consuming all clean pages.
# This is a soft limit and is not guaranteed to be maintained.
innodb_max_dirty_pages_pct = 90

# The flush method used by InnoDB for the log.
# Tablespaces always use double-write flush logic.
# The default value is "fdatasync", the other option is "O_DSYNC".
#innodb_flush_method = O_DSYNC

# How long an InnoDB transaction should wait to be granted a lock before rolling back.
# InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction.
# If you use the LOCK TABLES command in the same transaction, or other storage engines that are more transaction-safe than InnoDB, then a deadlock may occur later that InnoDB cannot prompt.
# In cases like this, timeouts are useful for troubleshooting.
innodb_lock_wait_timeout = 120

[mysqldump]
# Don't buffer the entire result set before writing to the file.
# Required when exporting very large tables.
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATE and DELETE using keys.
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the number of files allowed to be opened per process.
# WARNING: Make sure you have set the global system limits high enough!
# For a large number of open tables, a high value is necessary.
open-files-limit = 8192

You can refer to the above parameter settings according to your own implementation situation