If I run php as FPM, I can set my memory limit in php.ini or .user.ini. PHP can perform a MySQL query, and I can set memory limits in /etc/my.cnf for for instance the innodb buffer pool. If PHP runs a query, which memory limit applies?
Add a comment |
1 Answer
The two are independent. But, assuming you have both running on the same machine (this is not a requirement), they must share the available RAM.
php.ini has an upper limit for each instance of PHP. Often the web server will launch many instances. The default is usually adequate. If you have some PHP pages that need more, you can do a set_ini at the start of any that might need more. (Example: image processing) I do not change the limit until I encounter an "out of memory" message from PHP.
innodb_buffer_pool_size is the main tunable for MySQL. As a first cut, set it to 70% of available memory after allowing for reasonable usage by other apps. I realize this is rather vague, but it usually works.
answered May 20, 2023 at 22:35
- Thanks. But, when PHP delegates a query to MySQL, does it mean the work is no longer under the responsibility and therefor memory limit of PHP?Guido Goluke– Guido Goluke2023-05-22 06:39:37 +00:00Commented May 22, 2023 at 6:39
- PHP's memory limit does not include MySQL's. However, each will occupy some portion of RAM until PHP terminates.Rick James– Rick James2023-05-22 16:00:25 +00:00Commented May 22, 2023 at 16:00
- So the memory PHP consumes in this hypothetical situation is the memory needed to pass the query to MySQL and other specific PHP stuff, and then MySQL consumes the memory it needs to perform the query under its own process?Guido Goluke– Guido Goluke2023-05-24 08:46:06 +00:00Commented May 24, 2023 at 8:46
- @GuidoGoluke - Correct. MySQL's memory is relatively large and constant due to its use of RAM for caching. PHP has some space for the program, plus some transient space for data. If, for example, you fetch a million rows from a table, PHP's RAM usage will grow by many MB briefly, while MySQL does not change much.Rick James– Rick James2023-05-24 15:27:13 +00:00Commented May 24, 2023 at 15:27
- Ah yes, and on a sidenote, you can decrease the amount on memory PHP uses when iterating through MySQL rows by using a generator. OK, more clear now. Thanks!Guido Goluke– Guido Goluke2023-05-25 18:19:41 +00:00Commented May 25, 2023 at 18:19