How to make the project architecture correctly and which database to choose?

Question

Software design

Hello, there is a task to make a portal similar to the price pulse - pulscen.ru
What is required:
- Main site (test.ru) - authorization / registration, creation of a sub-site, control panel, products from sub-sites, filter
- Sub-sites (*.test. ru), which are created automatically when created on the main site
I go to the main site (test.ru), register my company and the site d3.test.ru is automatically created (3 is the serial number of the new site ++) rights to edit this site are given
I can add a product, when adding a product to my new site (d3.test.ru) I need it to be duplicated on the main site test.ru
The main site shows all the products that have been added to subsites (*.test.ru) with the company, site address on which the product was added
How I did it:
Based on the laravel framework and the MySQL database
I decided to make 2 separate platforms
1 - for the main portal site test.ru
2 - for all subsites *.test.ru
Both platforms are connected to the same database

A little about how everything works

1 - the main site test.ru
The categories tables are used in the

database - General categories for use on subsites *.test.ru
id | title | slug | description | photo | meta_title | meta description | view

prods - All products from sub sites *.test.ru
id | title | slug | up_text | down_text | category_id | price | nalichie | delivery | rating | meta_title | meta description | view

sites - All created sites
id | name | site_url | email | phone | city_id | rating | prefix | rating

2 - subsites *.test.ru
Each subsite uses its own file with the .env configuration, it differs only in the prefix for the database
Each subsite uses its own tables, they differ in the prefix d3_(3 - serial number of the site)

d3_prods - All products of the site d3.test.ru
id | prod_id | title | slug | up_text | down_text | category_id | price | nalichie | delivery | rating | meta_title | meta description | view - a new column prod_id has appeared, it contains the id of the product located on the main site
That is, when adding a product to the d3.test.ru subsite, the product is first added to the d3_prods table, then the user chooses to publish / not publish on the test.ru portal, and if yes, then this product is added to the prods table - from here we take its id and update the prod_id field in the d3_prods table for this product (the prod_id field is needed, in case of editing a product on a subsite, in order to find the product in the prods table by prod_id and also change the data in it)

d3_settings - settings for the subsite (name, description, logo, phone, email, address)
d3_seos - SEO settings for pages (meta_title, meta_description)
d3_pages - dynamic pages

And while everything was going well, but when more than 150 sub-sites had already been created and more than 5 ml of goods were added, everything started to hang terribly, it was almost impossible to enter the database.

Despite the fact that, in my opinion, a good server is used
Processor 26 X Intel (R) Xeon(R) Silver 4114 CPU @ 2.20GHz
RAM 6000 / 30249 MiB (average used / total) Disk
space 4.19 TiB

Question?
How to make a database structure for such a task, which database to choose?

Answer 1

You are asking the wrong question, start with why it hangs, where exactly is the bottleneck. Maybe there are no corny indexes or another bunch of reasons.