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?
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.
-
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? May 22 at 6:39
-
PHP's memory limit does not include MySQL's. However, each will occupy some portion of RAM until PHP terminates. May 22 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? May 24 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. May 24 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! May 25 at 18:19