We’ve all heard that in the big data scenario of the Internet of Things, one of the biggest advantages of TDengine is writing speed, which is the result of TDengine’s unique design. However, some users may feel that write performance is not as good as they expected when they first use TDengine. Some of these users use TDengine directly on the server side, some use TDengine on the client side, and some use a variety of different connectors.

So — how do you solve these various types of write slowness? The idea is to go through the hardware and software networks layer by layer, while ensuring that the server’s parameters and configurations are optimized.

For those who use TDengine through connectors, performance issues are slightly more difficult due to the more module involvement and network issues involved. But even then, the database itself needs to be configured for performance. Before this, using “my XXXX connector to write data is very slow” is inaccurate and not conducive to troubleshooting.

So no matter how complex your scenario is, it’s important to check TDengine’s performance on the server first. The most time-saving and efficient way to do this is to use the official tool TaosDemo to see if the write speeds are roughly the same. The advantage of this is that you can locate the problem on the database server itself in the first place. Then, we can target the rest of the factors. For example, the network communication between the server and the client is normal.

You can learn how to use TaosDemo by using “Taosdemo –help”, which can simulate similar scenarios in your work by setting the number of tables, the number of rows in a table, the data type, the number of rows in a single batch, and even simulating out-of-order writes. After running, TaosDemo automatically generates data to complete the write and gives performance data at the end.

If taosDemo performance is still slow, we can make the following optimizations:

1. To improve write efficiency, do not write only one record at a time, which will be a waste of resources. You are advised to write multiple records to an SQL statement. The more records that can be written at a time, the higher insertion efficiency is. Typically, a single SQL is written to multiple tables, such as: insert into TB1 values(….) tb2 values(….) tb3 values(…) . A record cannot exceed 16 KB, and the total length of an SQL statement cannot exceed 64 KB. (You can set the maximum length to 1 MB by using maxSQLLength. For details, see the official documents.) When using TaosDemo emulation, you can specify the number of rows to be written to a single INSERT with the -r parameter, but be careful not to calculate SQL length beyond maxSQLLength.

Note: The default maxSQLLength of TaosDemo in the latest version (2.1.0) is already 1MB.

2.TDengine supports multi-threaded simultaneous writing, to further improve the writing speed, a 12-core CPU client can open more than 20 threads at the same time to write. However, after a certain number of threads, the speed can no longer increase, or even decrease, because of the extra overhead caused by the frequent switching of threads. When using TaosDemo simulation, the number of threads can be specified by -t, usually the number of CPU cores or twice the number of cores is best.

3. When a large amount of data is pouring into the memory and waiting for the disk to fall off, the memory size of the vNode virtual data node is particularly important. The two parameters cache and blocks represent the size and number of memory blocks, respectively. The product of these two parameters is the reserved memory of a VNode. For many scenarios, the default number of blocks is not enough. When the write cache is insufficient, data cannot be written to the disk at a time, but will be written to. Last temporary file, which objectively increases I/O operations on the disk. Depending on your scenario, increase the value of this parameter (a multiple of 3) to see if the write speed improves, and see if memory is a performance bottleneck and remains within a safe usage range until the optimal solution is found.

Click here to see the manual for TaosDemo.

In this process, you need to constantly control variables and observe where your performance bottlenecks occur. Running out of pre-allocated memory, running out of OS memory, running out of CPU, or running out of hard disk read and write, and then make a specific adjustment.

After this adjustment, it should be enough for a direct user of the TDengine server. It’s worth noting that you don’t have to focus too much on how many lines TDengine writes per second. Because the number of columns in a row (measuring points) and the data type of each column (measuring points) are not fixed.

For users using connectors or clients, if this doesn’t solve your write performance problems, let’s look at network, client, and application level factors.

Because the server relies on the network to connect with the application or client, the network problem is an important part of the performance problem. Here is a typical case:

A user used interface C to insert data in a situation like this: The insertion speed is fast and slow, and the overall speed is much slower than expected.

So together, we configured the memory, the number of threads, and the number of rows per SQL write. But after that, the problem remained. So we looked at the logs together and found some alarms about network communication. Combined with the monitoring data of the server, we preliminarily diagnosed that the reason for the slow write was the route blocking on the network. The user then reconstructs the network topology using only local virtual machines as a LAN and the problem disappears.

In addition, the TDengine client has many functions (fetching and caching metadata; Forward insert, query and other requests to the correct data node; The final level of aggregation, sorting, filtering, and so on is required before the results are returned to the application.) So many times TDengine’s performance problems are not on the server side, but on the client side. When you find that your performance is lagging, take a look at the background of the client server and you may find something surprising. Unexpected – the client side can also be a bottleneck for write performance. At this point, you need to increase the client server performance or number as appropriate.

Finally, because connectors are logically thin layers, their performance is often affected by the language itself, and there is generally no room for optimization in case of bugs, such as Java being slower than C.

OK, that’s about it. Users reading this should have a rough idea of what TDengine can do to optimize write performance. That way, you’ll be able to ask accurate questions in the future, even if you need official tech support, which will be a very enjoyable experience for both parties. For many new users of TDengine in the community edition, due to tight work schedules or other reasons, they may not have the patience to read the documentation step by step to understand the product.

At this point, this article can do its job.