vapor/fluent-mysql-driver

Dramatic decrease of performance on Ubuntu

DanielsCode opened this issue · 10 comments

DanielsCode commented

Hi,
I have a very weird behavior .... my raw mysql calls take on mac just 0.02 seconds but on my ubuntu server it takes over 400 seconds ...

Log messages:

on my mac: 
[ DEBUG ] pluginHandler Get Plugin informations (WebAppController.swift:230)
[ DEBUG ] pluginHandler Finished within 0.0155929327011108 seconds (WebAppController.swift:242)
on my server (ubuntu): 
[ DEBUG ] pluginHandler Get Plugin informations (WebAppController.swift:230)
[ DEBUG ] pluginHandler Finished within 429.439689993858 seconds (WebAppController.swift:242)

It seems, that I just happened if I use raw sql queries like in this code:
https://gist.github.com/DanielsCode/ed2af6375acd8eb8ee932982e88f38a9

If I execute this sql statement via a sql console I will get a response in milliseconds.

Looking forward to get some feedback.

Cheers,
Daniel

proggeramlug commented

Can you verify that the mysql-server is the same and that the connection from server-to-server and mac-to-server is the same as well?

DanielsCode commented

Sure ... I did some further investigations - current setup:

Mac version with SQL connection to the Ubuntu server:

[ DEBUG ] pluginHandler Get Plugin informations
[ DEBUG ] pluginHandler Finished within 0.0193359851837158 seconds

Mac connection to the SQL Server (Ubuntu) via Sequel Pro:
16 rows in 3.7 ms

It has something todo with raw sql statements in combination with ubuntu.

vzsg commented

I don't remember from our discussion on Discord, did you also check the query with MySQL's command line tools on Ubuntu?

DanielsCode commented

yes, here are the results of the command line on Ubuntu: 16 rows in set (0.00 sec)

Tested SQL-Statement:

SELECT Plugin.*, count(Component.pluginId) AS numberOfComponents 
FROM Plugin LEFT JOIN Component ON (Plugin.id = Component.pluginId) 
GROUP BY Plugin.id ORDER BY numberOfComponents DESC;

Any ideas?

DanielsCode commented

Another interesting aspect:

The routes are secured with authSessionMiddleware. If I turned it off - everything works fine even with raw sql statements.

Ubuntu server without protected routes (session):

[ DEBUG ] pluginHandler Get Plugin informations 
[ DEBUG ] pluginHandler Finished within 0.011944055557251 seconds

so the problem is fluent with raw sql statements in combination with auth/sessions ....

Code:

    func boot(router: Router) throws {
        router.get("plugin", use: pluginHandler) // Is working with 0.02 seconds
        
        // Implement Session Security
        let authSessionRoutes = router.grouped(User.authSessionsMiddleware())
        let protectedRoutes = authSessionRoutes.grouped(RedirectMiddleware<User>(path: "/login"))
        // protectedRoutes.get("plugin", use: pluginHandler) // Is working with 400+ seconds
    }
tanner0101 commented

@DanielsCode what are you using to store the sessions cache / how are sessions configured in your app?

DanielsCode commented

Do you mean my configure.swift?

    // Register middleware
    var middlewares = MiddlewareConfig() // Create _empty_ middleware config
    middlewares.use(SessionsMiddleware.self)
    services.register(middlewares)

    // At the end of the function ...
    // Enable Sessions for WebApp
    config.prefer(MemoryKeyedCache.self, for: KeyedCache.self)

Do you need additional information? @tanner0101

ezfe commented

I'm also having this issue, first noticed it on Friday (July 20). I did make changes involving raw usage around this time.