Using PHP to dump large databases into JSON

Question

I have a slight problem with an application I am working on. The application is used as a developer tool to dump tables from a database in a MySQL server to a JSON file which the devs grab by using the Unix curl command. So far the databases we've been using are relatively small tables(2GB or less) however recently we've moved into another stage of testing that use fully populated tables (40GB+) and my simple PHP script breaks. Here's my script:

[<?php 

$database = $_GET['db'];

ini_set('display_errors', 'On');
error_reporting(E_ALL);

# Connect
mysql_connect('localhost', 'root', 'root') or die('Could not connect: ' . mysql_error());

# Choose a database
mysql_select_db('user_recording') or die('Could not select database');

# Perform database query
$query = "SELECT * from `".$database."`";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

while ($row = mysql_fetch_object($result)) {
   echo json_encode($row);
   echo ",";
}

?>]

My question to you is what can I do to make this script better about handling larger database dumps.

Is the script simply timing out? If so, might look into what your default script time limit is (max_execution_time in php.ini) or just test using set_time_limit(0). — user428517
– user428517, Commented Oct 19, 2012 at 15:03
If you call your PHP Script via Webbrowser there is a timeout after 30 seconds. Is that the reason why it breaks? If so, call it via a console. But why aren't you calling the mysqldump command? — PKeidel
– PKeidel, Commented Oct 19, 2012 at 15:05
It won't solve your problem, but I suggest renaming $database to $table (it will be more logical). — Jocelyn
– Jocelyn, Commented Oct 19, 2012 at 15:08
"...and my simple PHP script breaks..." - what is the error? — Aleksandar Vucetic
– Aleksandar Vucetic, Commented Oct 19, 2012 at 15:11
I believe it just times out, there's no error given. Thanks for noticing that Jocelyn, this was thrown together very quickly. — Chris Maness
– Chris Maness, Commented Oct 19, 2012 at 15:14

Aleksandar Vucetic · Accepted Answer · 2012-10-19 15:21:21Z

3

This is what I think that the problem is:

you are using mysql_query. mysql_query buffers data in memory and then mysql_fetch_object just fetches that data from the memory. For very large tables, you just don't have enough memory (most likely you are getting all 40G of rows into that one single call).

Use mysql_unbuffered_query instead. More info here on MySQL performance blog There you can find some other possible causes for this behavior.

answered Oct 19, 2012 at 15:21

Aleksandar Vucetic

15k9 gold badges56 silver badges56 bronze badges

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

1 Comment

Chris Maness Over a year ago

Simple yet effective just what I was looking for. This speeds everything up. Thanks.

Mahdi · Accepted Answer · 2012-10-19 16:26:58Z

1

I'd say just let mysql do it for you, not php:

SELECT 
 CONCAT("[",
      GROUP_CONCAT(
           CONCAT("{field_a:'",field_a,"'"),
           CONCAT(",field_b:'",field_b),"'}")
      )
 ,"]") 
AS json FROM table;

it should generates something like this:

[
    {field_a:'aaa',field_b:'bbb'},
    {field_a:'AAA',field_b:'BBB'}
]

answered Oct 19, 2012 at 16:26

Mahdi

9,4479 gold badges58 silver badges75 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:28:41Z

You might have a problem with MySQL buffering. But, you might also have other problems. If your script is timing out, try disabling the timeout with set_time_limit(0). That's a simple fix, so if that doesn't work, you could also try:

Try dumping your database offline, then transfer it via script or just direct http. You might try making a first PHP script call a shell script which calls a PHP-CLI script that dumps your database to text. Then, just pull the database via HTTP.
Try having your script dump part of a database (the rows 0 through N, N+1 through 2N, etc).
Are you using compression on your http connections? If your lag is transfer time (not script processing time), then speeding up the transfer via compression might help. If it's the data transfer, JSON might not be the best way to transfer the data. Maybe it is. I don't know. This question might help you: Preferred method to store PHP arrays (json_encode vs serialize)

Also, for options 1 and 3, you might try looking at this question:

What is the best way to handle this: large download via PHP + slow connection from client = script timeout before file is completely downloaded

Collectives™ on Stack Overflow

Using PHP to dump large databases into JSON

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related