This topic describes the impacts of network issues on database performance.

Frontend protocols

  • Simple query: A simple query cycle starts when the frontend sends a Query message to the backend. The message contains one or more SQL statements that are expressed in text strings. The backend sends one or more response messages to the frontend based on the content of the query statement strings. The last response is a ReadyForQuery message.
  • Extended query: In the extended protocol, the frontend first sends a Parse message that contains a textual query string. If a named prepared-statement object is created, the object lasts till the end of the current session. If a prepared statement exists, the prepared statement can be ready for execution by using a Bind message. The Bind message transfers the relevant parameters. If the prepared statement is repeatedly executed, the server may save the created plan and reuse the plan in the subsequent Bind messages for the same prepared statement. Then, the plan can be executed by using an Execute message. Finally, the system returns a CommandComplete message. This message indicates that the source SQL statement is executed.

Performance differences caused by different network latencies

  • If a latency of 1 ms occurs when the Ping command runs, it takes 4,000 ms to insert 1,000 records.Performance differences caused by different network latencies
  • If a latency of 0.1 ms occurs when the Ping command runs, it takes 500 ms to insert 1,000 records.Performance differences caused by different network latencies - Example 1

The following examples show the network packets that are captured when data is inserted.

  1. Search for tcpdump -i eth0 port 3433 -s 0 -w t.cap in the network packet.
  2. In the following example, B stands for Bind, D stands for Describe, and E stands for Execute.Captured network packet - 2
  3. In the following example, C stands for command complete.Captured network packet - 3

The following conclusion is obtained based on the preceding data:

  • Conclusion:

    If you insert one record to a single row, the response time reaches 2 milliseconds. If you execute 1,000 INSERT statements, the response time reaches 2 seconds.

  • Solution:

    You can execute the insert into values(),(),() statement to insert data in batches. This prevents repeated network interactions.