Netty server with MySql database backend, how to do it right?

[Frederic Bregier]

Hi Karasko,

I've started to answer in your other post but I think some more points have
their own place in this thread... ;-)


Karasko wrote:
> 
> Please help me with Netty server, which processes requests by doing
> various SQL queries to MySql server. 
> It should process as many clients as possible :)
> I need some advice about it architecture and using of SQL connection and
> SQL queries. I've tested it a little
> with one client at one time, it works. 
> 
I would first suggest you that if you want many clients to have many sql
connection. It should be a good idea to use a connectionPool for you sql
connection so that you can limit the overhead of the creation of the
connection (in a way similar to what Tomcat does with data connection
pooling). You can implement your own pool connection or try to reuse one
already done (probably by googling with "mysql connection pooling java").
If you don't do that, as sql connection are "sequential", any command will
have to wait for the previous one to finished... then you will have bad
latencies...


Karasko wrote:
> 
> How it works now:
> 1. DBMapper - A class responsive for DB creates a connection, an save it
> to a static class field.
> 2. Server instance is created, and binds to the specific port.
> 3. I use such pipeline factory
> 
So I would suggest you that DBMapper is something like a data pool
connection where you can get a new one for each channel (or an already
connected one so already ready). My suggestion would be to also check if the
connection is "really" ready by making a first request like a "select 1", in
order to be sure that no time out or other problems will bring you bad
connection status...
In the closed method of your MyHandlerObj, you can take care of the closing
of the sql connection, returning it to the pool for instance.


Karasko wrote:
> 
> Server.java
> <code>
> 	MyHandlerObj handler = new ServerHandlerObj();
>         bootstrap.setPipelineFactory(new ServerPipelineFactory(handler));
> </code>
> 
> ServerPipelineFactory.java
> <code>
>    public ChannelPipeline getPipeline() throws Exception {
>         ChannelPipeline pipeline = pipeline();
>         pipeline.addLast("cmdDecoder", new MyCommandDecoder());
>         pipeline.addLast("stringEncoder", new StringEncoder("UTF-8"));
>         pipeline.addLast("handler", this.myhandler);
>         return pipeline;
>     } 
> </code>
> 
As I post in the other thread, I would suggest you to create the
MyHandlerObj in the factory so that this is unique by channel. It is more
easier then to use private variables for each channel (like session
informations or the sql connection).


Karasko wrote:
> 
> 3. I use MyCommandDecoder which extends frameDecoder handler to return an
> object specific to the protocol.
> 
> Protocol idea
> Request: command code (byte), arguments string length (integer, 4 bytes),
> string in bytes utf-8 encoding
> Respose: string, two 0 bytes
> 
> If it is enough data in buffer, it should return a MyCommand object.
> 
> 4. I use a handler to process business logic. It has an
> @ChannelPipelineCoverage("all"), but i still don't understand
> the whole meaning of this, but i know that annotation is only for warning
> purposes.
> 
As far as I understand, "all" means that the handlers in your pipeline are
shared among all channel (or at least one like your implementation where the
final handler (MyHandlerObj) is unique and shared among all.
"one" in contrary means that your pipeline is totally private for one
channel like I suggest to you.


Karasko wrote:
> 
> In the handler - if e.getMessage is MyCommand instance, than i call
> MyCommand.execute() method
> 
> <code>
> 	if (!(e.getMessage() instanceof MobileCmd)) {
>             ctx.sendUpstream(e);
>             return;
>         }
>         MobileCmd cmd = (MobileCmd)e.getMessage();
>        
>         String response = cmd.execute();
>         ChannelFuture future = e.getChannel().write(response+"\00\00");
> 
>         if (cmd.isClose()) {
>               future.addListener(ChannelFutureListener.CLOSE);
>         }
> </code>
> 
> Method MyCommand.execute() depending on request call some static method of
> DBMapper class to do the
> SQL queries with this class static connection field.
> 
> I can append all Java sources if it will help.
> 
> Thanks in advance )
> 
I think you have to try to not use static fields (or at least the less
possible). I made most of the time the same mistake (easier to program), but
when it comes to concurrency (and you want many clients) then static fields
can be source of problems (except if you set up some "synchronize" or lock
or concurrent object in your code.
Now, I prefer to make private date really private (unique for each client)
and sharing using concurrent objet (from concurrent class of Java) when it
is really necessary.
Again, for instance, sharing one sql connection among several clients can
lead to real problem since one sql connection is not really thread safe (as
far as I know) if many threads use it at the same time with no concurrency
control. And moreover it could bring you very bad performance issue due to
synchronisation between all threads...

This is my two cents, I can be completely wrong for you needs, so take it
simply as one open discussion from my own little experience... ;-)

Thank you too to be part of the community !

HTH,
Frederic

-----
Hardware/Software Architect
-- 
View this message in context: http://n2.nabble.com/Netty-server-with-MySql-database-backend%2C-how-to-do-it-right--tp2668426p2672307.html
Sent from the Netty User Group mailing list archive at Nabble.com.