0

I have 5 DB servers (one in Reunion, one in Mauritius, one in Mayotte and two in mainland France. I made an API on our Marseille server which will pick the data in each BDD, (our other servers are accessible "locally" thanks to a core network if I don't say something stupid). Everything works well, but when you select on several servers it is long

I do not know if there is a way to keep the different connections in the cache, or to set up a service that manages the connections or I do not know what, if someone has a solution I am a taker. The server is in PHP 5.4.16, Oracle DBMS, oci_connect connector, and I made a stateless API with authentication by JWT. Basically we have a ping of 200 ms between Marseille and the other servers, but the creation of the connection takes between 2 and 3 seconds.

  • I think you would have a connection pool. Is this a valid answer https://stackoverflow.com/a/41362106/4210091? – Renato Jan 17 '20 at 12:58
  • I don't know exactly what i have to implement because i have to connect to 5 different servers, so maybe i have to "store" 5 connections "objects" in cache, and maybe thoses connections will manage pooling. But I think there is a step before Oracle pooling – franck garros Jan 17 '20 at 13:52
  • https://stackoverflow.com/questions/39753/connection-pooling-in-php - it sounds like you need to have 5 different database connections, and a connection pool for each. 200ms is a long time, though, and may suggest you've got bandwidth as well as latency problems... – Neville Kuyt Feb 12 '20 at 16:15

1 Answers1

0

Due to the process-based design of PHP and the use of stateless requests, there isn't a connection pool API in the OCI8 extension. What you should use instead is persistent connections with the oci_pconnect() call. This retains connections from one HTTP request to be usable by the next, so it is much faster.

(Internal to the OCI8 implementation, persistent connections do use Oracle's Call Interface session pool API with a fixed poolsize of only one connection. I.e. each PHP connection has its own pool. This is useful since the session pool is the technology that allows use of DRCP (see other comments), and also Oracle documentation on high availability recommends using session pools, e.g. for FAN events).

Make sure not to oversize the number of PHP processes that run. This will minimize the number of DB connections needed, allowing more reuse. It will also reduce the amount of memory required on each DB server to handle the connections, since they will remain open. Only if the DB servers run out of memory, would you then look at using DRCP pooling.

Reduce Apache process restarts, remove network firewall connection timeouts, and remove DB resource limits so idle connections are not killed. You want those 'persistent connections' to be persistent so they are immediately available for use when your app calls oci_pconnect(). Otherwise OCI8 will have to (internally) recreate connections unnecessarily.

Next, you need to reduce 'round-trips' between PHP and the database for the application, since over a slow network they really have an impact on performance. Move as much work into the DB as possible, e.g with PL/SQL.

If you run statements like ALTER SESSION after each oci_pconnect() call, move them into a LOGON trigger. Or at least wrap them with a PL/SQL block:

begin
     execute immediate
       'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
     -- other SQL statements could be put here
end;

This block only takes one round-trip if executed from PHP. If you did the same in a LOGON trigger it would take zero round-trips.

For normal statement execution, see if you can similarly put calls into a single anonymous PL/SQL block and use a single oci_parse() call.

Next, tune queries that fetch multiple rows by adjusting prefetch size. Again, this can help reduce round-trips.

If do you data uploads, consider using oci_bind_array_by_name(), or even calling out to a Python (with the cx_Oracle extension) and using executemany(), since these (again) reduce round-trips.

These are basic tips. You might find others in the manual examples or The Underground PHP and Oracle Manual. Or you might want to consider an alternative architecture, perhaps something that batches up requests and executes them on the "remote" database, and then sends all the results back.

Not related to the performance of the app, but since you have 'remote' databases, you may want to make use of Oracle Net features to detect dead network connections and avoid connect 'hangs' waiting for TCP timeouts. Look at creating a sqlnet.ora file where you run your PHP app and using options like SQLNET.OUTBOUND_CONNECT_TIMEOUT. If you can't stop firewalls from killing otherwise idle connections, you can use ENABLE=BROKEN in a connect string.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48