1

I am trying to improve the performance of my postgres instance in aws.

I am using aws rds db.t2.large

Here is my table structure

CREATE TABLE public.my_test

(
    test_index1 text COLLATE pg_catalog."default" NOT NULL,
    test_index2 text COLLATE pg_catalog."default" NOT NULL,
    json_dump json,
    time_stamp timestamp without time zone NOT NULL,
    meta_info text,
    CONSTRAINT my_test_pkey PRIMARY KEY (test_index1, test_index2)

)
WITH (OIDS = FALSE)
TABLESPACE pg_default;

The current time for taking to do the following operation is about 0.02 - 0.08 seconds

    cur.execute("DELETE FROM my_test \
        WHERE test_index1 = %s and test_index2 = %s", \
        (my_index1, my_index2,))

    cur.execute("INSERT INTO my_test ( \
            test_index1, \
            test_index2, \
            json_dump, \
            time_stamp \
            )  \
                 VALUES (%s, %s, %s, %s)",
        (my_index1_1,
         my_index2_2,
         json.dumps(some_json), 
         "now()", )
    )

Each json dump less than 500kb

This code exist in the an ec2 that is same zone of my rds

When I load test it I load test with 30k rows and perform reads and write.

I am only using 1 out of 20GB of space and 1 out of 4GB worth of memory.

My cpu utilization is low for both ec2 and rds eg. 10% or less

I am not entirely sure if this is normal, but I want to achieve 100 writes per second but right now it is sub 20.

I also try to upgrade my rds but the qps did not improve

Can someone point me a direction of how to debug this, like is there some network setting or memory setting I can check that is bottlenecking in ec2 or rds.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.