1.7GBのmysql読み込み

Question

3千万行以上あるmysqlからselectしたいのですが、下記のメッセージが出て読み込めません。

Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 211812368 bytes) in

環境はMAMPローカル環境でGoogle Chromeを使って表示しています。

「ini_set('memory_limit', '2G');」はやってみました。

やりたいことは300個程のデータを3千万行のデータから合うデータを探しすことなので最初のselect時に全部のデータを読み込まず、条件に合うデータだけを読み込めば良いのかと思い、色々やりましたがうまく出来ませんでした。

このエラーを回避するにはどうすればいいでしょうか。

<?php
require_once '../vendor/autoload.php';
use Cake\Chronos\Chronos;
ini_set('memory_limit', '2G');

$UserName = 'root';
$PassWord = 'root';

$Database = new PDO('mysql:host=localhost;dbname=Encryption_Currency;charset=UTF8;', $UserName, $PassWord);
$Database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
$sql = "SELECT * FROM `bitflyerJPY`";
$statement = $Database->query($sql);
// $statement->execute();
$records = $statement->fetchAll(PDO::FETCH_ASSOC);
$statement = null;
$Database = null;
} catch (PDOException $error){
echo $error->getMessage();
exit;
};

$bittrexFileName = "fullOrders_Ecxel.csv";
$bittrex = [];
$bittrexTime = [];
$bittrexUnixTime = [];

function READ_CSV($file) {
    if (pathinfo($file, PATHINFO_EXTENSION) === "csv") {
        try {
          $FileObject = new SplFileObject($file);
        } catch (Exception $e) {
          echo  $e->getMessage();
        }
        $FileObject->setFlags(
            SplFileObject::READ_CSV
        );
        foreach ($FileObject as $line) {
            $array[] = $line;
        }
    return $array;

    } else {
        echo "CSVファイルではありません。";
    }
}

function CONVERT_INT_BY_MAP ($stringarray) {
        $Int = intval($stringarray);
        return $Int;
}

function CONVERT_UNIX ($timearray){
    $Time = new Chronos($timearray); 
    $UnixTime = $Time->timestamp;
    return $UnixTime;
}

$bittrex = READ_CSV($bittrexFileName);

for ($k = 0; $k < count($bittrex[0]); $k++){
    $key = $bittrex[0][$k];
    for ($i = 1; $i < count($bittrex)-1; $i++){
        $bittrexWithkey[$i][$key] = $bittrex[$i][$k];
    }
}

$bittrexTime = array_column($bittrexWithkey, 'Closed');

$bittrexUnixTime = array_map("CONVERT_UNIX", $bittrexTime);


$BTCPrice =[];
for ($i = 0; $i < count($bittrexUnixTime); $i++){
    $Ikaarray = [];
    for ($j = 0; $j < count($records); $j++) {
        if ($records[$j]["Time"] <= $bittrexUnixTime[$i]) {
            array_push($Ikaarray, $records[$j]["Time"]);
        }
    }
    $BTCPrice[$i]["Time"] = $bittrexUnixTime[$i];
    $BTCPrice[$i]["Price"] = $records[count($Ikaarray)-1]["Price"];
}



$filepath = "bittrexBTCJPY.csv";

try {
  $FileObject = new SplFileObject($filepath, "w");
} catch (Exception $e) {
  echo $e->getMessage();
}
 
foreach ($BTCPrice as $row) {
  $FileObject->fputcsv($row);
}

Answer 1

全て必要でないならwhereで絞り込めば良いと思うのですがどうやって絞りこもうと込もうとしてますか？
また2Gメモリ確保は現実的に難しいと思います。
それ以上かかってるからそのサイズでのメモリーエラーが出ていますが、実際に必要なメモリーはデータ量だけではないのでマシンへの負荷も考えると、
SELECT時点で絞り込むしかないかなと。
「300個程のデータを3千万行のデータから合うデータ」がどういう条件か質問内容からは読み取れなかったので、そこは提示いただくか考えてもらうしかありませんが。

Comments

[rutawaru]

$aaarr = [];
$biTimearray = [1509493559, 1514880621, 1505338168];

for ($i = 0; $i < count($biTimearray)+1; $i++) {
$biTime = $biTimearray[$i];
try {
$sql = "SELECT * FROM `bitflyerJPY` WHERE `Time` <= {$biTime} ORDER BY `Time` DESC LIMIT 1";
$statement = $Database->prepare($sql);
$statement->execute();
$records = $statement->fetchAll(PDO::FETCH_ASSOC);
array_push($aaarr, $records);
$statement = null;
$Database = null;
} catch (PDOException $error){
echo $error->getMessage();
exit;
};
}

[rutawaru]

ご回答ありがとうございます。
こうゆう風にforで回そうと思ったのですが、出来ません。
SELECTでforは使えませんか？

[m.ts10806]

いえ。取得するデータ自体を絞りこんで込んで取得することでphpのforは不要となるようにすべきです。
何度もDBアクセスすることでその分、負荷が高まります。一度のSELECTで全てのデータを取得してください。
これって、どういうデータを取ろうとしてますか？

[m.ts10806]

もし「biTimearrayで設定している３つの時間以前」を取得であればor条件でつなげば取得できそうに思います。
SELECT * FROM `bitflyerJPY`
WHERE
`Time` <= 1509493559
or `Time` <= 1514880621
or `Time` <= 1505338168
ORDER BY `Time` DESC LIMIT 1

もし、「３つだけじゃなく不定数あるんだ」ということでしたら
配列に入れたうえで、上記のor条件の形を作って１つのSELECTにすればいいだけですし。
いずれにしても何回も接続して何回も実行するのはメモリの無駄遣いになります。

Answer 2

どんなテーブルを使っているのかがわかるように、質問にCREATE TABLE を追記してください。

やりたいことは300個程のデータを3千万行のデータから合うデータを探しすことなので最初のselect時に全部のデータを読み込まず、条件に合うデータだけを読み込めば良いのかと思い、色々やりましたがうまく出来ませんでした。

3千万件のデータから300件程度をどんな条件で取り出したいのかも質問に明記してください。

大型倉庫の荷物を全部外に出してその中から必要なものを取り出す、なんて無駄なことはしませんよね。普通必要な荷物だけ運び出します。
データーベースも同じです。

Answer 3

fetchAllでメモリ不足になっていると思うので、fetchでループして同等の処理に変えてみました。
たぶんこれで動くと思います。

<?php

require_once '../vendor/autoload.php';
use Cake\Chronos\Chronos;

function READ_CSV($file) {
  $array = [];
  if (pathinfo($file, PATHINFO_EXTENSION) === "csv") {
    try {
      $FileObject = new SplFileObject($file);
    } catch (Exception $e) {
      echo  $e->getMessage();
    }
    $FileObject->setFlags(SplFileObject::READ_CSV);
    foreach ($FileObject as $line) $array[] = $line;
  } else {
    echo "CSVファイルではありません。";
  }
  return $array;
}
function CONVERT_UNIX($timearray) {
    $Time = new Chronos($timearray); 
    $UnixTime = $Time->timestamp;
    return $UnixTime;
}

$bittrexFileName = "fullOrders_Ecxel.csv";
$bittrex = READ_CSV($bittrexFileName);
$bittrexWithkey = [];
for ($k = 0; $k < count($bittrex[0]); $k++) {
  $key = $bittrex[0][$k];
  for ($i = 1; $i < count($bittrex) - 1; $i++) {
    $bittrexWithkey[$i][$key] = $bittrex[$i][$k];
  }
}
$bittrexTime = array_column($bittrexWithkey, 'Closed');
$bittrexUnixTime = array_map("CONVERT_UNIX", $bittrexTime);

$BTCPrice =[];
foreach ($bittrexUnixTime as $time) $BTCPrice[] = array('Time' => $time, 'Price' => null);


$UserName = 'root';
$PassWord = 'root';
$Database = new PDO('mysql:host=localhost;dbname=Encryption_Currency;charset=UTF8;', $UserName, $PassWord);
$Database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
  $sql = "SELECT * FROM `bitflyerJPY`";
  $statement = $Database->query($sql);
  while ($record = $statement->fetch(PDO::FETCH_ASSOC)) {
    for ($i = 0; $i < count($bittrexUnixTime); $i++) {
      if ($record['Time'] <= $bittrexUnixTime[$i]) $BTCPrice[$i]['Price'] = $records['Price'];
    }
  }
} catch (PDOException $error){
  echo $error->getMessage();
  exit;
};


$filepath = "bittrexBTCJPY.csv";
try {
  $FileObject = new SplFileObject($filepath, "w");
} catch (Exception $e) {
  echo $e->getMessage();
}

foreach ($BTCPrice as $row) {
  $FileObject->fputcsv($row);
}

Answer 4

この回答は見当はずれの内容でした＾＾；失礼

そもそもコマンドラインから mysql コマンドで実行できるのでしょうか？

もし実行可能なのであれば、php 内で実行せず、直接 mysql コマンドで抽出しファイルに書き出すようにして、そのファイルを監視するような仕組みにしてみては？

Answer 5

参考までに
つ　Fatal error: Allowed memory size of エラーの対応【PHP】

timestampで過去のデータを切り捨てる方法もあるようですが・・・
参考：ビットコインのヒストリカルデータ（1分毎）の取得