Database Protocol Packet Size and Performance

The Data Access Handbook points out that the size of the database protocol packet is an important factor for the database application performance. Each database vendor defines a protocol for communication with the database system, a format that only that database system understands. For example Microsoft SQL Serveruses communication encoded with the Tabular Data Stream (TDS) protocol, IBM DB2 uses communication encoded with the Distributed RelationalDatabase Architecture (DRDA) protocol and Oracle defines Oracle Net.

When an application makes a standards-based API request, such as executinga Select statement to retrieve data, the database driver transforms that APIrequest into zero, one, or multiple requests to the database server. The database driver packages the requests into database protocol packets and sends them tothe database server, as shown in below

The size of database protocol packets sent by the database driver to the database server must be equal to or less than the maximum database protocol packet size allowed by the database server. For example, if the database server accepts a maximum packet size of 64KB, the database driver must send packets of 64KB or less. Typically, the larger the packet size, the better the performance, because fewer packets are needed to communicate between the driver and the database.Fewer packets means fewer network round trips to and from the database. For example, if the database driver uses a packet size of 32KB and the databaseserver’s packet size is configured for 64KB, the database server must limit its packet size to the smaller 32KB packet size used by the driver.

The increase in the number of packets also means an increase in packet overhead.High packet overhead reduces throughput, or the amount of data that is transferred from sender to receiver over a period of time. The extra CPU required to disassemble packets for transport and reassemble them when they reach their destination reduces the overall transmission speed of the raw data. Fewer packets require less disassembly and reassembly, and ultimately, use less CPU.

Network Packets

Once database protocol packets are created, the database driver hands over thepackets to TCP/IP for transfer to the database server. TCP/IP transfers the data in network packets. If the size of the database protocol packet is larger than the defined size of the network packet, TCP/IP breaks up the communication into even smaller network packets for transmission over the network and reassembles them at their destination. Similar to database protocol packets, the fewer the network packets, the better the performance. In contrast to database protocol packets, you can’t configurethe size of network packets. The network packet size is determined by a maximum transmission unit (MTU) setting for the network adapter in the operating system of the sending network node.

Database drivers and database servers only deal with database protocolpackets, not network packets. Once network packets reach their destination, suchas a database server, the operating system of the database server reassemblesthem into database protocol packets that deliver the communication to the database.

If network packets are really the way that data is transported over the network and the MTU of the network controls the size of network packets, why doesa larger database protocol packet size improve performance? Let’s compare thefollowing examples. In both examples, a database driver sends 25KB of data tothe database server, but Example B uses a larger database protocol packet sizethan Example A. Because a larger database protocol packet size is used, the number of network round trips is reduced. More importantly, actual network traffic is reduced.

Example A:
Database Protocol Packet Size = 4KBUsing a 4KB database protocol packet, as shown in below figure, the database driver creates seven 4KB database protocol packets (assuming a 30-byte packet header) to send 25KB of data to the database server (6 packets transporting 3.971KB of data and 1 packet transporting 0.199KB of data).

If the MTU of the network path is 1500 bytes, as shown in below figure,the database protocol packets are divided into network packets for transport across the network (total of 19 network packets). The first 6 database protocol packets are each divided into three 1500-byte networkpackets. The data contained in the last database protocol packet fits within one 1500-byte network packet.

Example B
Using a 32KB database protocol packet, the database driver only needs to create a single 32KB database protocol packet to send 25KB of data to the database server (assuming a 30-byte packet header), as shown in below figure

If the MTU of the network path is 1500 bytes, as shown in below figure,the single database protocol packet is divided into 17 network packetsfor transport across the network, a reduction of 10% when compared to Example A.

As you can see in Example A, because each 4 Kb packet needs to be divided in 1500 byte packets, the third packet would not be completely filled.

Oracle 11g release 2 Protocol Packet Size

Under typical database configuration, Oracle Net encapsulates data into buffers thesize of the session data unit (SDU) before sending the data across the network. Adjusting the size of the SDU buffers relative to the amount of data provided toOracle Net to send at any one time can improve performance, network utilization, andmemory consumption. The SDU size can range from 512 bytes to 65535 bytes. The default SDU for the clientand a dedicated server is 8192 bytes. The default SDU for a shared server is 65535 bytes.

Refer to this link for steps to set the SDU size for the database

Advertisement