2

I am developing a PHP application that processes IP adresses. I am working with mysql tables containing up to 4 billion rows.

I have a script that currently needs to fetch 65536 adresses from this table and the mysql<->php interface fails to give a response via PHP or even via phpMyAdmin when I try to extract these 65K lines. If used in command line, mysql will give the 65K lines without trouble in about 0.2 sec.

The table containing the IP addresses has 3 indexes ( 1 unique, 2 primary ) which are supposed to help it go faster but I simply cannot get past having mysql give an associative array back to PHP in order to continue my data processing.

Server is a dedicated recent Xeon machine with about 32GB memory (I don't know the exact specs).

Any clues about what could be going on here?

Thanks in advance.

Elliot B.
  • 1,316
  • 2
  • 18
  • 28
BlackPage
  • 21
  • 2
  • 1
    Welcome to Server Fault. Development issues are typically not **[on-topic](http://serverfault.com/help/on-topic)** here, but in general people use their (error) log files to give them a clue. A completely random guess is you have imposed [a memory limit](http://stackoverflow.com/q/5925885/2952385) you run into. – HBruijn Jun 25 '15 at 08:01
  • Hi and thx for your answer. I was advised to post in a server related forum via stackoverflow.com because to them it looks like a config issue but was force to provide the symptoms in a coding way since it's code related. I'll try to get my hands on any info available logwise. – BlackPage Jun 25 '15 at 08:13
  • Seems we have a memory limit there, I'll be posting this afternoon to tell if the problem is gone once the admin pushes the limit. – BlackPage Jun 25 '15 at 09:00

1 Answers1

1

mysqli and PDO both operate in buffered mode by default. This means that your queries are effectively subject to the PHP process memory limit.

From http://php.net/manual/en/mysqlinfo.concepts.buffering.php:

[In buffered mode], query results are immediately transferred from the MySQL Server to PHP and are then kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory.

You should either increase the memory limit of PHP processes in your php.ini by increasing the memory_limit setting or you can tell the queries to use unbuffered mode:

Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server.

Mysqli example:

$mysqli  = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

PDO example:

$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
Elliot B.
  • 1,316
  • 2
  • 18
  • 28