mysql - PHP->PDO() a query with about 500 byte content needs 100 MB of PHP memory suddenly
Get the solution ↓↓↓I've had a server reboot and something changed.
Suddenly tiny SQL queries using PDO to MYSQL require roughly 90MB of memory. It uses 3 times the highest input buffer.
MEM: 3586264 / 4718592
MEM: 96740584 / 98304000
MEM: 96740584 / 98304000
The code is as simple as possible:
$a=memory_get_peak_usage(false);
$b=memory_get_peak_usage(true);
echo "MEM: $a / $b\n";
$pdo->query("SELECT * FROM `results` WHERE `customer_id` = '456' AND `jobname` = 'job1' LIMIT 1")
$a=memory_get_peak_usage(false);
$b=memory_get_peak_usage(true);
echo "MEM: $a / $b\n";
$pdo->query("SELECT * FROM `results` WHERE `customer_id` = '456' AND `jobname` = 'job1' LIMIT 1")
$a=memory_get_peak_usage(false);
$b=memory_get_peak_usage(true);
echo "MEM: $a / $b\n";
Of course I checked the query, the content is indeed tiny, it's just a single row of a few varchar fields.
PDO is initialized like this:$opt = [PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_MAX_BUFFER_SIZE => 1024 * 1024 * 30];
I noticed the error when I increased the MAX_BUFFER_SIZE to 60MB. Suddenly my application was way beyond 140MB memory. It seems to use max_buffer_size as MIN_buffer_size*3
It seems like PDO is using 3 times the max_buffer_size variable as storage buffer for tiny queries. Which makes no sense at all ..
It's PHP 5.6.30
I just don't know where to look anymore, I debugged it down to a single query and now I am stuck, that's a PHP internal allocation.
Update The issue was likely present before reboot, just unnoticed. I don't know what triggered it but changing the library to php-mysqlnd solved it. No memory usage anymore.
Answer
Solution:
It seems that's a driver bug in the php-mysql driver, it allocates massive amounts of memory based on the MAX_BUFFER attribute and does not release it anymore when using PDO.
I switched to the native driver mysqlnd and the issue vanished.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: cannot set properties of undefined (setting '_dt_cellindex')
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.