MySQL/MariaDB optimisation

[jnijland]

I've got some issues with the MySQL connection: [17:33:39 WARN]: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 50,480,786 milliseconds ago. The last packet sent successfully to the server was 50,480,789 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Usually, I tweak the keepalive settings et cetera, however, this is currently not possible. Could that be done? Besides that, for a big server with many users online, might connection pooling (e.g. HikariCP) be a better option?

This all happened during the following error (on player join):

[17:33:39 WARN]: [FancyHomes] Statement: CREATE TABLE IF NOT EXISTS fancyhomes_data (ID int NOT NULL AUTO_INCREMENT,UUID varchar(36) NOT NULL,Player varchar(16),Name varchar(20) NOT NULL,Created timestamp NOT NULL DEFAULT NOW(),Icon varchar(20),World varchar(36) NOT NULL,X double NOT NULL,Y double NOT NULL,Z double NOT NULL,Yaw float NOT NULL,Pitch float NOT NULL,PRIMARY KEY (ID));

I might be incorrect, but does this mean that the plugin checks if the table exists and otherwise create it on every player join? Shouldn't this be done only once during the initialisation process of the plugin?

Sorry for all my questions :) I love your plugin and so do my players but I'm a bit of a performance guru.

[MikeNipkow]

I might be incorrect, but does this mean that the plugin checks if the table exists and otherwise create it on every player join? Shouldn't this be done only once during the initialisation process of the plugin?

That's correct. However it does have some advantages for testing purposes and will guarantee that the plugin runs stable if a user manipulates the database. However this might be an issue where the user can be blamed. As the table is cached in MySQL this should not take up any noticable amount of resources at all, but this might be adjusted if needed.

Usually, I tweak the keepalive settings et cetera, however, this is currently not possible. Could that be done? Besides that, for a big server with many users online, might connection pooling (e.g. HikariCP) be a better option?

I just digged into the autoReconnect argument that is set for the connector, but it looks like this is not the go-to-solution. This needs to be adjusted in one of the upcoming versions. Hikari might be added later on.

[MikeNipkow]

Update: The autoReconnect property is set in the code and will therefore correctly reconnect to the database. However the JDBC driver will still throw the mentioned warning. The code also checks a valid connection before executing a query.

Therefore this should be just a nice-to-have adjustment and does not break the functionality.