Scala, Akka-Http and mysql - Too slow

by ashishjohn   Last Updated October 12, 2019 18:26 PM - source

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()
  }
}



Answers 1


Since the db may be the primary bottleneck I try an answer.

If you can afford it from a consistency point of view you could batch the sql inserts, see a discussion here, and pack that operation in a separate thread, not blocking the rest of the processing. Batching may increase db throughput by a factor 3 as of my experience. In production the db may run on a separate server, that may change the load profile further. Here's another supposedly async db api, mybe worth a look

MySql docs promise a factor 20 taking the route over files and the LOAD DATA command. Here's a benchmark measuring 320000 inserts per second using this feature and 200000 with bulk ("extended") inserts.

Removing/disabling indexes on the table may benefit too, avoiding AUTOCOMMIT, using multiple db sessions to insert and generally fiddling with the db server parameters. If you hit the mysql performance limit and it is still too slow, you could switch to a key-value store like Redis.

curiosa
curiosa
October 12, 2019 18:20 PM

Related Questions


Http Websocket as Akka Stream Source

Updated January 09, 2018 20:26 PM

akka-http-testkit with akka-actor-testkit-typed

Updated August 16, 2019 07:26 AM

File upload using Akka HTTP

Updated August 14, 2017 16:26 PM

MJPEG streaming via akka http

Updated April 03, 2019 18:26 PM