Page 1 of 1

MySQL Slow

Posted: 12 Mar 2020, 21:34
by ceej
hello, We've been using Smartfox for a couple years and leveraging a HyperSQL database. Relatively simple setup and currently only a small development team accessing our application. We never had any problems with HSQL but decided to move over to MySQL for something more robust. We've stood up a basic MySQL 8 installation and ported our DB into it. Switching Smartfox to use MySQL was simple and things seem to work.

But we've found that if we send more than a few extension requests from the Unity client in rapid succession(like after an OnRoomJoin event) through Smartfox to perform simple queries on the DB, MySQL gets bogged down and doesn't respond with the results immediately. May take a few seconds and gets worse the more requests you send. The requests themselves work fine, its just the short time frame in which they are submitted to Smartfox that causes the slowdown.

Going back to HSQL, I've found we can send tons(tried upwards of 100) of the same type of extension requests simultaneously and they are completed very quickly by Smartfox/HSQL.

We've tried 2 different MySQL servers with similar results. There aren't errors in MySQL and the query times seem OK on that end.

SFS host OS: Windows 2016 Server (also tried Windows 10)
SFS: 2.14
MySQL 8.0.19. And v8 jar in SFSFX\lib

The request handler can't be any simpler, right? And our queries generally return only a small number of rows if not just a single one.

Code: Select all

private void handleRequestList(User player, ISFSObject params)
        SFSExtension ext = getParentExtension();
        Zone zone = ext.getParentZone();
        IDBManager dbManager = zone.getDBManager();

        SFSObject result = new SFSObject();
        result.putText("cmd", "list");
        result.putBool("ok", true);
        result.putText("msg", "");
            ISFSArray rows = dbManager.executeQuery(sql, new Object[] {});
            result.putSFSArray("rows", rows);
        catch (SQLException e)
            result.putBool("ok", false);
            result.putText("msg",  "Failed query");
        ext.send(prefix, result, player);

I assume I'm missing a MySQL setting beyond the default configuration. Thanks for any pointers.

Re: MySQL Slow

Posted: 13 Mar 2020, 09:07
by Lapo
is the MySQL server running on the same machine with SFS2X?
If so query times for a simple SELECT should be in the range of 2-4ms. I've tested on a Mac laptop running MySQL 5.6 and SFS2X 2.15 with a query that looks like this: "SELECT * FROM countries LIMIT 10"

This retrieves 10 records with two fields each, simple stuff, and runs in ~3ms.
If the server is running on another machine I would suggest to double check the latency of the connection.

Let us know

Re: MySQL Slow

Posted: 19 Mar 2020, 19:31
by ceej
Hi, sorry for the delay betting back.
Yeah, MySQL is on same server as SFS. HyperSQL is too and shows quick response. Where MySQL runs into trouble is with lots of requests in rapid succession.
We're going to try MSSQLServer instead of MySQL and see what we get....

Re: MySQL Slow

Posted: 20 Mar 2020, 09:00
by Lapo
Ok, let us know.


Re: MySQL Slow

Posted: 20 Mar 2020, 19:54
by Rob
You could try MariaDB instead. It's very similar to MySQL so it's easy to migrate to.

Re: MySQL Slow

Posted: 24 Mar 2020, 20:52
by ceej
MS SQL Server works very well too during our tests. Still unclear why MySQL isn't agreeing with our configuration. But I think we have a path forward with MSSQL

Re: MySQL Slow

Posted: 25 Mar 2020, 08:34
by Lapo
Thanks for the update. It may have to do with the MySQL config... though it's hard to guess what it could be, without more details.