How to organize a reliable infrastructure for a web project?

Question

DDoS Protection

Hello!
I have a few questions about building a RELIABLE infrastructure for our web portal (PHP Yii2 + Mysql + Vue js SPA, no dockers (except for a couple of microservice pieces) + Cloudflare)
I'll start with problems
1. network crash in DC, our server becomes unavailable
2. network lags in the DC, the network seems to be there, but some of the connections take a very long time and break off, the connection with adjacent systems collapses.
3. DDoS attacks

I would not want to store everything in one basket (DC), so I will separate the services into different DCs.
For example, the most important thing (DB) will be transferred to Amazon AWS RDS
The main powerful web server with SPA and Backend will remain in the current DC in the Netherlands
I want to make a backup web server in another DC
Both web servers will work with the same database in AWS RDS (MultiAZ for reliability).

But there was a problem - all SQL queries lag a lot (each 100-500ms, looked in the Yii2 Debug panel).
if in the client's personal account it is still tolerable, there are few requests, then in the admin panel it is a living hell, the page opens for 30 seconds.

Please share your experience (unless, of course, all your services work in different DCs, and not in one DC like Amazon)

I thought to keep Mysql MASTER-MASTER on the main and backup web servers, without a single SQL server, but the question arises - how will they lead master yourself if the connection break will be more than 3-7 hours?

in general, the main goal is to ensure that in case of breakdowns in one DC, the work of the resource does not stop and customers can use the portal.

Answer 1

For example, the most important thing (DB) will be transferred to Amazon AWS RDS
The main powerful web server with SPA and Backend will remain in the current DC in the Netherlands
I want to make a backup web server in another DC
Both web servers will work with the same database in AWS RDS (MultiAZ for reliability)

No, all components must be from the same provider, otherwise there will be huge delays.
Options from simple to complex (other than hiring an experienced architect):
- choose a reliable provider - cheapest and easiest option
- AWS (or GCP/Azure) - scatter components across different AZs
- multiple systems in different AWS regions, with GeoIP loadbalancing
- multiple systems from different providers (different data centers), loadbalancing Cloudflare or Incapsula, ...
In cases 3 and 4, you yourself must provide data replication.

Answer 2

Client - always works ONLY with one service server during one visit session. It can be either a separate structure or within the CDN structure. I prefer to use the second option.
Servers - constantly synchronize data asynchronously with each other (data exchange channel - always up!).
After the session is closed/changed by the client, a centralized notification occurs immediately to all servers and they queue up data synchronization for this particular user.
At the same time, the standard synchronization of database servers works in parallel in the normal mode.

all SQL queries lag a lot

Only a batch request pipeline with control over the load of the request-packet execution server and the necessary priority for executing all the necessary request-packages! The system must know (make its own decision!): when to fulfill the request, and when it is allowed to wait (prioritization).
Also, you can use HAProxy for fault tolerance / balancing, as a "head".
Or, as an alternative, Envoy .
PS:
1. Caching of statics and data from the database - did you forget to divide it into: EVERYONE, GUEST, USER?
2. Connection to the database - do not you reopen several times when you make calls to the database during the execution of the script?
3. Do you combine queries with stacks to get all the data you need with ONE query from the database?

Answer 3

It's called paranoia. You won't get 100% anyway. And if the application is interactive, also grab collisions. The fall of the data center is so rare that it makes no sense to bother.

Answer 4

Or maybe your problem is not in the network architecture, but still in the product architecture?