PHP->PDO() a query with about 500 byte content needs 100 MB of PHP memory suddenly

Question

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.

It does seem high, but why are you using PDO::MYSQL_ATTR_MAX_BUFFER_SIZE? — Lawrence Cherone
– Lawrence Cherone, Commented Jan 10, 2018 at 1:00
I need it to deal with large data sets, the new mysqlnd module does it internally but I'm on the previous normal php mysql module and this one simply rejects to deal with lots of data. It's the "max buffer" not "min buffer" it makes no sense how it behaves now. — John
– John, Commented Jan 10, 2018 at 1:08
One possibility is that reboot applied patches for spectre and meltdown - which have been shown to cause up to a 45% performance hit theregister.co.uk/2018/01/09/meltdown_spectre_slowdown — Jeff Puckett
– Jeff Puckett, Commented Jan 10, 2018 at 2:57
Hm no, no patches were made. The issue might have been there before the reboot and just not noticed it. I was too fast on putting it on the reboot. I have just solved the issue by moving from php-mysql to php-mysqlnd driver. It's a PHP driver bug — John
– John, Commented Jan 10, 2018 at 3:58

John · Accepted Answer · 2018-01-10 19:50:56Z

1

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.

answered Jan 10, 2018 at 19:50

John

7,9143 gold badges58 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP->PDO() a query with about 500 byte content needs 100 MB of PHP memory suddenly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related