Log file Viewer
#mongodb logs for Tuesday the 3rd of May, 2016
[00:21:36] <GothAlice> As was/is deeper authentication (LDAP-style) integration, which is a typically enterprise need. (And a hideous PITA to support.)
[00:29:56] <GothAlice> "You know that pre-signed host key you were using? Yeah… it's not so good any more." "But, but why?" "Reasons."
[00:41:36] <nicolas_FR> hi there. Maybe not the right place but I can't find help. Mongoose (?) problem I think with sub documents : http://pastebin.com/GLJhSHyd
[07:45:58] <zerOnepal> Hi there, I have a server running with a collection weigh 1TB and trending size, my application was designed that way, but I trying to trim down the un-necessary entries
[07:48:31] <Boomtime> hi zerOnepal, sounds like you have a use case problem - is there something specific you want help with?
[07:48:36] <zerOnepal> any recommendation on this, how do I safely delete old records without hampering live traffic, I don't have much time to figure this out and I can't afforord to add additional disk for temporary fix... My only choice is to trim the old datas, I am thinking of keeping latest 3months data only
[07:50:36] <Boomtime> zerOnepal: each deletion will add load, these are write operations, albeit small ones, but a large batch will have an effect - if you want to reduce this then delete in small batches (a fewhundred at a time perhaps?) with a writeConcern of majority and have a small sleep between batches (say, 1 second?)
[07:55:16] <Boomtime> clarify which thing you're laughing at; the shell suggestion, or the idea you have an index
[07:55:55] <zerOnepal> shell thing Boomtime, :D.. I wish there exists something like percona tools for such alterations...
[07:57:28] <Boomtime> TTL is extremely useful so long as not many documents are _initially_ to be deleted
[07:57:58] <Boomtime> if all the documents in the collection are more recent than 3 months, then by all means, create a TTL index for an expiry of 3 months
[07:58:23] <kurushiyama> Boomtime: Say the date field is set to the insert date, and you want to have the doc deleted after 3 months, so you'd set it to 3*30*24*60*60? As per the initial deletion: It is a background process, and we are talking of a fairly recent MongoDB ;)
[07:58:41] <Boomtime> if you have a large collection, with documents spread across a year, do not create a TTL with an expiry of 3 months,because that just means the first run needs to delete 3/4 of the collection in the first pass
[07:59:42] <kurushiyama> zerOnepal: Which version and (if applicaple) which storage engine do you use?
[08:05:58] <zerOnepal> so you guyz recommend/use built in mongo shell to interface/alter/trim mongo records ??
[08:15:26] <Boomtime> zerOnepal: you have provided very little information about your actual use case - collection size, but not document counts, or how many you expect to be deleted, or anything about the application - we'd use the shell for most simple one-off scripts because it's easy
[08:16:25] <Boomtime> a simple loop that deletes a certain number of matching docs out of a progressive cursor, with a sleep between is about 4 lines of javascript - and it's efficient - what do you want?
[08:16:35] <zerOnepal> its about 1TB weigh collection, that I get using mongo-hacker awesome prompt, and I can't afford to calculate the count the records :(
[08:18:18] <Boomtime> mongo-hacker is a shell extension - with or without it won't change the 4 lines of javascript - but the impact of those 4 lines will change radically depending on your circumstances
[08:20:15] <zerOnepal> my only available low hanging fruit is the "created_at" field on that collection and
[08:23:06] <Boomtime> are you running it with parameters? - when we say "count()" we mean count with no parameters
[08:23:16] <kurushiyama> Well, give it a sec or 2 for returning. 1TB may be a hell lot of docs, and iirc, the way count works was changed a little.
[08:25:03] <zerOnepal> thanks all, we are 297 in room and I am still getting a company here in irc room
[08:26:35] <zerOnepal> kurushiyama it's db.col.count() results around 500413374 in few secs, I was wrong
[08:27:08] <kurushiyama> zerOnepal: Ok, that relieves me a bit. If it takes longer than a few secs, there is something weird going on.
[08:34:34] <zerOnepal> Hey, isn't there a way to delete in the background? Like when we build indexes?
[09:09:19] <kurushiyama> zerOnepal: Well, we have to find out a few things. I am not too sure wether you say "We can not have any impact" because of the paranoia DBA's usually have or wether your System is really at its limits and a delete operation would have noticeable or even critical impact on UX.
[13:44:41] <bros> Why does this query return 1 matching store and not 4: { '$elemMatch': { 'stores': { '_id': { '$in': [ObjectId(...), ObjectId(...), ObjectId(...), ObjectId(...)] } } } }, { 'stores.$': 1 }
[14:44:03] <zylo4747> I posted a question to https://groups.google.com/forum/#!forum/mongodb-user but it isn't showing up. Is there something I need to do for a post to become visible? I can't even find it now, it's just gone
[14:54:29] <Industrial> I see, and https://docs.mongodb.org/manual/tutorial/query-documents/#specify-or-conditions
[14:55:59] <Industrial> so `.find({ $or: [ { retailer_id: { $exists: false } }, { category_id: { $exists: false } }, ] })` :)
[15:28:59] <Justin_T> Hi all, is possible to add the result from one collection to another for print in a query?
[15:32:29] <zylo4747> I posted the issue on serverfault if any of you want to take a look and see if you can help me out http://serverfault.com/questions/774567/intermittent-crashes-of-a-mongodb-replica-set
[15:38:46] <saml_> db._User.find().forEach(function(u){ u.count = db.GeneralTask.count({userID: u._id}); printjson(u); });
[15:41:06] <Justin_T> thanks saml_, can I add a match there? because I need this: $match: { "isDone": { $ne: false }
[15:41:29] <saml_> db._User.find().forEach(function(u){ u.count = db.GeneralTask.count({userID: u._id, "isDone": { $ne: false }}); printjson(u); });
[15:55:38] <zylo4747> Do you know if db.repairDatabase() will tell me if corruption was found and fixed?
[16:27:38] <jokke> i'm measuring write performance and read performance for different schemas on a dockerized mongodb sharded cluster. i've noticed that at some point the performance drops radically and the cpu usage of the replication set members goes to a constant 100%. Any leads what might be causing this?
[16:31:47] <bros> Does elemMatch only return the first element and I need to use aggregate to get them all?
[16:32:55] <jokke> elemMatch doesn't return anything. It just matches documents where at least one element of an array field matches the quer(y|ies)
[18:50:21] <hardwire> Just have to treat them as different things. MongoDB CE and MongoDB Enterprise. One reason I'm not using InfluxDB right now is due to it recently becoming crippleware.
[18:52:54] <cheeser> tthe philosophy with such enterprise features in mongodb is to only do that to features needed by large enterprises. the more "run of the mill" features will be open and available. (*not an official statement. details subject to change without notice or consultation)
[18:54:19] <hardwire> I'm not sure what made influx remove clustering capabilities other than the need for cashmoneyz
[20:45:12] <jokke> i have a compound index as _id consisting of the fields sensor_id, panel_id and timestamp
[20:58:07] <quadHelix> How do I pull back all records using mongoexport? Here is my command: mongoexport --type=csv --host <ip>:<port> --db myorders --collection 57290da5-b67c-4b6b-b9bd-3fa1608e448c -f 'created,job_id,name,' -o myfile.csv
[21:17:17] <jfhbrook> hello! Looking for opinions on when to use _id for a property vs making a new property
[21:17:31] <jfhbrook> I have a scenario where most of my lookups are against a unique key, but that key is a string
[21:20:04] <hardwire> jfhbrook: one big consideration is how you plan to partition data for sharding, if ever.
[21:20:05] <jfhbrook> yeah, we do that in one place but my team never trusted the contractor that wrote that code
[21:20:18] <kurushiyama> jfhbrook: _id's are unique. So you can not have more than one doc per string.
[21:20:55] <hardwire> if you want to determine how information is stored against a partitioned cluster, you'd use your own key regardless.
[21:21:09] <jokke> cheeser: ah yeah sorry, i meant that the timestamp fields of the docs are 2 secs apart
[21:21:31] <jfhbrook> so times I wouldn't want to do this is if I can get in a scenario where I want to update the doc by changing this key (not going to happen ever)
[21:23:09] <jfhbrook> do you mean that I can't change the _id of a document after the fact? because if so that's desirable
[21:27:18] <kurushiyama> jokke: a) you can not change the _id, which you are trying to do, as far as I understood. b) You need to use an {_id:"hashed"} index, since most likely you have a monotonically increasing shard key with the use of a date field.
[21:29:24] <kurushiyama> jokke: https://docs.mongodb.org/manual/core/sharding-balancing/#migration-thresholds
[21:29:26] <jokke> but isn't chunk migration done only when it doesn't distribute evenly on it's own?
[21:31:45] <jokke> since it results in scatter gather queries for time range queries (which is the most common kind of query)
[21:31:52] <kurushiyama> jokke: Actually, "hashed" was invented for a monotonically increasing shard key.
[21:33:16] <kurushiyama> jokke: The thing is this: let us assume your _id consists of 2 arbitary strings plus a timestamp
[21:38:13] <kurushiyama> We could use _any_ combination, and it would be monotonically increasing from a lexicographic pov.
[21:40:11] <jokke> but i thought that choosing compound indexes allows the sharding algorithm to distribute for example docs with the first string being a into one shard and chunk it via timestamp
[21:40:20] <kurushiyama> It would still be "bigger" than a1000b, and b-docs would monotonically increase in their own right.
[21:41:28] <kurushiyama> jokke: Well, even if you do assign the key ranges manually, you still would be adding to the plus infinity shard only.
[21:41:56] <kurushiyama> jokke: "plus infinity" being a rather abstract thing here, but you get the picture.
[21:45:12] <kurushiyama> Ok, your example is a bit complicated. let us assume you assign a* -b* to shard1 and c*-d* to shard2. At some point, you have an imbalance due to simple variance. Clear so far?
[21:50:05] <kurushiyama> And it goes beyond that. A: you can not assign this way. you can assign from -inifinity to a certain value, and from that value to +infinity
[21:59:04] <jokke> no? :/ i don't understand the output at all. what's up with the -->> and Timestamp(x, y)
[22:03:54] <kurushiyama> jokke: Now have a (casual) look over the other key ranges of that database only
[22:08:14] <kurushiyama> jokke: Sort of. That more data was written to rs1 is quite obvious from the chunk distribution. Question is: Why?
[22:09:05] <kurushiyama> jokke: Now we know that a broader range of keys gets written to rs1. Why does this happen?
[22:12:01] <kurushiyama> Now, let us assume our shard key increases monotonically. Autoincrement, if you will
[22:12:20] <jokke> quick follow up question: should $minKey and $maxKey sort of define the "middle" of the sharded data?
[22:13:06] <kurushiyama> In our example $minKey literally means -infinity, in case we talk of singned ints
[22:13:48] <jokke> ah i misinterpreted your sentence. you meant "from $minkey to 10" and "from 11 to $maxKey"
[22:15:27] <kurushiyama> Now, we get to doc 100 of our rather large docs and the chunk on rs2 crosses the 32MB border and gets split.
[22:15:58] <kurushiyama> This happens a few times, until the chunk migration threshold is reached and the balancer moves _one_ chunk to rs1
[22:16:49] <jokke> but wouldn't that mean rs1 is always chunk migration threshold chunks "behind" rs2?
[22:18:48] <kurushiyama> A chunk can have max 64MB, otherwise it becomes jumbo and is not migrated any more
[22:19:31] <kurushiyama> Which is one of the reasons for an early split. Furthermore, there can only be ony, and exactly one chunk migration in a cluster at any given point in time.
[22:20:32] <kurushiyama> jokke: Which can become part of the problem when choosing a bad shard key, for obvious reasons.
[22:21:19] <kurushiyama> So, now you want to access the data you saved. How does mongos know where to get the doc?
[22:22:44] <kurushiyama> It gets the key ranges assigned to the individual shards and does a rather simple lexicographic comparison (single or compound does not matter much, as we have seen) to identify the shard the document lives on.
[22:24:06] <kurushiyama> Then mongos contacts that shard, fetches the data pretty much like you'd do in a standalone instance (iirc, _exactly_ the same way) and gets you the data.
[22:24:46] <kurushiyama> That last part "gets you the data" can become pretty tricky, but for now that is sufficient.
[22:25:14] <kurushiyama> The thing is that we are talking of (more or less) contiguous ranges of keys assigned to each shard.
[22:27:38] <kurushiyama> So, and our example made it pretty obvious why you do want to prevent anything monotonically increasing at all costs.
[22:30:26] <jokke> the most common query would be "get me all data from panel X in the time range from Y to Z"
[22:31:47] <kurushiyama> jokke: Well, the alternative is a constantly overloaded shard. That does not much for performance. In worst case scenarios I have seen, there were 8 shards (rather big ones), 7 of them idling around, 1 of them totally overloaded up to a pint where the application came to a standstill.
[22:34:33] <kurushiyama> jokke: I know that is a stupid question. But you really have to ask yourself.
[22:35:10] <jokke> hm i don't know. even the write performance was way better without the hashed _id
[22:35:37] <kurushiyama> jokke: The actual writes? Sure. Taking balancing into account? I doubt that.
[22:36:30] <jokke> from the docs: Generally, choose shard keys that have both high cardinality and will distribute write operations across the entire cluster.
[22:40:44] <jokke> kurushiyama: i need to get some sleep.. i'll see you tomorrow. Thanks for being so patient and helping me out like that. I really appreciate it, knowing you're usually getting paid for this stuff.
[22:44:01] <kurushiyama> jokke: You might want to dig into "tag based sharding". Just an idea. havent elaborated yet
[22:46:37] <kurushiyama> nah, we still are monotonically increasing. Can you pastebin a sample doc quickly? So maybe I can come up with something.
[22:50:25] <jokke> ok, so this will be the schema i'm most likely going with based on the performance and the way the data comes in: https://p.jreinert.com/nzKb/
[22:53:19] <jokke> i won't go with a flat data model :) i know we had this discussion a few times already. The data comes in packets though, per panel. so it's way faster to store them inside a "panel-document" (since they're most likely being queried together too)
[22:54:37] <jokke> the doc i just pasted is basically the closest representation of how the data comes in
[22:56:22] <kurushiyama> jokke: Well, it would have the advantage of distributed load (no problems with monotonically increasing shard key). If panels do not have overlapping datasources, we could eliminate scatter/gather by tag based sharding.
[22:58:48] <kurushiyama> jokke: Ok, but I guess we can overcome that. Actually, we could use the panel's ID/name/distinctive whatever for that.
[22:59:59] <kurushiyama> jokke: even better: if the panel ID distincts them, we might use the panel ID as a shard key, halfway even distribution taken for granted. Scatter/gather eliminated altogether without tag based sharding.
[23:01:09] <kurushiyama> And if the distribution is uneven, we still could resort to tag based sharding.
[23:04:12] <jokke> gosh i feel so stupid :/ i thought something like a timestamp would be needed for the splitting to work...
[23:05:35] <jokke> just using _id.panel_id seems like hammering everything doc with the panel_id aaaa to aabb into one chunk
[23:05:45] <kurushiyama> Lets check. Distributed writes, no scatter/gather, an index we need anyway. At the expense of 8 bytes/record? Great deal in my book.
[23:08:09] <kurushiyama> Now, we simply tell MongoDB beforehand to split at 500 and put the panels from 0-500 on rs1 and the rest on rs2
[23:08:44] <kurushiyama> Since our shard key is panelID, and the data is even for each panel, everything is fine.
[23:09:21] <kurushiyama> jokke: You can always do that, but then the shard key must have a high cardinality.
[23:11:20] <kurushiyama> Hence, again: flat data model, with panel ID in the docs, sharded by said panelID, pre-split or even tagged.
[23:11:45] <jokke> wouldn't using something like the date + hour of a timestamp hashed with some modulo be a good shard key too?
[23:14:02] <jokke> it makes sure all docs within an hour on the same day gets written to the same chunk
[23:17:26] <kurushiyama> jokke: You can control the number of values your shard key can have. But so does the panelId, no?
[23:20:25] <kurushiyama> jokke: So, my solution eliminates scatter/gather and distributes writes evenly while giving good performance at the expense of like 20 bytes (12 ObjectId + 8 additional TS) record.
[23:21:56] <kurushiyama> jokke: Yeah, you'd need a TS for each of the flat docs to correlate for the panel. If a panel itself is time based, even that TS gets eliminated.
[23:23:15] <kurushiyama> jokke: either way, we could correlate, either by time or panelId. As said: your turn ;)
[23:25:16] <kurushiyama> jokke: So { _id: ObjectId(), panelId:yourPanelId, datasource:ds, r:1,i:1.5}, sharded by panelId
[23:28:19] <jokke> as i said. the data arrives packaged together. so for a panel with 50 datasources i'd have 50 inserts with the flat data model as opposed to 1 insert with my "flat_panel" model
[23:28:22] <kurushiyama> Well, you can not have the cake and eat it. You have to make tradeoffs. And as proven, updates are slower. Your call. But accepting a foul compromise when it comes to sharding, you are doomed.
[23:30:03] <kurushiyama> Well enough. But still, you have the sharding problem. You may be able to use something to get the distribution done, but that would be an index either way. Oh, and you might want to check bulk inserts ;)
[23:31:25] <kurushiyama> Bulk inserts for the datamodel above, sharding based on panelId. Note the details (you should have about 1M sample docs).
[23:32:46] <kurushiyama> 20/13 (iirc) with a lot of failed migrations because it was stopped is not what I call nice ;)
[23:37:59] <kurushiyama> jokke: I _think_ it happens when a chunk is supposed to be migrated, but changes after the chunk started to be migrated. Or sth like that. It is a symptom I have regularly seen at... You can guess three times ;)
[23:40:58] <jokke> wow.. i just killed and set up the cluster again and ran _just_ the insert benchmark for the flat_panel model
[23:45:35] <kurushiyama> There is something strange. But let us have a look then. You know what we need. If you can, please insert like a shitload of documents.
[23:46:02] <jokke> on the other hand i'm doing stupid queries... i'm getting the _latest_ data sample