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

[client]

Port = 3306

Socket = / tmp/mysql.sock

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock

Basedir = / usr/local/mysql

Datadir = / data/mysql

Pid-file = / data/mysql/mysql.pid

User = mysql

Bind-address = 0.0.0.0

Server-id = 1 # indicates that the serial number of the machine is 1, which generally means master

Skip-name-resolve

# prohibit MySQL from parsing DNS for external connections. Use this option to eliminate the time for MySQL to parse DNS. But it is important to note that if this option is turned on

# then all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to process connection requests properly

# skip-networking

Back_log = 600,

# the number of connections MySQL can have. This works when the main MySQL thread gets a lot of connection requests in a very short period of time

# then the main thread takes some time (albeit short) to check the connection and start a new thread. The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests.

# if you expect to have many connections in a short period of time, you need to add it. That is, if the connection data of MySQL reaches max_connections, the new request will be stored in the stack

# to wait for a connection to release resources, the number of the stack is back_log, if the number of waiting connections exceeds back_log, the connection resources will not be granted.

In addition, this value (back_log) is limited to the size of your operating system's listening queue for incoming TCP/IP connections.

# your operating system has its own limit on this queue size (you can check your OS document to find the maximum value of this variable), trying to set the limit that back_log is higher than your operating system will not be valid.

Max_connections = 1000

The maximum number of connections of # MySQL. If the number of concurrent connection requests of the server is relatively large, it is recommended to increase the number of parallel connections. Of course, this is based on the case that the machine can support it, because if the number of connections is more, because MySQL will provide a connection buffer for each connection, the more memory will be spent. Therefore, you should adjust this value appropriately, not blindly increase the setting value. You can view the number of connections in the current state through the 'conn%' wildcard to determine the size of the value.

Max_connect_errors = 6000

# for the same host, if there is an incorrect connection that exceeds the number of values of this parameter, the host will be disabled from connecting. To unblock the host, execute: FLUSH HOST.

Open_files_limit = 65535

The file descriptor opened by # MySQL is limited to a minimum of 1024 by default. When open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, which is the most useful one

# when open_file_limit is configured, compare the values of open_files_limit and max_connections*5, which is the most useful.

Table_open_cache = 128,

# MySQL each time a table is opened, some data is read into the table_open_cache cache. When the MySQL cannot find the corresponding information in this cache, it will read it on disk. The default is 64

# assuming that the system has 200 concurrent connections, set this parameter to 200 million N (N is the number of file descriptors required for each connection)

# when table_open_cache is set to very large, if the system cannot handle so many file descriptors, then the client will fail and the connection will not be made.

Max_allowed_packet = 4m

# accepted packet size; it is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MySQLd allocates more memory only if you issue a long query or if MySQLd must return a large result row.

The smaller default value for this variable is a precaution to capture error packets between the client and server and to ensure that memory spills are not caused by accidental use of large packets.

Binlog_cache_size = 1m

# A transaction, when it is not committed, the log generated is recorded in Cache; when the transaction commit needs to be committed, the log is persisted to disk. Default binlog_cache_size size 32K

Max_heap_table_size = 8m

# defines the size of the memory table (memory table) that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change

Tmp_table_size = 16m

# MySQL's heap (stacking) table buffer size. All joins are done within one DML instruction, and most unions can be done without even temporary tables.

# most temporary tables are HEAP tables. Temporary tables with large record lengths (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk.

# if the size of an internal heap (stacking) table exceeds tmp_table_size,MySQL, you can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. In other words, if you increase this value, MySQL will also increase the size of the heap table, which can improve the speed of join query.

Read_buffer_size = 2m

# 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

Read_rnd_buffer_size = 8m

The random read buffer size of # MySQL. When rows are read in any order (for example, in sort order), a random read cache is allocated. When making a sort 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 = 8m

# the buffer size used by MySQL to perform sorting. If you want to increase the speed of ORDER BY, first see if you can let MySQL use indexes instead of additional sorting phases.

# if not, you can try to increase the size of the sort_buffer_size variable

Join_buffer_size = 8m

# the size of the buffer that can be used for federated query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive per connection.

Thread_cache_size = 8

# this value (default 8) indicates that the number of threads saved in the cache can be reused. If there is still 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. (- > indicates the value to be adjusted)

# the rules for setting physical memory are as follows:

# 1G-> 8

# 2G-> 16

# 3G-> 32

# greater than 3G-> 64

Query_cache_size = 8m

# query buffer size of MySQL (starting from 4.0.1, MySQL provides a query buffer mechanism) use query buffering, and 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, the use of query buffering can be up to 238% efficient.

# you can know whether the query_cache_size setting is reasonable by checking the status value 'Qcache_%',: if the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient.

# if the value of Qcache_hits is also very large, it means that the query buffer is used very frequently, so you need to increase the buffer size; if the value of Qcache_hits is not large, it means that your query repetition rate is very low

# in this case, the use of query buffering will affect efficiency, so you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used

Query_cache_limit = 2m

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

Key_buffer_size = 4m

# specify the size of the buffer used for the index and increase it to get better-handled indexes (for all reads and multiple overrides) so much that you can afford it. If you make it too big,

# the system will start to change pages and really slow down. For servers with about 4GB memory, this parameter can be set to 384m or 512m. By checking the status values Key_read_requests and Key_reads

# you can know whether the key_buffer_size setting is reasonable. The proportion of key_reads/key_read_requests should be as low as possible

# it is better to have at least 1 SHOW STATUS LIKE 100 and 1 1000 (the above status values can be obtained by using the status value). Note: excessive setting of this parameter value will reduce the overall efficiency of the server.

Ft_min_word_len = 4

Minimum length of # participle vocabulary. Default is 4.

Transaction_isolation = REPEATABLE-READ

# MySQL supports four transaction isolation levels, which are:

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

# if not specified, MySQL defaults to REPEATABLE-READ,ORACLE and defaults to READ-COMMITTED

Log_bin = mysql-bin

Binlog_format = mixed

Expire_logs_days = 30 # binlog deletion for more than 30 days

Log_error = / data/mysql/mysql-error.log # error log path

Slow_query_log = 1

Long_query_time = 1 # slow query if the time is longer than 1 second

Slow_query_log_file = / data/mysql/mysql-slow.log

Performance_schema = 0

Explicit_defaults_for_timestamp

# lower_case_table_names = 1 # case insensitive

Skip-external-locking # MySQL option to avoid external locking. This option is enabled by default

Default-storage-engine = InnoDB # default storage engine

Innodb_file_per_table = 1

# InnoDB is an independent tablespace schema, and each table in each database generates a data space.

# advantages of independent tablespaces:

# 1. Each table has its own independent tablespace.

# 2. The data and indexes of each table will be stored in its own tablespace.

# 3. It is possible to move a single table in different databases.

# 4. Space can be reclaimed (table emptiness cannot be recycled by itself except for drop table operation)

# disadvantages:

# excessive increase in a single table, such as more than 100G

# conclusion:

# shared tablespaces have few advantages in Insert operations. Nothing else performs as well as independent tablespaces. When enabling independent tablespaces, adjust reasonably: innodb_open_files

Innodb_open_files = 500,

# limit the data of tables that Innodb can open. If there are a lot of tables in the library, please add this. This value defaults to 300

Innodb_buffer_pool_size = 64m

# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.

The larger the setting here, the less disk Imax 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

# do not set too large, otherwise, the page change of the operating system may be bumpy due to the competition of physical memory.

# Note that on a 32-bit system, each process may be limited to 2-3.5G user-level memory limits.

# so don't set it too high.

Innodb_write_io_threads = 4

Innodb_read_io_threads = 4

# innodb uses background threads to handle read and write I / O (input / output) requests on data pages, which can be changed according to your CPU core count. The default is 4.

# Note: these two parameters do not support dynamic change. You need to add this parameter to the my.cnf, and restart the MySQL service after modification. The allowed values range from 1 to 64.

Innodb_thread_concurrency = 0

# the default setting is 0, which means there is no limit on the number of concurrency. It is recommended to set it to 0 to better exert the multi-core processing capacity of CPU and improve the concurrency.

Innodb_purge_threads = 1

The purge operation in # InnoDB is a type of operation that periodically reclaims useless data. In previous versions, the cleanup operation was part of the main thread, which meant that it might block other database operations at run time.

# starting with the MySQL5.5.X version, the operation runs in a separate thread and supports more concurrency. Users can choose whether to use single for cleanup operation by setting innodb_purge_threads configuration parameters.

# single thread. By default, the parameter is set to 0 (no separate thread is used). When set to 1, a separate purge thread is used. Recommended is 1

Innodb_flush_log_at_trx_commit = 2

# 0: if the value of innodb_flush_log_at_trx_commit is 0MagneLog buffer, the log file will be written to disk every second, and no action will be done when the transaction is committed (the execution is performed by the master thread thread of mysql.

Every second in the main thread, the redo log buffer is written to the redo log file (REDO LOG) on disk. Regardless of whether the transaction has been committed or not) the default log file is ib_logfile0,ib_logfile1

# 1: when set to the default value of 1, log buffer is written to the log every time the transaction is committed.

# 2: if set to 2, each commit transaction will be logged, but the brush operation will not be performed. The log file is brushed at the same time every second. It is important to note that there is no guarantee that 100% will be flushed to disk every second, depending on the scheduling of the process.

# data is written to the transaction log every time a transaction is committed, and the write here only invokes the write operation of the file system, which 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 a complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but you will lose 1 second of data in the event of a system crash.

# if set to 0, the last second transaction will be lost when the mysqld process crashes. Set to 2, the last second of data will be lost only in the event of an operating system crash or power outage. InnoDB ignores this value when doing a restore.

# Summary

Setting # to 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If the requirements for data consistency and integrity are not high, you can set it to 2. If you only want the most performance, such as a log server with high concurrent writes, set it to 0 to get higher performance.

Innodb_log_buffer_size = 2m

# this parameter determines the amount of memory used by some log files, in M units. Larger buffers can improve performance, but unexpected failures will result in data loss. MySQL developers suggest setting it to between 1m and 8m.

Innodb_log_file_size = 32m

# this parameter determines the size of the data log file. A larger setting can improve performance, but it will also increase the time required to recover the failed database

Innodb_log_files_in_group = 3

# to improve performance, MySQL can write log files to multiple files in a circular manner. Recommended setting to 3

Innodb_max_dirty_pages_pct = 90

# innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%

Innodb_lock_wait_timeout = 120

# the number of timeout seconds that an InnoDB transaction can wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own locking table. InnoDB notices the lock setting with the LOCK TABLES statement. The default value is 50 seconds

Bulk_insert_buffer_size = 8m

# bulk insert cache size, this parameter is for MyISAM storage engine. It is suitable for improving efficiency when inserting 100-1000 + records at one time. The default value is 8m. It can be doubled according to the size of the amount of data.

Myisam_sort_buffer_size = 8m

# MyISAM sets the size of the buffer used when recovering the table, when sorting the buffer allocated by the MyISAM index during REPAIR TABLE or creating indexes with CREATE INDEX or ALTER TABLE

Myisam_max_sort_file_size = 10G

# if the temporary file becomes larger than the index, do not use the quick sort index method to create an index. Note: this parameter is given in bytes

Myisam_repair_threads = 1

# if the value is greater than 1, create MyISAM indexes in parallel during Repair by sorting (each index is in its own thread)

Interactive_timeout = 28800

# the number of seconds the server waits for activity before closing the interactive connection. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect (). Default: 28800 seconds (8 hours)

Wait_timeout = 28800

# the number of seconds the server waits for activity before closing a non-interactive connection. Initializes the session wait_ timeout value based on the global wait_ timeout value or the global interactive_ timeout value when the thread starts

# depends on the client type (defined by the connection option CLIENT_INTERACTIVE of mysql_real_connect ()). Default value of parameter: 28800 seconds (8 hours)

# there is an upper limit on the maximum number of connections supported by the MySQL server, because the establishment of each connection consumes memory, so we hope that after the client connects to the MySQL Server to handle the corresponding operation

# should disconnect and release the occupied memory. If your MySQL Server has a large number of idle connections, they will not only consume memory in vain, but also keep opening if the connections are accumulating.

# will eventually reach the maximum number of connections in MySQL Server, which will report an error in 'too many connections'. The setting of the value of wait_timeout should be judged according to the operation of the system.

# after the system has been running for a period of time, you can check the connection status of the current system through the show processlist command. If you find that there are a large number of connection processes in sleep status, the parameter setting is too large.

# you can make appropriate adjustments. Interactive_timeout and wait_timeout must be set at the same time to take effect.

[mysqldump]

Quick

Max_allowed_packet = 16m # maximum packet length sent and accepted by the server

[myisamchk]

Key_buffer_size = 8m

Sort_buffer_size = 8m

Read_buffer = 4m

Write_buffer = 4m

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.