This article covers how to allow remote connections to a MySQL server. Hosting databases and web servers on a separate database server can improve security, hardware performance, and enable you to scale resources quickly.
One of the most frequently observed database bottlenecks encountered by large projects is high MySQL traffic. Sure, we could talk about moving to a bigger cloud instance size, faster cores, and better storage, but that’s too obvious and costly. Instead, we would like to focus on several tips that can reduce traffic with a bit of configuration.
Methods to reduce Internet traffic when connecting to the remote host:
- Disable and/or optimize the high-load features of your application.
- Use ProxySQL to enable connection pooling. This will prevent your application from overloading MySQL with a multitude of concurrent connections. Another nice feature of ProxySQL helps cache query results for a certain period of time.
- Monitor your databases for performance bottlenecks.
- Identify and optimize queries that cause high load. If you use dbForge Studio for MySQL, you get a specialized tool—Query Profiler—which helps you deal with this task easily.
- Add missing indexes and eliminate redundant and unused ones.
- Conduct a thorough check of your background operations. Some of them can be postponed, some may work just as effectively with limited resources (e.g. limited concurrencies for batch jobs), and some may be perfectly running on replicas.