Sudden very slow indexing of the views

[Alan Malta <al...@gmail.com>]

Hi everyone,

it's been more than a week that I have been debugging a strange
performance problem with CouchDB; mainly affecting couchdb views.

About my Couch setup, I have one central couch instance and around 5
to 15 other instances replicating documents to it. In addition to the
replication, each of those other couch instances are also running a
service that posts documents to the central one via '_bulk_docs' API.
It's important to note that this model is deployed in production for
many years now.

What started to happen is that the indexing of the views became very
very slow, like < 1k changes within 10min. Making GET calls to the
views (either with or without reduce function), I also see a poor
response rate (a few tens kilobytes, either remotely or localhost).

Has anyone ever faced such slowness with CouchDB (views)? Would you
have any recommendations on where I should start looking and tests to
be performed? I have already ruled out problems with the virtual
machine and the hypervisor (load is normal for months). I have also
already recreated the views from scratch; recreated the database from
scratch (dumping deleted docs). I have also created a view to see
whether there were any large documents, and the biggest one is only
.5MB.

When I replicated the database from scratch today, CouchDB indexed
around 1.5M docs in an hour or so; while it's been the last 2h
indexing 26k changes to the database...

Any help or pointers would be very much appreciated here.
Thanks,
Alan.

Re: Sudden very slow indexing of the views

[Alan Malta <al...@gmail.com>]

Hi all,

I just wanted to update this thread to say that I _sort of_ found what
was causing the overall
slowness in CouchDB.
I have found in the frontend logs that we had one single client making
150 concurrent requests
to our service, each of those requests can map to 1 or many (~20) GET
couch views. This, in
addition to the high number of documents (~3M), again in addition to
all the other clients as well,
was causing couch to not perform well its tasks.

Thanks to all of you that helped me investigating this issue.
Best,
Alan.

On Sun, May 24, 2020 at 8:16 PM Alan Malta <al...@gmail.com> wrote:
>
> Hi Joan, all,
>
> prometheus node metrics suggests a memory consumption of ~90% (around
> 55GB), however I have two other CouchDB instances
> - in different nodes - that have the same memory pressure, and they
> work just fine. Load of the node is pretty low, usually between 2
> and 4 (for a 16VCPUs node). About swapping, it shouldn't be a problem
> because those nodes don't really have a swap partition (?).
>
> On a slightly different subject. I read that separating couch views
> into different design docs provide a boost in performance, because
> this forks a different process for each design doc. Is it also true if
> all the views in a given database/ddoc are written in erlang instead
> of javascript?
>
> Thank you very much.
> Alan.
>
> On Fri, May 22, 2020 at 12:57 AM Joan Touzet <wo...@apache.org> wrote:
> >
> > Hi Alan,
> >
> > On 2020-05-21 18:41, Alan Malta wrote:
> > > Joan, indeed I had around 60 documents with conflicts. I managed to fix
> > > them and I no longer see any conflicts. Low quality performance remains
> > > though.
> >
> > Double-check that you're not running out of memory (swapping to disk) or
> > CPU (lots of process churn). The hypervisor may not be telling you what
> > you need to know here - try an in-guest tool like top, atop, or s-tui.
> >
> > -Joan

Re: Sudden very slow indexing of the views

[Alan Malta <al...@gmail.com>]

Hi Joan, all,

prometheus node metrics suggests a memory consumption of ~90% (around
55GB), however I have two other CouchDB instances
- in different nodes - that have the same memory pressure, and they
work just fine. Load of the node is pretty low, usually between 2
and 4 (for a 16VCPUs node). About swapping, it shouldn't be a problem
because those nodes don't really have a swap partition (?).

On a slightly different subject. I read that separating couch views
into different design docs provide a boost in performance, because
this forks a different process for each design doc. Is it also true if
all the views in a given database/ddoc are written in erlang instead
of javascript?

Thank you very much.
Alan.

On Fri, May 22, 2020 at 12:57 AM Joan Touzet <wo...@apache.org> wrote:
>
> Hi Alan,
>
> On 2020-05-21 18:41, Alan Malta wrote:
> > Joan, indeed I had around 60 documents with conflicts. I managed to fix
> > them and I no longer see any conflicts. Low quality performance remains
> > though.
>
> Double-check that you're not running out of memory (swapping to disk) or
> CPU (lots of process churn). The hypervisor may not be telling you what
> you need to know here - try an in-guest tool like top, atop, or s-tui.
>
> -Joan

Re: Sudden very slow indexing of the views

[Joan Touzet <wo...@apache.org>]

Hi Alan,

On 2020-05-21 18:41, Alan Malta wrote:
> Joan, indeed I had around 60 documents with conflicts. I managed to fix
> them and I no longer see any conflicts. Low quality performance remains
> though.

Double-check that you're not running out of memory (swapping to disk) or 
CPU (lots of process churn). The hypervisor may not be telling you what 
you need to know here - try an in-guest tool like top, atop, or s-tui.

-Joan

Re: Sudden very slow indexing of the views

[Alan Malta <al...@gmail.com>]

Hi Ermouth, Joan,

thanks for suggesting this. It was straight forward to install it,
but in the end I didn't manage to visualize any information because
the user/role I was mapped to didn't have enough privileges. I tried
logging out too, but it doesn't work because the user is taken from
the grid certificate provided by the client (something peculiar in my setup).

Joan, indeed I had around 60 documents with conflicts. I managed to fix
them and I no longer see any conflicts. Low quality performance remains
though.

After reading further about it, it could be that the mapReduce functions are
slowing everything down, as suggested in this section:
https://docs.couchdb.org/en/1.6.1/maintenance/performance.html#builtin-reduce-functions

I will learn myself a bit more on this, and check whether anything can be done
in native erlang.

Thanks for your help so far.
Alan.

On Wed, May 20, 2020 at 10:57 PM ermouth <er...@gmail.com> wrote:
>
> Alan,
>
> latest version of Photon, an alternative admin panel for Couch, shows list
> of ddocs with info about data size and disk size. When ddoc indices files
> size is much larger than data size, you probably need to compact
> viewindices of the ddoc. You can select several design docs at a time, and
> compact their indices in a turn just clicking [Compact...] btn. Hope it
> might help.
>
> Btw, Photon UX is very close to Futon.
>
> https://github.com/ermouth/couch-photon
>
> ermouth
>
>
> ср, 20 мая 2020 г. в 17:03, Alan Malta <al...@gmail.com>:
>
> > Hi everyone,
> >
> > it's been more than a week that I have been debugging a strange
> > performance problem with CouchDB; mainly affecting couchdb views.
> >
> > About my Couch setup, I have one central couch instance and around 5
> > to 15 other instances replicating documents to it. In addition to the
> > replication, each of those other couch instances are also running a
> > service that posts documents to the central one via '_bulk_docs' API.
> > It's important to note that this model is deployed in production for
> > many years now.
> >
> > What started to happen is that the indexing of the views became very
> > very slow, like < 1k changes within 10min. Making GET calls to the
> > views (either with or without reduce function), I also see a poor
> > response rate (a few tens kilobytes, either remotely or localhost).
> >
> > Has anyone ever faced such slowness with CouchDB (views)? Would you
> > have any recommendations on where I should start looking and tests to
> > be performed? I have already ruled out problems with the virtual
> > machine and the hypervisor (load is normal for months). I have also
> > already recreated the views from scratch; recreated the database from
> > scratch (dumping deleted docs). I have also created a view to see
> > whether there were any large documents, and the biggest one is only
> > .5MB.
> >
> > When I replicated the database from scratch today, CouchDB indexed
> > around 1.5M docs in an hour or so; while it's been the last 2h
> > indexing 26k changes to the database...
> >
> > Any help or pointers would be very much appreciated here.
> > Thanks,
> > Alan.
> >

Re: Sudden very slow indexing of the views

[ermouth <er...@gmail.com>]

Alan,

latest version of Photon, an alternative admin panel for Couch, shows list
of ddocs with info about data size and disk size. When ddoc indices files
size is much larger than data size, you probably need to compact
viewindices of the ddoc. You can select several design docs at a time, and
compact their indices in a turn just clicking [Compact...] btn. Hope it
might help.

Btw, Photon UX is very close to Futon.

https://github.com/ermouth/couch-photon

ermouth


ср, 20 мая 2020 г. в 17:03, Alan Malta <al...@gmail.com>:

> Hi everyone,
>
> it's been more than a week that I have been debugging a strange
> performance problem with CouchDB; mainly affecting couchdb views.
>
> About my Couch setup, I have one central couch instance and around 5
> to 15 other instances replicating documents to it. In addition to the
> replication, each of those other couch instances are also running a
> service that posts documents to the central one via '_bulk_docs' API.
> It's important to note that this model is deployed in production for
> many years now.
>
> What started to happen is that the indexing of the views became very
> very slow, like < 1k changes within 10min. Making GET calls to the
> views (either with or without reduce function), I also see a poor
> response rate (a few tens kilobytes, either remotely or localhost).
>
> Has anyone ever faced such slowness with CouchDB (views)? Would you
> have any recommendations on where I should start looking and tests to
> be performed? I have already ruled out problems with the virtual
> machine and the hypervisor (load is normal for months). I have also
> already recreated the views from scratch; recreated the database from
> scratch (dumping deleted docs). I have also created a view to see
> whether there were any large documents, and the biggest one is only
> .5MB.
>
> When I replicated the database from scratch today, CouchDB indexed
> around 1.5M docs in an hour or so; while it's been the last 2h
> indexing 26k changes to the database...
>
> Any help or pointers would be very much appreciated here.
> Thanks,
> Alan.
>

Re: Sudden very slow indexing of the views

[Alan Malta <al...@gmail.com>]

Hi Joan, Robert,

I appreciate your comments, thanks! Answers inline:
> * What version of CouchDB?
Unfortunately we are still stuck with 1.6.1

> * Do you regularly compact your databases?
Probably in a daily bases for both the databases and the views. We
started using the compact_daemon
as of a couple of months ago, with configs like:
"""
[compactions]
_default = [{db_fragmentation, "70%"}, {view_fragmentation, "60%"},
{from, "20:00"}, {to, "05:00"}]

[compaction_daemon]
check_interval = 3600
min_file_size = 209715200
"""

> * Have you looked for conflicted documents recently?
Not yet. Can you please remind me whether there is any straight
forward way to look for conflicts? Or
do I need to create a view for it?

> and note that view compaction is a separate operation to database compaction, and is necessary for performance.
Yes, views are regularly compacted as well.

Early this morning we had 3M documents, and I thought it could somehow
be related to the total amount of docs.
Given that all that data is temporary, and most of it was still
available on the other couchdb instances, we dumped the
database and views. Final result was around 1.5M docs getting
replicated within an hour or so, views took slightly longer
to build (1.5h). Two hours late, we got hit by the super slow views
indexing and low rate view response (kB/s).

I read about separating the views into multiple design docs, which - I
guess - is going to be the next thing I will try.

One more note, all those views are written in erlang, and the config is:
[native_query_servers]
erlang = {couch_native_process, start_link, []}

so I'd expect the best performance possible in such setup.

Thanks,
Alan.

On Wed, May 20, 2020 at 7:25 PM Robert Samuel Newson <rn...@apache.org> wrote:
>
> and note that view compaction is a separate operation to database compaction, and is necessary for performance.
>
> in 3.0 both of these types of compaction are automated.
>
> B.
>
> > On 20 May 2020, at 18:23, Joan Touzet <wo...@apache.org> wrote:
> >
> > HI Alan,
> >
> > * What version of CouchDB?
> > * Do you regularly compact your databases?
> > * Have you looked for conflicted documents recently?
> >
> > -Joan
> >
> > On 2020-05-20 10:02, Alan Malta wrote:
> >> Hi everyone,
> >> it's been more than a week that I have been debugging a strange
> >> performance problem with CouchDB; mainly affecting couchdb views.
> >> About my Couch setup, I have one central couch instance and around 5
> >> to 15 other instances replicating documents to it. In addition to the
> >> replication, each of those other couch instances are also running a
> >> service that posts documents to the central one via '_bulk_docs' API.
> >> It's important to note that this model is deployed in production for
> >> many years now.
> >> What started to happen is that the indexing of the views became very
> >> very slow, like < 1k changes within 10min. Making GET calls to the
> >> views (either with or without reduce function), I also see a poor
> >> response rate (a few tens kilobytes, either remotely or localhost).
> >> Has anyone ever faced such slowness with CouchDB (views)? Would you
> >> have any recommendations on where I should start looking and tests to
> >> be performed? I have already ruled out problems with the virtual
> >> machine and the hypervisor (load is normal for months). I have also
> >> already recreated the views from scratch; recreated the database from
> >> scratch (dumping deleted docs). I have also created a view to see
> >> whether there were any large documents, and the biggest one is only
> >> .5MB.
> >> When I replicated the database from scratch today, CouchDB indexed
> >> around 1.5M docs in an hour or so; while it's been the last 2h
> >> indexing 26k changes to the database...
> >> Any help or pointers would be very much appreciated here.
> >> Thanks,
> >> Alan.
>

Re: Sudden very slow indexing of the views

[Robert Samuel Newson <rn...@apache.org>]

and note that view compaction is a separate operation to database compaction, and is necessary for performance.

in 3.0 both of these types of compaction are automated.

B.

> On 20 May 2020, at 18:23, Joan Touzet <wo...@apache.org> wrote:
> 
> HI Alan,
> 
> * What version of CouchDB?
> * Do you regularly compact your databases?
> * Have you looked for conflicted documents recently?
> 
> -Joan
> 
> On 2020-05-20 10:02, Alan Malta wrote:
>> Hi everyone,
>> it's been more than a week that I have been debugging a strange
>> performance problem with CouchDB; mainly affecting couchdb views.
>> About my Couch setup, I have one central couch instance and around 5
>> to 15 other instances replicating documents to it. In addition to the
>> replication, each of those other couch instances are also running a
>> service that posts documents to the central one via '_bulk_docs' API.
>> It's important to note that this model is deployed in production for
>> many years now.
>> What started to happen is that the indexing of the views became very
>> very slow, like < 1k changes within 10min. Making GET calls to the
>> views (either with or without reduce function), I also see a poor
>> response rate (a few tens kilobytes, either remotely or localhost).
>> Has anyone ever faced such slowness with CouchDB (views)? Would you
>> have any recommendations on where I should start looking and tests to
>> be performed? I have already ruled out problems with the virtual
>> machine and the hypervisor (load is normal for months). I have also
>> already recreated the views from scratch; recreated the database from
>> scratch (dumping deleted docs). I have also created a view to see
>> whether there were any large documents, and the biggest one is only
>> .5MB.
>> When I replicated the database from scratch today, CouchDB indexed
>> around 1.5M docs in an hour or so; while it's been the last 2h
>> indexing 26k changes to the database...
>> Any help or pointers would be very much appreciated here.
>> Thanks,
>> Alan.

Re: Sudden very slow indexing of the views

[Joan Touzet <wo...@apache.org>]

HI Alan,

* What version of CouchDB?
* Do you regularly compact your databases?
* Have you looked for conflicted documents recently?

-Joan

On 2020-05-20 10:02, Alan Malta wrote:
> Hi everyone,
> 
> it's been more than a week that I have been debugging a strange
> performance problem with CouchDB; mainly affecting couchdb views.
> 
> About my Couch setup, I have one central couch instance and around 5
> to 15 other instances replicating documents to it. In addition to the
> replication, each of those other couch instances are also running a
> service that posts documents to the central one via '_bulk_docs' API.
> It's important to note that this model is deployed in production for
> many years now.
> 
> What started to happen is that the indexing of the views became very
> very slow, like < 1k changes within 10min. Making GET calls to the
> views (either with or without reduce function), I also see a poor
> response rate (a few tens kilobytes, either remotely or localhost).
> 
> Has anyone ever faced such slowness with CouchDB (views)? Would you
> have any recommendations on where I should start looking and tests to
> be performed? I have already ruled out problems with the virtual
> machine and the hypervisor (load is normal for months). I have also
> already recreated the views from scratch; recreated the database from
> scratch (dumping deleted docs). I have also created a view to see
> whether there were any large documents, and the biggest one is only
> .5MB.
> 
> When I replicated the database from scratch today, CouchDB indexed
> around 1.5M docs in an hour or so; while it's been the last 2h
> indexing 26k changes to the database...
> 
> Any help or pointers would be very much appreciated here.
> Thanks,
> Alan.
>