How to optimize site performance in the face of concurrency?

Each project will adapt its architecture to face future problems as users and data grow

With the increase of users, the platform began to become stagnant, and began to troubleshoot and optimize problems. Let’s talk about how to deal with it.

Nginx

A website, the core will be divided into several parts: front-end, back-end services, database, server.

Our initial project was jar packages. A Tomcat could not support much concurrency.

Tomcat’s default maximum number of requests is 150, which means that 150 concurrent requests are supported, although this can be increased. So you have to start building clusters.

After the cluster was set up, Nginx was introduced as the reverse proxy. The load balancing was also solved, and the weight was made according to the performance of different servers.

Sure enough, Nginx has been added to make the site visit much faster after the load is done.

But then came the problem of Session sharing. We put the user’s Session information into Redis, and Session sharing was done.

Or use IPHash

IPhash hash the IP address requested by the client and then distributes the requests from the same client IP address to the same server for processing. In this way, sessions are not shared.

After the access layer is done, the forum module is still slow.

Slow SQL

I started to check the logs and found that some SQL processes were longer than 1S.

The fundamental problem was that we did a join table query, and there were a lot of associated tables, so we began to optimize the database structure, adding a lot of redundant fields,

Behind only query a table, from cochlear speed to now almost seconds open.

In terms of classification,

From database query optimization to the use of static data management, because little will change.

Primary/secondary replication and read/write separation

After the problem of access layer was solved, we found that the pressure bottleneck of the project was transferred to the database.

Start with a single database, but read and write to the same database, performance is not enough,

So he used another server to take charge.

MySQL master/slave is relatively simple, several commands are set up, and then we use Sharding JDBC to do read/write separation.

After writing it, the overall performance of the site improved.

Mysql configures the primary and secondary databases

1. Modify the master server.

#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[must] enable binary log server-id=222//[must] Unique ID of the server, default is1, usually the last segment of the IP addressCopy the code

2. Modify the slave server.

#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[not necessary] enable binary log server-id=226//[must] Unique ID of the server, default is1, usually the last segment of the IP addressCopy the code

3. Restart mysql on the two servers

service mysqld restart;

Copy the code

4. Create an account on the master server and authorize slave:

#/usr/local/mysql/bin/mysql -uroot -pmttang mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'password'; // Do not use the root accountCopy the code

% indicates that all clients can be connected. As long as the account and password are correct, you can use the client IP address, for example, 192.168.145.226, to enhance security.

5. Log in to the mysql database on the primary server to query the master status

   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

Copy the code

Note: Do not operate the primary server MYSQL after this step to prevent the status value of the primary server from changing

6. Configure the Slave server

   mysql>change master to master_host='192.168.145.222',master_user='username',master_password='password',
         master_log_file='mysql-bin.000004',master_log_pos=308; // Be careful not to disconnect,308There are no single quotation marks around digits. Mysql>startslave; // Enable secondary replicationCopy the code

7. Check the status of the secondary server replication function.

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.222 Mysync // Authorization account name, avoid using root Master_Port: 3306 // database port. Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 600 //# Exec_Master_Log_Pos Relay_Log_File ddte-relay-bin.000003 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes // This status must be Yes Slave_SQL_Running: Yes // This status must be Yes......Copy the code

Note: Slave_IO and Slave_SQL processes must be running correctly (YES), otherwise they are both in an error state.

The primary and secondary servers are configured.

Sharding-JDBC implements read and write separation

Technical selection: SpringBoot + Sharding-JDBC + MyBatis

Using Sharding-JDBC to configure read/write separation, the advantage is that the data source is fully managed by Sharding. The master library is automatically executed for write operations and the slave library is automatically executed for read operations. There is no need for the programmer to care about this implementation in the program.

1. Core JAR packages

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.6. RELEASE</version>
</parent>
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <! -- sharding -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0 - RC1</version>
    </dependency>
    <! -- mybatis-plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.2.0</version>
    </dependency>
    <! Ali database connection pool -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.14</version>
    </dependency>
    <! -- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.18</version>
     </dependency>
     <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.60</version>
     </dependency>
 </dependencies>
Copy the code

2. Configure the YML file

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names:
        master,slave
      # primary data source
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_master? characterEncoding=utf-8
        username: * * * *
        password: * * * *
      # from data source
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_slave? characterEncoding=utf-8
        username: * * * *
        password: * * * *
    masterslave:
      # read-write split configuration, used to configure the secondary library load balancing algorithm type, optional values: ROUND_ROBIN, RANDOM
      load-balance-algorithm-type: round_robin
      The final data source name
      name: dataSource
      Primary database data source name
      master-data-source-name: master
      List of data source names from the library, separated by multiple commas
      slave-data-source-names: slave
    props:
      # enable SQL display, default false, print SQL when executing SQL, and display the name of the execution library
      sql:
        show: true
Copy the code

Question: How to solve the problem of read delay that often occurs in read/write separation architectures?

I just inserted a piece of data, and I’m going to read it, and I’m not going to read it, right?

After all, the data is copied to the slave node after the master node writes to it. The reason why the data cannot be read is that you have already read the data from the slave node before it is copied to the slave node.

The master/slave replication of mysql5.7 is multi-threaded, which means that it will be faster, but it is not guaranteed to be 100% read immediately. There are two ways to solve this problem:

(1) Whether to read immediately, compromise at the business level, whether to read immediately after the operation

(2) control the main library, whether directly go for the operation to read out immediately, and will not compromise on the business, we can read directly go for this type of the main library, of course, Sharding – JDBC is also consider the existence of the problem, so provide us with a function, can allow the user to specify when using do not go to the library to read. Before reading, use the following method to set it:

public List<UserInfo> getList(a) {
        // Force the primary route library
        HintManager.getInstance().setMasterRouteOnly();
        return this.list();
    }
Copy the code