Java application slow when connecting to MySQL server when not on LocalHost - Stack Overflow

admin管理员组

文章数量:1125487

TLDR at the bottom

I'm fairly new to java and have been studying for around 2 years in my spare time.

I've recently decided to try my hand at making some retail software for my small business.

I am using Java to create the main application and all products, sales, etc, are stored on a MySQL server I have setup.

When I was initally putting my program together, I had the database on the local machine and it was all running smoothly.

I then made the database accessible over the network by changing the ip address from local host to the computer hosting the database.

I seem to be able to access the database quickly enough when connecting via MySQL Workbench, but when I run the program, it seems to take maybe 10/15 seconds for a simple query to be returned.

Here is the java code I use to connect to the server:

import javax.swing.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private Connection connection;

    public DatabaseConnection() {
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connection URL
            String url = SystemSettings.sqlAddress;
            String username = SystemSettings.sqlUsername;
            String password = SystemSettings.sqlPassword;

            // Establish the connection
            connection = DriverManager.getConnection(url, username, password);

            // Connection successful
            if (connection != null) {
                System.out.println("Connected to the database!");
            }

        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC driver not found.");
            JOptionPane.showMessageDialog(null, "error" + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database.");
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("Database connection closed.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The url I am using is "jdbc:mysql://192.168.56.1:3306/my_database";

I'm using the mysql-connector-j-9.0.0.jar module/driver.

I have looked around the posts on here and various places online, but I am mainly finding people that are having problems connecting to the database rather than just having a slow connection.

When I access files on the host computer or run a ping, it is a very quick response time. I have also switched off the firewall to see if that was having an impact and the result was the same

I'm still an amateur at coding at the moment so I apologise if it looks a mess

Edit

One thing to point out is that each time my program queries the database it will open a new connection and then close it when it has finished the query. I have just created a new testing class and kept the connection open then ran several queries which were coming back instantly. So should I re-write the code so it keeps connections to the database open while the program runs? I had it close the connection each time as I thought that was what you were meant to do

TLDR I made a java program that connects to a mysql server. It worked fine when the server was on local host, but became very slow (10+ seconds to retrieve queries) when I moved it to a machine that could be accessed within the network

Can anyone offer any advice on this?

TLDR at the bottom

I'm fairly new to java and have been studying for around 2 years in my spare time.

I've recently decided to try my hand at making some retail software for my small business.

I am using Java to create the main application and all products, sales, etc, are stored on a MySQL server I have setup.

When I was initally putting my program together, I had the database on the local machine and it was all running smoothly.

I then made the database accessible over the network by changing the ip address from local host to the computer hosting the database.

I seem to be able to access the database quickly enough when connecting via MySQL Workbench, but when I run the program, it seems to take maybe 10/15 seconds for a simple query to be returned.

Here is the java code I use to connect to the server:

import javax.swing.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private Connection connection;

    public DatabaseConnection() {
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connection URL
            String url = SystemSettings.sqlAddress;
            String username = SystemSettings.sqlUsername;
            String password = SystemSettings.sqlPassword;

            // Establish the connection
            connection = DriverManager.getConnection(url, username, password);

            // Connection successful
            if (connection != null) {
                System.out.println("Connected to the database!");
            }

        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC driver not found.");
            JOptionPane.showMessageDialog(null, "error" + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database.");
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("Database connection closed.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The url I am using is "jdbc:mysql://192.168.56.1:3306/my_database";

I'm using the mysql-connector-j-9.0.0.jar module/driver.

I have looked around the posts on here and various places online, but I am mainly finding people that are having problems connecting to the database rather than just having a slow connection.

When I access files on the host computer or run a ping, it is a very quick response time. I have also switched off the firewall to see if that was having an impact and the result was the same

I'm still an amateur at coding at the moment so I apologise if it looks a mess

Edit

One thing to point out is that each time my program queries the database it will open a new connection and then close it when it has finished the query. I have just created a new testing class and kept the connection open then ran several queries which were coming back instantly. So should I re-write the code so it keeps connections to the database open while the program runs? I had it close the connection each time as I thought that was what you were meant to do

TLDR I made a java program that connects to a mysql server. It worked fine when the server was on local host, but became very slow (10+ seconds to retrieve queries) when I moved it to a machine that could be accessed within the network

Can anyone offer any advice on this?

• java
• mysql

Share Improve this question Follow edited 2 days ago spectation asked 2 days ago spectationspectation 1122 bronze badges New contributor spectation is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 6

• not a java coder, however, try : step through the code with a debugger, see what bits are taking the time, if unfamiliar with a debugger (add to your learning list :-) ), put debug statements around in sensible places with timestamps to help narrow down 'slow bits'. try updating to later/latest version of all components (if possible) – ticktalk Commented 2 days ago
• There are probably many factors that could contribute to such a problem. The broad 'reason' could be that your LAN is slow. If you run something like iperf3 on that host and on the host that's the mysql client then you should get some measure of how fast 'the LAN' is – g00se Commented 2 days ago
• So you have one pc that run Db and you trying to access it from another device? Is it still local network?(I guess so as begining of ip 192.168.x.x). Are you sure you suppose to use it as usually ip addresses ended by .1 is some kind access point gateway (not sure how it called, not internet guy). And if I remember correctrly there is some Profiler tool in MySql, so may be you can activate it and see, does you queries taking long time inside server or not. And as someone already said use debugging to check if slowness comeing from code. Otherwise it is your internet connection. – maximelian1986 Commented 2 days ago
• @g00se I'm not sure if I have done this correctly, but I ran a loopback test (i think its called) with iperf3 on the same computer and was getting 5.78GBytes transfer and 49.6Gbits/sec Bitrate (roughly). I figured I could run it on the same computer as the slow speed issue seems to be the same whether I run the program on the host computer or any other computer – spectation Commented 2 days ago
• @maximelian1986 Correct, it is within the local network. I'm not sure regarding the ip address and access point gateway. I've always been terrible with networking. I will see if I can look into this profiler tool – spectation Commented 2 days ago

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 0

Ok so I managed to solve the issue. It turns out that I am an idiot.

The url I was using was "jdbc:mysql://192.168.56.1:3306/my_database"

The actual IP address shown on MySQL workbench for the server is 192.168.1.60

I have no idea how the program was even connecting to the database when it was given the wrong IP address, but when I corrected it, my program was connecting instantly and returning all queries straight away!

Thank you for trying to help, guys. For the future, if you see me posting about a problem I am having, just tell me I'm an idiot and you will most likely be correct!

It is important to understand that the usual flow of a production application is not to open and close connections several times, as that is an expensive / slow operation.

Ideally, you would use some form of connection pooling - you keep some connections to the server open, and when you need to perform a query you pick one of the connections that is idle, perform the query, and return the connection to the pool. If no connections are idle, usually you can set a configuration to either wait for one to be idle or to open a new one (that will belong to the pool once it becomes idle).

There are several libraries that can perform connection pooling for you on Java; a quick search returned this result https://www.baeldung.com/java-connection-pooling

本文标签： Java application slow when connecting to MySQL server when not on LocalHostStack Overflow