John Davidson

Message:

Got an obscure one. Been at this one for weeks. Here is what we’re doing:

• Upgrade of Silverstripe 3.1 -> 3.7


• Upgrade of Platform. PHP 5.6 -> 7.4, Postgres 9.5 -> 13


• Old system is running mod_php, New is running PHP_FPM


• Parallel hosting systems running. Old and New. Both have identical resources.


• Performance tests run on both systems for Old -> New comparisons.


• Both platforms consist of two App nodes, and one DB node

Most tests are great. PHP 7.4 obviously smokes the old system. But one test is Apache Bench just hitting home page. Which shows a pretty serious degradation on the New system. Digging further into this, we created a series of test files for Apache Bench to hit that isolate specific things - Static HTML, Raw PHP, PHP + Raw pg_ commands and queries.

All thrash the old system except the latter. DB interactions. So drilling down further we created a new target file that simply does a pg_connect() and immediately closes it, then hit that with Apache Bench. Same result as the query test script. Suggesting the deficit is in the action of pg_connect(). Database connections.

Clearly there's a lot of variables in this issue. And I'm loath to pack this question out with a bunch of details about the tests (and other tests like pgbench) until needed. I'm more hoping to stumble across someone who has observed a similar issue with pg_connect() specifically, and has even the most random idea for an angle to test.

Other testing ideas would be welcome. One thing we are trying to work out right now is how to get connection timing from pgbench to see if the issue exists on Postgres itself. Our tests are as follows:

• Apache Bench hitting several target pages


• PGBench (showing the new system performing better than the old)


• PHP test


• jMeter is being set up now

UPDATE

We've done a bit of manual digging in the Postgres logs in each environment, and see something a bit strange looking at the received/authorised log entry pairs like this:

2021-05-19 11:34:25.708 NZST,,,404734,"10.220.218.21:50560",60a44f01.62cfe,1,"",2021-05-19 11:34:25 NZST,,0,LOG,00000,"connection received: host=xx.xx.xx.xx port=50560",,,,,,,,,""

2021-05-19 11:34:25.908 NZST,"db_user","db_name",404734,"xx.xx.xx.xx:50560",60a44f01.62cfe,2,"authentication",2021-05-19 11:34:25 NZST,7/486524,0,LOG,00000,"connection authorized: user=db_user database=db_name",,,,,,,,,""

I captured a bunch of these pairs on both environments (Old and New) and calculated the gap between the recieved log message and the authentication log message.

On Old, the average is 0.011s. On New, the average is 0.195s. Difference aside, this makes no sense, as the test page on the application node of that environment takes ~0.02s to complete in full.

---

My Answer:
PostgreSQL is a powerful open-source relational database management system that is commonly used in web development. PHP is a popular server-side scripting language that is often used in conjunction with PostgreSQL to create dynamic web applications.

PHP provides a way to connect to a PostgreSQL database using the PDO (PHP Data Objects) extension or the pgsql extension. With these extensions, developers can execute SQL queries, fetch data from the database, and perform various database operations within their PHP code.

Overall, PostgreSQL and PHP work well together to create robust and scalable web applications that can handle large amounts of data efficiently.