PHP海量内存使用SQL查询

问题描述

我穿过一个奇怪的问题，迷迷糊糊，同时优化我的Apache + PHP内存使用情况。基本上，code，出现错误消息打击了。致命错误：用尽16777216字节允许内存大小（试图分配50331646字节），试图绑定库MySQLi查询的结果时

相关表格是：

  CREATE TABLE`note`（
  `noteID` INT（11）NOT NULL AUTO_INCREMENT，
  `contentID` INT（11）NOT NULL，
  `text` MEDIUMTEXT NOT NULL，
  PRIMARY KEY（`noteID`）
）ENGINE = InnoDB的默认字符集= UTF8;CREATE TABLE`content`（
  `contentID` INT（11）NOT NULL AUTO_INCREMENT，
  `text` VARCHAR（2048）NOT NULL，
  `datestamp`日期时间NOT NULL，
  PRIMARY KEY（`contentID`）
）ENGINE = InnoDB的默认字符集= UTF8;
 

和那个吹起来的查询是：

 选择content.contentID，
content.text，
content.datestamp，
note.noteID，
note.contentID，note.text
从basereality.content如内容
内部联接basereality.note如注
在content.contentID = note.contentID
其中，content.contentID = 1028;
 

在服务器上的MySQL中运行查询运行正常，和'注意'返回的大小是一个千字节之下。

一个奇怪的是，它试图分配50331646大小0x2FFFFFE十六进制是一个可疑的轮数。这几乎就像PHP是试图分配大到足以容纳最大MEDIUMTEXT领域可能的，而不是实际分配为检索到的数据存储的缓冲区。

任何人都知道这一种解决方法 - 除了要增加允许在PHP中最大内存

顺便说一句其他人有同样的问题，但似乎不知道如何解决它。
Out内存（分配50855936）（试图拨出50331646字节）

这打击了确切的行是：

  $陈述书＆GT; bind_result（
  $ content_contentID，
  $ content_text，
  $ content_datestamp，
  $ note_noteID，
  $ note_contentID，
  $ note_text）;
 

更改查询，选择'测试字符串'作为音符，而不是不再读取列显示了大量的内存使用情况。

顺便说一句，我相信，我不是试图检索大量数据。这显示在音符表MEDIUMTEXT场的实际长度：

 从basereality.note选择长度（文本）;
+ -------------- +
|长度（文本）|
+ -------------- +
| 938 |
| 141 |
| 1116 |
| 431 |
| 334 |
+ -------------- +
 

解决方案

我找到了答案，并在另一个问题评论说：

Allowed 67108864的字节的内存大小耗尽

基本上，在旧的MySQL连接器库中，而不仅仅是分配字段文本的实际大小来存储的结果，而不是类型MEDIUMTEXT的最大可能大小被分配

即。 16兆字节总是分配即使结果现场只有100千字节。

解决这个问题的最简单，也许最好的办法是切换到使用新的MySQLND连接器，它不显示此行为。

I've stumbled across an odd issue while optimising my Apache + PHP memory usage. Basically the code blows up with the error message "Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 50331646 bytes)" when trying to bind the results of a MySQLi query.

The relevant tables are:

CREATE TABLE `note` (
  `noteID` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` int(11) NOT NULL,
  `text` mediumtext NOT NULL,
  PRIMARY KEY (`noteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content` (
  `contentID` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(2048) NOT NULL,
  `datestamp` datetime NOT NULL,
  PRIMARY KEY (`contentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the query that blows up is:

select content.contentID,
content.text,
content.datestamp,
note.noteID,
note.contentID, note.text
from basereality.content as content
inner join basereality.note as note
on content.contentID = note.contentID
where content.contentID = 1028 ;

Running the query in MySQL on the server runs fine, and the size of 'note' returned is under a kilobyte.

One weird thing is that the size that it is trying to allocate 50331646 is 0x2FFFFFE in hex which is a suspiciously round number. It's almost like PHP is trying to allocate a buffer large enough to hold the largest mediumtext field possible, rather than actually allocating memory for the data retrieved.

Anyone know a workaround for this - other than to increase the max memory allowed in PHP?

btw Other people had the same issue but didn't seem to know how to resolve it.Out of memory (allocated 50855936) (tried to allocate 50331646 bytes)

The exact line that blows up is:

$statement->bind_result(
  $content_contentID,
  $content_text,
  $content_datestamp,
  $note_noteID,
  $note_contentID,
  $note_text);

Changing the query to select "'A test string' as note", rather than fetching the column no longer shows the massive memory usage.

btw I am sure that I'm not trying to retrieve large data. This shows the actual length of the mediumtext field in the note table:

 select length(text) from basereality.note;
+--------------+
| length(text) |
+--------------+
|          938 |
|          141 |
|         1116 |
|          431 |
|          334 |
+--------------+

解决方案

I found the answer and commented in another question:

Allowed memory size of 67108864 bytes exhausted

Basically, in old MySQL connector library, rather than just allocating the actual size of the field 'text' to store the result, instead the maximum possible size of the type mediumtext is allocated.

i.e. 16 megabytes is always allocated even if the field for the result is only 100 kilobytes.

The easiest and probably best way to fix this is to switch to using the new MySQLND connector which doesn't show this behaviour.

这篇关于PHP海量内存使用SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！