Received: by 10.101.2.3 with SMTP id e3mr4476961ani.3.1352905135561;
Wed, 14 Nov 2012 06:58:55 -0800 (PST)
X-BeenThere: mongodb-user@googlegroups.com
Received: by 10.236.132.114 with SMTP id n78ls552338yhi.9.gmail; Wed, 14 Nov
2012 06:58:45 -0800 (PST)
Received: by 10.236.159.74 with SMTP id r50mr2783701yhk.19.1352905125975;
Wed, 14 Nov 2012 06:58:45 -0800 (PST)
Date: Wed, 14 Nov 2012 06:58:45 -0800 (PST)
From: Thamesmead <pietro...@gmail.com>
To: mongodb-user@googlegroups.com
Message-Id: <57c2df15-4eff-4d7f-96b9-e75160cd1224@googlegroups.com>
Subject: Need help speeding up spatial query
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="----=_Part_753_6601950.1352905125548"
------=_Part_753_6601950.1352905125548
Content-Type: multipart/alternative;
boundary="----=_Part_754_28025711.1352905125548"
------=_Part_754_28025711.1352905125548
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Hi,
I'm really struggling to get performance out of a spatial query. Perhaps I
am on the limit of what my hardware can do, but I have a feeling that there
is more that can be done.
I'd really appreciate some feedback here. I've been trying to figure out
how to speed this up for days to no avail, but then I am noob...
My documents contain:
{
"l" : [ -0.0013, 51.4779 ],
"d" : ISODate("2012-11-14T00:00:00Z"),
"w" : ObjectId("50a292d2abddc1bd89000001"),
"k" : 13
}
I need to query for specific w and k, ranges of d, and boxes (later
polygons) containing l.
I'm querying like so:
$ db.locs.find({ l: { $within: { $box: [[lon1, lat1], [lon2, lat2]] } }, w:
ObjectId("50a292d2abddc1bd89000001"), k: 13 }).explain()
{
"cursor" : "GeoBrowse-box",
"isMultiKey" : false,
"n" : 11156,
"nscannedObjects" : 11156,
"nscanned" : 11156,
"nscannedObjectsAllPlans" : 11156,
"nscannedAllPlans" : 11156,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 90,
"nChunkSkips" : 0,
"millis" : 1457,
"indexBounds" : {
"l" : [ ]
},
"lookedAt" : NumberLong(621631),
"matchesPerfd" : NumberLong(459159),
"objectsLoaded" : NumberLong(11156),
"pointsLoaded" : NumberLong(41),
"pointsSavedForYield" : NumberLong(0),
"pointsChangedOnYield" : NumberLong(0),
"pointsRemovedOnYield" : NumberLong(0)
}
Above it reports 1457 milliseconds. The logs show the queries sometimes
take up to 7 seconds. In any event, 1.4 seconds seems a little slow to me.
Is it?
There are 623k documents in the collection (I get only a tiny improvement
if I trim that down to 10% of the size):
$ db.locs.stats()
{
"ns" : "development.locs",
"count" : 623129,
"size" : 85332600,
"avgObjSize" : 136.94210990019724,
"storageSize" : 123936768,
"numExtents" : 11,
"nindexes" : 2,
"lastExtentSize" : 37625856,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 113409296,
"indexSizes" : {
"_id_" : 61516224,
"l_2d_d_1_w_1_k_1" : 51893072
},
"ok" : 1
}
These are my indexes:
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "development.locs",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"l" : "2d",
"d" : 1,
"w" : 1,
"k" : 1
},
"unique" : true,
"ns" : "development.locs",
"name" : "l_2d_d_1_w_1_k_1"
}
]
$ version()
version: 2.2.1
Thanks for any help.
------=_Part_754_28025711.1352905125548
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi,<div><br></div><div>I'm really struggling to get performance out of a sp=
atial query. Perhaps I am on the limit of what my hardware can do, but I ha=
ve a feeling that there is more that can be done.</div><div><br></div><div>=
I'd really appreciate some feedback here. I've been trying to figure out ho=
w to speed this up for days to no avail, but then I am noob...</div><div><b=
r></div><div>My documents contain:</div><div><br></div><div><div><font face=
=3D"courier new, monospace">{</font></div><div><font face=3D"courier new, m=
onospace"> "l" : [ -0.0013, 51.4779 ],</font></div><div><font face=3D=
"courier new, monospace"> "d" : ISODate("2012-11-14T00:00:00Z"),</fon=
t></div><div><font face=3D"courier new, monospace"> "w" : ObjectId("5=
0a292d2abddc1bd89000001"),</font></div><div><font face=3D"courier new, mono=
space"> "k" : 13</font></div><div><font face=3D"courier new, monospac=
e">}</font></div></div><div><br></div><div>I need to query for specific&nbs=
p;<font face=3D"courier new, monospace">w</font> and <font face=
=3D"courier new, monospace">k</font>, ranges of <font face=3D"courier =
new, monospace">d</font>, and boxes (later polygons) containing <font =
face=3D"courier new, monospace">l</font>.</div><div><br></div><div>I'm quer=
ying like so:</div><div><br></div><div><div><font face=3D"courier new, mono=
space">$ db.locs.find({ l: { $within: { $box: [[lon1, lat1], [lon2, lat2]] =
} }, w: ObjectId("50a292d2abddc1bd89000001"), k: 13 }).explain()</font></di=
v><div><font face=3D"courier new, monospace">{</font></div><div><font face=
=3D"courier new, monospace"> "cursor" : "GeoBrowse-box",</font></div>=
<div><font face=3D"courier new, monospace"> "isMultiKey" : false,</fo=
nt></div><div><font face=3D"courier new, monospace"> "n" : 11156,</fo=
nt></div><div><font face=3D"courier new, monospace"> "nscannedObjects=
" : 11156,</font></div><div><font face=3D"courier new, monospace"> "n=
scanned" : 11156,</font></div><div><font face=3D"courier new, monospace">&n=
bsp; "nscannedObjectsAllPlans" : 11156,</font></div><div><font face=3D"cour=
ier new, monospace"> "nscannedAllPlans" : 11156,</font></div><div><fo=
nt face=3D"courier new, monospace"> "scanAndOrder" : false,</font></d=
iv><div><font face=3D"courier new, monospace"> "indexOnly" : false,</=
font></div><div><font face=3D"courier new, monospace"> "nYields" : 90=
,</font></div><div><font face=3D"courier new, monospace"> "nChunkSkip=
s" : 0,</font></div><div><font face=3D"courier new, monospace"> "mill=
is" : 1457,</font></div><div><font face=3D"courier new, monospace"> "=
indexBounds" : {</font></div><div><font face=3D"courier new, monospace">&nb=
sp; "l" : [ ]</font></div><div><font face=3D"courier new, monospace"=
> },</font></div><div><font face=3D"courier new, monospace"> "l=
ookedAt" : NumberLong(621631),</font></div><div><font face=3D"courier new, =
monospace"> "matchesPerfd" : NumberLong(459159),</font></div><div><fo=
nt face=3D"courier new, monospace"> "objectsLoaded" : NumberLong(1115=
6),</font></div><div><font face=3D"courier new, monospace"> "pointsLo=
aded" : NumberLong(41),</font></div><div><font face=3D"courier new, monospa=
ce"> "pointsSavedForYield" : NumberLong(0),</font></div><div><font fa=
ce=3D"courier new, monospace"> "pointsChangedOnYield" : NumberLong(0)=
,</font></div><div><font face=3D"courier new, monospace"> "pointsRemo=
vedOnYield" : NumberLong(0)</font></div><div><font face=3D"courier new, mon=
ospace">}</font></div></div><div><br></div><div>Above it reports 1457 milli=
seconds. The logs show the queries sometimes take up to 7 seconds. In any e=
vent, 1.4 seconds seems a little slow to me. Is it?</div><div><br></div><di=
v>There are 623k documents in the collection (I get only a tiny improv=
ement if I trim that down to 10% of the size):</div><div><br></div><div><di=
v><font face=3D"courier new, monospace">$ db.locs.stats()</font></div><div>=
<font face=3D"courier new, monospace">{</font></div><div><font face=3D"cour=
ier new, monospace"> "ns" : "development.locs",</font></div><div><fon=
t face=3D"courier new, monospace"> "count" : 623129,</font></div><div=
><font face=3D"courier new, monospace"> "size" : 85332600,</font></di=
v><div><font face=3D"courier new, monospace"> "avgObjSize" : 136.9421=
0990019724,</font></div><div><font face=3D"courier new, monospace"> "=
storageSize" : 123936768,</font></div><div><font face=3D"courier new, monos=
pace"> "numExtents" : 11,</font></div><div><font face=3D"courier new,=
monospace"> "nindexes" : 2,</font></div><div><font face=3D"courier n=
ew, monospace"> "lastExtentSize" : 37625856,</font></div><div><font f=
ace=3D"courier new, monospace"> "paddingFactor" : 1,</font></div><div=
><font face=3D"courier new, monospace"> "systemFlags" : 1,</font></di=
v><div><font face=3D"courier new, monospace"> "userFlags" : 0,</font>=
</div><div><font face=3D"courier new, monospace"> "totalIndexSize" : =
113409296,</font></div><div><font face=3D"courier new, monospace"> "i=
ndexSizes" : {</font></div><div><font face=3D"courier new, monospace"> =
; "_id_" : 61516224,</font></div><div><font face=3D"courier new, mon=
ospace"> "l_2d_d_1_w_1_k_1" : 51893072</font></div><div><font =
face=3D"courier new, monospace"> },</font></div><div><font face=3D"co=
urier new, monospace"> "ok" : 1</font></div><div><font face=3D"courie=
r new, monospace">}</font></div></div><div><br></div><div>These are my inde=
xes:</div><div><br></div><div><div><font face=3D"courier new, monospace">[<=
/font></div><div><font face=3D"courier new, monospace"> {</font></div=
><div><font face=3D"courier new, monospace"> "v" : 1,</font></=
div><div><font face=3D"courier new, monospace"> "key" : {</fon=
t></div><div><font face=3D"courier new, monospace"> "_i=
d" : 1</font></div><div><font face=3D"courier new, monospace"> =
},</font></div><div><font face=3D"courier new, monospace"> "n=
s" : "development.locs",</font></div><div><font face=3D"courier new, monosp=
ace"> "name" : "_id_"</font></div><div><font face=3D"courier n=
ew, monospace"> },</font></div><div><font face=3D"courier new, monosp=
ace"> {</font></div><div><font face=3D"courier new, monospace"> =
"v" : 1,</font></div><div><font face=3D"courier new, monospace">&nb=
sp; "key" : {</font></div><div><font face=3D"courier new, monospace"=
> "l" : "2d",</font></div><div><font face=3D"courier ne=
w, monospace"> "d" : 1,</font></div><div><font face=3D"=
courier new, monospace"> "w" : 1,</font></div><div><fon=
t face=3D"courier new, monospace"> "k" : 1</font></div>=
<div><font face=3D"courier new, monospace"> },</font></div><di=
v><font face=3D"courier new, monospace"> "unique" : true,</fon=
t></div><div><font face=3D"courier new, monospace"> "ns" : "de=
velopment.locs",</font></div><div><font face=3D"courier new, monospace">&nb=
sp; "name" : "l_2d_d_1_w_1_k_1"</font></div><div><font face=3D"couri=
er new, monospace"> }</font></div><div><font face=3D"courier new, mon=
ospace">]</font></div></div><div><font face=3D"courier new, monospace"><br>=
</font></div><div><div style=3D"font-family: 'courier new', monospace;">$ v=
ersion()</div><div style=3D"font-family: 'courier new', monospace;">version=
: 2.2.1</div><div><font face=3D"arial, sans-serif"><br></font></div><div><f=
ont face=3D"arial, sans-serif">Thanks for any help.</font></div></div>
------=_Part_754_28025711.1352905125548--
------=_Part_753_6601950.1352905125548--
Message from discussion Need help speeding up spatial query
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |