On Wed, 17 Feb 2016 16:29:53 GMT, Louie Bacani Foronda wrote:
https://github.com/eBookingServices/mysql-lited/issues/4
Although, I would advise you not to store connections. Connection should be locked for as little amount of time as possible, so that they get back to the connection pool and get reused as much as possible.
I took the above from your link, so basically how do you close the connection there is lockConnection but no freeConnection or releaseConnection, basically what I wanted to achieve is that upon running the server, I will initialize the connectionpool and keep it running until I close the server... when I was using DDBC + mysql-native I can basically run -c100 -t10s without error, but when I use mysql-lited, I get "too many connections" error, bad file descriptors, connection refused etc, I know that each of it is caused by some other events on the system, which prompts me to think that the connection is not being reused instead the code is spawning a connection for every single request, because the connection is not closed properly upon exiting, or the query is not being finished because the next query force closed the old connection, I was thinking of something like this in the class wrapper
auto insert(string[string] fv, string table){
conn.lockConnection(); // lock connection
scope(exit) conn.releaseConnection(); //release back to the pool on exit or error
// query codes
conn.execute("query here");
}
so that even if the code fails or error occurs, the connection will still be released as soon as it sees the closing bracket or as soon as the error occurs...
below is my source code for testing, afterwards i try to benchmark it using siege -c100 -t10s http://localhost:8080 and it fails...
class Db {
static MySQLClient client;
private MySQLClient.LockedConnection conn;
shared static this(){
client = new MySQLClient("host=127.0.0.1;user=root;pwd=rootpassword;db=mewmew");
this.conn = client.lockConnection();
}
static void query (string s=""){
//conn = client.lockConnection();
conn.execute("insert into users (name, email) values (?, ?)", "frank", "thetank@cowabanga.com");
}
}
here is a similar insert using my class that is using ddbc + mysql-native
class Db {
static DataSource ds;
MySQLDriver driver;
static string[string] params;
static string[string] config;
static string url;
shared static this(){
MySQLDriver driver;
driver = new MySQLDriver();
url = MySQLDriver.generateUrl(config["host"], std.conv.to!ushort(config["port"]), config["db"]);
params = MySQLDriver.setUserAndPassword(config["user"], config["pass"]);
ds = new ConnectionPoolDataSourceImpl(driver, url, params);
}
auto insert( string[string] values, string table ){
if(values.length <= 0){
return false;
}
else{
string[] placeholders = new string[values.length];
placeholders[] = "?";
string sql = "INSERT INTO `" ~ table ~ "`(`" ~ join(values.keys, "`, `") ~ "`) VALUES (" ~ join(placeholders, ",") ~ ")";
auto conn = ds.getConnection();
scope(exit) conn.close();
auto pstmt = conn.prepareStatement(sql);
scope(exit) pstmt.close();
int i=0;
foreach(key, val; values){
++i;
pstmt.setString(i, val);
}
if(pstmt.executeUpdate()){
return true;
}
else return false;
}
}
}
I like your approach on the mysql-lited, so I wanted to push through with it. I hope you could help me.
So LockedConnection has a destructor that sends the connection back to the pool, so it should be automatically collected when it goes out of scope. If you store it in a class, then it will require your class to be finalized, i.e. the garbage collector to run or you calling destroy() on it explicitly.
Our app opens anywhere from 1-4 connections to MariaDB per request concurrently. We run independent queries concurrently to reduce user-facing latency, and we serve roughly ~20 of these requests per second. Our max_connections in MariaDB is set at 200 if I am not mistaken, and we have never reached it yet.
Basically, we open a do auto db = connection(); at that top of a route handler, after validations and authentications and whatnot, and it goes back to the pool as soon as the request ends. If your requests are long-lived you might end-up hitting max_connections, yes.
I would also suggest you try the text protocol instead if you want to squeeze in more performance. We are now using that on our whole infrastructure, and at some point I might make that the default for the driver, but at the moment I didn't want to change it under anyone's feet. The text protocol has some advantages, particularly interesting one is that it allows query caching since MariaDB 10.1 doesn't cache prepared queries, which is a shame, really.
There is an example in the README on how to turn on the text protocol. You shouldn't see any difference in your user code, everything is the same.
I see you are building an insert function - I am not sure if you've seen it, but mysql-lited comes with a very convenient batch inserter that automatically builds the query, escapes everything and also throttles by packet length for you, so you don't get those mysql pack length exceeded errors. :)
If that is not dynamic enough for you, then you can build your query using the appender module also provided, which has an appendValue() function that will automatically escape all supported D values for you and append it to your query. The inserter I mentioned uses that internally, if you need an example.
At some-point I might make that more dynamic i.e. accept arrays of column names and structs for values, but haven't had a pressing need for it yet.
