I tried option number 1, ie modifying the primary key, but oddly that didn’t give any noticeable improvements. also storing Index might not be the right solution for my requirement because I cannot do that for all my tables.
I will be definitely storing the group_id as an INT only, only for random generation I was using float, because I was getting some errors while casting.
Also, during the tests I found a problem with the diminishing speed of inserts, as first 400,000 odd inserts, the inserts ran at 400 - 500 QPS, but after that gradually dropped to 70 - 100 QPS. I had built a custom piece of code to insert random values into the database for load tests and Row Level Security, I was running the test on the following server configurations:
- Cockroach DB running on 3 g1-small Instances in GCE with 50 GB Storage Each.
- Java Tomcat, to run the tests.
I checked the RAM usage was really low, ie. 250 mb, but the CPU usage was high from the very beginning. So, I wanted to figure out what is going on, and why is there just a massive drop in the speed.
In the beginning, the P99 latency per query was below 100 ms, but as it reached to a 700,000 entries in the database it slowed down to 4 to 5 secs for the P99 latency.
Can’t figure out what is going on, so I spun up a mysql server, on the same configuration, only on one instance though, and ran the same test, it ran at 300 - 400 queries per sec right till the end, ie. I ended up running it till it inserted 5 million rows into the table.
Over that I also ran the Group IDs where clause over cockroach, which had about 2 million rows in the table, and it would take about 10 - 15 or even 30 times slower than mysql, which mysql was able to perform in a matter of 10 - 20 ms, cockroach would take about 600 ms when running the same QPS, cockroach would take much lesser time, but still it would take 150 ms.
I also did run another load test last night, the test goes as follows :
- I inserted a few queries (100,000) in the “first” table and it was performing at 70 - 100 QPS.
- Then I created another table “second”, and inserted 300,000 rows into that table, and it was able to perform at 500 - 600 QPS.
I have sent you the details of the cluster and test server as private message, please advice. Thanks a ton.