Mysql Performance between go and php7

By specifying it in the connection string as in this example from the readme:

user@unix(/path/to/socket)/dbname
2 Likes

I continued testing and wrote a minimal webservice both in php and go. It returns a random result as json. php is 7.3 with opcache, apache 2.4 and mod_php.

package main

import (
	"database/sql"
	"encoding/json"
	"log"
	"math/rand"
	"net/http"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

const (
	dbUser = "root"
	dbPass = "secret"
	dbHost = "0.0.0.0"
	dbPort = "3306"
	dbName = "log"
)

const (
	startrecord = 1
	endrecord   = 200000
)

var stmt *sql.Stmt

type response struct {
	ErrorLogID, ErrorID int
}

func main() {

	var DBConnect string

	DBConnect = dbUser + ":" + dbPass + "@"
	DBConnect = DBConnect + "(" + dbHost + ":" + dbPort + ")/" + dbName + "?charset=utf8&autocommit=true"
	db, _ := sql.Open("mysql", DBConnect)

	rand.Seed(time.Now().UnixNano())

	var err error

	stmt, err = db.Prepare("select ErrorLogID, ErrorID from logErrors where ErrorLogID = ?")
	if err != nil {
		log.Fatal(err)
	}

	http.HandleFunc("/", index)

	http.ListenAndServe("localhost:8080", nil)
}

func index(w http.ResponseWriter, req *http.Request) {
	ran := rand.Intn(endrecord-startrecord) + startrecord
	row := stmt.QueryRow(ran)

	res := response{}
	row.Scan(&res.ErrorLogID, &res.ErrorID)

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(res)
}
<?php

$server = "0.0.0.0";
$user = "root";
$pass = "secret";
$database = "log";

$startrecord = 1;
$endrecord = 200000;

$conn = new mysqli( $server, $user, $pass, $database );

$ran = rand( $startrecord, $endrecord );
$query = "select * from logErrors where ErrorLogID=$ran";

$result = $conn->query( $query );

$row = $result->fetch_assoc();

header("Content-Type: application/json");
echo json_encode($row);   

I then tested them by using ab (apacheBenchmark) and sending 1000 requests, 100 at the same time.

ab -n 1000 -c 100 http://127.0.0.1:8080/

Php served ~380 requests/second and go ~600 requests/second.

Thanks,

Ok, after making that change and confirming it is no longer communicating over tcp, php7 is now only 1 second faster at doing 580,000 select queries. It is still faster but not by nearly as much. However, given the fact that it is not faster ( and I am still doing other tests) would you guys still recommend writing a RESTFUL service in Go instead of keeping it in PHP? I am worried that Iā€™m going to spend months transferring work over to the GO module to find a 1% gain in speed at which point the entire exercise was pointless.

For PHP:

We already know it.
Tweaking a single method only requires one file change and the rest of the service stays up.
Tweaking a method can be done live without shutting down the server
Queries are much easier to manage in PHP than in GO

Against PHP

It requires a good number of extra libraries to be loaded but this only is important for setup of new servers.
It requires nginx or apache to function as a service
It MIGHT be slower than GO but still researching by just how much if at all.

For GO:

It only requires a single binary to distribute and is therefore easier to setup on new servers.
It is a typed language with a compiler so some bugs can be caught up front.
It is cool    

Against GO:

It requires stopping the service, loading in the new binary and then restarting any time you want to hotfix
We haven't learned it yet so there is a steep learning curve.
Our services are very query intensive and therefore I'm not sure Go will be any faster at that level.
If the only speed benefit we get is through the other, "non SQL" related functions then would it be worth all 
the effort?


I was really looking forward to this project but am starting to wonder if I'm not just wasting time and making our environment more complex.

Thanks,

Glenn

Ok, that is something I can chew on. It is a fact that I donā€™t do 1 million queries in a single call. It would be a few queries on each request and therefore I should be monitoring network response times instead of actual query loops. Iā€™ll get on that next.

Thanks for waking up the brain,

Glenn

1 Like

Iā€™d not measure raw query time or even raw response times of a single request. Iā€™d check how the system behaves under load.

As far as I remember, in PHP a single operating system thread has to be spawned for each request, while in go a ā€œgreenā€ thread is used, scheduled by the go runtime. So the overhead of spawning/forking on the OS level is removed. Also Apache and NGinX do both limit the maximum of living PHP processes as far as I remember which results on some request queueing.

Another thing to consider is, that in GO you can use a pool of connections to the database, where in PHP each request has to create and maintain its own connection to the database.

It requires stopping the service, loading in the new binary and then restarting any time you want to hotfix

This is actually a proā€¦ I had a lot of issues because of the PHP way to just copy over the old scriptsā€¦

Even in PHP you should stop, delete, copy, start, or at least create other directory with new code, change config to use new code, reload config, delete old scripts.

I have to add thoughā€¦ If your shop really knows PHP and you have no one available who already did something larger in go who could teach the team, its probably best to stick to PHP.

Another advantage of Go is the scalability. If your application become bigger and heavy loaded you can easily distribute it around multiple servers by splitting into multiple microservices. You can also use the goroutines to make concurent access to database to reduce the time and many other tricks are possible.

Thanks for all the info guys. I am sticking to my guns and continuing my testing. Now that Iā€™m looking at this thing better I can see many more advantages to Go and am going to continue learning. As to everyone already knowing PHP that has never stopped me from moving. I used to only know Delphi at some point in my life. :slight_smile:

Glenn

2 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.