1, Optimization direction

1. Five resources + kernel at host level
2. At the service level, find the core function of the service in the architecture → core optimization
3. Code, business level → assistance / assistance direction
4. Docking (tomcat connector nginx → php → fpm module fastCGI 9000)

2, Hardware optimization

There are several methods to optimize server hardware:

• Configure larger memory
• Configure the high-speed disk system to reduce the waiting time for disk reading and improve the response speed
• Distribute disk I/O reasonably and distribute disk I/O on multiple devices to reduce resource competition and improve parallel operation ability
• Configure multiprocessors. MySQL is a multithreaded database. Multiprocessors can execute multiple threads at the same time

3, Software configuration

1. For network configuration, modify / etc / sysctl Conf file

Increase the number of queues supported by tcp

net.ipv4.tcp_max_syn_backlog=65535

net.ipv4.tcp_fin_timeout = 30
#TIME_WAIT timeout, the default is 60s
net.ipv4.tcp_tw_reuse = 1
#1 indicates that multiplexing is enabled and time is allowed_ Wait socket is used again for new TCP connection. 0 means close
net.ipv4.tcp_tw_recycle = 1
#1 means time is on_ Wait socket is quickly recycled. 0 means it is closed
net.ipv4.tcp_max_tw_buckets = 4096
#System hold time_ The maximum number of wait sockets. If this number is exceeded, the system will randomly clear some TIME_WAIT and print warning message
net.ipv4.tcp_max_syn_backlog = 4096
#The maximum length of the SYN queue. Increasing the queue length can accommodate more waiting connections

2. Some optimization of mysql itself. mysql configuration file / etc / my cnf

Default value of public parameter:

max_connections = 151 #At the same time, the maximum number of connections is recommended to be about 80% of the upper limit 
sort_buffer_size = 2M #When querying and sorting, the buffer size only works for order by and group by. This value can be increased to 16M 
query_cache_limit = 1M #Query cache limit: only query results below 1M will be cached to avoid overwriting the cache pool due to large result data 
query_cache_size = 16M #View the buffer size, which is used to cache the SELECT query results. The next time there is the same SELECT query, the results will be returned directly from the cache pool. You can multiply this value appropriately 
open_files_limit = 1024 #Limit the number of open files, if show global status like 'open_ The value of 'files' is equal to or greater than open_ files_ When the limit value is, the program will be unable to connect to the database or get stuck

Note: if there are configuration files in multiple locations, the content of the later configuration will overwrite the previous content, the latter will take effect, and the former will become invalid.

3. innodb scheme

Common configuration parameters,

innodb_buffer_pool_size  ,Very important parameters for configuration innodb The size of the buffer pool if only innodb Table, the recommended configuration amount is 60% of the total memory-70%
innodb_buffer_pool_instances 5.5 Introduced in later versions, the number of buffer pools can be controlled. Increasing the number can increase concurrency. The default value of this parameter is 1. It is recommended to set 4 or 8
innodb_log_buffer_size  innodb log The buffer size is usually not too large because the log will be refreshed every second da. 
innodb_flush_log_at_trx_commit     The key parameter determines how long the database will refresh the changes to disk. The parameter has three values, right innodb of io The efficiency is greatly affected. The default value is 1. You can take 0 and 1,2 The three values are generally recommended to be 2, but if the data security is high, the default value of 1 is used
innodb_read_io_threads
innodb_write_io_threads  The above two parameters determine innodb Read and write io Number of processes, 4 by default
innodb_file_per_table  Key parameters, control innodb Each table uses an independent table space. The default is off，That is, all tables will be established in the shared tablespace. It is recommended to change to on ，Using separate tablespaces,
innodb _stats_on_metadata  decided mysql When will it refresh innodb Table statistics, in show table The table statistics information will be refreshed when. It is recommended to set it to off So it won't show table Refresh table statistics.

4. MyISAM parameter configuration

The following settings are only for DB dominated by MyISAM engine. If the DB is dominated by InnoDB engine, skip this section.

4.1. Set the index cache size

The index cache size is generally set to 30% - 40% of the physical memory.

key_buffer_size = 2048M

4.2. Set the read buffer size

The read buffer size defaults to 128K. If the sequential scan request for the table is very frequent and you think the frequent scan is too slow, you can improve its performance by increasing the value of the variable and the memory buffer size. Generally, 2M is enough.

read_buffer_size = 2M

4.3. Set query cache function

(1) In order to speed up the execution performance of query statements, a caching technology called Query Cache has been provided since the early version of MySQL.
How Query Cache works: when a client connects (session) when the SQL query is performed and the returned information is obtained, the MySQL database not only returns the query results to the client, but also caches the results of the SQL query statement in a specific memory area, so that when all customers including the client execute the same query request again, MySQL can directly return the results from the cache.
2) Next, configure query_ cache_ If the type is set to 1, the Query Cache function is enabled, and the buffer size of Query Cache is set to 32M.

query_cache_type = 1``query_cache_size = 32M

4, Architecture optimization

With the increasing business volume, the performance of a single database server can no longer meet the business needs. It's time to consider adding machines and cluster ~ ~ ~. The main idea is to decompose the load of a single database, break through the disk I/O performance, store the hot data in the cache, and reduce the disk I/O access frequency.

4.1 master slave copy and read / write separation

Because most databases in the production environment are read operations, a master-slave architecture is deployed. The master database is responsible for write operations and double-click hot standby. Multiple slave databases are responsible for load balancing and read operations. The mainstream load balancers include LVS, HAProxy and Nginx.
Master-slave copy, read-write separation

4.2 add cache

Add a cache system to the database to cache the hot data into memory. If there is data to be requested in the cache, it will no longer return results in the database to improve the reading performance. Cache implementation includes local cache and distributed cache. Local cache caches data into local server memory or file. Distributed cache can cache massive data with good scalability. The mainstream distributed cache systems include memcached and redis. Memcached has stable performance, data is cached in memory, and the speed is very fast. QPS can reach about 8w. If you want to persist data, you can choose redis, which has a performance no lower than memcached.
redis

5, Performance status key indicators

Enable slow query log
MySQL starts the slow query log to analyze which SQL statement is slow. Use set to set the variable and restart the service failure. You can use my CNF the added parameter takes effect permanently.

mysql> set global slow-query-log=on  #Enable slow query function
mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #Specify slow query log file location
mysql> set global log_queries_not_using_indexes=on;   #Records queries that do not use indexes
mysql> set global long_query_time=1;   #Only slow queries with a processing time of more than 1s are recorded