0

I tried to connect MySQL to my scala-Akka code. In the receive method, I have 3 URL's and for these URL's, I implemented a simple hit counter. So whenever I visit that particular URL, I insert a row into a table which will be the (URL, count, its timestamp). But I noticed its too slow. Completing only 180 requests in 10 seconds! What can I do to improve performance? Please help!

import akka.actor.{Actor, ActorLogging, ActorSystem, Props}
import akka.http.scaladsl.Http
import akka.http.scaladsl.model.{ContentTypes, HttpEntity}
import akka.http.scaladsl.server.Directives._
import akka.pattern.ask
import akka.stream.ActorMaterializer
import akka.util.Timeout

import scala.concurrent.duration._
import scala.io.StdIn
import java.sql.{Connection, DriverManager, PreparedStatement, Timestamp}
import java.time.{LocalDateTime, LocalTime}
import java.util.Date

import akka.http.javadsl.server.Route


object DBCP3 {


  final case class fetch1(param:String)
  val sql = "INSERT INTO URLInfo (url, time, count)" + "VALUES (?, ?, ?)"

  val url = "jdbc:mysql://127.0.0.1/url"
  val driver = "com.mysql.jdbc.Driver"
  val username = "root"
  val password = "SomePassword"


  Class.forName(driver)
  var connection = DriverManager.getConnection(url, username, password)
  val date = new Date




  private var number1: Int = 0
  private var number3: Int = 0
  private var number2: Int = 0

  def insertIntoDB(path: String, num: Int) = {
   val stm: PreparedStatement = connection.prepareStatement(sql)

    stm.setString(1, path)
    stm.setTimestamp(2, new Timestamp(date.getTime))
    stm.setInt(3, num)
    stm.execute()
  }

  class ActorA extends Actor with ActorLogging {
    def receive = {
      case fetch1(param) =>
        if(param=="path1") {
          number1+=1
          insertIntoDB("http://localhost:8080/path1",number1)
          context.sender() ! number1

        }
        if(param=="path2") {
          number2+=1

          insertIntoDB("http://localhost:8080/path2",number2)

          context.sender() ! number2
        }
        if(param=="path3") {
          number3+=1

          insertIntoDB("http://localhost:8080/path3",number3)

          context.sender() ! number3
        }

    }
  }

  def main(args: Array[String]) {
    implicit val system = ActorSystem("my-system")
    implicit val materializer = ActorMaterializer()
    implicit val executionContext = system.dispatcher
    implicit val timeout: Timeout = 1.seconds



    val actor1 = system.actorOf(Props[ActorA], "SimpleActor1")


    val route = concat(
      path("path1") {
        get {

          onComplete((actor1 ? fetch1("path1")).mapTo[Int])
          {
            number => complete(HttpEntity(ContentTypes.`text/html(UTF-8)`, s"<h1>You visited $number times</h1>"))

          }
        }

      },


      path("path2") {
        onComplete((actor1 ? fetch1("path2")).mapTo[Int])
        {
          number => complete(HttpEntity(ContentTypes.`text/html(UTF-8)`, s"<h1>You visited $number times</h1>"))

        }


      },

      path("path3") {

        onComplete((actor1 ? fetch1("path3")).mapTo[Int])
        {
          number => complete(HttpEntity(ContentTypes.`text/html(UTF-8)`, s"<h1>You visited $number times</h1>"))

        }


      }
    )

    val bindingFuture = Http().bindAndHandle(route, "localhost", 8080)
    println(s"Server online at http://localhost:8080/\nPress RETURN to stop...")
    StdIn.readLine() // let it run until user presses return
    val _ = bindingFuture
      .flatMap(_.unbind()) // trigger unbinding from the port
    connection.close()
  }
}

ashishjohn
  • 69
  • 7
  • I dont think it depends on Akka + MySql, in 60 seconds i inserted 200 thousands records like this for (a <- 1 to 200000) { connection.createStatement().executeUpdate( s"INSERT INTO some(sender_id, receiver_id) VALUES (${a}, ${200000 - a})" ) } – Dmitry Reutov Oct 12 '19 at 16:54
  • 1
    You are using only 1 Actor. Actors process the messages atomically, which means `exactly one by one`. Since you are doing 180 requests in 10 seconds... in simple terms that would have meant that that `actor1` is taking roughly 1/18 seconds to process that message. But there is a lot more which is going on. ActorSystem manages the execution of all the actors in it... using a thread-pool. So, You are using the same thread pool for both your `actor1` and akka-http which does the rest of things like request-processing etc. – sarveshseri Oct 12 '19 at 17:01
  • 1
    Now, the actor-system's default thread-pool is `2 * number-of-cores`... so if you have 4 cores... you have a thread pool of size 8. And your implementation of `insertIntoDB ` is a blocking implementation... which means that while you do your db-call, you are blocking the same thread-pool which was supposed to do everything else. If you are running this on a laptop... you most likely have s thread-pool of size 4... which is doing everything despite getting blocked by your db-calls. – sarveshseri Oct 12 '19 at 17:08
  • And then you are making these all `db-writes` to be guaranteed atomic by making use of a the same actor. I think a throughput of 18/second is pretty decent for this implementation. – sarveshseri Oct 12 '19 at 17:09
  • @SarveshKumarSingh Understood!! How do I alter the code to get around 8000 requests atleast? – ashishjohn Oct 12 '19 at 17:11
  • You can improve the code (to do as best as possible...) by being non-blocking everywhere but the throughput limits will depend on your machine. One step will be to just get rid of that actor and directly call `insertIntoDB` in your routes. As for making `insertIntoDB` non-blocking, first of all you will not be able to maintain those `numberX` counters in a non-blocking implementation. Other than this you will require a "relatively non-blocking" sql library. You can look at `doobie` for this. – sarveshseri Oct 12 '19 at 17:18
  • And you need to know that 8000/second is a really high expectation... – sarveshseri Oct 12 '19 at 17:21
  • Thankyou so much @SarveshKumarSingh!! Really appreciate your help! :) – ashishjohn Oct 12 '19 at 17:23
  • Your DB updates will run order of magnitude faster if you remove all indices on the table so that inserts become appends. – Igor Urisman Oct 12 '19 at 21:42
  • There are few things that you can do to increase the performance, use database connection pooling, and since you are asking a single actor, it will block any other messages to the same, I don't think you need to use an actor for the same, you can use libraries like slick with hikaricp and get a decent performance improvement. – Shankar Shastri Nov 03 '19 at 08:41

0 Answers0