Solution to insufficient memory caused by PHP query database

How to solve the problem of insufficient memory caused by querying the database? This article mainly introduces the solution to insufficient memory caused by PHP querying a large amount of MySQL data. It analyzes the causes and corresponding solutions of insufficient memory during MySQL query with examples, and analyzes the usage skills of the mysql_unbuffered_query function in more detail. I hope it will be helpful to everyone. .

The example of this article analyzes the solution to the problem of insufficient memory caused by PHP querying a large amount of MySQL data. Share it with everyone for your reference. The specific analysis is as follows:

1. Problem

When using php to query mysql large data volume, the program has not been executed yet, and a warning pops up:
Fatal error: Allowed memory size of 100663296 bytes exhausted (tried to allocate 103 bytes)
Error message: The 100M memory allocated by php has been occupied.

2. Solution:

The simplest solution is to add:

ini_set('memory_limit','256M');

Increase the memory to 256M or more to increase the memory space used by php
But what should I do if I want to read more data next time? You can't increase it again and again, causing the server's memory to be eaten up by PHP.

Here is a function:

Use the memory_get_usage() method to get the amount of memory used by PHP. It is found that as the number of data items read increases, the memory used by PHP increases step by step.
Is the data when PHP queries mysql stored in memory? After some searching, I found that this is what it actually means.
Mysql’s C API functions include mysql_use_result() and mysql_store_result()
mysql_store_result() will read the result set from mysqlServer to the client, while mysql_use_result() only reads the meta-information of the result set

1. PHP’s mysql_query calls mysql_store_result(), which automatically obtains and caches the result set.
2. Another function of PHP, mysql_unbuffered_query(), calls mysql_use_result(). On the one hand, , which saves considerable memory when processing very large result sets. On the other hand, the result set can be operated on immediately after the first row is fetched, rather than waiting for the entire SQL statement to be executed.

So when we read a large amount of data, we can use mysql_unbuffered_query() instead of mysql_query(). After testing, it is indeed the case. And it is quite powerful. After importing all data, the memory has been kept within 1MB and has not increased.
mysql_unbuffered_query() sends a SQL query to MySQL, but it does not automatically obtain and cache the result set like mysql_query(). On the one hand, this saves considerable memory when processing very large result sets. On the other hand, the result set can be operated on immediately after the first row is fetched, rather than waiting for the entire SQL statement to be executed. When using multiple database connections, the optional parameter link_identifier must be specified.
The benefits of mysql_unbuffered_query() come at a cost: mysql_num_rows() and mysql_data_seek() cannot be used on top of the result set returned by mysql_unbuffered_query(). In addition, all result rows generated by uncached SQL queries must be fetched before sending a new SQL query to MySQL.
So you must select the function appropriately based on your own business needs

Related recommendations:

php solve and avoid repeated submission of form forms Method

PHP Method to solve session deadlock_PHP tutorial

php Solution Incorrect string value appears when inserting data into MySQL: 'xF0x9Fx92x8BTi...'Error

The above is the detailed content of Solution to insufficient memory caused by PHP query database. For more information, please follow other related articles on the PHP Chinese website!