Database latency - all suggestions appreciated, will compensate

[Rennur]

My current thinking is that this is a database issue (MySQL 5.6).

My suggestion would be to upgrade to 5.7 or 8.0 (inc. Connector/J to 8.1)

[kklosson]

I've spun up a replica db server with AIM installed so I can start going in that direction with some tests.
Thanks

[kklosson]

For those following this saga, I replicated the AWS database server to a new EC2 instance and installed a spare AIM license on it, running it locally (localhost). To my surprise, the latency was simply not there. All of the latent queries were normal. For example, a query that is now taking 33 seconds on the production server is taking 3. So I wonder what to make of this. Note that my production system is 1 App Server and 1 Database Server, both on AWS. Never a problem. Also, I fully experience the latency when running from localhost on the production app server. What have I learned?

1. Is reduced latency from running the app server on the DB server? I'm sure there cannot be that much, so I think not. What I am seeing is how the app always performed in production until this issue.
2. Is there something wrong with the production App or DB server? Okay, what? Both are running Windows Server 2008, but so is the Test DB server which is chugging nicely. It's created from a snapshot of the production DB server.
3. A connection problem between my App Server and DB Server? Why would it only affect some queries?

So before I take action, I'm looking for what action makes the most sense. Interested in your thoughts.

[ACDC]

So before I take action, I'm looking for what action makes the most sense. Interested in your thoughts.

I don't see the point in running a dedicated DB server within the context of an AwareIM app. It's just another thing that can go wrong, and I've always considered it less secure. Frankly, the obvious solution is to have the App server and DB server on the same machine, as you eventually did.

I did extensive testing with an external DB server a while back, and there was always a frustrating latency issue. Running it in a virtual environment made it even worse. So, I have a rule: stick with a bare-metal machine with enough CPU and RAM to handle the app efficiently. If the app grows and needs more resources, upgrade the hardware. If you can't "scale up" the hardware with more cpu and ram, then "scale out" by adding another server.

Regarding the problem you're facing, it's probably something messing with your DB server setup. But really, who cares? Just switch back to a single server setup and be done with it. You may even get a general increase in overall efficiency.

[PointsWell]

I don't see the point in running a dedicated DB server within the context of an AwareIM app. It's just another thing that can go wrong, and I've always considered it less secure.

Hard disagree on this. Database should always be segregated from App server and even more so if you are running AIM without a proxy server.

[ACDC]

Hard disagree on this. Database should always be segregated from App server

Could you provide some reasoning behind this, other than just making a statement?

my reasoning was efficiency and shutting down a point of direct access to the DB, not to mention the complexities that go with managing a DB server exposed to the network (leave the proxy server out of the equation for the time being)

[PointsWell]

The ideal set up is 3 tier.

Code: Select all

         Webserver -----> AIM -----> DB Server
Ports    80, 443          8080       3306
IP       Public           Private    Private
Address

Webserver can only receive via 80 and 443, webserver can only communicate via 8080 and only to AIM server
AIM server only receives on 8080 and can only communicate on 8080 to web server and to DB server on 3306, which you can configure to be a more obscure port to be extra.
Database should be able to receive traffic only on port 3306 unless you changed it and only from the private IP address of the AIM server

There's a wealth of published papers on the efficacy and safety of this not least from DDoS which if you put everything on one server and attract malicious traffic, then you not only lose your web facing traffic, you lose your ability to interact with AIM and you lose access to your data.

Running everything on the same server is a honey pot for malicious traffic and you are more likely to set the ports incorrectly.

Also if you use AWS RDS instead of a self managed server you benefit from a backup schedule for your data.

There's a whole thread on why a reverse proxy is a good idea here

[kklosson]

For those following and especially those who lent their ideas and active assistance, thank you. We discovered the root cause of this issue.

Shame on me, I've been running on two Windows 2008 servers on AWS for 13 years. Re-platforming was on the agenda. In one post above, I described how the latency issue went away when the app and db servers were combined. This pointed to a network issue. My consultant also tested a large file transfer between the two servers and observed a significant lag in the two-server configuration not present in the single-server configuration. With some deep digging, he learned that AWS had published updated (virtual) network adapters for these older images along with some specific recommended settings. Once implemented, the problem was solved.

Lesson: keep your stack modern (I knew that - just didn't do it).

All of your contributions are highly appreciated.