Mysql5.6 Startup memory Footprint Solution _mysql

The VPS memory is 512M, installs good nginx,php and so on to start up, the MySQL does not start to look the log only to see corresponds the PID to be ended, after tracking sees discovers is the memory is not enough to be killed;

Adjust the my.cnf parameters, reconfigure (the system default configuration is too high to directly occupy 400M of memory, small players can not afford to play it)

performance_schema_max_table_instances=200
table_definition_cache=200
table_open_cache=128

A description of the related MY.CNF configuration file is attached below

[Client] Port = 3306 socket =/tmp/mysql.sock [mysqld] Port = 3306 Socket =/tmp/mysql.sock Basedir =/usr/local/mysql da Tadir =/data/mysql Pid-file =/data/mysql/mysql.pid user = MySQL bind-address = 0.0.0.0 Server-id = 1 #表示是本机的序号为1, generally speaking is M Aster mean Skip-name-resolve # disable MySQL for DNS resolution of external connections, use this option to eliminate the time that MySQL makes DNS resolution. Note, however, that if you turn on this option, # then all remote host connection authorizations will use the IP address, otherwise MySQL will not be able to handle the connection request #skip-networking Back_log = number of connections MySQL can have. This works when the main MySQL thread gets a lot of connection requests in a very short time, # then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. # If you expect to have a lot of connections in a short time, you need to increase it.
That is, if MySQL's connection data reaches Max_connections, the new request will be on the stack, # to wait for a connection to release the resource, the number of that stack is back_log, and the connection resource will not be granted if the number of pending connections exceeds Back_log.
# 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 limits on the size of this queue (you can check your OS documentation to find the maximum value for this variable), and trying to set Back_log above your operating system will be ineffective. max_connections = 1000 # mysql maximum number of connections, if the server's concurrent connection request is large, it is recommended to raise this value to increase the number of parallel connections, of course, this is based on the machine can support the case, because if the number of connections, The more memory the MySQL will provide for each connection, the more RAM it will cost, so you can't blindly increase the set value if you want to adjust the value appropriately.
You can view the number of connections in the current state by using the ' conn% ' wildcard to decide on the size of the value. Max_connect_errors = 6000 # for the same host, the host will be blocked from connecting if there is an interrupt error connection that exceeds the number of the parameter values.
If the host is required to be lifted, execute: FLUSH host. Open_files_limit = 65535 # mysql Open file descriptor limit, default minimum 1024; When Open_files_limit is not configured, compare max_connections*5 and Ulimit-n values,
Which is the big one, # when the Open_file_limit is configured, compare the values of Open_files_limit and max_connections*5, which one is most used. Table_open_cache = 128 # MySQL Every time you open a table, you will read some data into the Table_open_cache cache, and when MySQL does not find the appropriate information in this cache, it will read on the disk. The default value of 64 # assumes that the system has 200 concurrent connections, you need to set this parameter to 200*n (N is the number of file descriptors required for each connection); # when the Table_open_cache is set to very large, if the system can not handle so many file descriptors, then there will be client failure, The packet size accepted by Max_allowed_packet = 4M # is not connected; 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 mysqld must return a large result row.
# The smaller default value of this variable is a precaution to capture the error packets between the client and the server and to ensure that there is no memory overflow caused by accidental use of large packets. Binlog_cache_size = 1M # a transaction, the resulting log is recorded in the cache when it is not submitted, and the log is persisted to disk when the transaction submission needs to be submitted. The default binlog_cache_size size 32K max_heap_table_size = 8M # Defines the size of the memory table (memory table) that users can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamically changing tmp_table_size = 16M # MySQL's heap (stacked) table buffer size.
All unions are completed within a DML instruction, and most unions can be completed without a temporary table. # Most temporary tables are memory based (HEAP) tables.
Temporary tables with large record lengths (the length of all columns) or tables containing BLOB columns are stored on the hard disk. # If an internal heap (heapProduct table size exceeds Tmp_table_size,mysql can automatically change the heap table in memory to a MyISAM table based on the hard disk as needed. You can also increase the size of the temporary table by setting the Tmp_table_size option. That is, if you raise this value, MySQL will also increase the size of the heap table, to improve the speed of the join query read_buffer_size = 2M # mysql read buffer size. A request to sequentially scan a table allocates a read buffer, which is allocated a memory buffer by MySQL.
The read_buffer_size variable controls the size of this buffer. # If the sequential scan request for a table is very frequent, and you think frequent scans are too slow, you can increase the value of the variable and the memory buffer size to improve its performance read_rnd_buffer_size = 8M # MySQL's random read buffer size. When rows are read in any order (for example, in sorted order), a random read buffer is assigned. When sorting queries, # MySQL first scans the buffer to avoid disk search, improves query speed, and if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will release this buffer space for each client connection, so try to set this value as appropriate to avoid excessive memory overhead sort_buffer_size = 8M # MySQL performs sort use of the buffer size.
If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sorting stage.
# 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 the Federated query operation can use, as in Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection Thread_cache_size = 8 # This value (default 8) indicates that the number of saved threads stored in the cache can be recycled, and if there is room in the cache when disconnected, the client's thread 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 is a new request, the thread will be recreated, and if there are many new threads, # Adding this value can improve system performance. You can see the effect of this variable by comparing the variables of connections and threads_created states. (–> represents the value to be adjusted) # The rules are set according to physical memory: # 1g-> 8 # 2g-> # 3g-> 32 # greater than 3g->E_size = 8M #MySQL的查询缓冲大小 (from 4.0.1, MySQL provides query buffering mechanism) using query buffering, MySQL stores SELECT statements and query results in a buffer, # future for the same SELECT statement (case sensitive), The result is read directly from the buffer.
According to the MySQL user manual, you can use query buffering up to 238% efficiency. # By checking the status value ' qcache_% ', you can know if the query_cache_size setting is reasonable: If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient, # if the Qcache_hits value is also very large, It means that the query buffer is used very frequently, at this time need to increase the buffer size, if the value of qcache_hits is not large, it indicates that your query is very low repetition rate, # in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to the SELECT statement makes it clear that no query buffering query_cache_limit = 2M #指定单个查询能够使用的缓冲区大小, the default 1M key_buffer_size = 4M # Specify the size of the buffer to use for the index, and increase the index that it can be better processed (for all read and multiple writes), to the extent that you can afford it. If you make it too big, # The system will start to change pages and it really slows down. This parameter can be set to 384M or 512M for servers with around 4GB. By checking the status values Key_read_requests and Key_reads, # you can see if the key_buffer_size settings are reasonable. The proportional 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 show status like ' key_read% '). Note: This parameter value setting is too large will be the overall efficiency of the server reduced ft_min_word_len = 4 # Word segmentation minimum Length, default 4 transaction_isolation = repeatable-read # MySQL supports 4 transaction isolation levels,
They were: # read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE. # if not specified, MySQL defaults to use the repeatable-read,oracle default is read-committed Log_bin =Mysql-bin Binlog_format = Mixed Expire_logs_days = #超过30天的binlog删除 Log_error =/data/mysql/mysql-error.log #错误日志路径 Slow _query_log = 1 Long_query_time = 1 #慢查询时间 more than 1 seconds for slow query slow_query_log_file =/data/mysql/mysql-slow.log Performance_schema = 0 Explicit_defaults_for_timestamp #lower_case_table_names = 1 #不区分大小写 skip-external-locking #MySQL选项以避免外部锁定. 
This option defaults to open default-storage-engine = InnoDB #默认存储引擎 innodb_file_per_table = 1 # InnoDB for standalone tablespace mode, each table in each database generates a data space # Independent table space benefits: # 1.
Each table has its own table space. # 2.
The data and indexes for each table exist in the table space of their own. # 3.
You can implement a single table to move in a different database. # 4. Space can be recycled (except for drop table operations, tablespaces cannot be retrieved) # Disadvantages: # single table increases too large, such as more than 100G # Conclusion: # Shared table spaces have few advantages in insert operations. Others do not perform well in their own table space. When enabling standalone tablespace, make reasonable adjustments: Innodb_open_files Innodb_open_files = 500 # Restricts the data of the table that InnoDB can open, if the table in the library is very much, please add this.
This value defaults to Innodb_buffer_pool_size = 64M # InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The larger you set here, the less disk I/O you need to access the data in the table.
# on a standalone database server, you can set this variable to the size of the server's physical memory 80% # do not set too large, otherwise, due to the competition of physical memory may cause the operating system to change page bumps.
# Note that each process on a 32-bit system 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 use background threading to process read and write I/O (input/output) requests on the data page, depending on your CPU kernel number, the default is 4 # Note: These two parameters do not support dynamic change, you need to add this parameter to the MY.CNF , after the modification of the MySQL service, allow the range of values from 1-64 innodb_thread_concurrency = 0 # By default set to 0, which means unlimited concurrency, the recommended setting is 0, better to play CPU multi-core processing capacity, improve the concurrency of the volume innodb_ purge_threads = 1 # InnoDB The purge operation is a class of operations that periodically recycle unwanted data.
In previous versions, the purge 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 on a separate thread and supports more concurrent numbers. The user can set the innodb_purge_threads configuration parameter to select whether the purge operation uses a single # lone thread, the parameter is set to 0 by default (no separate threads are used), and a separate purge thread is used when set to 1. Recommended for 1 innodb_flush_log_at_trx_commit = 2 # 0: If the value of Innodb_flush_log_at_trx_commit is 0,log, the log file will be brushed to disk every second.
Commits a transaction without doing anything (execution is performed by the master thread thread of MySQL). # The Redo log buffer is written to the disk's redo log file (REDO log) per second in the main thread.
The default log file, regardless of whether the transaction has been committed, is Ib_logfile0,ib_logfile1 # 1: When set to the default value of 1, every time the transaction is committed, log buffer brush is written to the log. # 2: If set to 2, each commit transaction will write a log, but does not perform the operation of the brush. Each second is periodically brushed to the log file.
Note that there is no guarantee that 100% will be brushed to disk per second, depending on the scheduling of the process. # writes data to the transaction log each time the transaction is committed, while writing here only invokes the file system write operation, 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 complete acid.
Of course, you can set this configuration to a value other than 1 in exchange for higher performance, but you will lose 1 seconds of data when the system crashes. # set to 0, when the mysqld process crashes, it loses the last 1 seconds.Works Set to 2, the last 1 seconds of data will be lost only when the operating system crashes or loses power.
InnoDB ignores this value when doing a restore. # The summary # set 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 data consistency and integrity requirements are not high, can be set to 2, if only the most performance, such as high concurrent write log server, set to 0来 for higher performance innodb_log_buffer_size = 2M # This parameter determines the amount of memory used by some log files, in M. A larger buffer can improve performance, but unexpected failures will lose data. MySQL developer recommends setting to 1-8m innodb_log_file_size = 32M # This parameter determines the size of the data log file, and larger settings can improve performance, but it also increases 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 fashion. Recommended set to 3 innodb_max_dirty_pages_pct = # InnoDB main thread flushes data in cache pool so that the proportion of dirty data is less than 90% innodb_lock_wait_timeout = 120 # InnoDB the number of timeout seconds that a transaction can wait for a lock before being rolled back. InnoDB automatically detects a transaction deadlock in its own locking table and rolls back the transaction. InnoDB notes 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 the MyISAM storage engine. Applies to increase efficiency when inserting 100-1000+ records at one time. The default value is 8M.
You can increase the amount of data by doubling it.
Myisam_sort_buffer_size = 8M # MyISAM Sets the size of the buffer to use when restoring the table, when sorting MyISAM index-allocated buffers in repair table or when creating index or ALTER TABLE with CREATE INDEX Myisam_max_sort_file_size = 10G # If the temporary file becomes more than the index, do not use the Quick Sort indexing method to create an index. Note: This parameter is given in bytes myisam_repair_threads = 1 # If the value is greater than 1, the MyISAM table index is created in parallel during repair by sorting (each index in its own line agenda) inteRactive_timeout = 28800 # Number of seconds the server waits for activity before shutting down an interactive connection. An interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect (). Default value: 28,800 seconds (8 hours) wait_timeout = 28800 # The number of seconds the server waits for activity before it closes the non-interactive connection. When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or the global interactive_timeout value, # Depending on the client type (by the Mysql_real_connect () connection option Client_ Interactive definition). Parameter default: 28,800 seconds (8 hours) # The maximum number of connections supported by the MySQL server is capped because each connection is built to consume memory, so we want the client to disconnect and free up memory after it has been connected to MySQL server to handle the appropriate operation. If your MySQL server has a large number of idle connections, they will not only consume memory, but if the connection has been accumulating and constantly open, # will eventually reach the MySQL server connection limit, which will report ' Too many connections ' error.
For the Wait_timeout value setting, it should be judged according to the operation of the system. # After the system runs for a period of time, you can view the current system's connection status through the show Processlist command, and if you find a connection process with a large number of sleep states, this parameter is set too large, # can be adjusted to the appropriate size. To set both Interactive_timeout and wait_timeout will not take effect.

[Mysqldump]
Quick
Max_allowed_packet = 16M #服务器发送和接受的最大包长度
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
Read_buffer = 4M
Write_buffer = 4M

The above mentioned is a small set to introduce the Mysql5.6 start memory occupancy solution, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!