non-persistent db connection in a kelp app

[Fth Bdg]

Hi all,

i just wanna share something about rdbms/db based app.

There are many different ways to connect a Kelp app to a database.

One of them is by using lazy initialization via "attr" :

# lib/App.pm :

attr dbh => ....

but then, this gives us persistent connections : one connection for each forked child process.
there are many articles out there mentioning that non-persistent connection is much safer. one of them :
http://stackoverflow.com/questions/3765925/persistent-vs-non-persistent-which-should-i-use

Persistent connections do not bring anything you can do with non-persistent connections.
Then, why to use them, at all?


so how to get a non-persistent connection in a kelp app ?
Change this line in lib/App.pm :

attr dbh => DBI->connect(......)

into this :

sub dbh {
    shift->stash->{_dbh} ||= DBI
        ->connect(......)
}

and then add this method for cleanup :

sub before_finalize {
    delete shift->stash->{_dbh}
}

then, we can use dbh method as usual : $self->dbh->....

suggestions/comments are welcome, thanks

fatih

[Maurice Aubrey]

I have always used persistent connections without any problem. In fact, at least in the olden days, persistent connections were a huge performance improvement, since you eliminated the overhead of the connection startup. It may depend on the database. I use Postgresql with persistent connections, and they've never been an  issue.

Maurice

[Fatih]

Thank you for the sharing of experiences in postgresql / huge perf.improvement using persistent connection.

I mostly using mysql/mariadb for web apps, so I don't have many experiences of using other rdbms.

I'd like to add a few notes here, in terms of safety/robustness of the psgi app (not in terms of performance) when using db connection (mysql for example).
Let's start by writing a small kelp app :

# app.psgi :

use lib 'lib';
use App1;

my $app = App1->new();
$app->run;


the application class (using attr dbh => sub {...} for db connection) :

# lib/App1.pm :

package App1;
use Kelp::Base 'Kelp';
use DBI;

attr dbh => sub { DBI
    ->connect("dbi:mysql:cs", "root", "", {RaiseError => 1}) };

sub build {
    my $self = shift;
    my $r    = $self->routes;

    $r->add('/dbTest', sub {
        my $self = shift;

        my $row = $self->dbh
            ->selectall_arrayref("select id from admin limit 1");

        return $row->[0][0]. "\n";
    });
}


1;

then, starting the server :

$ plackup -r
Watching ./lib app.psgi for file updates.
HTTP::Server::PSGI: Accepting connections at http://0:5000/

making / issuing http requests to the psgi server :

$ GET 'http://127.0.0.1:5000/dbTest'
1

$ GET 'http://127.0.0.1:5000/dbTest'
1


What would happen when unexpected things occur in the connection :
- database server is down
- network problem between db server and web (app) server
- etc.

Let' simulate this :
 
$ su -c 'service mysqld restart'
Password:
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]


making http requests again to the server :

$ GET 'http://127.0.0.1:5000/dbTest'
DBD::mysql::db selectall_arrayref failed: MySQL server has gone away at lib/App1.pm line 15
    App1::__ANON__('App1=HASH(0x163fd48)') called at ...
...

$ GET 'http://127.0.0.1:5000/dbTest'
DBD::mysql::db selectall_arrayref failed: MySQL server has gone away at lib/App1.pm line 15
    App1::__ANON__('App1=HASH(0x163fd48)') called at ...
...


People then cannot access our web based app, eventhough the db server has been up (again) & available.
the website becomes unusable.

the same observation results can also be achieved by other simulation methods :

• by killing the connection id (via mysql interactive shell; after getting conn id from mysql command: show processlist)
• by using another plack server (such as perforking starman), we'll also get the same result
  

now, let's try a non-persistent connection, by replacing this :

attr dbh => sub { DBI
    ->connect("dbi:mysql:cs", "root", "", {RaiseError => 1}) };

with this :

sub dbh { shift->stash->{_dbh} ||= DBI

    ->connect("dbi:mysql:cs", "root", "", {RaiseError => 1}) }

sub before_finalize { delete shift->stash->{_dbh} }

$ GET 'http://127.0.0.1:5000/dbTest'
1

then, let's simulate db connection/link problem

$ !su
su -c 'service mysqld restart'
Password:
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ GET 'http://127.0.0.1:5000/dbTest'
1

$ GET 'http://127.0.0.1:5000/dbTest'
1

by using non-persistent connection, our app becomes usable / ready again as soon as database has also been up again.

thanks,

fatih

[Stefan Geneshky]

Fatih,

The DBI module has a ping method which you can use to reconnect an expired/closed connection. You can wrap your dbh variable in a sub that pings it first and reconnects if needed.

Stefan

[Alvar Freude]

Hi,

> Am 26.03.2016 um 04:24 schrieb Fatih <fati...@gmail.com>:
>
>
> What would happen when unexpected things occur in the connection :
> - database server is down
> - network problem between db server and web (app) server
> - etc.

You can use DBI->connect_cached instead of ->connect; this caches the connection and reconnects after failure.

http://search.cpan.org/dist/DBI/DBI.pm#connect_cached


The best and most robust persistent connection manager for Perl ist Apache::DBI; it uses timeout (only does ->ping after some time) and much more; but it only works with the good old mod_perl.

> I mostly using mysql/mariadb for web apps, so I don't have many experiences of using other rdbms.

Try PostgreSQL – you never want MySQL/Mariadb back ... ;-)


Ciao
Alvar


--
Alvar C.H. Freude
http://alvar.a-blast.org/ | http://blog.alvar-freude.de/
Coole Sachen: http://www.assoziations-blaster.de/ | http://www.wen-waehlen.de/

[Alvar Freude]

> Am 24.03.2016 um 10:19 schrieb Fth Bdg <fati...@gmail.com>:
>
> there are many articles out there mentioning that non-persistent connection is much safer. one of them :
> http://stackoverflow.com/questions/3765925/persistent-vs-non-persistent-which-should-i-use
>
> Persistent connections do not bring anything you can do with non-persistent connections.
> Then, why to use them, at all?

this is wrong, persistent connections have some huge benefits:


No Startup time. If you have a RDBMS with fast startup time, you may have TCP overhead if the DB is on an other host. If you have good and optimized queries, this does matter. If you have 1000 SQL queries per page, then it doesn’t matter, but then there is some junk SQL ;-)

You can use prepared statements effectively. Real RDBMS can use prepared statements to cache the query plan for a query. Building the query plan can take longer then executing the query (see EXPLAIN output of PostgreSQL since 9.4, it prints both times when using ANALYZE and TIMING, e.g. run EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING) SELECT 1).
When you have good optimized queries, cost of planning is often higher then the execute time, here an example of a query with 81 lines query plan including some joins and subplans:

Planning time: 1.638 ms
Execution time: 0.772 ms

[Fatih]

Thanks for all the valuable suggestions.

1. DBIx::Connector

Stefan shared his suggestion to use 'ping' method to check connection.
so, i tried a cpan module : DBIx::Connector, which seems to have a wrapped ping-like mechanism.
here's the application class, using attr method :

package App1;
use Kelp::Base 'Kelp';

use DBIx::Connector;

attr connector => sub { DBIx::Connector
    ->new("dbi:mysql:cs", "root", "", {RaiseError => 1}) };

sub build {
    my $self = shift;
    my $r    = $self->routes;

    $r->add('/dbTest', sub {
        my $self = shift;

        my $row = $self->connector->dbh->selectall_arrayref(
            "select id, connection_id() from admin");

        return join( ", ", @{ $row->[0] } ). "\n";
    });
}


1;

then, several http requests were issued/sent to test it :

$ GET 'http://127.0.0.1:5000/dbTest'

1, 5

$ GET 'http://127.0.0.1:5000/dbTest'

1, 5

we got a persistent connection (tcp/socket mysql-client connection was still the same between different http request; it can be observed by mysql-connection-id which was always : 5).
then, simulating a database-down event / network problem :

$ mysql -u root -e 'kill 5'

issuing http requests again :

$ GET 'http://127.0.0.1:5000/dbTest'

1, 7

$ GET 'http://127.0.0.1:5000/dbTest'

1, 7

the app automatically got a new db connection (connection-id changed), and this new connection is still persistent (connection id was always the same : 7)


2. DBI's connect_cached method

Hi, Alvar, thanks for pointing this. 'connect_cached' can also be used for persistent connection. the application class :

package App1;
use Kelp::Base 'Kelp';
use DBI;

sub dbh { shift->stash->{_dbh} ||= DBI

    ->connect_cached("dbi:mysql:cs", "root", "", {RaiseError => 1}) }

sub build {
    my $self = shift;
    my $r    = $self->routes;

    $r->add('/dbTest', sub {
        my $self = shift;

        my $row = $self->dbh->selectall_arrayref(

            "select id, connection_id() from admin");

        return join( ", ", @{ $row->[0] } ). "\n";
    });
}


1;

issuing several http requests :

$ GET 'http://127.0.0.1:5000/dbTest'

1, 2

$ GET 'http://127.0.0.1:5000/dbTest'

1, 2

it's persistent (connection-id was always: 2).
then, forcing to close socket connection :

$ mysql -u root -e 'kill 2'

$ GET 'http://127.0.0.1:5000/dbTest'

1, 4

$ GET 'http://127.0.0.1:5000/dbTest'

1, 4

successfully automatically reconnected to db, persistently.


3. lite ORM

in a medium-large web app development, chances are we will use an ORM.
Using ORM can also help us to provide friendly/easy development environment for junior developers or beginners (for example: dynamic where clause, paging, etc.)
here's what i've tried using DBIx::Lite :

package App1;
use Kelp::Base 'Kelp';

use DBIx::Lite;

attr connector => sub { DBIx::Connector
    ->new("dbi:mysql:cs", "root", "", {RaiseError => 1}) };

sub db { DBIx::Lite->new(dbh => shift->connector->dbh) }

sub build {
    shift->routes->add('/dbTest', sub {

        my $row = shift->db
            ->table("admin")
            ->select_also(\"connection_id() as conn_id")
            ->single();

        return join( ", ", $row->id, $row->conn_id ). "\n";
    });
}


1;

testing persistent connection

$ GET 'http://127.0.0.1:5000/dbTest'

1, 4

$ GET 'http://127.0.0.1:5000/dbTest'

1, 4

testing dropped/killed db connection :

$ mysql -u root -e 'kill 4'

$ GET 'http://127.0.0.1:5000/dbTest'

1, 6

$ GET 'http://127.0.0.1:5000/dbTest'

1, 6


thanks again for all, i think i've got a safe persistent db connection.
about PostgreSQL, i've seen techempower.com's benchmark results (we can find benchmark results for kelp too in there), and from the results, we can quickly/roughly summarized that PostgreSQL nowadays is also very good in terms of speed/performance.