SUPER SLOW Specify 6 and Cloud hosted MySQL

Get Help
1

Hi everyone. We decided to move our DB’s to the cloud from local hosting.

But we are expericieng an awful delay runing queries (from almost instant results locally to queries that are now taking 3-5 minutes for results), anyone here with tips on how to optimize this?

I know Specify6 is obviously not optimized for cloud (I hope Specify 7 is?) mysql hosting but seems a bit weird that it just drags.

Running a MySQL server with 8 CPU’s and 16GB of ram…

Any help is appreciated.

2

Hi @ray, by moving from local to the cloud, do you mean a database hosted on a local area network, or the localhost of your computer?

In my opinion, when moving from local to the cloud, additional factors such as network speeds and geographical location of the cloud datacenter relative to your location can have an impact on the speed at which you are able to perform actions.

I would recommend:

  1. Performing a general internet speed test, or a ping test to the connectivity between your computer and the database location.

  2. Look at the metrics of the database server if available when performing actions. Is it running close to threshold for either CPU or RAM? How does this new server compare in specs compared to the local server you were running before?

  3. If possible, locating the cloud closer to your location may improve speeds. If you are using something like AWS, DigitalOcean etc you should have the ability to pick the region in which your server will be located, and picking one closer to you may reduce latency.

  4. (Edit) Additionally, how do speeds of running raw SQL queries compare between the two environments. This could help isolate whether it is Specify specific or a general decrease in speeds. You could try running the same SQL query in both environments and compare.

3

Hi @ray , are you using a hosted MySQL service or a virtual server? For PostgreSQL I have found that the service (which was Azure in our case), does not have great performance, unless you throw serious money at it (which I have not tried). On the other hand, on a virtual server your specs of 8 CPU and 16 GB RAM should be more than enough. I myself have MariaDB running on an Azure general purpose production server with 4 CPU and 16 GB of RAM without problems. And that server runs a lot of other stuff as well. We have at least ten staff using Specify every day.

I had a problem at first with the query I use for the IPT, which took 35 minutes locally and took almost four hours on the Azure server (with both MariaDB and the IPT on the same server). When I took MariaDB out of the Docker compositions (and using my old IPT instance) I got that down to about 50 minutes, which is fine. I never noticed any delay in the Specify clients. In fact, when working from home and using a VPN, Specify 6 has become a lot faster (or less glacial I should say).

4

Hi, thanks for the reply, yes, the new DB’s are store on a VM with enough resources. I actually did a search here in the forum and I can read from other users that Specify6 is not optimized to be setup with a non local network DB.

I did a little bit of tweaking on query cache and runs a bit faster, not as before, but sort of works for now.

We will start thinking about moving to Specify7, since it’s cloud based, hopefully that will fix our issue.