which memory limit applies when php performs a MySQL query

Guido Goluke

5/20/24, 6:25 PM

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?

209

1 + 0

mysql

php

memory

memory-usage

Score:0

Server

Rick James

5/20/24, 10:35 PM

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.

+ 5

Guido Goluke

5/22/24, 6:39 AM

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?

Rick James

5/22/24, 4:00 PM

PHP's memory limit does not include MySQL's. However, each will occupy some portion of RAM until PHP terminates.

Guido Goluke

5/24/24, 8:46 AM

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?

Rick James

5/24/24, 3:27 PM

@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.

Guido Goluke

5/25/24, 6:19 PM

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!

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: which memory limit applies when php performs a MySQL query

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.