I don’t know a lot about networks. I’m hoping some of you might be able to help.
Wehave a customer who are experiencing a very slow response time when querying a MySQL database. The database is hosted with our internet provider. When I run the application from our network, searches take about 1-2 seconds where as for them it can often be 30-40 seconds per query and never as fast as what I experience.
Our Broadband speeds are 25 Down/15 Up/26ms Ping/23ms Jitter and we are using a wireless network
Our customer’s speeds are 95 Down / 2.6 Up 21ms Ping/12ms Jitter and are using an Ethernet network.
They only have an issue with the Xojo app, all other networking, file serving, browsing apps work without and issue for them.
We are using Xojo’s standard MySQLCommunityPlugin with Xojo Release 3.1
Would anyone know possible causes for this issue or how I could start looking at finding the source of the problem?
what if they connect to the mysql database with mysqladmin or equivalent from their network ?
what is the siez of the database / number of records max on the tables
I have a Xojo app that uses MySQL. When the database is on the same local network, response is almost instant. When the database is remote, queries typically take a second or two depending on the complexity of the query and amount of data being returned. I haven’t done any actual measurements.
Can you try connecting to the database from your customer site using MySQL Workbench (or other database tool) to make sure its not a Xojo issue?
Does your ISP have any rate limiting devices? This is common for email servers - if you’re sending more than x messages per second, you’re probably spamming. They could be rate limiting other services as well.
Is your database server using the default port (3006)? If so it is likely under constant attack. MySQL has its own IP blocking in addition to anything your ISP may have.
How does your app handle connection failures? It either needs to create a new connection for each query, or be able to reconnect if it gets an error. The reconnect attempt needs to try once or twice and then give up. If it retries in a long or endless loop, it might succeed after a while giving the appearance that the query takes a long time to run.
Thanks very much for responding Eric and Jean-Yves
Jean-Yves, there are about 100,000 records in the primary and 200,000 in the child table with search fields indexed. If the number of records were an issue, would I also experience speed issues from our offices?
mysqladmin is a good suggestion, which I will try, thanks.
Eric, yes, I experience the same from our side here. If I install the database locally, it’s effectively instant, and when I access through the internet database, it’s the 2-3 seconds.
Again, the workbench is also a good suggestion. I will test to isolate the issue - I’ll need to go on-site so it will take a little while to report back on the results.
The port is 3306 and my server is a virtual server configured by myself running Centos 7 so there shouldn’t any IP blocking. If there was, would this not stop all communication rather then slowing things down?
Connection failures is a good question. Once the connection is made, it tries to keep the same connection open. If there are any issues, it drops and tries to reconnect (it will attempt 3 times and then fail). Because the connection is trying to establish, it cannot save problem to the audit table. I will check the system’s local log.
I have skip-name-resolve enabled but would you know if enabling skip-name-resolve would be worth exploring?
I presume you’ve used “explain” before your query to test the method it is using to perform the query. It’s useful for spotting lacking in structures. Assuming that side is good the amount of data transferred will have a significant effect on the speed. Beware of using “select *” simply to make it easier to write the query. Typical suggestions include only retrieving the data you require.
MySQL workbench should be able to provide both the query execution time and the transit time for the retrieval of the data. Other things to wonder about is if the plugin is retrieving all the data before the first result is returned, or is it pulling just the first result. Obviously that would be faster.
Thanks Ian and Eric for your replies. Very helpful. Yes. Xojo pulls down all at once, which I’m glad of as I don’t have to handle anything asynchronously. I limit and page for large amounts of data. Explain and Workbench were very helpful, but the primary issues seems to be an intermitted network speed
Thanks again everyone for taking the time to respond.
