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.

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.