We’ve been using N1QL for a new game we’ve been developing. However when the application went to load testing. N1QL blew up.
We generated 100,000 users, which totals 900,000 user recored. And then there is additional data which is stored in different buckets as the load testing occurs. But we have barely broke the 20,000 entries for these buckets.
The cluster setup is a 3 node cluster on AWS with 30GB RAM each. (90GB total in cluster).
With couchbase 4.0 community
With 4GB allocated to indexing (12GB total)
And 20GB(60GB total) allocated to the buckets which are split up as follows:
Feed 15GB, replica 1, 10119 items, 94.5MB / 15GB used
Performance 30GB, replica 1, 15205 items, 102MB / 30GB used
User 12GB, replica 1, 900001 items, 505MB / 12GB used
When we look at the system stats we hover around 60-80% memory and never swap.
However the indexer and cbq-engine are consuming most of the memory.
The query we are trying to run is the following:
SELECT userId, avatarId, name FROM user WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
What we found was composite indexes we’re pointless. Only the first entry in the composite index was actually being used. And if we tried to use the 2nd or 3rd entry we would get a Need a global index error. Also performance was super shotty.
So we tried to index each conditional parameter separately and this sped things up a bit. However using LIKE for string comparison even on null values slowed it down to 2-3 seconds. And ORDER BY will always cause it to take 25+ seconds. In fact if any one of the optimisations wasn’t done, it would take 25+ seconds.