MySQL Configuration instructions

Description of the Oschina MySQL configuration file for sweet potato Open Source:

[Client]password        = port            = 3306socket   &NBSP ;      =/tmp/mysql.sock# limit the size of packets received by MySQL server (this is the client incoming packet) Max_allowed_packet = 48m # here Follows entries for some specific programs# the MySQL Server[mysqld]datadir=/data/mysql#tmpdir=/dev/shm/mysqlevent_ Scheduler=on   #开启定时器default-storage-engine=innodb #默认数据库引擎 #default-character-set=utf8character-set-server = UTF8 #默认字符集port            = 3306max_allowed_packet = 16m  #从一个连接上次SQL执行后算起, If the current idle (sleep) exceeds that time (500 seconds), #则会被强制断开 (more useful for some load-heavier MySQL, reclaim the sleep connection thread to free up memory). wait_timeout=    #和wait_timeout配合使用, the number of seconds the server waits for action on an interactive connection before closing the connection interactive_timeout= 500# Refers to the time-out of the handshake during the connection (the TCP protocol will have 3 handshake interactions, #成功后客户端会阻塞等待服务消息, #服务端这个时候会创建一个线程 (or take a thread from the thread pool) to process the request #主要验证部分包括host和用户名密码验证. #host验证用grant命令授权用户的时候是有指定host的. #用户名密码认证则是服务端先生成一个随机数发送给客户端, #客户端用该随机数和密码进行多次sha1加密后发送给服务端验证. #如果通过, the entire connection handshake process is complete. ) Connect_timeout = 20 socket   &nbsp      =/tmp/mysql.sock# When a client requests a connection over Max_connections, the #会将超过请求放入堆栈中 (Back_log) waits for the resource to be released, #如果等待的请求超过了堆栈的大小, The connection resource will not be granted. Back_log = 512 max_connections = 2048 #mysql最大连接数 #mysql Security-related counters, it is responsible for preventing excessive attempts to fail clients to prevent brute-force password violations. #max_connect_errors的值与性能并无太大关系. Max_connect_errors = 128  #mysql用户能够打开多少个文件描述符, #注意: Open_files_limit size and max_connections*5 need to be compared, # That's the big one. Set Open_files_limit with that value. # "Compare maximum in Open_files_limit,max_connections*5 and 10+max_connections+table_cache_size*2" # (/etc/security/limits.conf View system maximum number of files) Open_files_limit = 8192  #指定用于索引的缓冲区大小, #通过检查状态值Key_read_requests和Key_reads, you know Key_buffer_size setting is reasonable. #比例key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better # (the above status values can be obtained using the show status like ' key_read% ')  key_buffer _size=1024m#mysql performs a sort using the buffer size (not larger the better, set according to business application)  sort_buffer_size = 32m#mysql reads the buffer size. Requests for sequential scanning of tables will be assigned a read-in buffer read_buffer_size = 32m  #在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度 (the default setting is 128K).   join_buffer_size = 32m  #MySql的随机读缓冲区大小. When rows are read in any order (for example, in sort order), a random read cache is assignedZone read_rnd_buffer_size = 8m  #为一次插入多条新记录的INSERT命令分配的缓存区长度 (the default setting is 8M).  bulk_insert_buffer_size = 32m # the size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).  # If the file size is larger than this value, the index will be created by the key buffer (slower)  myisam_max_sort_file_size = 100g# This buffer when MySQL needs to be in REPAIR, OPTIMIZE, alter  # and LOAD DATA INFILE is allocated when rebuilding an index in an empty table.  # this is assigned in each thread. So be careful when setting large values. myisam_sort_buffer_size=64m# can re-use the number of threads saved in the cache (important optimization metrics?). #当断开连接时如果缓存中还有空间, #那么客户端的线程将被放到缓存中, #如果线程重新被请求, then the request will be read from the cache, #如果缓存中是空的或者是新的请求, #那么这个线程将被重新创建, if there are many new threads, # Increasing this value can improve system performance. Thread_cache_size = 64#thread_concurrency = The heap size used by the 16# thread. The memory for this capacity is reserved on each connection.  # MySQL itself often does not need more than 64K of memory  thread_stack = 512k# This setting is used to protect query buffering, preventing a large result set from overwriting all other query results.  query_cache_limit = 4m# query buffering is often used to buffer the results of a SELECT and no longer executes the direct return result at the next same query.  query_cache_size = 128mquery_cache_min_res_unit= 2k# Specifies the size of the table cache. #每当MySQL访问一个表时, if there is room in the table buffer, #该表就被打开并放入其中 so that the table content can be accessed more quickly. Table_cache = 2048# The maximum length of the temporary heap data table tmp_table_size = 256mmax_heap_table_size = 256mmax_tmp_tables = 512 bulk_insert_buffer_size = 16m# Disable DNS resolution (you must use an IP connection before setting this parameter to grant root%127.0.0.1 's local IP rights) skip-name-resolve# Slow query log slow_query_log# record slow query over time 100ms of SQL (why is this high?) ) Long_query_time = 0.1innodb_data_home_dir =/data/mysqlinnodb_data_file_path = Ibdata1:10m:autoextendinnodb_log_ Group_home_dir =/data/mysql# Set stand-alone tablespace innodb_file_per_table = 1innodb_open_files = 2048#memlock# open multiple memory buffer pools, Hash the data that needs to be buffered into a different buffer pool so that it can read and write parallel memory. #在非常高的 I/O load is turned on, of course innodb_buffer_pool_size corresponds. innodb_buffer_pool_instances=4# represents the buffer pool byte size, InnoDB the memory area of the cache table and index data. The default value is 128m# reasonable setting, not the larger the better (specific test for CPU impact) Innodb_buffer_pool_size = 8g# In addition to caching table data and indexes, you can allocate caching for other internal items required by the Operation Innodb_additional_mem _pool_size=64m# the size of the data log file, in M, the larger setting can improve performance, #但也会增加恢复故障数据库所需的时间  innodb_log_file_size = 64minnodb_lock_wait_ Timeout = 50# If this parameter is set to 1, the log will be written to disk after each commit transaction. #为提供性能, can be set to 0 or 2, but bear the risk of losing data in the event of a failure. #设置为0表示事务日志写入日志文件, and the log files are flushed to disk once per second. #设置为2表示事务日志将在提交时写入日志, but the log files are refreshed to disk once at a time.  innodb_flush_log_at_trx_commit = 0### Small swap/io# parameter controls InnoDB data file and redo log open, brush write mode Innodb_flush_method = O_dIrectinnodb_write_io_threads = 8innodb_read_io_threads = 8innodb_thread_concurrency = 16innodb_log_files_in_group = 3# Write disk innodb_max_dirty_pages_pct = 90[mysqldump]quickmax_allowed_packet = 48m[mysql]no-auto-rehash# Remove when dirty page data exceeds The next comment character if you is not a familiar with Sql#safe-updatesdefault-character-set=utf8[isamchk]key_buffer = 12 8msort_buffer_size = 128mread_buffer = 2mwrite_buffer = 2m[myisamchk]key_buffer = 128msort_buffer_size = 128Mread_buffer  = 2mwrite_buffer = 2M 

MySQL Configuration instructions