2

So far we have a server with 2 databases and a mysql user that accesses any of them. For example

`select * from magento.maintable, erp.maintable`

now our erp is very slow and we want to separate our database on another server, but we have hundreds (almost a thousand) sql queries that have access in the same query to the two databases, for example

`insert into magento.table 
select * from erp.maintable`

or select * from erp.maintable inner join magento.table... and more and more

How can I make everything work the same without changing these queries? but with the databases on different servers

To access the databases I have created a class for each database and through an object I make the queries, insertions, updates and deletions, like this

`    public function exec($query, $result_array = true)
{
    $this->data->connect();
    $result = $this->data->query($query, $result_array);
    $this->data->disconnect();
    return $result;
}`

all help is welcome, the point is to find an optimal way to do this and not have to manually change 1000 sql queries made by another programmer

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rafael Pastor García
  • 15
  • 1
  • 4
  • Additional information request - from your ERP server. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Oct 18 '20 at 10:41
  • Thanks Wilson I have a doubt, the code create a nw conecction foreach script maybe those scripts are run hundreds of times every morning and the connection won't close, is there a way to do that automatically? and not having to go script by script destroying the mysql object This is the object that is created $sql = new mysqli('localhost', 'user_db', pass_db') or die(mysql_error()); – Rafael Pastor García Oct 19 '20 at 11:07

1 Answers1

3

To access more than one database server in one query, you either have to use FEDERATED database engine or use replication to replicate the ERP-data from another server to the original one.

The use of FEDERATED engine is likely to cause additional performance problems and the replication requires some work to set up.

If the sole reason for the new server is the performance in ERP, you might want to see why the ERP is slow and try to solve that (optimize, move both databases to a new server, etc). When you have both databases on the same server, the query optimizer is able to combine and make efficient use of indexes.

slaakso
  • 8,331
  • 2
  • 16
  • 27
  • Hello slaakso, thanks for your reply We have bouht an VPS server for database for 1 year then migrate database is needed (in principle, everything can be aborted) What is replication? Can you explain it to me with your words, I can't quite understand it in the link you gave me Thanks – Rafael Pastor García Oct 16 '20 at 06:10
  • Replication is basically copying of data from a database in one server to a database in another server. MySQL replication does this for you in the background. It would allow for you to move the ERP to another server, but still keep the old code working on top of the replicated data. What I would suggest, is to hire a consultant to take a look why your ERP is slow. Not likely that the database server could not handle the two databases on the same server and if solved, your setup would be simpler to maintain. – slaakso Oct 16 '20 at 09:03