N1QL poor upsert performance (< 1000 docs/sec)

Couchbase Server
1

Is it normal performance?
Cassandra is 10x faster - around 10 000 docs/sec.

I am testing on a single PC with 4 cores under Windows 10.
One document is 1 KB and has simple structure:
{‘content’:’…’}
I use N1QL query like this:
upsert into <bucket_name> (key, value) values (, {‘content’:<1KB_data>}), …, (, {‘content’:<1KB_data>})
i.e. one query contains 100 documents.
While testing, I see that CPU is 100% busy and 50% of CPU is used by memcached.exe.

If this performance (only 1000 docs/sec) is below normal, what could be the reason and how can it be tuned? Is it possible to reach performance comparable to that of Cassandra (i.e. 10 000 docs/sec)?

2

The fastest way to insert data into Couchbase Server is to use the KV API’s directly (through the SDKs) instead of the N1QL UPSERT statement. The DML statements like INSERT/UPSERT/UPDATE/DELETE have advantages in manipulating bulk data and provide great deal of flexibility. However architecturally, KV can perform the operations directly on the data node that owns the vbucket thus is much faster.

if you are still no seeing better performance pls post the code here so we can help tshoot the remaining issues.
thanks
-cihan

3

But what is the expected insertion performance with KV API and with N1QL?
Did anyone perform any benchmark?
If yes, what are the results? (which API did you use - N1QL or RV API? Which OS? Which document size? How many documents in one batch? And what is your insertion speed?)

I will benchmark with PHP SDK for PHP 5 and will write here, but when is it expected to be available for PHP 7?

Thank you for reply.

4

Hi,
could you please give more information or link, because I’m trying to insert bulk data from oracle to couchbase by python and upsert_multi function. is your suggestion difference with my doing?

5

We have a beta2 of the extension for PHP 7 which is available on PECL. We’re working toward release.

I’d expect the performance to vary depending on size of object, to what degree you parallelize the insertion and a bit about system capabilities. I would not be surprised to see a 10x different.

Another way to look at it is that a N1QL insert also goes through the KV API, so using KV directly removes a layer of work and distributes the data directly over the cluster instead of needing to go over two network hops.

6

The upsert_multi() function is what’s being suggested here. That uses a direct K-V interface when adding data to the cluster.

1 Like
7

Hi @i321,

Let’s get your desired performance using N1QL. What is the current status?