Short description:
We have multiple standalone database instance (rdbms) for mysql and RESTAPI server for each, on different region. Each RESTAPI server will need to connect other DB instance to grab data. We are also unable to move on no-sql DB now so mysql is our only choice.
What strategy would be better if we want grab data from other instance? Currently we are using mysql_connect() for each request to connect & fetch data as we've a single instance and single rdbms now.
Is there any benefit is we rewrite our code using mysql_pconnect or pdo and create one connection for each server before starting processing request & then grab data from that specific server ?
Details:
We already developed a RESTAPI service with a single server instance and a separate rdbms instance. Now we are expanding the server as the number of active users are increasing rapidly. Also all the users are grouped by region and we are not allowed to store his data outside of his region. So we can't use a centralized DB system.
We know which data is stored in which DB. Problem is how we will connect to the remote database and grab data from there. Which strategy will be better for designing a heavy traffic multi-instance server?
If there are four(4) DB instance & each time we use mysql_connect() function to connect remote db then there will be 1x4 socket (port) allocation. then if we need to process >10000 request concurrently the more than 40000 port or resource need to allocated (not sure if works that way).
I'm a newbie on multi-server system and not interested to reinventing wheel again. Any suggestion will be appreciated. Please help me to over come this problem? Thanks.
Short answer:
MySQL is deprecated, so PDO is the answer.
Long answer:
Someone else may provide ;p.