I am currently building my first REST API, based around an RSS aggregator. I have it implemented using one of two traits, either a MemoryBasedDB or PostgresDB. On each access to the root url, it will make an asynchronous call out to the feed to grab the newest articles, and return that as an XML string for parsing. After parsing, it is persisted in the database as an Article object.
Functionally, it's fine either way for me. However, when load testing with weighttp or gatling it will fail under 1k requests/1k concurent users using Postgres with the following:
In weighttp:
error: read() failed: Connection reset by peer (104)
And in my server logs:
final [WARN] [09/21/2014 14:45:27.224] [on-spray-can-akka.actor.default-dispatcher-36] [akka://on-spray-can/user/IO-HTTP/listener-0/523] Configured registration timeout of 1 second expired, stopping
I believe it has something to do with the way my queries are laid out. They are blocking and as each actor has to wait for a response, the load behind them piles up higher and higher to the point of failure (timeout). However, in my research I have only been able to find this asynchronous driver for postgres which is currently incompatible with Squeryl (to my understanding).
How can I make DB access faster? Currently I am achieving ~10-15req/s using Postgres, and ~400req/s using in-memory persistence.
My model:
case class Article(id: Option[String], idint: Option[Int], title: String, author: String, published: String, updated: String, `abstract`: Option[String], content: Option[String], link: Option[String])
My queries:
trait PostgresDB extends Schema {
val articles = table[Article]("articles")
on(articles)(e => declare(e.idint is(unique)))
def create(x: Article) = inTransaction {
articles.insert(x)
}
def getAll: Set[Article] = inTransaction {
from(articles)(article => select(article)).toSet
}
def getArticle(x: Int) = inTransaction {
from(articles)(article => where(article.idint === Some(x)) select(article)).toList(0)
}
def printy = transaction {
articles.schema.printDdl(println(_))
}
}
So far I have tried:
Relevant info:
Yes I agree with @experquiste, give the db actors their own despatcher and tune its thread pool size and number of actors to the number of concurrent requests that your db can handle. Place a router in front of this. You should measurer the database servers disk queue length. This should be stable under sustained high load, keep adding threads until you the queue starts to grow.
Another approach is to use a thread pool and futures for your db access layer. It seems to be easier to configure, but lacks supervision and error recovery. http://www.chrisstucchio.com/blog/2013/actors_vs_futures.html personally I still use actors for concurrency.
I have never used squeryl, do the inTransaction blocks create db transactions? The database trait you show doesn't seem to need transactions, have you tried without them.