MySQL Slow
Posted: 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.
I assume I'm missing a MySQL setting beyond the default configuration. Thanks for any pointers.
Chris
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