Section on debugging INSERT performance

[mfreed]

When users encounter slower-than-expected INSERT performance, we often see that it's attributed to a set of common reasons. We should add a section around "best practices" or "what to check if your inserts are slow" to the docs.

Find below a draft outline of this section; posting as a github issue so that the community could offer their own suggestions before we finalize the docs pages.

[mfreed]

Adding a draft section here. Feedback from community welcome!

[mfreed]

We occasionally get questions from new users that they are unable to insert data into TimescaleDB at nearly the rates described in our benchmarks and blog posts.

There are typically a handful of reasons that users may occasionally see reduced ingest rates. Here, we include some of the more common "best practices" for good performance, including how to check and fix the problem.

1. Use parallel writes. Each INSERT or COPY command to TimescaleDB is executed as a single transaction and thus runs in a single-threaded fashion. To achieve higher ingest, you should execute multiple INSERTS or COPY commands in parallel. For help with bulk loading large CSV files in parallel, check out TimescaleDB's parallel copy command.

2. Write data in loose time order. When chunks are sized appropriately, the latest chunk(s) and their associated indexes are naturally maintained in memory. New rows inserted with recent timestamps will be written to these chunks and indexes already in memory. If a row with a sufficiently older timestamp is inserted -- it's an out-of-order or backfilled write -- the disk pages corresponding to the older chunk (and its indexes) will need to be read in from disk. This will significantly increase write latency and lower insert throughput.

   Particularly when you are loading data for the first time, try to load data in sorted, increasing timestamp order. Be careful if you are bulk loading data about many different servers, devices, and so forth: Do not bulk insert data sequentially by server, i.e., all data for server A, then server B, then C, and so forth. This will cause disk thrashing as loading each server will walk through all chunks before starting anew. Instead, arrange your bulk load so that data from all servers are inserted in loose timestamp order (e.g., day 1 across all servers in parallel, then day 2 across all servers in parallel, etc.).

3. Insert rows in batches. In order to achieve higher ingest rates, you should insert your data with many rows in each INSERT call (or else use some bulk insert command like COPY). Don't insert your data row-by-row, and instead try at least hundreds (or thousands) of rows per INSERT. This allows the database to spend less time on connection management, transaction overhead, SQL parsing, etc. and more time on data processing.

4. Avoid too large chunks. To maintain higher ingest rates, you want your latest chunk, as well as all its associated indexes, to stay in memory, so that writes to the chunk and index updates merely update memory. (The write is still durable, as inserts are written to the WAL on disk before the database pages are updated.) If your chunks are too large, then writes to even the latest chunk will start swapping to disk.

   As a rule-of-thumb, we recommend that the latest chunks and all their indexes fit comfortably within the database's shared_buffers. You can check your chunk sizes via the chunk_relation_size_pretty SQL command.

    => SELECT chunk_table, table_size, index_size, toast_size, total_size
            FROM chunk_relation_size_pretty('hypertable_name')
            ORDER BY ranges DESC LIMIT 4;
   
                   chunk_table               | table_size | index_size | toast_size | total_size 
    -----------------------------------------+------------+------------+------------+------------
     _timescaledb_internal._hyper_1_96_chunk | 200 MB     | 64 MB      | 8192 bytes | 272 MB
     _timescaledb_internal._hyper_1_95_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB
     _timescaledb_internal._hyper_1_94_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB
     _timescaledb_internal._hyper_1_93_chunk | 388 MB     | 108 MB     | 8192 bytes | 500 MB
   

   If your chunks are too large, you can update the range for future chunks via the set_chunk_time_interval command. This does not modify the range of existing chunks (e.g., by rewriting large chunks into multiple small chunks), however. For really bad configurations, you might consider dumping and reloading your hypertable data to properly sized chunks.

   Note that keeping the latest chunk applies to all active hypertables; so if you are actively writing to two hypertables, the latest chunks from both should fit within shared_buffers.

5. Properly configure shared_buffers. If you install TimescaleDB via a method that runs timescaledb-tune, it should automatically configure shared_buffers to something appropriate given your hardware specs. (Although in certain cases with virtualization and constrained cgroups memory allocation, these automatically-configured settings may not be appropriate.) It's worth checking that your shared_buffers are set to something appropriate; we typically recommend 25% of available RAM.

6. Avoid too many or too small chunks. Unless you are running multi-node TimescaleDB, we do not currently recommend most users using space partitioning. And if you do, remember that this number of chunks are created for every time interval. So if you create 64 space partitions and daily chunks, you'll have 24,640 chunks per year. This may turn into a bigger performance hit during query time (due to planning overhead) compared to insert time, but something to consider nonetheless.

   Another mistake is to use an incorrect integer value when specifying the time interval range in create_hypertable. If your time column uses a native timestamp type, then any integer value should be in terms of microseconds (so one day = 86400000000). We recommend using interval types ('1 day') to avoid potential for any confusion. If you time column is an integer or bigint itself, you need to use the appropriate range: if the integer timestamp is in seconds, use 86400; if the bigint timestamp is in nanoseconds, use 86400000000000.

   In both cases, use chunk_relation_size_pretty to make sure your chunk sizes or partition ranges seem reasonable:

   => SELECT chunk_table, ranges, total_size
          FROM chunk_relation_size_pretty('hypertable_name')
          ORDER BY ranges DESC LIMIT 4;
   
                  chunk_table               |                         ranges                          | total_size 
   -----------------------------------------+---------------------------------------------------------+------------
    _timescaledb_internal._hyper_1_96_chunk | {"['2020-02-13 23:00:00+00','2020-02-14 00:00:00+00')"} | 272 MB
    _timescaledb_internal._hyper_1_95_chunk | {"['2020-02-13 22:00:00+00','2020-02-13 23:00:00+00')"} | 500 MB
    _timescaledb_internal._hyper_1_94_chunk | {"['2020-02-13 21:30:00+00','2020-02-13 22:00:00+00')"} | 500 MB
    _timescaledb_internal._hyper_1_93_chunk | {"['2020-02-13 20:00:00+00','2020-02-13 21:00:00+00')"} | 500 MB
   

7. Use indexes in moderation. Having the right indexes can speed up your queries, but also requires additional work to incrementally maintain those indexes on each new row written. Check the number of indexes you currently have defined on your hypertable with the psql command \d hypertable_name, and determine whether their potential query benefits outweigh their storage and insert overhead. Be reasonable.

8. Watch row width. The overhead from inserting a wide row (say, 50, 100, 250 columns) is going to be much higher than inserting a narrower row (more network IO, more parsing and data processing, larger writes to WAL, etc.). Most of our published benchmarks are using TSBS, which uses 12 columns per row. So you'll correspondingly see lower insert rates if you have very wide rows.

   If you are considering very wide rows because you have different types of records, and each type has a disjoint set of columns, you might consider using multiple hypertables (one per record type), particularly if you don't often query across these types.

   Additionally, you might consider JSONB records if virtually all columns are sparse. That said, if you are using sparse wide rows, use NULLs for missing records whenever possible, not default values: NULLs are much cheaper to store and query.

9. Reconsider foreign key constraints. Sometimes it's necessarily to build foreign keys from your hypertable to other relational tables, sometimes they aren't. When you have an FK constraint, every INSERT will typically include a SELECT to your referenced table, which can degrade performance. Consider also if you can denormalize your data, especially given data savings and grouping that will happen if you use that same denormalized column as your segmentby key in compression.

10. Avoid unnecessary UNIQUE keys. Developers are often trained to specify primary keys in database tables, and many ORMs love them. Yet many time-series use cases do not require them, as each event or sensor reading can simply be logged as a separate event by inserting it at the tail of a hypertable's current chunk during write time. If a UNIQUE constraint is otherwise defined, that insert can necessitate an index lookup to determine if the row already exists.

11. Use performant disks. Sometimes users deploy TimescaleDB in environments with slower disks, whether poorly-performing HDD, remote SANs, or other types of configuration. And because when you are inserting rows, the data is durably stored to TimescaleDB's write-ahead log (WAL) before the transaction completes, slow disks can impact insert performance. One thing to do is check your disk IOPS using the ioping command.

    Read test:

    $ ioping -q -c 10 -s 8k .
    
    --- . (hfs /dev/disk1 930.7 GiB) ioping statistics ---
    9 requests completed in 208 us, 72 KiB read, 43.3 k iops, 338.0 MiB/s
    generated 10 requests in 9.00 s, 80 KiB, 1 iops, 8.88 KiB/s
    min/avg/max/mdev = 18 us / 23.1 us / 35 us / 6.17 us
    

    Write test:

    $ ioping -q -c 10 -s 8k -W .
    
    --- . (hfs /dev/disk1 930.7 GiB) ioping statistics ---
    9 requests completed in 10.8 ms, 72 KiB written, 830 iops, 6.49 MiB/s
    generated 10 requests in 9.00 s, 80 KiB, 1 iops, 8.89 KiB/s
    min/avg/max/mdev = 99 us / 1.20 ms / 2.23 ms / 919.3 us
    

    You should see at least 1000s of read IOPS and many 100s of write IOPS.

12. Use separate disks for WAL and data. While a more advanced optimization that is rarely needed, you can further increase throughput if disk becomes a bottleneck by using a separate disk (tablespace) for the database's write-ahead log (WAL) and data.

13. Run our Docker images on Linux hosts. If you are running a TimescaleDB docker container (which runs Linux) on top of another Linux operating system, you're in great shape. The container is basically providing process isolation, and the overhead is basically nothing. If you are running the container on a Mac or Windows machine, however, you'll be paying some performance hit for the OS virtualization, including for I/O.

[solugebefola]

All of the section titles are addressing problem except for the first one, which seems to be suggesting a solution?

[solugebefola]

Checking in on this: Has this been put into a PR or added to docs?

[taitung]

being waiting for this since I started the test on Timescale. yeah, I got to change my chunk size now with a smaller chunk interval. Thank you @mfreed for sharing these valuable tips :)