Performance severely impacted by TTL index

[Colin M]

I wrote a simple Zend_Cache backend using Mongo that supports tags and automatic expiration. It hasn't seen much use but one user started testing it as a replacement for Redis and found some serious performance issues that went away when he removed the tags and TTL indexes. Please see here for background: https://github.com/colinmollenhour/Cm_Cache_Backend_Mongo/issues/3

The performance spikes look like they occur with the TTL thread and the log is filled with "key seems to have moved in the index, refinding" errors. The backend code is very simple:

* load: findOne by _id

* save: upsert

* clean: remove by tags:$in:[..]

Why is the TTL thread causing such a huge performance hit? I can't imagine that a large number of keys were expiring in a simple 5 minute test..

Thanks,

Colin

[Asya Kamsky]

Hi Colin:

Actual logs would be helpful to diagnose what the actual cause it - actually most helpful would be section of logs spanning the performance degradation combined with "mongostat" output covering the same time period.

Is that something you can provide?

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/da0e4dd8-3744-4b1a-8acb-59de80c0ca61%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

{ "name" : "Asya Kamsky",
  "place" : [ "New York", "Palo Alto", "Everywhere else" ],
  "email" : "as...@mongodb.com",
  "blog" : "http://www.askasya.com/",
  "twitter": "@asya999" }

[Colin Mollenhour]

The user did more testing and it is the large number of tags that is the issue. With indexes the performance is terrible, without the performance is pretty good.

You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/wXnA4OE5fC0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAOe6dJBw0UxU_%3DdKyQm1E8mj%3DoQ_ZYy8dbfp%2B62TAEWwCR218A%40mail.gmail.com.

[Tim Callaghan]

Colin,

Keep in mind that removing data is at least as hard as inserting it, often times harder.

So if you benchmark a given configuration/workload and can achieve 10000 inserts per second on a collection that contains a TTL index, expect the steady state over time to be half that number as the TTL deletions begin. The more secondary indexes the collection has, the worse the impact.

One way to avoid this penalty is to use multiple collections, each one containing a set "time slice" of your data. The benefit is that you can just drop the "January 1 - 7" collection and delete a weeks worth of data. A huge downside is the complexity it adds to your application, the additional operational overhead of creating/dropping collections, and you'll need to query multiple collections if you run queries that span them.

Good luck!

-Tim

[Colin M]

Thanks for the insight, Tim. It seems though that the issue is the tags index and not the TTL index (user performed new tests to confirm). What I don't understand is why the performance is *so* bad. I mean I'm pretty sure that a two-table MySQL setup in the exact same test would not have performance that is this bad. I would guess that even MongoDb with two separate collections would probably perform much better, it just wouldn't be atomic. Is MongoDb's multi-key index really that unoptimized? Can/will it ever be "fixed"?

Thanks again,

Colin

[Tim Callaghan]

By "tags" I assume you mean an indexed array in the documents themselves, correct?

Think of it this way. When the TTL worker wakes up and looks for documents to delete (since they are now expired) there is an index on the tags that also needs to be maintained. If you have a lot more data than RAM then each tag removal could require an IO. These IOs add up and really hurt performance.

-Tim

[Asya Kamsky]

What's the general data pattern as far as average number of tags per document?

The reason I ask is that with regular indexes, the maintenance is
proportional to the number of documents being inserted/deleted.
However, with multikey indexes it is proportional to the number of
entries in the array of each document.

So if you are removing document:

{ _id:1, tags: [ "foo", "bar" ] } then two indexes need to be updated,
one entry in _id index and two entries in "tags" index.

If the document is

{ _id:1, tags: [ "foo1", "foo2", "foo3", ... ] } and there a few
hundred entries in the tags array, that's a few hundred entries that
need to be updated when this document is inserted, deleted (or updated
in a way that requires moving it).

This is bad for efficient performance - this is why we recommend that
you only index arrays whose size is naturally bounded (and to
something reasonable).

Asya
P.S. I wrote a little on the subject last year
http://askasya.com/post/largeembeddedarrays

> https://groups.google.com/d/msgid/mongodb-user/0812ae37-6100-411a-9496-181edaa6094c%40googlegroups.com.