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.