Shulou(Shulou.com)06/03 Report--

The following mainly brings you how to configure mysql files. I hope these contents can bring you practical use, which is also the main purpose of this article when I edit mysql files. All right, don't talk too much nonsense, let's just read the following.

Description

1. Red represents key parameters

2, "global cache" and "thread cache". Global cache is shared by all threads. Thread cache is the creation of a thread when each thread connects to data (if no thread pool is set). If there are 200 connections, that is 200 threads. If the parameter setting value is 10m, then the parameter value is 10*200=2000M=2G, which is likely to eat up memory, so thread cache does not take up as much as global cache. Do not set too large, must pay attention to

Character-set-server=utf8

# CVM character set

Collation-server=utf8_bin

# the school team rules of the character set, this is a pit, beginners have to be careful

# default * * _ ci,ci is case insensitive, that is, "case insensitive". An and A will be treated as the same in character judgment.

* * _ bin is binary data compilation and storage, which is case-sensitive

Lower_case_table_names = 0

# default is 0, and database and table names are case sensitive

Back_log = 600,

# how many requests can be stored in the stack in a short period of time before MYSQL temporarily stops responding to new requests. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. The default is 50.

Max_connections = 3000

# MySQL allows the maximum number of process connections, which needs to be increased if there are frequent Too Many Connections errors, but the higher the value, the greater the memory footprint.

Max_connect_errors = 50

# set the maximum number of connection requests for each host to be interrupted abnormally. When this number is exceeded, the MYSQL CVM will prohibit the connection requests of the host until the mysql CVM is restarted or the relevant information of the host is cleared by the flush hosts command.

Wait_timeout=864000

The initial value of # wait_timeout is 28800. When the application does not connect for 8 hours, it will start to disconnect from the application connection pool, and when it reconnects, it will prompt a wait_timeout error.

External-locking = FALSE

# use the-skip-external-locking MySQL option to avoid external locking. This option is enabled by default

Max_allowed_packet = 32m

# set the maximum amount of messages transmitted at one time in network transmission. The system default value is 1MB, the maximum value is 1GB, and a multiple of 1024 must be set.

Skip-name-resolve

# disable DNS reverse parsing, the only limitation is that only IP addresses can be used in GRANT statements, so you must pay attention to adding this setting to an existing system

Slow_query_log=on

# Open slow query record

Slow_query_log_file=mysql-slow

# slow query logging

Long_query_time = 1

# record queries whose execution time exceeds N (seconds)

Server-id = 1

# parameters that must be set for master-slave replication, and cannot be repeated with other machines

Log-bin=MySQL-bin

# binary log

Binlog_cache_size = 4m

# [Thread cache] memory allocated for each session, the cache used to store binary logs during a transaction.

# show global status like 'bin%'

# We can get the usage of the current database binlog_cache_size by using the above statement

# +-+ +

# | Variable_name | Value |

# +-+ +

# | Binlog_cache_disk_use |??

# | Binlog_cache_use |?

# Binlog_cache_disk_use indicates the number of temporary files used in the cache binary log due to insufficient memory designed by our binlog_cache_size

# Binlog_cache_use indicates the number of times binlog_cache_size cache is used

# when the corresponding Binlog_cache_disk_use value is relatively large, we can consider increasing the corresponding binlog_cache_size value appropriately.

Max_binlog_cache_size = 2m

# indicates the maximum cache memory size that binlog can use, which is fine by default

Max_binlog_size = 512m

# maximum capacity of a single binlog file. If it exceeds that, a new binlog file will be created.

Expire_logs_days = 7

# the number of days for binary files to be deleted automatically. Default is 0, which means no deletion

# mysql uses the operation of flush logs to clear logs. Flush logs can be triggered in the following situations

# 1. Restart

# 2. BINLOG file size reaches the parameter max_binlog_size limit

# 3. Execute the command manually.

# in the case of dual primary replication or cascading replication, the standby master or cascade node writes all operations to the binlog

Log-slave-updates

# Relay log path

Relay-log = relay-bin

# when the slave is down from the library, if the relay-log is damaged, causing part of the relay log not to be processed, then automatically discard all outstanding relay-log and retrieve the log from the master, thus ensuring the integrity of the relay-log. This feature is off by default. If you set the value of relay_log_recovery to 1, you can enable this feature on the slave slave library. It is recommended to enable it.

# relay_log_recovery = 1

# this parameter is the same as sync_binlog, when set to 1, every time the slave I / O thread receives the binlog log sent by master, it will be written to the system buffer, and then brushed into the relay log relay log, which is the safest, because in the event of a crash, you will lose at most one transaction, but it will cause a large number of Imax O on the disk. When set to 0, it is not immediately brushed into the relay log, but it is up to the operating system to decide when to write, although the security is reduced, but a large number of disk Ihop O operations are reduced. This value defaults to 0 and can be modified dynamically. It is recommended to use the default value.

# sync_relay_log=0

# this parameter is the same as the sync_relay_log parameter, when set to 1, every time the slave I / O thread receives the binlog log sent by master, it will be written to the system buffer and then brushed into the relay-log.info, which is the safest, because in the event of a crash, you will lose at most one transaction, but it will cause a large number of Icano on the disk. When set to 0, it is not immediately brushed into the relay-log.info, but is decided by the operating system when to write, although the security is reduced, but a large number of disk Igamo operations are reduced. This value defaults to 0 and can be modified dynamically. It is recommended to use the default value.

# sync_relay_log_info=0

Default-storage-engine = InnoDB

# default engine

Transaction_isolation = READ-COMMITTED

# set the default transaction isolation level. The levels available are as follows:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

# 1.READ UNCOMMITTED- read unsubmitted 2.READ COMMITTE- read submitted 3.REPEATABLE READ-repeatable 4.SERIALIZABLE-Serial

Table_cache = 2000

# set the number of maximum cache tables. Default 2000, global parameters, cache table information, including fields, indexes, etc. The advantage of using table caching is that you can access the contents of the table more quickly. Executing flush tables clears the contents of the cache. In general, you can look at the state values Open_tables and Opened_tables of the database peak run time through show status like 'open%tables%' to determine whether you need to increase the value of table_cache (where open_tables is the number of tables currently open and Opened_tables is the number of tables already open). That is, if open_tables is close to table_cache and the value of Opened_tables is gradually increasing, then you should consider increasing the size of this value. In addition, when the Table_locks_waited is relatively high, you also need to add table_cache.

Tmp_table_size = 20m

# [Thread cache] it specifies the maximum value of the internal memory temporary table, which is allocated by each thread. (it is the minimum values of tmp_table_size and max_heap_table_size that actually limit. If the temporary table exceeds the limit, MySQL automatically converts it to a disk-based MyISAM table, while the memory table does not

# if temporary tables are rarely used, you can default

Max_heap_table_size = 256m

# if you do not use memory tables, you can default

Thread_cache_size = 64

# CVM thread cache this value indicates that the number of threads stored in the cache can be reused. If there is space in the cache when disconnected, the client thread will be placed in the cache. If the thread is re-requested, the request will be read from the cache. If the cache is empty or a new request, then the thread will be recreated, if there are many new threads. Increasing this value can improve system performance. By comparing the variables of the Connections and Threads_created states, you can see the effect of this variable. The setting rules are as follows: 1GB memory configuration is 8jin2GB, configuration is 16jie 3GB, configuration is 32je 4GB or higher, but it can be configured larger.

Thread_concurrency = 8

The # hread_concurrency variable is for Solaris 8 and earlier systems. Setting this variable mysqld calls the thr_setconcurrency () function. This function allows the application to prompt threads running at the same time as many threads as needed. This parameter no longer works in the current version of Solaris. This parameter has been marked obsolete in mysql 5.6.1 and removed in version 5.7.2 of mysql.

Query_cache_size = 64m

# [Global Cache] the previous article specifically talked about this parameter, which is not suitable for allocating too large. And in systems with high concurrency and large amount of writing, it is recommended that this function be disabled.

Query_cache_limit = 4m

# specify the buffer size that can be used by a single query. The default is 1m

Query_cache_min_res_unit = 2k

# the default is 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

# query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

# if the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the cache, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.

# query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

# if the query cache utilization is less than 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and Qcache_lowmem_prunes > 50, the query_cache_size may be a little small, or there may be too many fragments.

# query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Read_buffer_size = 1m

# [Thread cache] MySql read buffer size. A request for a sequential scan of the table allocates a read buffer and MySql allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Read_rnd_buffer_size = 16m

# [Thread cache] the random read (query operation) buffer size of MySql. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Sort_buffer_size = 2m

# [Thread cache] Sort_Buffer_Size is a connection-level parameter. When each connection (session) needs to use this buffer for the first time, it allocates the set memory at one time.

# Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, too large setting + high concurrency may deplete system memory resources. For example, 500 connections will consume 500*sort_buffer_size (8m) = 4G memory

When # Sort_Buffer_Size exceeds 2KB, mmap () instead of malloc () is used for memory allocation, resulting in inefficiency.

# filesort appears in explain select*from table where order limit;

Max_length_for_sort_data= 1024

# mysql sorting uses both sort_buffer_size and max_length_for_sort_data. If the length and value of query column and orderby column will be greater than 1024, max_length_for_sort_data will be used.

# you can default

Join_buffer_size = 2m

# [Thread cache] is used for the size of the cache associated between tables. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Bulk_insert_buffer_size = 64m

# Multi-valued INSERT or LOAD DATA adds records to a non-empty data table. The insertion efficiency can be effectively improved by adjusting this parameter. The default is 8m.

Key_buffer_size = 2048m

# [Global cache] for MyISAM engine, approve the buffer size for indexing, and increase it to get better index processing performance. For CVMs with memory around 4GB, this parameter can be set to 256MB or 384MB.

Myisam_sort_buffer_size = 128m

# buffers required to reorder when the MyISAM table changes

Myisam_max_sort_file_size = 10G

# the maximum temporary file size allowed by MySQL when re-indexing (when REPAIR, ALTER TABLE or LOAD DATA INFILE).

# if the file size is larger than this value, the index is created through key-value buffering (slower)

Myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

If a table has more than one index, MyISAM can fix them with more than one thread through parallel sorting.

This is a good choice for users with multiple CPU and a lot of memory.

Myisam_recover

# automatically check and repair MyISAM tables that are not properly closed

Innodb_buffer_pool_size = 2048m

# [Global caching] this is very important for Innodb tables. Innodb is more sensitive to buffering than MyISAM tables. MyISAM can run in the default key_buffer_size setting, while Innodb is like a snail in the default innodb_buffer_pool_size setting. Because Innodb caches both data and indexes, there is no need to leave too much memory for the operating system, so if you only need to use Innodb, you can set up up to 70-80% of its available memory (note that this is available, not the total amount of memory).

Innodb_additional_mem_pool_size = 16m

# [Global Cache] this parameter is used to set the memory pool size of data directory information and other internal data structures stored in InnoDB, similar to Oracle's library cache.

# you can default

Innodb_data_file_path = ibdata1:1024M:autoextend

# important data of tablespace files

# you can default

Innodb_read_io_threads

# number of threads read. Default is 4.

Innodb_write_io_threads

# number of threads written. Default is 4.

Innodb_thread_concurrency = 0

# number of concurrent threads. Default is 0, which means no limit. Values range from 0 to 1000 and cannot be dynamically modified.

# you can default

Innodb_flush_log_at_trx_commit = 1

# if this parameter is set to 1, the log will be written to disk after each transaction is committed. It can be set to 0 or 2, but at the risk of losing data in the event of a failure.

The default value of 1 means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk, which is time-consuming. Set to 2 for many applications, it means writing not to the hard disk but to the system cache. Logs still flush to the hard disk per second, so you don't usually lose updates of more than 1-2 seconds.

# setting it to 0 will be faster, but the security aspect is relatively poor. Even if the MySQL is down, the transaction data may be lost. Setting it to 2 will only lose data when the entire operating system is down. Setting it to 1 is the safest setting and the performance is also the weakest.

Innodb_log_buffer_size = 1m

# [Global caching] this configuration determines the cache allocated for transactions that have not yet been executed. Its default value (1MB) is generally sufficient, but if your transaction contains binary large objects or large text fields, the cache will quickly fill up and trigger additional Icano operations. Take a look at the Innodb_log_waits state variable, and if it is not 0, add innodb_log_buffer_size. MySQL developers suggest setting it to between 1m and 8m.

Innodb_log_file_size = 500m

# the size of the redo log, the redo log is used to ensure that writes are fast and reliable and recover in the event of a crash. Until the total size of the MySQL 5.5 redo log is limited to 4GB (there can be 2 log files by default). This is improved in MySQL 5.6. If you set innodb_log_file_size to 512m (so that you have 1GB's redo log), you will have plenty of room to write. It is generally set to 256m 512m

Innodb_log_files_in_group = 3

# to improve performance, MySQL can write log files to multiple files in a circular manner. Default is 2, recommended setting is 3m

Innodb_max_dirty_pages_pct = 75

The number of Dirty_Page (dirty pages) in # Buffer_Pool directly affects the shutdown time of InnoDB. The parameter innodb_max_dirty_pages_pct can directly control the percentage of Dirty_Page in Buffer_Pool, and fortunately innodb_max_dirty_pages_pct can be changed dynamically. Therefore, reducing the innodb_max_dirty_pages_pct before shutting down the InnoDB and forcing the block Flush for a period of time can greatly shorten the MySQL shutdown time.

# you can default

Innodb_lock_wait_timeout = 50

# InnoDB has a built-in deadlock detection mechanism that can cause outstanding transactions to be rolled back. However, if you use MyISAM's lock tables statement or a third-party transaction engine with InnoDB, InnoDB does not recognize deadlocks. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer value that tells MySQL how long to wait before allowing other transactions to modify data that will eventually be rolled back by the transaction.

# when a transaction waits for a lock, roll back if it exceeds the time

# set waiting time as needed

Innodb_print_all_deadlocks = 1

# print deadlock information to the error log. Parameters only available since version 5.6

Innodb_file_per_table = 0

# set independent tablespaces. Default is 0, then shared tablespaces; if 1, independent tablespaces.

# it is recommended to open it, especially when the amount of data is large, the ibdata1 file will only increase, not decrease, and the insert update speed will be affected to a certain extent. In addition, if the table is deleted frequently, the shared tablespace will generate more fragments and cannot reclaim space from OS.

Innodb_buffer_pool_load_at_startup = 1

# OFF is disabled by default. If this parameter is enabled, MySQL loads local hot data into the InnoDB buffer pool when the MySQL service is started.

# the startup time will be extended when MySQL starts

Innodb_buffer_pool_dump_at_shutdown = 1

# OFF is disabled by default. If this parameter is enabled, when the MySQL service is stopped, InnoDB saves the hot data in the InnoDB buffer pool to the local hard disk.

# will extend the shutdown time when MySQL shuts down

[mysqldump]

Quick

Max_allowed_packet = 32

[mysqld_safe]

Log-error=/data/3306/mysql_oldboy.err

Pid-file=/data/3306/mysqld.pid

For the above about how to configure mysql files, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.