Database latency - all suggestions appreciated, will compensate

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by kklosson »

I've spun up a replica db server with AIM installed so I can start going in that direction with some tests.
Thanks
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: [UPDATE] Database latency - all suggestions appreciated

Post by 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.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
ACDC
Posts: 1135
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 1451
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Database latency - all suggestions appreciated, will compensate

Post by PointsWell »

ACDC wrote: Thu Sep 07, 2023 8:38 pm 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
Posts: 1135
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Database latency - all suggestions appreciated, will compensate

Post by 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)
Last edited by ACDC on Fri Sep 08, 2023 6:22 am, edited 1 time in total.
PointsWell
Posts: 1451
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency [RESOLVED]

Post by 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.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
Post Reply