$userinfo = exec("python2 get_user_info.py ".$username);
PHP is perfectly capable of connecting to a MySQL database on its own. Look at the mysqli and PDO extensions. PDO is easier to learn if you are starting from scratch.
Alternately, Python is capable of generating web pages.
By doing things the way that you are, you are creating a PHP process that then invokes a shell process that invokes a Python script that calls the database. That’s three separate processes for each time this code is called (and that is skipping the Apache/Nginx and MySQL processes). You only need one, either the PHP or the Python, not both. And the shell process is pure waste.
The PHP exec is slower than running the same command in the shell directly. It is not meant to be used this way. I don’t know but wouldn’t be surprised if the PHP process was better about sharing resources when it calls MySQL directly. This is because many people use PHP to call MySQL, so that path is relatively optimized. I’m not sure that
exec is nearly as well optimized. In particular, I don’t know that it knows that it can pass control back to Apache while the shell script runs. But even if it does know, there is additional overhead at every step.
- PHP has to invoke the shell and wait for a response.
- Shell has to invoke Python and wait for a response.
- Python has to invoke the MySQL server and wait for a response.
And each of those “wait for a response” may not resume immediately on the response. As the server is doing other things at the same time and these waits are not linked. So when the MySQL server responds, Python gets the information and sends a message that is ready to respond. But the server may do other things before it relays that to shell, adding more overhead. Now the shell passes information to PHP. The server now needs to shut down the Python and shell processes. But we only needed one wait.
PHP needs information. The MySQL server has it. If they communicate directly, you only have that overhead for a single connection. You don’t need the extra two.
I don’t know if this will fix your server problems, but it can’t hurt. Note that the overhead here is more likely to make your server memory bound than CPU bound. If CPU or network bound, you probably have a different underlying problem. Although this would increase CPU as well as memory. It’s just that it increases memory by more.