MySQL database cluster vs. replication setup for large crawler indexes What is the most efficient way to set up a MySQL cluster or replication in an application that has a crawler/indexing engine with thousands of read/writes and then a separate front-end for public access.

It is kind of like creating a mini Google search engine. The database that keeps the latest crawl data has to be separate in a way not to impact the performance of the publicly-accessed database. However, eventually the two databases need to be reconciled so that the publicly-accessed database has all the latest data.

So my idea would be the crawler DB would be the master, while the publicly accessed database would be the slave. And then edit/insert actions on the publicly assessed database would really need to be done on the master DB (same as crawler).

Is this the best way to do such a thing? Is there a better setup?

More information:

Currently there is a lot of reading/updating/inserting at a very fast pace while the crawler is running. 

For example, the crawler could send 10 businesses with location and items for each business per every 2-3 seconds, at which, the receiving server needs to do the following. Search DB if business already exists (out of few hundred thousand records), if not create it. Then search if that location (out of half a million records) for that business exists, if not, create it. Then loop through each item of the business and check if it exists (out of 40+ million records), update, if not, create it. Old ones are backed up and then deleted.
Crawler:


Put this process on a separate server (or multiple servers).
If practical, use multi-row INSERTs to batch-insert the rows.
See this for more tips on high-speed ingestion:  http://mysql.rjweb.org/doc.php/staging_table
Write to the Master (or, if allowed by the cluster, any of the Masters).


For the web side:


Have multiple servers with Apache / nginx / whatever receiving the requests.
Send the requests to the Slave(s) (or nodes).
Use round-robin (if there is more than one server for reading).


Other things:


300% CPU is high.  Let's see the worst offender, plus SHOW CREATE TABLE.
Your dataset will probably be very large, so shrink datatypes where practical, and normalize where appropriate.  (The above link has more on normalizing.)
So far, a single Master seems to be adequate, hence any of the replication or clustering options is still viable.
All writes are performed on all servers.  This puts a cap on how much you can scale.  The only way to go beyond that is via "sharding".
Read scaling can be achieved by adding more Slaves, virtually without limit.  This applies to all flavors of replication/clustering.
Indexing -- Use FULLTEXT for searching for "words"; use regular INDEXes where appropriate.
Since neither FULLTEXT nor INDEX handles "model numbers" and "synonyms", you may need to sanitize the data as it is inserted.