0

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
    ```
4
  • Just to clarify: your system can currently handle 6 systems, and you need it to handle 1000? Jun 22 at 17:18
  • yes that's correct. Jun 22 at 17:20
  • 1
    As the problem is asked, it seems more like a SO/SUperUser/DBA question. However you might have a software engineering problem at first : do you really need a relational database with ACID support for your needs ? I suggest if you want a proper engineering software answer to your technical problem and only layout your requirement (performance, acid needed ?, ...).
    – Walfrat
    Jun 23 at 10:43
  • 1
    I agree with @Walfrat. The next question you need to answer is what are you doing with these records once they are inserted? That is, what is the purpose of storing this data: how will it be accessed, what kinds of queries are expected to be run against it?
    – JimmyJames
    Jun 23 at 17:31
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.