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

path

/etc/mysql/conf.d/my.cnf

Content [client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]character-set-server=utf8pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysql#log-error = /var/log/mysql/error.log#Connection validity time, default is 28800 (8 hours) wait_timeout=28800#indicates whether the table name is case-sensitive, 0-sensitive;1-insensitive lower_case_table_names=1#It is recommended to disable symbolic links to prevent various security risks symbolic-links=0#127.0.0.1 can bind IP addresses or only allow local access. By default, we only accept connections from hosts #bind-address = 127.0.0.1# MySQL reads some data into table_open_cache cache every time it opens a table. MySQL will read data from disk when it cannot find corresponding information in this cache. Default value 64table_open_cache = 128#A transaction, when there is no commit, the generated log is recorded in the Cache; when the transaction commits and needs to be committed, the log is persisted to disk. The default binlog_cache_size 32Kbinlog_cache_size = 1M #defines the size of memory tables that users can create. This value is used to calculate the maximum number of rows in the memory table. This variable supports dynamically changing max_heap_table_size = 8M #MySQL heap table buffer size. All unions are done within a single DML instruction, and most unions can be done even without temporary tables. tmp_table_size = 16M #MySQL reads buffer size. A request for a sequential scan of a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer.# If sequential scans of tables are requested very frequently and you think frequent scans are too slow, you can improve performance by increasing the value of this variable and the memory buffer size #read_buffer_size = 2M #MySQL's random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When sorting queries,# MySQL scans the buffer first to avoid disk searches and improve query speed. If you need to sort a large amount of data, you can increase this value appropriately. MySQL allocates this buffer space for each client connection, so try to set it appropriately to avoid excessive memory overhead read_rnd_buffer_size = 8M #Buffer size used by MySQL to perform sorting. If you want to speed up ORDER BY, first see if MySQL can use indexes instead of extra sort stages.# If not, you can try increasing the size of the sort_buffer_size variable sort_buffer_size = 8M #The size of the buffer that can be used by the joint query operation is the same as sort_buffer_size. The allocated memory corresponding to this parameter is also exclusive per connection. join_buffer_size = 8M #This value (default 8) indicates that the number of threads stored in the cache can be reused. If there is room in the cache when disconnecting, the client threads will be placed in the cache.#If the thread is requested again, the request will be read from the cache, if the cache is empty or new, the thread will be recreated, if there are many new threads,#Increasing this value improves system performance. You can see what this variable does by comparing the Connections and Threads_created state variables. (-> indicates the value to be adjusted)#The rules for setting according to physical memory are as follows: # 1G -> 8# 2G -> 16# 3G -> 32#greater than 3G -> 64thread_cache_size = 16# MySQL query buffer size (since 4.0.1, MySQL provides query buffer mechanism) Using query buffer, MySQL stores SELECT statements and query results in a buffer.#In the future, for the same SELECT statement (case-sensitive), the result will be read directly from the buffer. According to the MySQL user manual, query buffering can be used up to 238% efficiently.# By checking the status value 'Qcache_%', you can know whether the query_cache_size setting is reasonable: if the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffer often occurs,#If the value of Qcache_hits is also very large, it indicates that query buffer is used very frequently, and it is necessary to increase the buffer size at this time; If the value of Qcache_hits is not large, it indicates that your query repetition rate is very low.#In this case, using query buffering will affect efficiency, so you can consider not using query buffering. Additionally, adding SQL_NO_CACHE to the SELECT statement makes it clear that instead of using query buffering query_cache_size = 64M #specifying the buffer size for the index, increasing it gets better handled indexes (for all reads and multiple writes) as much as you can afford. If you make it too big,#system will start paging and really slow down. This parameter can be set to 384M or 512M for servers with around 4GB of memory. By checking the status values Key_read_requests and Key_reads,#can tell if the key_buffer_size setting is reasonable. The ratio key_reads/key_read_requests should be as low as possible,#at least 1:100, preferably 1:1000 (the above status values can be obtained using SHOW STATUS LIKE 'key_read %'). Note: If the parameter value is too large, the overall efficiency of the server will be reduced.#key_buffer_size = 4M#binlog over 30 days will be deleted expire_logs_days = 30max_connections=2000#slow query time exceeds 1 second. Slow query_log = 1long_query_time = 1slow_query_log_file = /var/log/mysql/mysql-slow.log#Limit the data of tables that Innodb can open. If there are many tables in the library, please add this. This value defaults to 300innodb_open_files = 500# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.# Here, the larger your settings, the less disk I/O you need to access the data in the table.# On a stand-alone database server, you can set this variable to 80% of the server's physical memory size #Do not set it too large, otherwise it may cause page hopping of the operating system due to physical memory competition.# Note that on 32-bit systems you may be limited to 2-3.5G user-level memory per process,#so don't set it too high.innodb_buffer_size = 64M #Use 5.7 to support both orderby and deduplication, and try to avoid this design # sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION# 0: If innodb_flush_log_at_trx_commit has a value of 0,log buffer is flushed to disk every second, committing transactions without doing anything (execution is performed by mysql's master thread)# The redo log buffer is written every second in the main thread to a redo log file (REDO LOG) on disk. The default log file is ib_logfile0,ib_logfile1#1: When set to the default value of 1, the log buffer is flushed to the log every time a transaction is committed.# 2: If set to 2, log is written every time a transaction is committed, but no flush operation is performed. Every second, the log file will be refreshed. Note that there is no guarantee that the disk will be flushed 100% every second, depending on the scheduling of the process.# Write data to the transaction log every time a transaction is committed, and the write here is only a write operation that invokes the file system, and the file system is cached, so this write does not guarantee that the data has been written to the physical disk #The default value of 1 is to ensure the complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for better performance, but in the event of a system crash, you will lose 1 second of data.# If set to 0, the last second of the transaction will be lost when the mysqld process crashes. Set to 2 so that the last second of data is lost only if the operating system crashes or loses power. InnoDB ignores this value when doing recovery.# Setting summary #to 1 is certainly the safest, but the performance page is the worst (relative to the other two parameters, but not unacceptable). If data consistency and integrity requirements are not high, it can be set to 2. If only performance is required, such as log servers with high concurrent writes, it can be set to 0 to obtain higher performance innodb_flush_log_at_trx_commit = 0#This parameter determines the memory size used by these log files, in M units. Larger buffers improve performance, but unexpected failures will result in data loss. MySQL developers recommend setting innodb_log_buffer_size = 2Mquery_cache_type=2#Maximum packet length sent and received by the server max_allowed_packet=64M# MySQL supports 4 transaction isolation levels, they are: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.# If not specified, MySQL defaults to REPEATABLE-READ, ORACLE defaults to READ-COMMITTEDtransaction_isolation = REPEATABLE-READ#Force InnoDB recovery, this option is usually only opened when there is an exception in the data file and forced recovery is needed #innodb_force_recovery = 1#Close default update time field explicit_defaults_for_timestamp=true#Set Beijing time zone #default-time_zone = '+8: 00'

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.