Can anyone explain 5.8 million DB operations?

[Sarang]

Hello,

I have an application that was consuming around 11 cents/day of datastore read operations. Here is  a snapshot from historical billing:

http://gyazo.com/ee441da7686ddff4f3648383e8d7418c

Today, I wanted to make a small change to a table with around 3000 entries. I wanted to add another bool field. So I looped over each entry, read each row, added the boolean field, and saved. I was shocked to note that my app went over quota and my loop stopped in between. When I looked at the dashboard, it is showing me 5.8 million datastore read operations. See the snapshot:

http://gyazo.com/2dc918ac61c3af295378b8c1a54de77a 

This is absolutely crazy. Can anyone from the App Engine team please explain this?

My setup is Django non-rel. The model had only a few fields and I am not doing anything special in save() method. 

Regards,

Sarang

[Kaan Soral]

Considering you use terms like "table" and "row" I think you made a bug that caused your code to enter an infinite-task loop :)
Joke aside, with the same logic I assume you didn't use tasks and this update was done on a user-facing request with 10s deadline?
So is there really a bug from GAE-side or is it possible that there are >3000 entities or extreme indexes or a bug from your side?

[Tom Newton]

Can we see the code you used to change your table? 

Also, how many properties do you have on the entity you were modifying, and are they all indexed?

T

[Jeff Schnitzer]

Do you have some sort of loop that read forwards N entries to get to
each value? Ie, each iteration skipped forwards N-1 to get to N.
That would produce N^2 reads, or about 9 million.

Jeff

> --
> You received this message because you are subscribed to the Google Groups
> "Google App Engine" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/google-appengine/-/p9YMSDN5AEAJ.
> To post to this group, send email to google-a...@googlegroups.com.
> To unsubscribe from this group, send email to
> google-appengi...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/google-appengine?hl=en.

[Sarang]

Thank you for the replies. I will try to answer all of your questions.

@Kaan, yeah, wrong terms to use. But thats legacy SQL mindset in terminology :-)

I was doing the update via a remote shell. I used the following code to update:

for ph in phs:                                          

    ph.use_cc = True

    ph.save()

    

The Phone kind has 12 properties. I looked into my index.yaml and there is no index with this kind mentioned in there.

These are the stats on this particular Kind. So there were only 1616 entities I was trying to update.

	Entites	Built-in Indexes	Composite Indexes
Total Size:	697 KBytes	5 MBytes	0 Bytes
Entry count:	1,616	43,642	0
Average Size:	441 Bytes	127 Bytes

Any pointers would be highly appreciated. If anyone can replicate this on their end, that would also be really helpful. 

Regards,

Sarang

On Wednesday, July 4, 2012 1:51:20 AM UTC+5:30, Jeff Schnitzer wrote:

Do you have some sort of loop that read forwards N entries to get to
each value?  Ie, each iteration skipped forwards N-1 to get to N.
That would produce N^2 reads, or about 9 million.

Jeff

On Tue, Jul 3, 2012 at 11:53 AM, Sarang <sarang@....net> wrote:
> Hello,
>
> I have an application that was consuming around 11 cents/day of datastore
> read operations. Here is  a snapshot from historical billing:
>
> http://gyazo.com/ee441da7686ddff4f3648383e8d7418c
>
> Today, I wanted to make a small change to a table with around 3000 entries.
> I wanted to add another bool field. So I looped over each entry, read each
> row, added the boolean field, and saved. I was shocked to note that my app
> went over quota and my loop stopped in between. When I looked at the
> dashboard, it is showing me 5.8 million datastore read operations. See the
> snapshot:
>
> http://gyazo.com/2dc918ac61c3af295378b8c1a54de77a
>
> This is absolutely crazy. Can anyone from the App Engine team please explain
> this?
>
> My setup is Django non-rel. The model had only a few fields and I am not
> doing anything special in save() method.
>
> Regards,
> Sarang
>
> --
> You received this message because you are subscribed to the Google Groups
> "Google App Engine" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/google-appengine/-/p9YMSDN5AEAJ.

> To post to this group, send email to google-appengine@googlegroups.com.

> To unsubscribe from this group, send email to

> google-appengine+unsubscribe@googlegroups.com.

[Sarang]

Can anyone from Google care to explain this and tell us what is the "right" way to make such changes? Otherwise it is very difficult to justify using GAE for us.

Thanks.

Sarang

[Barry Hunter]

On Thu, Jul 5, 2012 at 1:40 PM, Sarang <sar...@mycontactid.net> wrote:

 

tell us what is the "right" way to make such changes?

Well the datastore is in general 'schemaless' - so you should be able to *avoid* having to make such changes. 

It doesnt matter that some entities have a different schema to others. Drop the SQL mindset ;)

So you just add the extra field, the next time you happen to modify that particular entity anyway. So the 'migration' happens, very slowly over time. Costs very little extra, because the write would of happened anyway. 

 

Each entity could have a 'version' value, which the code notes is 'outdated' and then updates it automatically. 

[Sarang]

Thanks Barry. That is indeed what we have done so far. But this scenario is different and we need to make the update. 

For what you are suggesting, we have to change the code in many different locations to catch the missing property and update. In some of these places, we may not be even in a position to make the update! Another problem could be that if I am accessing 100s of elements, and I have to update them all, it could lead to slow response for the activity and may even exceed 60s limit.

Also, if we are not re-writing those elements inside our code, then we will have to bear the same cost as with my method? You are assuming we will write the element anyway and hence defray the cost.

Bottom line is, even if we had to write those 1600 numbers, why should it cost 5.8 million OPs? If we signup 1600 users in a day, each with one entry in the DB, then we'll use up all our quota!

Regards,

Sarang

[Barry Hunter]

On Thu, Jul 5, 2012 at 2:09 PM, Sarang <sar...@mycontactid.net> wrote:
> Thanks Barry. That is indeed what we have done so far. But this scenario is
> different and we need to make the update.
>
> For what you are suggesting, we have to change the code in many different
> locations to catch the missing property and update. In some of these places,
> we may not be even in a position to make the update! Another problem could
> be that if I am accessing 100s of elements, and I have to update them all,
> it could lead to slow response for the activity and may even exceed 60s
> limit.

No. You only 'update' the the entity, when you would be writing it anyway.

>
> Also, if we are not re-writing those elements inside our code, then we will
> have to bear the same cost as with my method? You are assuming we will write
> the element anyway and hence defray the cost.

Exactly. You only do it when you are writing anyway.

>
> Bottom line is, even if we had to write those 1600 numbers, why should it
> cost 5.8 million OPs? If we signup 1600 users in a day, each with one entry
> in the DB, then we'll use up all our quota!

Your 5.8M was READ operations anyway. It wasnt't the writing that cost as such.



The other thing, it might even be a red-herring that the the bulk
update was the issue. Looking at your screenshot, you had a much
higher request rate for the prev 3 hours.

So maybe all those extra requests where the culprit, and used all your quota.

Did you look what those requests did?

>
> Regards,
> Sarang
>
>
> On Thursday, July 5, 2012 6:23:49 PM UTC+5:30, barryhunter wrote:
>>
>>
>>
>> On Thu, Jul 5, 2012 at 1:40 PM, Sarang <sar...@mycontactid.net> wrote:
>>
>>>
>>> tell us what is the "right" way to make such changes?
>>
>>
>> Well the datastore is in general 'schemaless' - so you should be able to
>> *avoid* having to make such changes.
>>
>> It doesnt matter that some entities have a different schema to others.
>> Drop the SQL mindset ;)
>>
>>
>> So you just add the extra field, the next time you happen to modify that
>> particular entity anyway. So the 'migration' happens, very slowly over time.
>> Costs very little extra, because the write would of happened anyway.
>>
>>
>> Each entity could have a 'version' value, which the code notes is
>> 'outdated' and then updates it automatically.
>>
>>

> --
> You received this message because you are subscribed to the Google Groups
> "Google App Engine" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/google-appengine/-/jMq4CRzcsjsJ.
>
> To post to this group, send email to google-a...@googlegroups.com.

> To unsubscribe from this group, send email to

> google-appengi...@googlegroups.com.

[Sarang]

Exactly. You only do it when you are writing anyway.

We may not be writing them.. that is the problem!

 

>
> Bottom line is, even if we had to write those 1600 numbers, why should it
> cost 5.8 million OPs? If we signup 1600 users in a day, each with one entry
> in the DB, then we'll use up all our quota!


Your 5.8M was READ operations anyway. It wasnt't the writing that cost as such.

That is also strange. Why should it need 5.8m OPs to do 1600 reads?

 

The other thing, it might even be a red-herring that the the bulk
update was the issue. Looking at your screenshot, you had a much
higher request rate for the prev 3 hours.

It was the update that was taking all this time. Updates from remote shell are very slow. So it took 3 hrs for 1000 or so entities that it updated and consumed 5.8m OPs in the process!

Regards,

Sarang 

[Barry Hunter]

>>
>> Your 5.8M was READ operations anyway. It wasnt't the writing that cost as
>> such.
>
>
> That is also strange. Why should it need 5.8m OPs to do 1600 reads?

Well you didnt do 1600 reads. You obvisoully did a lot more.

>
>>
>>
>> The other thing, it might even be a red-herring that the the bulk
>> update was the issue. Looking at your screenshot, you had a much
>> higher request rate for the prev 3 hours.
>
>
> It was the update that was taking all this time. Updates from remote shell
> are very slow. So it took 3 hrs for 1000 or so entities that it updated and
> consumed 5.8m OPs in the process!

Ah, well there you have your answer. The remote shell is not efficent
for certain things.

Looking back at the code you posted in the thread, looks like reading
https://developers.google.com/appengine/articles/remote_api

Would be worthwhile, seems like did two mistakes as noted in the
Limitations section.

An artefact of how the remote_api works, caused all the reads :)







>
> Regards,
> Sarang

>
> --
> You received this message because you are subscribed to the Google Groups
> "Google App Engine" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/google-appengine/-/YxgUMr0fJoEJ.

[Jeff Schnitzer]

On Thu, Jul 5, 2012 at 7:14 AM, Barry Hunter <barryb...@gmail.com> wrote:
>>
>> It was the update that was taking all this time. Updates from remote shell
>> are very slow. So it took 3 hrs for 1000 or so entities that it updated and
>> consumed 5.8m OPs in the process!
>
> Ah, well there you have your answer. The remote shell is not efficent
> for certain things.
>
> Looking back at the code you posted in the thread, looks like reading
> https://developers.google.com/appengine/articles/remote_api

Wow, that limitations section is interesting. And kinda bizarre.
Google, why does the remote_api need to use offsets when it could be
using cursors? My guess is that the remote_api code simply hasn't
been updated since cursors were invented. This is worth filing an
issue over.

Another issue is that the OP is reporting 5.8M *read* operations;
offsets should cause *small* operations. Either the OP is confusing
us or this is another major issue.

Sarang: The real lesson here is that you should not try to map/reduce
your entities across the remote api. If you're more careful about how
you iterate you can do it, but it's really too slow. There is a
"right way" to do this on GAE:

* Iterate over the entire keyset
* For each key, enqueue a task
* Each task loads-modifies-saves the entity in a transaction

This is what the map/reduce framework does. It's clever about
iterating over the keyset because it can do that in parallel
("mapping" by dividing up the keyspace into multiple queries), and of
course the tasks all can run in parallel. Depending on how many
resources you're willing to allocate to the problem, you can update
tens of thousands of entities per second.

You can either use the map/reduce framework, or whip up your own. The
"reduce" part is trivial - a task that modifies your entity as you
like. If you have small numbers of entities or you are willing to
wait, a simple iteration over your keyset is easy enough. Queries
time out after 60s or so, so if you have more than ~100k entities you
will need to stop, grab a cursor, and re-encode a new task that
continues the query. Use named tasks so that mapper tasks that repeat
don't go haywire and spin up new mapper tasks.

Of course, since you're doing this server-side, you will need to
upload code. That shouldn't be a big deal. If you want you can even
do this on a non-default version; tasks which are started on a
non-default version (ie nondefault.yourappid.appspot.com) get executed
on that same version. Just watch out because task queue definitions
are shared among all versions; either use the same queue.yaml or don't
upload the task queue definitions when you upload the nondefault
version.

Jeff

[Amir Shamsuddin]

On Thursday, July 5, 2012 3:14:52 PM UTC+1, barryhunter wrote:

>>
>> Your 5.8M was READ operations anyway. It wasnt't the writing that cost as
>> such.
>
>
> That is also strange. Why should it need 5.8m OPs to do 1600 reads?

Well you didnt do 1600 reads. You obvisoully did a lot more.


>

> It was the update that was taking all this time. Updates from remote shell
> are very slow. So it took 3 hrs for 1000 or so entities that it updated and
> consumed 5.8m OPs in the process!

Ah, well there you have your answer. The remote shell is not efficent
for certain things.

Looking back at the code you posted in the thread, looks like reading
https://developers.google.com/appengine/articles/remote_api

Would be worthwhile, seems like did two mistakes as noted in the
Limitations section.

An artefact of how the remote_api works, caused all the reads :)

To elucidate for anyone else reading, because this wasn't obvious to me (and I just hit this problem):

 

It isn't _just_ that the remote_api has limitations / is inefficient, if you try and query past 1000 or so results, *it will loop until your quota is exhausted* retrying the query. That's why you have 5.8 million reads ...

Rewriting the query to fetch batches with key based offset/filtering as suggested in the docs solves this, of course.