MYSQL-MY-INNODB-HEAVY-4G.CNF configuration file annotations

mysql-As with Nginx and other features of multi-instance, it is simple to open a server on a number of different service ports (3306,3307) simultaneously run multiple MySQL service processes, these service processes through different sockets listening to different service ports to provide services. These MySQL multi-instance common set of MySQL installers use different my.conf configuration files, startup programs, and data files.

The following is a note of the master file and the single-instance configuration file

Cat my-innodb-heavy-4g.cnf #BEGIN CONFIG INFO #开始配置信息 #descr:4gb RAM, InnoDB only, ACID, few connections, heavy queries # Description: 4GB memory, InnoDB, ACID, several connections, heavy query #type:system #类型: System #end CONFIG INFO #结束配置信息 # # This is a MySQL example CONFIG file f or systems with 4GB of memory #这是4GB内存系统-a MySQL instance profile that mainly runs MySQL with only InnoDB tables and performs complex queries with several connections--one that is running mostly MySQL Usin g InnoDB only tables and performing complex #主要运行MySQL, use only InnoDB tables and perform complex operations # queries with few connections. #查询连接很少 # # MySQL programs look for option files in a set of #MySQL程序在一组中查找选项文件 # locations which depend on the deployment P Latform. #位置取决于部署平台 # can copy this option file to one of those #你可以将这个选项文件复制到其中之一 # locations. For information on these locations, see: #有关这些位置的信息, refer to: # http://dev.mysql.com/doc/mysql/en/option-files.html## in This file, you can use the all long options, the program supports. #在这个文件中, you can use all the long options supported by the program. # If you want to know which options a programs supports, run the program #如果您想知道程序支持哪些选项.E "--help" option. #用 the "--help" option. # # More detailed information on the individual options can also be# found in the manual. #有关各个选项的更多详细信息也可以在手册中找到. # # #下面的选项将被MySQL客户端应用程序读取. Please note that only the client application that is published by MySQL is guaranteed. If you want your MySQL client program to adhere to these values, you need to specify it as an option #MySQL客户端库初始化. [client] #客户端 #password = [Your_password]port = 3306 #客户端服务端口socket =/application/mysql-5.5.32/tmp/mysql.so  CK # * * * * application-specific options follow here ***## the MySQL server #server-end #[mysqld]# Generic configuration options #通用配置选项port = 3306 #Mysql服务运行时端口socket =/application/mysql-5.5.32/tmp/mysql.sock #sock文件是在Linux, UNIX environment Special Yes, a client connection in a Linux/unix environment can use UNIX directly without a TCP/IP network. The socket connects to MySQL #back_log是操作系统可以保持的连接数侦听队列 and processes them before the MySQL Connection Manager thread. If you have a very high connection speed and experience "connection is denied" error, you may need to increase this value. Check your OS documentation to get the maximum value for this parameter. An attempt was made to set Back_log above your operating system limit. will not work Back_log = #back_log参数的值, before MySQL temporarily stops responding to a new request, multiple requests within a short period of time can be present on the stack, and if the system has many connections in a short period of time, you need to increase the value of the parameter specifying the arrival tcp/ The size of the listening queue for the IP connection. Different operating systems have their own limits on the size of this queue. If you try to set Back_log to a limit that is higher than the operating system, it will be invalid with a default value of 50For Linux systems, it is recommended to set an integer value less than 512 to listen on #不要在TCP/IP port. This can be a security mechanism.     Enhanced if all processes need to be connected to the mysqld run. On the same host.     All interactions with mysqld must be done through UNIX. For sockets or named pipes, note that you do not enable named Pipes on Windows by using this option. (Through the "Enable-Named pipes" option) will invalidate the mysqld! #skip-networking #MySQL服务器的并发会话的最大数量. Allowed. One of the connections will be reserved for the user. Super privilege, allowing administrators to log in even if. The connection limit has reached its limit. max_connections = 100 # The maximum number of errors allowed per host. If this limit is reached, the host will be blocked from connecting to the MySQL server until "FLUSH HOSTS" is already running or the server has been restarted. Invalid enter password and other errors during connection phase. Increase this value. See "Aborted_connect" Status variables, global counters. Max_connect_errors = Ten #max_connect_errors设置每个主机的连接请求异常中断的最大次数, when this number of times is exceeded, the MySQL server will disallow host connection requests until the MySQL server restarts or through flush The hosts command clears the information about this host Table_open_cache = 2048 #table_open_cache设置表告诉缓存的数目.     At least one table cache is opened for each connection. Therefore, the table_cache is related to the size of the English max_connections setting. For example, for 200 parallel-running connections, the table should have at least 200xN of cache, where n is the maximum number of tables for a join of the query that the application can execute, and some additional file descriptors are reserved for temporary tables and files Max_allowed_packet = 16mbinlog_cache_size = 1M #binlog_cache_size在一个事务中binlog为了记录sql状态所持有的cache大小, if you often use large, multi-declarative transactions, you can increase this value to get greater performance, all engaged in The state of the service is buffered in the Binlog buffer and then committed once to Binlog, and if the transaction is larger than this value, the temporaryFile instead, this buffer is created at the first update state of each linked transaction Max_heap_table_size = 64M #独立的内存表所允许的最大容量read_buffer_size = 2M #read_buffer_size读查询操作所 The buffer size that can be used, like sort_buffer_size, the allocated memory for this parameter is also the exclusive read_rnd_buffer_size = 16M #read_rnd_buffer_size设置进行随机读的时候使用的缓冲区 for each link. This parameter is in contrast to the buffer set by Read_buffer_size, one that is used when sequential reads, and one that is randomly read. But both are set on the thread, and each thread can produce either of the two types of buffer. The default value is 256KB, the maximum value is 4gbsort_buffer_size = 8M #设置查询排序时所能使用的缓冲区大小, the system default size is 8MB Note: This parameter corresponds to the allocated memory for each connection exclusive, if there are 100 links, then the actual allocation of the total sort buffer large Small for 100*6=600MB, so for the server around 4GB, it is recommended to set it to 6mb~8mbjoin_buffer_size = 8M #联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, The allocated memory for this parameter is also exclusive to each connection Thread_cache_size = 8 #设置Thread The maximum number of connection threads that can be cached in the cache pool, which can be set 0~16384, which defaults to 8, which means that the number of threads saved in the cache can be re-used. If there is room in the cache when the connection is disconnected, the client's thread is placed in the cache, and if the thread is requested again, read from the cache and if the cache is empty or a new request, the thread will be recreated, and if there are many threads, increasing the value can improve system performance. By comparing the variables of the connections and threads_created states, you can see the effect of this variable. 1GB memory we configured for 8,2GB memory we configured for 16,3GB memory we configured 32,4GB or 4GB above we give this value to 64 or greater value Thread_concurrency = 8 #该参数取值为服务器逻辑CPU数量x2, in this case, The server has two physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4 x2 = 8. This is also a dual-core mainstream server configuration Query_cache_size = 64M #指定MySQL查询缓冲区的大小, can be observed through the MySQL console, if the value of Qcache_lowmem_prunes is very large,     Indicates that there are often insufficient buffers, and that if the value of qcache_hits is very large, the query buffer is used very frequently. In addition, if the value is smaller, it will affect the efficiency, then you can consider not the query buffer, for qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer. Query_cache_limit = 2M #只有小于此设置值的结果才会被缓存ft_min_word_len = 4 #如果果是英文默认值是比较合理的, but the majority of Chinese is 2 characters, which results in words less than 4 words can not be indexed, full-text indexing function It's just a fake. Default-storage-engine = MYISAM #如果在创建表的语句中没有指定, when a new table is created with the default table type Thread_stack = 192K #设置MySQL每个线程的堆栈大小, the default value is sufficient Large enough to meet normal operation. Can be set to a range of 128KB to 4GB, the default is 192kbtransaction_isolation = Repeatable-read #数据库隔离级别 read uncommitted= reads uncommitted content read Commi Tted= Read commit repeatable read= reread serializable= serializable tmp_table_size = 64M #设置内存临时表最大字号, if this value is exceeded, the temporary table is written to disk, and its range of 1kb refers to 4gblog-bin=mysql-binbinlog_format=mixed #表示开启binlog日志 Slow_query_log #记录慢查询, a slow query is a query that consumes more than the total time defined in "Long_query_time" If "Log_short_format" is not enabled, the indexed query is not used. Long_query_time = 2 #记录慢查询的时间, all queries that exceed that time will be considered slow. # * * * * Replication Related Settings Server-id = 1 #1到32The unique server identification number, if it is a master-slave or multi-instance ID number cannot be the same key_buffer_size = 32M #指定用于索引的缓冲区大小, increase it to get better index processing performance. For a server that has around 4GB, this parameter can be set to 256MGB or 384mbbulk_insert_buffer_size = 64M #如果经常性的需要使用批量插入的特殊语语句来插入数据, can adjust the parameters to 16mb~32mb properly, Recommendation 8M, setting 0 disables the optimization myisam_sort_buffer_size = 128M #当MySQL需要REPAIR, optimeize, ALTER TABLE statement rebuild index, and load DATA infile to an empty table. The buffer is allocated. To set the range from 48tytes to 4gbmyisam_max_sort_file_size = 10G #当重新建索引 (repair,alter,table, or Load,data,tnfile), MySQL is allowed to use the maximum value for temporary files. Myisam_repair_threads = 1 #如果一个表拥有超过一个索引, MyISAM can use more than one thread to fix myisam_recover by parallel ordering #自动检查和修复没有正确关闭的MyISAM表innodb_file _io_threads = 4 #InnoDB中的文件I/o thread, usually set to 4. If Windows can set a larger value to increase the disk I/oinnodb_additional_mem_pool_size = 16M #用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小, the more tables in the application,     The more memory you need to allocate here. For a relatively stable application, the size of this parameter is also relative. If InnoDB runs out of memory in the pool, InnoDB starts allocating memory from the operating system and writes a warning message to the MySQL error log. You should increase the size of this parameter appropriately when you find that there are already warning messages in the error log. Innodb_buffer_pool_size = 2G #InnoDB使用一个缓冲池来保存索引和原始数据, the larger the setting, the less disk I/O is required to access the data inside. It is strongly recommended that the buffer pool value of INNODB be configured to 50%-80% of physical memory, depending on the environment. InnodB_data_file_path = Ibdata1:10m:autoextend #设置配置一个可扩展大小的尺寸为10M的单独未见, named Ibdata1 does not give the location of the file, so the default is in the MySQL data directory, you can also set the number , autoextend represents self-increment innodb_write_io_threads = 8 #此处代表MySQL innodb support for I/O read-write threads, too much is useless, because the IO capability is limited and needs to be combined with a special model Innodb_read_io_ Threads = 8 #在MySQL5. Parameters used in version 1.x: Innodb_file_io_threads: The number of read/write IO requests on the data page processed by the background thread, and innodb_read_io_threads in mysql5.5.x And innodb_write_io_threads supersedes this version prior to the parameter, the sum of the values of the =2*CPU number of *CPU cores; If your system reads > writes, you can set the Innodb_read_io_threads value to a relatively large point; Innodb_force_recovery=1innodb_thread_concurrency = #innoDB内核里面允许线程数量, the best height depends on the application, hardware, and operating system read-through properties.     Too high a value can cause a thread to bump Innodb_flush_log_at_trx_commit = 1 #如果设置为1, InnoDB flushes the transaction to disk each time it commits (providing full acid behavior).     If you want to compromise safely and you are running small transactions, you can reduce the disk I/O for logs by 0 or both. A value of 0 means that only the log file is written to, and the log file flushes approximately every 1 seconds to the disk value 2 for the log to be written to the log at each commit, but the log file knowledge is refreshed about once per second to disk innodb_log_buffer_size = 8M #InnoDB缓冲日志数据所使用的 Buffer size, once it is full, innodb flushes it to disk. Innodb_log_file_size = 256M #一个日志组中每个日志文件的大小, you can set the federated size of the log to 25%-100% of the size of your buffer pool to avoid unnecessary buffer pool refresh overrides on the log file innodb_log_files_i   N_group = 3  #日志组中文件的总数, the usual value of 2-3 is enough innodb_max_dirty_pages_pct = #InnoDB缓冲池中允许的脏页面的最大百分比, if it is reached, InnoDB will start actively cleaning up so as not to consume all the clean pages, This is a soft limit and is not guaranteed to keep Innodb_lock_wait_timeout = # #InnoDB事务被回滚之前可以等待一个锁定的超时描述, InnoDB automatically detects the transaction deadlock in its own lock table and rolls back the transaction. InnoDB Use the Local tables statement to note that the lock table setting [Mysqldump]quickmax_allowed_packet = 16M #设定在网络传输中一次消息量的最大值, the maximum value is 1GB, must be set to a multiple of 1024, in bytes.     [mysql]no-auto-rehash# only allow UPDATEs and deletes the use keys. #只允许使用键值的updates和delete #safe-updates[myisamchk]key_buffer_size = 512M #指定用于索引的缓冲区大小, increase it to better index processing performance,     This parameter can be set to 256MB or 384mbsort_buffer_size = 512M #设置查询排序时所能使用的缓冲区大小 for servers about 4GB in memory. Note: The allocated memory for this parameter is exclusive for each connection, and if there are 100 links, the actual allocated total sort buffer size is 100X6=600MB, so it is recommended to set 6mb~8mbread_buffer = 8mwrite_ for servers around 4GB Buffer = 8m[mysqlhotcopy]interactive-timeout[mysqld_safe] #Increase The amount of open files allowed per process. Warning:make#sure you has the set the global system limit high enough! The high value#is required for a large number of opened Tablesopen-files-limit = 8192 #增加每The number of files allowed to open in the secondary processing, make sure that you have set the global system limit high enough. High values are required for a large number of open tables

"MY.CNF"-configuration file Annotations

[Client] # # # #客户端 port = 3306 # # # #mysql客户端连接时的默认端口号 socket =/application/mysql-5.5.32/ Tmp/mysql.sock [mysqld] port = 3306 # # # #mysql服务器监听的默认端口 socket =/application/mysql-5.5.32/tmp/mysql.so CK back_log = # # # #是指保持在操作系统监听队列的连接数量, that is, the number of connections before the MySQL Connection Manager thread processes them max_connections = # # # # #MySQL允许的并发 Maximum number of sessions max_connect_errors = # # # # #每个主机允许的最大错误数量 Table_open_cache = 2048 # # # #所有线程打开表的数字, file descriptor size Max_allowed_pac ket = 16M # # # #服务器一次能处理的最大的查询包的值, is also the largest server program can handle the query binlog_cache_size = 1M # # # #在一个事务中, the binary log can handle the maximum number of SQL statement cache, if you often use complex Statement transactions, you can increase this value for better performance, and all statements in the transaction are cached in Binlog and written to the binary log immediately after a one-time commit.  If the transaction is larger than this value, the temporary file on the disk is replaced with a buffer that is allocated on each link when the transaction first changes state.  Max_heap_table_size = 64M # # # #独立的内存表所允许的最大容量 read_buffer_size = 2M # # #MySql读入缓冲区大小.  Read_rnd_buffer_size = 16M # # # #是MySql的随机读缓冲区大小. Sort_buffer_size = 8M # The sort buffer is used to sort the order by as well as the group by queue join_buffer_size = 8M # # # #此缓冲被使用来优化全联合 (Full JO InsUnion without an index).  Thread_cache_size = 8 # # # #我们在cache中保留多少线程用于重用 thread_concurrency = 8 # # #此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量. Query_cache_size = 64M # # # #查询缓冲的大小.  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_limit = 2M # # # #只有小于此设定值的结果才会被缓冲 Ft_min_word_len = 4 # # # # # # # # # # # # # # # Default-storage-engine = MYISAM # # # #当创建新表时作为默认使用的表类型 thread_stack = 192K # # # #线程使用的堆大小. The memory for this capacity is reserved for each connection Transaction_isolation = Repeatable-read tmp_table_size = 64M # # # # #内部 (in-memory) the maximum size of the temporary table log-  Bin=mysql-bin # # # #打开二进制日志功能.                         binlog_format=mixed # # # #设定记录二进制日志的格式, there are three formats, based on the statement statement, Row row-based, mixed mode mixed slow_query_log                          # # # #记录慢查询, long_query_time = 2 # # # #所有的超过这个参数时间的请求将被作为慢查询 Server-id = 1 # # # #唯一的服务标识号, its value between 1 to 2^32-1, this value on the primary server and from the server is required to set.   His default parameter is 1, if the host does not need to be set, but if this option is omitted, MySQL will not take effect as master. Key_buffer_size = 32M # ## #关键词缓冲的大小, commonly used to buffer the index block of a MyISAM table. Bulk_insert_buffer_size = 64M # # # #MyISAM Use a special tree-like cache to make a burst insert, (these inserts are, insert ...) SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) are faster.   This variable limits the number of bytes in the buffer tree per process. Myisam_sort_buffer_size = 128M # # # # #当需要对于执行REPAIR, OPTIMIZE, ALTER statement when the index is rebuilt, MySQL allocates this cache, and load DATA infile loads into a new table,   It will be carefully allocated according to the maximum configuration of each thread.  Myisam_max_sort_file_size = 10G # # # #当重新建索引 (repair,alter,table, or load,data,tnfile), MySQL is allowed to use the maximum value of the temporary file.  Myisam_repair_threads = 1 # # # #如果一个表拥有超过一个索引, MyISAM can use more than one thread to fix them by using parallel sorting.  Myisam_recover # # # #自动检查和修复没有适当关闭的 MyISAM table. Innodb_additional_mem_pool_size = 16M # # # #附加的内存池被InnoDB用来保存 Metadata Info innodb_buffer_pool_size = 2G # # # #InnoD  b uses a buffer pool to hold the index and raw data, Innodb_data_file_path = ibdata1:10m:autoextend # # # #InnoDB saves the data in one or more data files as a tablespace.  Innodb_write_io_threads = 8 Innodb_read_io_threads = 8 # # # #用来同步IO操作的IO线程的数量. innodb_thread_concurrency = # # # #使用InnoDB引擎, kernelThe number of threads allowed, the best value depends on the application, the hardware, and the operating system scheduler.  A value that is too high will certainly cause thread jitter.            Innodb_flush_log_at_trx_commit = 1 # # # #如果设置为1, InnoDB flushes (fsync) the transaction log to disk after each commit, Innodb_log_buffer_size = 8M  # # # #用来缓冲日志数据的缓冲区的大小. Innodb_log_file_size = 256M # # # #在日志组中每个日志文件的大小, innodb_log_files_in_group = 3 # # # #在日志组中文件的总量, usually 2-3 is enough  The innodb_max_dirty_pages_pct = # # # #在InnoDB缓冲池中最大允许的脏页面的比例.  Innodb_lock_wait_timeout = # # # # #在被回滚前, how long a InnoDB transaction should wait for a lock to be approved. [Mysqldump] Quick max_allowed_packet = 16M [MySQL] no-auto-rehash [myisamchk] key_buffer_size = 512M Sort_buffer_ Size = 512M Read_buffer = 8M Write_buffer = 8M [Mysqlhotcopy] interactive-timeout [Mysqld_safe] Open-files-limit = 8  192 # # #每个程序允许打开文件的数量

MYSQL-MY-INNODB-HEAVY-4G.CNF configuration file annotations