MySQL Slow

Post here your questions about Actionscript and Java server side extensions development.

Moderators: Lapo, Bax

ceej
Posts: 6
Joined: 12 Mar 2020, 20:21

MySQL Slow

Postby ceej » 12 Mar 2020, 21:34

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.

Specs:
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", "");
       
        String sql = "SELECT LOCATION_NAME, DISPLAY_NAME FROM LOCATIONS";
         
        try
        {
            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.
Chris
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: MySQL Slow

Postby Lapo » 13 Mar 2020, 09:07

Hi,
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
Lapo
--
gotoAndPlay()
...addicted to flash games
ceej
Posts: 6
Joined: 12 Mar 2020, 20:21

Re: MySQL Slow

Postby ceej » 19 Mar 2020, 19:31

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....
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: MySQL Slow

Postby Lapo » 20 Mar 2020, 09:00

Ok, let us know.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games
User avatar
Rob
Posts: 53
Joined: 01 Jul 2017, 07:33

Re: MySQL Slow

Postby Rob » 20 Mar 2020, 19:54

You could try MariaDB instead. It's very similar to MySQL so it's easy to migrate to.
ceej
Posts: 6
Joined: 12 Mar 2020, 20:21

Re: MySQL Slow

Postby ceej » 24 Mar 2020, 20:52

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
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: MySQL Slow

Postby Lapo » 25 Mar 2020, 08:34

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.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games

Return to “Server Side Extension Development”

Who is online

Users browsing this forum: No registered users and 28 guests