Mailing List Archive: Cherokee: users

Fwd: DBSlayer. Persistent connections. Still cannot get them persisting.

1 2

View All

Index | Next | Previous | View Threaded

atif.ghaffar at gmail

Oct 8, 2009, 8:37 PM

Post #1 of 31 (1326 views)
Permalink

Fwd: DBSlayer. Persistent connections. Still cannot get them persisting.

Sorry for replying directly to your email address instead of replying to the
list.
I was assuming that the default reply-to is set to the list address.

---------- Forwarded message ----------
From: Atif Ghaffar <atif.ghaffar [at] gmail>
Date: Fri, Oct 9, 2009 at 5:33 AM
Subject: Re: [Cherokee] DBSlayer. Persistent connections. Still cannot get
them persisting.
To: Taher Shihadeh <taher [at] unixwars>

I have this setup.

application server -> cherokee-dbslayer -> dbserver
I run the following code on the application server

#!/usr/bin/perl
use LWP::Simple;
while(true){
getprint("
http://dbslayer:3307/dbslayer/ecmsdb/select%20*%20from%20user%20where%20user_id%3D1306399%20limit%201
");
}

1. I see the output of the script continously (expected behaviour)
2. I see the logs on the cherokee server (expected behaviour)
3. I dont see the persistent connection on the mysql server (not expected).

What did I miss here?

If I do a script like this

#!/usr/bin/perl
# 1. connect to database.
# 2. do a select
# 3. sleep for a long time
then I do see the connection open from this script.

thanks for help.
I have many more questions but one at a time :-)

best regards
--
Atif

On Thu, Oct 8, 2009 at 8:09 PM, Atif Ghaffar <atif.ghaffar [at] gmail> wrote:

>
>
> On Thu, Oct 8, 2009 at 7:51 PM, Atif Ghaffar <atif.ghaffar [at] gmail>wrote:
>
>>
>>
>> On Thu, Oct 8, 2009 at 7:42 PM, Taher Shihadeh <taher [at] unixwars>wrote:
>>
>>> Hello.
>>>
>>> Yes. You probably want to read [1] to have some things in mind ;-)
>>>
>>>
>>> [1] http://www.cherokee-project.com/doc/modules_handlers_dbslayer.html
>>>
>>> Thanks Taher.
>>
>
> I tried on a devserver where a perl script constantly hit the dbslayer with
> a query.
> I see the output but I do not see this connections persisting on the
> dbserver.
>
>
> Can you please elaborate on how to use the chunked encoding etc to get the
> persistence.
> thanks
>
>
> --
> best regards
> Atif Ghaffar
>

--
best regards
Atif Ghaffar

taher at unixwars

Oct 9, 2009, 1:59 AM

Post #2 of 31 (1295 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Hi Atif.
Sorry for not replying earlier, but hadn't had access to the computer
for the whole night.

I asume you do have 'Keep alive' and 'Chunked encoding' enabled in the
'Advanced' section of Cherokee-Admin, right?

Regards

Atif Ghaffar wrote:
> Sorry for replying directly to your email address instead of replying
> to the list.
> I was assuming that the default reply-to is set to the list address.
>
>
> ---------- Forwarded message ----------
> From: *Atif Ghaffar* <atif.ghaffar [at] gmail
> <mailto:atif.ghaffar [at] gmail>>
> Date: Fri, Oct 9, 2009 at 5:33 AM
> Subject: Re: [Cherokee] DBSlayer. Persistent connections. Still cannot
> get them persisting.
> To: Taher Shihadeh <taher [at] unixwars <mailto:taher [at] unixwars>>
>
>
>
>
> I have this setup.
>
> application server -> cherokee-dbslayer -> dbserver
> I run the following code on the application server
>
> #!/usr/bin/perl
> use LWP::Simple;
> while(true){
>
> getprint("http://dbslayer:3307/dbslayer/ecmsdb/select%20*%20from%20user%20where%20user_id%3D1306399%20limit%201");
> }
>
> 1. I see the output of the script continously (expected behaviour)
> 2. I see the logs on the cherokee server (expected behaviour)
> 3. I dont see the persistent connection on the mysql server (not
> expected).
>
> What did I miss here?
>
> If I do a script like this
>
> #!/usr/bin/perl
> # 1. connect to database.
> # 2. do a select
> # 3. sleep for a long time
> then I do see the connection open from this script.
>
> thanks for help.
> I have many more questions but one at a time :-)
>
> best regards
> --
> Atif
>
>
> On Thu, Oct 8, 2009 at 8:09 PM, Atif Ghaffar <atif.ghaffar [at] gmail
> <mailto:atif.ghaffar [at] gmail>> wrote:
>
>
>
> On Thu, Oct 8, 2009 at 7:51 PM, Atif Ghaffar
> <atif.ghaffar [at] gmail <mailto:atif.ghaffar [at] gmail>> wrote:
>
>
>
> On Thu, Oct 8, 2009 at 7:42 PM, Taher Shihadeh
> <taher [at] unixwars <mailto:taher [at] unixwars>> wrote:
>
> Hello.
>
> Yes. You probably want to read [1] to have some things in
> mind ;-)
>
>
> [1]
> http://www.cherokee-project.com/doc/modules_handlers_dbslayer.html
>
>
> Thanks Taher.
>
>
> I tried on a devserver where a perl script constantly hit the
> dbslayer with a query.
> I see the output but I do not see this connections persisting on
> the dbserver.
>
>
> Can you please elaborate on how to use the chunked encoding etc to
> get the persistence.
> thanks
>

--
taher [at] unixwars
http://unixwars.com/

_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 2:17 AM

Post #3 of 31 (1306 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Hi Taher,

On Fri, Oct 9, 2009 at 10:59 AM, Taher Shihadeh <taher [at] unixwars> wrote:

> Hi Atif.
> Sorry for not replying earlier, but hadn't had access to the computer for
> the whole night.
>
Oh, I am so sorry about that. :-)

>
> I asume you do have 'Keep alive' and 'Chunked encoding' enabled in the
> 'Advanced' section of Cherokee-Admin, right?
>
Yes, they are both set.

Note there is no special communication between the application and the
dbslayer. Just a simple GET from the shell.

This layer is already helping us ( from the logs ) to see how much redundant
junk is being sent to the db.

Thats the thing about architechture, you add a new element and it makes the
older one looks bad :-) but thats the whole point.

thanks for looking into it.

> Regards
>
> Atif Ghaffar wrote:
>
>> Sorry for replying directly to your email address instead of replying to
>> the list.
>> I was assuming that the default reply-to is set to the list address.
>>
>>
>> ---------- Forwarded message ----------
>> From: *Atif Ghaffar* <atif.ghaffar [at] gmail <mailto:
>> atif.ghaffar [at] gmail>>
>> Date: Fri, Oct 9, 2009 at 5:33 AM
>> Subject: Re: [Cherokee] DBSlayer. Persistent connections. Still cannot get
>> them persisting.
>> To: Taher Shihadeh <taher [at] unixwars <mailto:taher [at] unixwars>>
>>
>>
>>
>>
>> I have this setup.
>>
>> application server -> cherokee-dbslayer -> dbserver
>> I run the following code on the application server
>>
>> #!/usr/bin/perl
>> use LWP::Simple;
>> while(true){
>> getprint("
>> http://dbslayer:3307/dbslayer/ecmsdb/select%20*%20from%20user%20where%20user_id%3D1306399%20limit%201
>> ");
>> }
>>
>> 1. I see the output of the script continously (expected behaviour)
>> 2. I see the logs on the cherokee server (expected behaviour)
>> 3. I dont see the persistent connection on the mysql server (not
>> expected).
>>
>> What did I miss here?
>>
>> If I do a script like this
>>
>> #!/usr/bin/perl
>> # 1. connect to database.
>> # 2. do a select
>> # 3. sleep for a long time
>> then I do see the connection open from this script.
>>
>> thanks for help.
>> I have many more questions but one at a time :-)
>>
>> best regards
>> --
>> Atif
>>
>>
>> On Thu, Oct 8, 2009 at 8:09 PM, Atif Ghaffar <atif.ghaffar [at] gmail<mailto:
>> atif.ghaffar [at] gmail>> wrote:
>>
>>
>>
>> On Thu, Oct 8, 2009 at 7:51 PM, Atif Ghaffar
>> <atif.ghaffar [at] gmail <mailto:atif.ghaffar [at] gmail>> wrote:
>>
>>
>>
>> On Thu, Oct 8, 2009 at 7:42 PM, Taher Shihadeh
>> <taher [at] unixwars <mailto:taher [at] unixwars>> wrote:
>>
>> Hello.
>>
>> Yes. You probably want to read [1] to have some things in
>> mind ;-)
>>
>>
>> [1]
>>
>> http://www.cherokee-project.com/doc/modules_handlers_dbslayer.html
>>
>>
>> Thanks Taher.
>>
>>
>> I tried on a devserver where a perl script constantly hit the
>> dbslayer with a query.
>> I see the output but I do not see this connections persisting on
>> the dbserver.
>>
>>
>> Can you please elaborate on how to use the chunked encoding etc to
>> get the persistence.
>> thanks
>>
>>
>
> --
> taher [at] unixwars
> http://unixwars.com/
>
>

--
best regards
Atif Ghaffar

taher at unixwars

Oct 9, 2009, 5:18 AM

Post #4 of 31 (1285 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Atif Ghaffar wrote:
>
> I asume you do have 'Keep alive' and 'Chunked encoding' enabled in
> the 'Advanced' section of Cherokee-Admin, right?
>
> Yes, they are both set.
>
> Note there is no special communication between the application and the
> dbslayer. Just a simple GET from the shell.
Ahemmm.... don't know what you are using as client to make the requests,
but does it support HTTP1.1 headers?
:)

--
taher [at] unixwars
http://unixwars.com/

_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 6:24 AM

Post #5 of 31 (1290 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Fri, Oct 9, 2009 at 2:18 PM, Taher Shihadeh <taher [at] unixwars> wrote:

> Atif Ghaffar wrote:
>
>>
>> I asume you do have 'Keep alive' and 'Chunked encoding' enabled in
>> the 'Advanced' section of Cherokee-Admin, right?
>>
>> Yes, they are both set.
>>
>> Note there is no special communication between the application and the
>> dbslayer. Just a simple GET from the shell.
>>
> Ahemmm.... don't know what you are using as client to make the requests,
> but does it support HTTP1.1 headers?
> :)
>
>
Taher,
a simple
netcat host port
GET /rawurlencoded_query

or a php's
file($url)

or from perl
use LWP::Simple;
get($url);

Would sending http 1.1 header help? would the application (caller) need to
keep the connection open?

thanks.

stefan at konink

Oct 9, 2009, 6:45 AM

Post #6 of 31 (1292 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
> Would sending http 1.1 header help? would the application (caller) need
> to keep the connection open?

This is totally unrelated how the backend behaves.

Stefan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrPPosACgkQYH1+F2Rqwn1Y5QCcCOoh5GjeAdviF4CSdvtN4VQ2
/EEAnRgqgwazvkADPaMa3T1hFPffS/5m
=RNuH
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 6:57 AM

Post #7 of 31 (1295 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Stefan,
I agree with you and that is how I understood it.

You have the cherokee-dbslayer_handler and then there is a database.
I was expecting that the connections from the dbslayer will stay open on the
dbserver.

but this does not seem to be the case or I have done some mistake in my
setup.

where to look next?
should I have had compiled with some different settings?

My compile settings are

./configure --prefix=/opt/cherokee-dbslayer
--with-mysql --without-libssl --without-ldap --without-geoip
--without-ffmpeg --disable-ipv6
--enable-internal-pcre --without-sendfile-support --disable-pam --disable-nls

On Fri, Oct 9, 2009 at 3:45 PM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> > Would sending http 1.1 header help? would the application (caller) need
> > to keep the connection open?
>
> This is totally unrelated how the backend behaves.
>
>
> Stefan
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrPPosACgkQYH1+F2Rqwn1Y5QCcCOoh5GjeAdviF4CSdvtN4VQ2
> /EEAnRgqgwazvkADPaMa3T1hFPffS/5m
> =RNuH
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 9, 2009, 7:01 AM

Post #8 of 31 (1287 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
> I agree with you and that is how I understood it.
>
> You have the cherokee-dbslayer_handler and then there is a database.
> I was expecting that the connections from the dbslayer will stay open on
> the dbserver.
>
> but this does not seem to be the case or I have done some mistake in my
> setup.
>
> where to look next?
> should I have had compiled with some different settings?

What happens:

- - you send a request to cherokee keepalive or not
- - this query is sent to one of the dbmachines in the loadbalancer
- - the connection between cherokee and the databases are 'persistent'
- - the result returns as normal http request to the client

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrPQjsACgkQYH1+F2Rqwn0KIACeKjXhYSm9lF2giwFsggh/JQ/Z
Z3oAoINK1km+e4k3waO1sjWmh17ewyIw
=evlu
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 7:10 AM

Post #9 of 31 (1295 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Fri, Oct 9, 2009 at 4:01 PM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> > I agree with you and that is how I understood it.
> >
> > You have the cherokee-dbslayer_handler and then there is a database.
> > I was expecting that the connections from the dbslayer will stay open on
> > the dbserver.
> >
> > but this does not seem to be the case or I have done some mistake in my
> > setup.
> >
> > where to look next?
> > should I have had compiled with some different settings?
>
> What happens:
>
> - - you send a request to cherokee keepalive or not
> - - this query is sent to one of the dbmachines in the loadbalancer
> - - the connection between cherokee and the databases are 'persistent'
>

Stefan, how can I verify it?

For example: When write a normal SCGI app and start 5 instances of it and
make a few requests to the loadbalancer (Cherokee+SCGI_handler), I see five
open connections in mysql process list.

I was expecting the same behaviour from the dbslayer but its not doing the
same.

At the moment we are running
20 instances of SCGI app running as 10 instance on each Cherokkee host. Here
the dbuser name is "xcmsbl"
I see in mysql user list 20 connections from this user.

I am doing the same now with the dbslayer
2 machines running cherokee_dbslayer getting atleast 10 queries per second.
Here the dbusername is "dbslayer".
I see zero connections from this user.

If I keep refreshing madly the processlist, I see from time to time that the
dbslayer connected, made the request and disconnected.

thanks again for the help.

> - - the result returns as normal http request to the client
>
>
>
> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrPQjsACgkQYH1+F2Rqwn0KIACeKjXhYSm9lF2giwFsggh/JQ/Z
> Z3oAoINK1km+e4k3waO1sjWmh17ewyIw
> =evlu
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 9, 2009, 7:14 AM

Post #10 of 31 (1286 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
>> For example: When write a normal SCGI app and start 5 instances of it
>> and make a few requests to the loadbalancer (Cherokee+SCGI_handler), I
>> see five open connections in mysql process list.

Completely correct. For each request a connection is forked, read how
the mysql_open works :)

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrPRVAACgkQYH1+F2Rqwn26OwCgjdeGGmLxtls0Jk5xTQL+b3e6
7ucAoIksQzipWA8ShzaVSMpsQ6qZUTZe
=0l3N
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

taher at unixwars

Oct 9, 2009, 7:14 AM

Post #11 of 31 (1292 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Atif Ghaffar wrote:
>
> Note there is no special communication between the application
> and the dbslayer. Just a simple GET from the shell.
>
> Ahemmm.... don't know what you are using as client to make the
> requests, but does it support HTTP1.1 headers?
>
WOW! I should really stop skimming my reading on the list the way I do.
Of course, this is unrelated, as Stefan pointed out. Dunno why I got
mixed up while replying.
Ironic thing is precisely you mentioned the word architecture ... for me
to disregard. ;-)

--
taher [at] unixwars
http://unixwars.com/

_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 7:17 AM

Post #12 of 31 (1282 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Fri, Oct 9, 2009 at 4:14 PM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> >> For example: When write a normal SCGI app and start 5 instances of it
> >> and make a few requests to the loadbalancer (Cherokee+SCGI_handler), I
> >> see five open connections in mysql process list.
>
> Completely correct. For each request a connection is forked, read how
> the mysql_open works :)
>
>

Stefan, yes I totaly agree with you.
I am saying that this is what I was expecting with dbslayer handler, but
not getting it.

The connections from the dbslayer -> database are not staying persistent.
I will do some more tests over the weekend and get back with my findings.

> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrPRVAACgkQYH1+F2Rqwn26OwCgjdeGGmLxtls0Jk5xTQL+b3e6
> 7ucAoIksQzipWA8ShzaVSMpsQ6qZUTZe
> =0l3N
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 9, 2009, 7:19 AM

Post #13 of 31 (1296 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
>> Stefan, yes I totaly agree with you.
>> I am saying that this is what I was expecting with dbslayer handler,
>> but not getting it.
>
>> The connections from the dbslayer -> database are not staying persistent.
>> I will do some more tests over the weekend and get back with my findings.

Did you expect the actual database connection was stayed up so that you
could set variables and do transactions?

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrPRlUACgkQYH1+F2Rqwn1obwCfb2hYucMCbi9P1QrvdlWy5tjI
KA8AniwWBdK5Oz78Qp36gapZ5wFe6fdG
=mtSv
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 9, 2009, 7:49 AM

Post #14 of 31 (1292 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Fri, Oct 9, 2009 at 4:19 PM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> >> Stefan, yes I totaly agree with you.
> >> I am saying that this is what I was expecting with dbslayer handler,
> >> but not getting it.
> >
> >> The connections from the dbslayer -> database are not staying
> persistent.
> >> I will do some more tests over the weekend and get back with my
> findings.
>
> Did you expect the actual database connection was stayed up so that you
> could set variables and do transactions?
>
>
Stefan, No, I have read the docs and absolutely do not want to do that.
just regular simple select from this/that , update, insert, delete.

Ok, perhaps some description might help.

1 master DB. (This is the absolute last thing to scale)

Stage 1.
--------------
2 application servers. (apache+mod_php+html spaghetti, serving html, css,
js, image, php, cofee, soup, etc)
scale to 4 spaghetti.
scale to 6 spaghetti.

Stage 2.
--------------
2. nginx servers to handle everything static and pass everything php via
FCGI to php processes running on different machines.
4. php server. All they run is php with FPM (FastCGI Process Manager)
Start to looks better but now the code starts to look horrible.
All the presentation logic (how to create a select box) and the business
logic is in the same place.
This was just an isolation step. It did not make anything better, it just
shows where the problems are so they could be worked on.

Stage 3.
--------------
2 nginx server ... same role, handle static pass all the php stuff to php
processing servers.
2 buiness-logc servers (using app-server-in-php using SCGI being served by
cherokee-SCGI_handler)
http://code.google.com/p/appserver-in-php/
This really remove all the presentation stuff. Even a print "hello" would
not be sent to the browser but to the console and forces the
developers to think outside the http+browser model.

4 php processors, they now take care of the presentation and talk to the
businesslogic system using http -> cherokee-scgi -> app-server

So now the situation is that with 20 processes, I have 20 connections open
to the database.
I want to go to around 200 - 300 processes but no point of keeping 300
connections open to the db.
Its not a big deal if we really have to but why do that if it can be
avoided.

Stage 4. (planning/hoping)
--------------
Cherokee-dbslayer comes in.
4-5 servers connecting to the dbserver.
This lets the app servers talk to the cherokee dbslayer instead of the
dbserver itself.

I see it like a pool of 50 connections always open from the dbslayer and the
appservers can talk to dbslayers to do the db stuff.

This way we can scale each different element separately instead of just
throwing resources where we dont know.

I hope this explains why I was looking at db_slayer but perhaps I
misunderstood how it worked.

best regards

>
> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrPRlUACgkQYH1+F2Rqwn1obwCfb2hYucMCbi9P1QrvdlWy5tjI
> KA8AniwWBdK5Oz78Qp36gapZ5wFe6fdG
> =mtSv
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

taher at unixwars

Oct 9, 2009, 8:11 AM

Post #15 of 31 (1296 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Atif Ghaffar wrote:
>
> On Fri, Oct 9, 2009 at 4:19 PM, Stefan de Konink <stefan [at] konink
> <mailto:stefan [at] konink>> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> >> Stefan, yes I totaly agree with you.
> >> I am saying that this is what I was expecting with dbslayer
> handler,
> >> but not getting it.
> >
> >> The connections from the dbslayer -> database are not staying
> persistent.
> >> I will do some more tests over the weekend and get back with my
> findings.
>
> Did you expect the actual database connection was stayed up so
> that you
> could set variables and do transactions?
>
>
> Stefan, No, I have read the docs and absolutely do not want to do that.
> just regular simple select from this/that , update, insert, delete.
Good then.
I suppose you already know that Atif, but in case you hadn't noticed:
beware of transactions, locking tables and that sort of things. Separate
queries could very well be handled by separate servers depending on your
balancing scheme. Either contain several operations on a single query,
or don't try. Never dispatch them as separate queries.

--
taher [at] unixwars
http://unixwars.com/

_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 10, 2009, 2:38 AM

Post #16 of 31 (1280 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Fri, Oct 9, 2009 at 5:11 PM, Taher Shihadeh <taher [at] unixwars> wrote:

> Atif Ghaffar wrote:
>
>>
>> On Fri, Oct 9, 2009 at 4:19 PM, Stefan de Konink <stefan [at] konink<mailto:
>> stefan [at] konink>> wrote:
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA512
>>
>> Atif Ghaffar schreef:
>> >> Stefan, yes I totaly agree with you.
>> >> I am saying that this is what I was expecting with dbslayer
>> handler,
>> >> but not getting it.
>> >
>> >> The connections from the dbslayer -> database are not staying
>> persistent.
>> >> I will do some more tests over the weekend and get back with my
>> findings.
>>
>> Did you expect the actual database connection was stayed up so
>> that you
>> could set variables and do transactions?
>>
>>
>> Stefan, No, I have read the docs and absolutely do not want to do that.
>> just regular simple select from this/that , update, insert, delete.
>>
> Good then.
> I suppose you already know that Atif, but in case you hadn't noticed:
> beware of transactions, locking tables and that sort of things. Separate
> queries could very well be handled by separate servers depending on your
> balancing scheme. Either contain several operations on a single query, or
> don't try. Never dispatch them as separate queries.

Sure, thats well described in the documentation.

>
>
> --
> taher [at] unixwars
> http://unixwars.com/
>
>

--
best regards
Atif Ghaffar

atif.ghaffar at gmail

Oct 11, 2009, 8:00 AM

Post #17 of 31 (1282 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

stefan at konink

Oct 11, 2009, 8:20 AM

Post #18 of 31 (1291 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
>> Can I contact you off-list?
>
>> I think we have a misunderstanding on how we interpret persistent
>> connection.

I think onlist is much better to share information.

>> You are thinking in the terms of
>> one dbslayer -> many db replicas. (so perhaps you mean that the reqest
>> from the slayer will always go to the same replica)
>
>> I am trying the opposite.
>> many dblsayers -> one db. (the reason I want to do it , is that I want
>> to know exactly how many connections will the db handle).

The key observation is here:

conn = mysql_real_connect (hdl->conn,
hdl->src_ref->host.buf,
props->user.buf,
props->password.buf,
props->db.buf,
hdl->src_ref->port,
hdl->src_ref->unix_socket.buf,
CLIENT_MULTI_STATEMENTS |
CLIENT_MULTI_RESULTS);

mysql_real_connect() attempts to establish a connection to a MySQL
database engine running on host. mysql_real_connect() must complete
successfully before you can execute any other API functions that require
a valid MYSQL connection handle structure.

In that perspective you are right, for each request a new connection is
made. I wrote a handler that used pooling of connections, but there I
could always trust on the fact that I didn't introduce anything /new/.
We could implement such thing here, but as I explained that has serious
downsides regarding to what is stored for the connection local
transaction wise.

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrR970ACgkQYH1+F2Rqwn2c9gCbBub60tGmYAQWZexqYfcCgZc4
5dUAmwefFX6uj2nx0dcWryxuqnryI5ni
=UHui
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 11, 2009, 9:24 AM

Post #19 of 31 (1289 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Sun, Oct 11, 2009 at 5:20 PM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Atif Ghaffar schreef:
> >> Can I contact you off-list?
> >
> >> I think we have a misunderstanding on how we interpret persistent
> >> connection.
>
> I think onlist is much better to share information.
>

ok. I just wanted to avoid discussions if they were off-topic

>
> >> You are thinking in the terms of
> >> one dbslayer -> many db replicas. (so perhaps you mean that the reqest
> >> from the slayer will always go to the same replica)
> >
> >> I am trying the opposite.
> >> many dblsayers -> one db. (the reason I want to do it , is that I want
> >> to know exactly how many connections will the db handle).
>
> The key observation is here:
>
> conn = mysql_real_connect (hdl->conn,
> hdl->src_ref->host.buf,
> props->user.buf,
> props->password.buf,
> props->db.buf,
> hdl->src_ref->port,
> hdl->src_ref->unix_socket.buf,
> CLIENT_MULTI_STATEMENTS |
> CLIENT_MULTI_RESULTS);
>
> mysql_real_connect() attempts to establish a connection to a MySQL
> database engine running on host. mysql_real_connect() must complete
> successfully before you can execute any other API functions that require
> a valid MYSQL connection handle structure.
>
>
> In that perspective you are right, for each request a new connection is
> made. I wrote a handler that used pooling of connections, but there I
> could always trust on the fact that I didn't introduce anything /new/.
> We could implement such thing here, but as I explained that has serious
> downsides regarding to what is stored for the connection local
> transaction wise.
>
>
I like this idea. Can we make it a configuaration option / or a separate
handler.

It would work in my case for example I will know that there are always max
10/100/1000 connections open to the DB no matter what.
Everything else will fail in other layers.

This way when I know that I must upgrade the DB to handle 100 instead of 10
connections it could be justified.

For example if there is a complain "App is too slow" then instead of
throwing hardware on it, we can figure out where it is slowing down.
Imagine this. A careless application might use DB for session storage. A
DDOS on just that website will bring down the DB. Thats just silly.

thanks for your answer and best regards

>
> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrR970ACgkQYH1+F2Rqwn2c9gCbBub60tGmYAQWZexqYfcCgZc4
> 5dUAmwefFX6uj2nx0dcWryxuqnryI5ni
> =UHui
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 11, 2009, 9:32 AM

Post #20 of 31 (1281 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Atif Ghaffar schreef:
>> I like this idea. Can we make it a configuaration option / or a separate
>> handler.
>
>> It would work in my case for example I will know that there are always
>> max 10/100/1000 connections open to the DB no matter what.
>> Everything else will fail in other layers.
>
>> This way when I know that I must upgrade the DB to handle 100 instead of
>> 10 connections it could be justified.
>
>
>> For example if there is a complain "App is too slow" then instead of
>> throwing hardware on it, we can figure out where it is slowing down.
>> Imagine this. A careless application might use DB for session storage. A
>> DDOS on just that website will bring down the DB. Thats just silly.

http://repo.or.cz/w/handlerosm.git?a=blob;f=handler_osm.c;h=2b4ac855a8d3eea4e69af1e9e6c587a6ab03bf9e;hb=HEAD

Look for the section ifdef pooling.

I think we could implement this in dbslayer too. But I kinda want to
have a more abstract dbslayer before I get into this development. I
already have a patch for more database in dbslayer, but that is also
based on the assumption that you connect for each request.

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrSCIYACgkQYH1+F2Rqwn3pEgCfb7xN8XCOCQDRRw5Ayh2vufXK
rJsAnjIhECtZ2p7bG+IiWvZPLIcZfXGi
=G76b
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 13, 2009, 2:59 PM

Post #21 of 31 (1250 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Hi Stefan,

I have eventually had some time to do my tests.
My results are that the cherokee version of the dbslayer does not keep the
persistent connections open.

Here is my test with the cherokee version.
http://worldsoft-blog.com/b/25/dbslayer-test-with-the-cherokee-handler

Here is the result with nyt version which does indeed keep the handle open.
http://worldsoft-blog.com/b/26/dbslayer-test-with-the-nyt-version

best regards

On Fri, Oct 9, 2009 at 4:17 PM, Atif Ghaffar <atif.ghaffar [at] gmail> wrote:

>
>
> On Fri, Oct 9, 2009 at 4:14 PM, Stefan de Konink <stefan [at] konink> wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA512
>>
>> Atif Ghaffar schreef:
>> >> For example: When write a normal SCGI app and start 5 instances of it
>> >> and make a few requests to the loadbalancer (Cherokee+SCGI_handler), I
>> >> see five open connections in mysql process list.
>>
>> Completely correct. For each request a connection is forked, read how
>> the mysql_open works :)
>>
>>
>
> Stefan, yes I totaly agree with you.
> I am saying that this is what I was expecting with dbslayer handler, but
> not getting it.
>
> The connections from the dbslayer -> database are not staying persistent.
> I will do some more tests over the weekend and get back with my findings.
>
>
>
>
>
>> Stefan
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v2.0.11 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iEYEAREKAAYFAkrPRVAACgkQYH1+F2Rqwn26OwCgjdeGGmLxtls0Jk5xTQL+b3e6
>> 7ucAoIksQzipWA8ShzaVSMpsQ6qZUTZe
>> =0l3N
>> -----END PGP SIGNATURE-----
>>
>
>
>
> --
> best regards
> Atif Ghaffar
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 13, 2009, 3:07 PM

Post #22 of 31 (1254 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

...and what performs better?

Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkrU+gQACgkQYH1+F2Rqwn2a5QCfSS3rSGTOrosmT7CANLX3/265
sLMAnilKinWxfsj4fVkWbkRDVB3HcQSS
=zR3M
-----END PGP SIGNATURE-----
_______________________________________________
Cherokee mailing list
Cherokee [at] lists
http://lists.octality.com/listinfo/cherokee

atif.ghaffar at gmail

Oct 13, 2009, 10:45 PM

Post #23 of 31 (1253 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Wed, Oct 14, 2009 at 12:07 AM, Stefan de Konink <stefan [at] konink> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> ...and what performs better?
>
> I am sure that your version performs better :-)
Thats not the point.

For this specific case (Connection limiting) it does not work for me but was
a good experiment.

best regards

> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrU+gQACgkQYH1+F2Rqwn2a5QCfSS3rSGTOrosmT7CANLX3/265
> sLMAnilKinWxfsj4fVkWbkRDVB3HcQSS
> =zR3M
> -----END PGP SIGNATURE-----
>

--
best regards
Atif Ghaffar

stefan at konink

Oct 13, 2009, 11:00 PM

Post #24 of 31 (1251 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

Op 14 okt 2009 om 07:45 heeft Atif Ghaffar <atif.ghaffar [at] gmail>
het volgende geschreven:\

>
>
> On Wed, Oct 14, 2009 at 12:07 AM, Stefan de Konink
> <stefan [at] konink> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> ...and what performs better?
>
> I am sure that your version performs better :-)
> Thats not the point.

It is the point ;) because if I want pooling is implemented in a
blink, but if it is slower, why bother ;)

The only reason to bother is if the login time has a significant
overhead, what exists for some databases. MonetDB for example.

> For this specific case (Connection limiting) it does not work for me
> but was a good experiment.

So you would like to have a version that serializes requests or
creates a connection when no free connection is available?

Stefan

>
> best regards
>
>
> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkrU+gQACgkQYH1+F2Rqwn2a5QCfSS3rSGTOrosmT7CANLX3/265
> sLMAnilKinWxfsj4fVkWbkRDVB3HcQSS
> =zR3M
> -----END PGP SIGNATURE-----
>
>
>
> --
> best regards
> Atif Ghaffar

atif.ghaffar at gmail

Oct 13, 2009, 11:08 PM

Post #25 of 31 (1253 views)
Permalink

Re: Fwd: DBSlayer. Persistent connections. Still cannot get them persisting. [In reply to]

On Wed, Oct 14, 2009 at 8:00 AM, Stefan de Konink <stefan [at] konink> wrote:

>
>
> Op 14 okt 2009 om 07:45 heeft Atif Ghaffar <atif.ghaffar [at] gmail> het
> volgende geschreven:\
>
>
>
> On Wed, Oct 14, 2009 at 12:07 AM, Stefan de Konink < <stefan [at] konink>
> stefan [at] konink> wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA512
>>
>> ...and what performs better?
>>
>> I am sure that your version performs better :-)
> Thats not the point.
>
>
> It is the point ;) because if I want pooling is implemented in a blink, but
> if it is slower, why bother ;)
>
>
> The only reason to bother is if the login time has a significant overhead,
> what exists for some databases. MonetDB for example.
>
>
> For this specific case (Connection limiting) it does not work for me but
> was a good experiment.
>
>
> So you would like to have a version that serializes requests or creates a
> connection when no free connection is available?
>
> Stephan I would like where I can tell the server open 5 connections to the
server and not 1 more and reuse them.
This way I would be able to scale the db with real numbers instead of
pulling them out of a hat.

Exactly what I got with the nyt dbslayer experiment and exactly what our
SCGI based apps are doing right now, except that I want to put yet another
layer between the SCGI app instances and the database to even reduce the
connection more.

Maybe I should draw a diagram.. perhaps later today...

>
> Stefan
>
>
> best regards
>
>
>> Stefan
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v2.0.11 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - <http://enigmail.mozdev.org>
>> http://enigmail.mozdev.org
>>
>> iEYEAREKAAYFAkrU+gQACgkQYH1+F2Rqwn2a5QCfSS3rSGTOrosmT7CANLX3/265
>> sLMAnilKinWxfsj4fVkWbkRDVB3HcQSS
>> =zR3M
>> -----END PGP SIGNATURE-----
>>
>
>
>
> --
> best regards
> Atif Ghaffar
>
>

--
best regards
Atif Ghaffar

1 2

View All

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads