designing high throughout system for storing hundred thousands incoming records per second in SQL server database

Question

In our company we have a requirement where we would like to store hundred thousands incoming records per seconds. we currently a pub-sub model for processing many records(100/sec) from many system(~1000) for listening to incoming events. now the problem is storing these records as fast as they come,(i.e. with minimum delay)

I have python3 code written which leverages existing framework and store events in the database. I used asyncio along with Threads for running code in parallel, I was thinking this would help to tackle the the delay I am seeing in inserting the records for a single system, but the gains are minimal as if I increase the load to 6 systems or more I am seeing that there is a increased delay over the period of time. I investigated the system performance

with TOP I am seeing CPU % used is well above 100%

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 711944 dvmt-ev+  20   0 2258788 226220  17800 S 107.7   1.4  84:41.20 python

is there a way I can maintain consistent throughput with increased loads ?

EDIT this is latest top command result.

top - 17:20:43 up 90 days, 19:48,  1 user,  load average: 1.17, 1.00, 0.68
Tasks: 200 total,   1 running, 199 sleeping,   0 stopped,   0 zombie
%Cpu0  : 11.6 us,  4.3 sy,  0.0 ni, 82.7 id,  0.0 wa,  0.3 hi,  1.0 si,  0.0 st
%Cpu1  : 11.4 us,  4.7 sy,  0.0 ni, 82.3 id,  0.0 wa,  0.3 hi,  1.3 si,  0.0 st
%Cpu2  : 10.4 us,  5.0 sy,  0.0 ni, 83.3 id,  0.0 wa,  0.3 hi,  1.0 si,  0.0 st
%Cpu3  : 13.0 us,  4.7 sy,  0.0 ni, 80.7 id,  0.0 wa,  0.7 hi,  1.0 si,  0.0 st
%Cpu4  : 11.8 us,  3.7 sy,  0.0 ni, 83.5 id,  0.0 wa,  0.3 hi,  0.7 si,  0.0 st
%Cpu5  : 11.5 us,  3.7 sy,  0.0 ni, 84.4 id,  0.0 wa,  0.3 hi,  0.0 si,  0.0 st
%Cpu6  : 16.8 us,  4.7 sy,  0.0 ni, 77.1 id,  0.0 wa,  0.3 hi,  1.0 si,  0.0 st
%Cpu7  : 10.1 us,  4.0 sy,  0.0 ni, 84.6 id,  0.0 wa,  0.3 hi,  1.0 si,  0.0 st
MiB Mem :  15848.1 total,   9050.0 free,   1595.5 used,   5202.6 buff/cache
MiB Swap:   8192.0 total,   8100.5 free,     91.5 used.  13098.2 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
1876629 dvmt-ev+  20   0 2386288 293820  17880 S 148.8   1.8   6:24.34 python
    976 root      20   0  390256   2184   1440 S   0.3   0.0  32:25.65 NetworkManager
4119967 dvmt-to+  20   0 1205460  59196  17972 S   0.3   0.4   1:47.69 python
      1 root      20   0  247384   7852   4520 S   0.0   0.0  14:40.79 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:07.67 kthreadd
    ```

Answer 1

When you are talking about hundreds of thousands of inserts per second you have probably exceeded the limits of the database software and hardware.

It's easy to scale your inserting code, you can add more threads and boxes the language doesn't really matter. But as long as you only have a single database, you have a hard limit to the number of inserts it can make, the write speed of the disc drive, or the bandwidth of the network card if nothing else.

A quick google shows people can get up to 30k inserts per second to relational dbs with bulk inserts. But it you want this kind of sustained performance you will have to shard or distribute your database, or look at nosql or alternate solutions.

see this kafka 3 machine cluster get to 2 million writes per second

https://engineering.linkedin.com/kafka/benchmarking-apache-kafka-2-million-writes-second-three-cheap-machines

this rabbit MQ test gets to 200,000 mps

https://blog.rabbitmq.com/posts/2012/04/rabbitmq-performance-measurements-part-2https://blog.rabbitmq.com/posts/2012/04/rabbitmq-performance-measurements-part-2

It's the performance of the server that you need to concern yourself with, not the code