On Fri, Aug 3, 2012 at 11:23 AM, Richard <[email protected]> wrote:
> Hi Takashi,
>
> I can definately look into it, but I would prefer a cheaper solution than
> MANY B1 backends... especially since my app is a free android game :)
>
> In the mean time, I have configured another app to talks to a Cloud SQL
> instance. I just generates a random username and score and saves it to the
> DB. Here are the apache bench results for 1000 requests with 250
> concurrent:
>
> Concurrency Level: 250
> Time taken for tests: 49.137 seconds
> Complete requests: 1000
> Failed requests: 0
> Write errors: 0
>
>
> Connection Times (ms)
> min mean[+/-sd] median max
> Connect: 31 48 17.8 38 93
> Processing: 397 10564 3119.1 11951 12820
> Waiting: 395 6176 3352.6 6091 12819
> Total: 434 10613 3118.9 11990 12858
>
> Percentage of the requests served within a certain time (ms)
> 50% 11990
> 66% 12193
> 75% 12250
> 80% 12290
> 90% 12395
> 95% 12436
> 98% 12477
> 99% 12507
> 100% 12858 (longest request)
>
> That is not very encouraging. I removed appstats, but that did not make
> any difference. Time to try connection pooling ?
>
Probably no. As I said before, we don't recommend using connection pooling
with Cloud SQL.
BTW, my implementation steadily hit 300-500QPS with 10 b1 backends. Is it
still too expensive?
Here is the ab result:
Concurrency Level: 500
Time taken for tests: 1.842 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 803899 bytes
HTML transferred: 2000 bytes
Requests per second: 542.98 [#/sec] (mean)
Time per request: 920.850 [ms] (mean)
Time per request: 1.842 [ms] (mean, across all concurrent requests)
Transfer rate: 426.27 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 32 34 0.7 34 35
Processing: 252 634 293.1 581 1776
Waiting: 252 634 293.1 581 1776
Total: 284 668 293.1 615 1809
Percentage of the requests served within a certain time (ms)
50% 615
66% 761
75% 823
80% 869
90% 1037
95% 1248
98% 1485
99% 1641
100% 1809 (longest request)
>
> -R
>
>
> On Thursday, August 2, 2012 8:57:44 PM UTC-4, Takashi Matsuo (Google)
> wrote:
>>
>>
>> Here is a good and bad news.
>>
>> Bad news is that currently, the number of concurrent connections to a
>> single CloudSQL instance is limited to 100.
>>
>> Good news is that, my experiment implementation of in-memory database
>> with backend instances got 1k QPS constantly even with 20 B1 backends.
>> Actually, the bottleneck of my previous experiment was just the network
>> problem of the machine on which I ran the test. When I tested with a good
>> environment, the throughput had been constantly +1k QPS. Yay!
>>
>> Richard,
>>
>> Can you start look into my prototype? I definitely believe it's worth
>> looking :)
>>
>> -- Takashi
>>
>>
>> On Fri, Aug 3, 2012 at 8:49 AM, Takashi Matsuo <[email protected]>wrote:
>>
>>>
>>> Hi everyone,
>>>
>>> We don't recommend using connection pool mechanism with Cloud SQL. Also
>>> see this thread:
>>> https://groups.google.com/**forum/?fromgroups#!topic/**
>>> google-cloud-sql-discuss/**sS38Nh7MriY<https://groups.google.com/forum/?fromgroups#%21topic/google-cloud-sql-discuss/sS38Nh7MriY>
>>>
>>>
>>> On Fri, Aug 3, 2012 at 6:25 AM, Mauricio Aristizabal
>>> <[email protected]>wrote:
>>>
>>>> Wait, sorry I just realized you do still need some mechanism for
>>>> pooling the connections (even if it doesn't automatically test for their
>>>> health), so yeah hopefully Python folks can chime in with that.
>>>>
>>>>
>>>> On Thu, Aug 2, 2012 at 2:19 PM, Mauricio Aristizabal <[email protected]
>>>> > wrote:
>>>>
>>>>> Richard, I do use a connection pool (Apache commons dbcp) but i'm on
>>>>> the Java side so can't help you with specifics. The only wrinkle for me
>>>>> was that I couldn't use the strategy where it checks connection health at
>>>>> intervals because of the GAE thread limitations, so instead it has to do a
>>>>> test query before every query, but in practice this ends up costing only
>>>>> about 5ms (it's a fast query: "select 1").
>>>>>
>>>>> Depending what you end up using though, you may be able to do without
>>>>> this and instead trap error, get new connection and retry. If you setup a
>>>>> monitoring ping to your app or a cron that results in a query always
>>>>> happening before the 15m instance-inactive window then you can keep your
>>>>> GCSQL instance up around the clock and then such stale connections will be
>>>>> very rare.
>>>>>
>>>>> In fact since in your case there will only be a couple queries you
>>>>> might just add this retry logic right into your app. Should be pretty
>>>>> simple, just wait for wind down, see what exception is thrown, and trap
>>>>> that / connect / retry.
>>>>>
>>>>>
>>>>> On Thu, Aug 2, 2012 at 12:55 PM, alex <[email protected]> wrote:
>>>>>
>>>>>> As far as I understand, Google recommends closing connection right
>>>>>> away, after you perform all operations. The latency to check whether a
>>>>>> connection is still open is (almost?) the same as opening a new one, so
>>>>>> it
>>>>>> doesn't seem to be worth keeping it open.
>>>>>>
>>>>>> They also say a per-hour-usage db instance gets inactive after 15 min
>>>>>> idle, and12 hours (if I remember correctly) forthe other plan (you pay
>>>>>> flat
>>>>>> per day). I've seen high latencies only on first connect to a "cold" db
>>>>>> instance. You'll find more in the official docs.
>>>>>>
>>>>>> I liked it. Works better than I expected.
>>>>>>
>>>>>> --
>>>>>> 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/-/**usXB3qS55KcJ<https://groups.google.com/d/msg/google-appengine/-/usXB3qS55KcJ>
>>>>>> .
>>>>>> To post to this group, send email to google-appengine@googlegroups.**
>>>>>> com <[email protected]>.
>>>>>> To unsubscribe from this group, send email to
>>>>>> google-appengine+unsubscribe@**googlegroups.com<google-appengine%[email protected]>
>>>>>> .
>>>>>> For more options, visit this group at http://groups.google.com/**
>>>>>> group/google-appengine?hl=en<http://groups.google.com/group/google-appengine?hl=en>
>>>>>> .
>>>>>>
>>>>>>
>>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Google App Engine" group.
>>>> To post to this group, send email to google-appengine@googlegroups.**
>>>> com <[email protected]>.
>>>> To unsubscribe from this group, send email to
>>>> google-appengine+unsubscribe@**googlegroups.com<google-appengine%[email protected]>
>>>> .
>>>> For more options, visit this group at http://groups.google.com/**
>>>> group/google-appengine?hl=en<http://groups.google.com/group/google-appengine?hl=en>
>>>> .
>>>>
>>>
>>>
>>>
>>> --
>>> Takashi Matsuo
>>>
>>
>>
>>
>> --
>> Takashi Matsuo
>>
> --
> 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/-/m6NgLswZ5gcJ.
>
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/google-appengine?hl=en.
>
--
Takashi Matsuo
--
You received this message because you are subscribed to the Google Groups
"Google App Engine" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en.
