1

There is a Log DB. As I understand, there are two constraints which influence the answer to my question: space needed to store the database, number of reads-writes/sec needed.

There are 72 M records inserted per day, which is equivalent to 2000 records generated per second during 10 active hours. There is no limitation on number of client threads that need to do inserts. It can be hundreds of clients. We need to store 180 days of this information like a sliding window - 13 000 M records in total, 30 TB (2500 bytes per record). When a new day starts, data for the oldest day shall be removed;

In parallel some subsystem periodically queries this DB with 500 reads/sec, there is no limitation on number of threads for that, it can be tens of threads:

select * from T_LOG_RECORD
where
    ((F_EXTERNAL_ID="92" AND F_SCOPE="CONT") OR F_GROUP_TAG="100000164")
    AND F_RECORD_TIME < "2016-12-12 23:59:59" 
    AND "2016-11-12 00:00:00" < F_RECORD_TIME AND F_STATUS = 0

Here is the draft DDL (Oracle syntax) of the single table in this DB to understand the idea, indexes are skipped:

CREATE TABLE T_LOG_RECORD (
    T_LOG_RECORD NUMBER(20) NOT NULL,
    F_NOTIFICATION_ID VARCHAR(45) NOT NULL,
    T_OPERATOR_ID NUMBER(20) NULL,
    T_LANGUAGE_ID NUMBER(2),
    F_RECORD_TIME TIMESTAMP NOT NULL,
    F_ADDRESS_TYPE NUMBER(2) NULL,
    F_ADDRESS_VALUE VARCHAR(45) NULL,
    F_EXTERNAL_ID VARCHAR(45) NULL,
    F_SCOPE VARCHAR(45) NULL,
    F_SUBJECT VARCHAR(250) NULL,
    F_TEMPLATE_ID NUMBER(20) NULL,
    F_MESSAGE_TEMPLATE CLOB NULL,
    F_MESSAGE CLOB NULL,
    F_NOTIFICATION_PARAMS CLOB NULL,
    F_TRIGGERED_TIME TIMESTAMP(0) NULL,
    F_EXPIRED_TIME TIMESTAMP(0) NULL,
    F_GROUP_TAG VARCHAR(20) NULL,
    F_STATUS NUMBER(2) NOT NULL,
    F_DISCARD_REASON NUMBER(2) NULL,
    F_PRIORITY NUMBER(2) NULL,
    F_ERROR_CODE VARCHAR(45) NULL,
    F_DELIVERY_TIME TIMESTAMP(8) NULL
)
PARTITION BY RANGE (F_RECORD_TIME)
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (PARTITION p1 VALUES LESS THAN (TO_DATE('08-01-2017', 'DD-MM-YYYY')))
    TABLESPACE SOME_TABLESPACE
;

I assume that commodity server hardware will be used. We will implement sharding on application level, with active-standby replication with 1 redundant copy.

My current assumption is that PostgreSQL on a single host (8 cores, 64 GB RAM, 2 x 4 TB SSD without any RAID) will be able to handle 10 K inserts/sec/host. So we will need 8 hosts/VMs with such parameters. The calculation is the following: 30 TB * 2 (replication factor) / 8 TB (total disk size on host) = 8 hosts. So there will be 4 host-shards and 4 hosts-replicas. Inserts will be done on host-shards, selects will be done on all hosts (shards and replicas).

Is my calculation correct?

  • One thing you should note first, is how this data will be written. Is there any parallelism? How will your application work with the PostgreSQL? How many concurrent users? Because 1 user inserting 2000 lines needs an X hardware, but 100 users inserting 2000 lines is Y. You can use SQLBenchmark [1] to benchmark your environment. I've seen environments with 8k inserts / sec, 1 host only, used lots of CPU, almost no memory, and MUCH disk. I would tell you to test, and test a lot, not to spend what you do not need, or not to have surprises. 1- sourceforge.net/projects/benchmarksql – Cleiton Domazak Jan 11 '17 at 15:30
  • No amount of theoretical calculations will replace proper capacity testing. – mustaccio Jan 11 '17 at 15:35
  • you'll get faster read and write performance for less money using 8x1 TB drives in RAID 10 – Neil McGuigan Jan 11 '17 at 21:26
  • 1
    "active-standby replication with 1 redundant copy" is not a substitute for RAID on each node. RAID5 over 5 2TB SSDs will give you 8TB and fits nicely in 1U with space for boot disks etc. – Jack Douglas Feb 7 '18 at 8:06
  • 1
    What is the distribution of the "500 reads/sec" queries, are they evenly spread out over the whole data set? – Jack Douglas Feb 7 '18 at 8:10

Your Answer

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

Browse other questions tagged or ask your own question.