MySql-optimized InnoDB, 4 GB memory, detailed explanation of my. ini Chinese configuration scheme for multiple queries, innodbmy. ini

MySql-optimized InnoDB, 4 GB memory, detailed explanation of my. ini Chinese configuration scheme for multiple queries, innodbmy. ini

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

# Start Configuration
# Description: 4 GB memory, only InnoDB, ACID, several connections, and heavy queries
# Type: System
# End configuration information

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

# The following options will be read by the MySQL client application.
# Note: Only MySQL standard client applications can read this chapter.
# If you want your MySQL client to accept these values, you need to specify them as an option in MySQL client library initialization.
#
[Client]
# Password = [your_password]
Port = 3306
Socket =/tmp/mysql. sock

# *** Application-specific options are listed below ***

# MySQL server.
[Mysqld]

# General configuration options
Port = 3306
Socket =/tmp/mysql. sock

# Back_log refers to the number of connections in the operating system listening queue, that is, the number of connections before the MySQL Connection Manager thread processes them.
# If you have a very high connection rate and have seen an error of "rejecting connections", you may need to increase the value.
# Check the maximum value of this parameter in your system documentation.
# Trying to set back_log higher than your operating system does not play any role.
Back_log = 50

# You do not need to listen to a TCP/IP Port.
# If all processes running on the same host need to connect to mysqld, this may be a security enhancement.
# All interactions with mysqld must be performed through Unix sockets (socket) or named pipeline.
# Note that using this option on Windows does not enable named pipes (using the "enable-named-pipe" option) will make mysqld useless.
#
# Skip-networking

# Maximum number of concurrent sessions allowed by MySQL.
# One of the connections will be retained to users with the SUPER privilege. Even if the connection restriction has been reached, the administrator can still log on.
Max_connections = 100

# Maximum number of errors allowed by each host.
# If this restriction is reached, the host will block connection to the MySQL server until "flush hosts" is run or the server is restarted.
# The invalid password and other errors in the connection phase will increase the value.
# Check the "Aborted_connects" status variable of the Global counter.
Max_connect_errors = 10

# The number of tables opened by all threads.
# Increasing this value will increase the number of file descriptors required by mysqld.
# Therefore, you must determine the number of open files to be set. In the "open-file-limit" variable in the "mysqld security" section, it is allowed to be at least 4096.
Table_open_cache = 2048

# Enable external file lock.
# Enabling file locking has a negative impact on individual characteristics. Therefore, only when multiple database instances run on the same file (note that some restrictions are still applied) or if you use other software to lock the MyISAM table at the file level.
# External-locking

# The maximum capacity of a query package that the server can process and the maximum query size that the server can process (it is important to work on large BLOB fields ).
# Dynamically expand for each connection.
Max_allowed_packet = 16 M

# The cache size that can be maintained for binary log SQL statements in a transaction.
# If you often use large, multi-statement transactions, you can increase the value to achieve better performance.
# All statements from the transaction are buffered into the binary log cache and immediately written to the binary log after the COMMIT.
# If the transaction value is greater than this value, the temporary files on the disk will be used in place.
# The buffer is allocated to each connection when the first update statement is used in the transaction.
Binlog_cache_size = 1 M

# Maximum allowable size of a single HEAP (in memory) table.
# This option protects a very large HEAP table created by accident, otherwise it will consume all the memory resources.
Max_heap_table_size = 64 M

# The sort buffer is used to sort order by and group by queries.
# If the sorted data is not in the SORT buffer, a disk-based merged sort will be used instead-see "Sort_merge_passes" status variable.
# If sorting is required, it will be allocated to each thread.
Sort_buffer_size = 8 M

# This buffer is used to optimize full join (JOIN without indexes ).
# In any case, this JOIN is very bad for performance in most cases, but setting this variable to a large value will reduce the impact on performance.
# Check the "Select_full_join" status variable for a certain number of full join statements.
# If full join is found, it will be allocated to each thread.
Join_buffer_size = 8 M

# There are many reusable threads in the cache.
# When a client is disconnected, if the previous thread does not exceed thread_cache_size, the client thread will be placed in the cache.
# If you have many new connections, this will greatly reduce the number of threads required for creation.
# (If you have a good thread implementation, this usually does not give a significant performance improvement .)
Thread_cache_size = 8

# This allows the application to give the thread system a prompt about the number of threads required to run at the same time.
# This value is only valid for systems that support thread_concurrency () function calls (for example, 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. The same query will not be executed again.
# If you have many identical queries and rarely change tables, enabling the query cache will significantly improve the speed.
# Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your loading.
# Note: If your table changes frequently, or if your query contains different original texts, the query cache slows down and replaces the performance improvement.
Query_cache_size = 64 M

# Only the cache result set is smaller than this limit.
# This protects the query cache of a very large result set from overwriting all other query results.
Query_cache_limit = 2 M

# Specify the minimum word length for full-text search indexes.
# If you need to retrieve a shorter word, you may want to reduce it.
# Note: After you modify this value, you need to recreate your FULLINDEX index.
Ft_min_word_len = 4

# If your system supports the memlock () function call, you may want to enable this option (run MySQL to keep it locked to memory, and avoid potential switching output when there is high memory pressure ).
# This is very beneficial to performance.
# Memlock

# If no difference is specified during the create table statement, the default TABLE type used when creating a new TABLE is used.
Default-storage-engine = MYISAM

# The size of the thread stack used.
# The memory size is always retained during the connection time.
# MySQL usually requires no more than 64 KB of memory. However, if you use your own stack UDF function or your system needs more stacks for some operations, you may need to set this value to a higher value.
Thread_stack = 192 K

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

# The maximum capacity of the internal (in memory) temporary table.
# If the growth of a table exceeds this value, it is automatically converted to a disk-based table.
# This restriction applies to a single table, but there are many such tables.
Tmp_table_size = 64 M

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

# Recommended binary log format-mixed.
Binlog_format = mixed

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

# Enable full query logs. Each query (or even an incorrect syntax) received by the server is recorded.
# This is useful for debugging. It is usually disabled during product use.
# Log

# Print the warning to the error log file.
# If you have any MySQL problems, you should enable the warning log and check possible explanations in the error log.
# Log_warnings

# Slow query of records.
# Slow query refers to a query that consumes more than the total time defined in long_query_time. Alternatively, if log_short_format is not enabled, no index is used.
# If you frequently add new queries to the system, enabling this is a good note.
Slow_query_log

# All queries that consume more time than the total time will be considered as slow.
# Do not use the "1" value here, because this will cause even very fast queries to be recorded from time to time (MySQL's current measurement time is only accurate to seconds ).
Long_query_time = 2

# Directory used by MySQL to store temporary files.
# For example, it is used to execute large disk-based sorting and internal and explicit temporary tables.
# If you do not create a very large temporary file, it is advantageous to put it in a swapfs/tmpfs file system.
# In addition, you can place it on a dedicated disk.
# You can specify multiple paths separated by ";"-they will be used in a loop later.
# Tmpdir =/tmp

# *** Replication-related settings

# The unique server ID between 1 and 2 ^ 32-1.
# This value is required for both the master server and slave server.
# If "master-host" is not set, the default value is 1. If it is ignored, MySQL will not act as a master server.
Server-id = 1

# Copy the slave server (comment out the master server section for use ).
#
# To configure the host as a replication slave server, you can select either of the following methods:
#
#1) use the change master to command (which is fully described in our manual)-its syntax is:
#
# Change master to MASTER_HOST = #
# Replace "host", "user", and "password" with a string with quotation marks, and "port" is the port number of the master server (3306 by default ).
#
# Example:
#
# Change master to MASTER_HOST = '192. 564.12.1 ', MASTER_PORT = 125, MASTER_USER = 'job', MASTER_PASSWORD = 'secret ';
#
# Or
#
#2) set the following variables. However, if you select this method, start replication immediately (even if it is unsuccessful, for example, if you do not enter a password in MASTER_PASSWORD and the connection from the server fails ), the slave server will create a master.info file. Any changes to the following variable values in this file will be ignored and overwritten by connections in the master.info file, unless you disable the slave server, delete master.info, and restart the slave server.
# Based on this factor, you may want TO leave the untouched rows (commented) below and replace the change master to (see the above ).
#
# It requires a unique id between 2 and 2 ^ 32-1 (different from the master server ).
# If "master-host" has been set, the default value is 2.
# If it is ignored, it will not be used as a slave server function.
# Server-id = 2
#
# Required.
# Master-host = #
# User name. when connected to the master server, the slave server will use this for authentication-required.
# Master-user = <username> 〉
#
# Password. when connected to the master server, the slave server will use this for authentication-required.
# Master-password = <password> 〉
#
# Port, which the master server is listening.
# Optional-3306 by default.
# Master-port = <port> 〉

# Read-only the slave server.
# Only users with the SUPER privilege and the slave server thread can modify its data.
# You can use this to ensure that no application will inadvertently Replace the master server to modify data on the slave server.
# Read_only

# *** Unique MyISAM options

# The size of the key buffer, used to cache index blocks for the MyISAM table.
# Do not set it to more than 30% of your available memory, because the operating system also needs some memory to cache rows.
# Even if you do not use the MyISAM table, you should set it to 8-64 M because it is also used for internal temporary disk tables.
Key_buffer_size = 32 M

# The buffer size used for full table scan in the MyISAM table.
# If full table scan is required, it will be allocated to each thread.
Read_buffer_size = 2 M

# When reading rows in an ordered sort, you can use this buffer to read rows to avoid searching for disks.
# If you set this value to a very high value, you can greatly improve the performance of order.
# Assign it to each thread as needed.
Read_rnd_buffer_size = 16 M

# MyISAM uses a special tree-like cache to make operations on large batches of inserts (INSERT... SELECT, INSERT... VALUES (...) and load data infile) faster.
# This variable limits the size of the cache tree for each thread in bytes.
# Setting it to 0 will disable this optimization.
# Do not set it to be larger than "key_buffer_size" to optimize performance.
# When a large number of inserts are detected, the buffer is allocated.
Bulk_insert_buffer_size = 64 M

# When MySQL needs to re-index through REPAIR, OPTIMIZE, alter table statements, and load data infile to an empty table, the buffer is allocated.
# It is allocated to each thread, so be careful with large settings.
Myisam_sort_buffer_size = 128 M

# The maximum size of temporary files allowed by MySQL when the index is rebuilt (during REPAIR, alter table, or load data infile.
# If "file-size" is greater than this value, the index will be created through the key cache (slower.
Myisam_max_sort_file_size = 10G

# If a table has more than one index, MyISAM can concurrently use more than one thread to fix them during sorting.
# It makes sense if you have multiple CPUs and enough memory.
Myisam_repair_threads = 1

# Automatically check and fix MyISAM tables that are not properly closed.
Myisam_recover

# *** Specific INNODB options ***

# Use this option if you have a MySQL server that supports InnoDB activation but you are not planning to use it.
# This saves some memory and disk space and increases the speed.
# Skip-innodb

# Additional memory pool. InnoDB is used to store metadata information.
# If InnoDB needs more memory for this purpose, it will start to allocate it from the operating system.
# Because this is fast enough in most recent operating systems, you usually do not need to change this value.
# Show innodb status displays the current total usage.
Innodb_additional_mem_pool_size = 16 M

# InnoDB, unlike MyISAM, uses a buffer pool to cache indexes and row data.
# If you set this value to a greater value, the less disk I/O you need to access in the table.
# On a dedicated database server, you can set this parameter to 80% of the physical memory size of the machine.
# Do not set it too large because physical memory competition may cause paging in the operating system.
# Note: On a 32-bit system, you may be limited to 2-3.5 GB of user-level memory for each processor, so do not set it too high.
Innodb_buffer_pool_size = 2G

# InnoDB stores data to one or more data files to form a tablespace.
# If you have a single physical device for your data, a single auto-scaling file is enough.
# In other cases, a single file per device is a good choice.
# You can also configure InnoDB to use the original disk partition-refer to the Manual for more information about this.
Innodb_data_file_path = ibdata1: 10 M: autoextend

# If you want the InnoDB tablespace file to be stored elsewhere, set this option.
# The default is the MySQL data directory.
# Innodb_data_home_dir = <directory> 〉

# The number of I/O threads used by asynchronous IO operations.
# This value is hard coded as 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 decay, setting this value to a non-zero value will easily help you export your table.
# Start with 1 and raise it until you can successfully export the table.
# Innodb_force_recovery = 1

# Number of threads allowed in the InnoDB kernel.
# The optimal value height depends on the application, hardware, and Operating System Scheduling attributes.
# A high value may cause thread bumps.
Innodb_thread_concurrency = 16

# If it is set to 1, InnoDB refresh the transaction log to the disk each time it submits (providing complete ACID behavior.
# If you want to safely compromise and you are running a small transaction, you can reduce the log disk I/O by 0 or 2.
#0 indicates that the log is only written to the log file, and the log file is refreshed to the disk every second.
# Value 2 indicates that the log is written to the log file at each commit, but the log file is refreshed to the disk only once per second.
Innodb_flush_log_at_trx_commit = 1

# Disable InnoDB acceleration.
# This will disable InnoDB for a complete cleanup and insert buffer merge when it is disabled.
# It may increase the shutdown time, but the alternative is that InnoDB will complete it at the next startup.
# Innodb_fast_shutdown

# The buffer size used by InnoDB to buffer log data.
# Once it is full, InnoDB will refresh it to the disk.
# Because it is Refresh Every second, there is no need to make it large (or even a long transaction ).
Innodb_log_buffer_size = 8 M

# The size of each log file in a log group.
# You can set the size of the union of log files to 25%-100% of the size of your buffer pool to avoid Dynamic Refresh and rewriting of unnecessary buffer pools of log files.
# However, note that a larger log file size will increase the time required for restoration.
Innodb_log_file_size = 256 M

# Total number of files in the log group.
# Generally, 2-3 is enough.
Innodb_log_files_in_group = 3

# The location of the InnoDB log file.
# The default is the MySQL data directory.
# You may want to specify it to a dedicated hard disk or a RAID1 volume label to improve performance.
# Innodb_log_group_home_dir

# Maximum percentage of dirty pages allowed in the InnoDB buffer pool.
# If it reaches, InnoDB will actively clean them to avoid consuming all the clean pages.
# This is a soft restriction and cannot be maintained all the time.
Innodb_max_dirty_pages_pct = 90

# How InnoDB refreshes logs.
# The tablespace always uses the dual-write refresh logic.
# The default value is "fdatasync", and other options are "O_DSYNC ".
# Innodb_flush_method = O_DSYNC

# The authorization lock duration of an InnoDB Transaction before rollback.
# InnoDB automatically detects transaction deadlocks in its own locked 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-secure than InnoDB, a deadlock that InnoDB cannot prompt will occur later.
# Timeout is useful for solving problems like this.
Innodb_lock_wait_timeout = 120

[Mysqldump]
# Do not buffer the entire result set before writing to the file.
# Required when exporting a very large table.
Quick

Max_allowed_packet = 16 M

[Mysql]
No-auto-rehash

# Only keys for UPDATE and DELETE are allowed.
# Safe-updates

[Myisamchk]
Key_buffer_size = 512 M
Sort_buffer_size = 512 M
Read_buffer = 8 M
Write_buffer = 8 M

[Mysqlhotcopy]
Interactive-timeout

[Mysqld_safe]
# Increase the number of files allowed to be opened for each processing.
# Warning: Make sure that you have set a high global system limit!
# A high value is required for a large number of open tables.
Open-files-limit = 8192

You can refer to the above parameter settings based on your own implementation.