Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
<?php
declare(strict_types = 1);
ini_set('memory_limit', '1024M');
class Benchmark
{
private $start;
public function __construct()
{
$this->start = $this->getMicroTime();
}
public function output($message)
{
$elapsedTime = number_format((int)($this->calcElapsedTime() * 1000));
printf("[% 8sms] % 20s: RSS[% 8s]kb / memory_get_usage[% 8s]kb\n",
$elapsedTime,
$message,
number_format($this->getRss()),
number_format(memory_get_usage() / 1024)
);
}
private function calcElapsedTime(): float
{
if (is_null($this->start)) {
$this->start = $this->getMicroTime();
return 0.0;
}
return $this->getMicroTime() - $this->start;
}
private function getMicroTime(): float
{
return microtime(true);
}
private function getRss(): int
{
$pid = posix_getpid();
$output = '';
$output = exec("ps -p {$pid} -o rss", $output);
return (int)$output;
}
}
class Cursor
{
private $name;
private $fetchCount;
/**
* Cursor constructor.
* @param string $name
* @param int $fetchCount
*/
public function __construct(string $name, int $fetchCount)
{
$this->name = $name;
$this->fetchCount = $fetchCount;
}
public function name(): string
{
return $this->name;
}
public function fetchCount(): int
{
return $this->fetchCount;
}
}
class UserRepository
{
public function select(array $pdoStatementOptions = [])
{
$this->bench(null, $pdoStatementOptions, function (PDOStatement $statement) {
while ($row = $statement->fetch()) {
yield $row;
}
});
}
public function selectAndFetchAll()
{
$this->bench(null, [], function (PDOStatement $statement, Benchmark $benchmark) {
$users = $statement->fetchAll();
var_dump(count($users));
$benchmark->output('after fetchAll');
foreach ($users as $user) {
yield $user;
}
});
}
public function selectWithCursor(int $fetchCount = 100)
{
$this->bench(
new Cursor('pdo_cursor_0001', $fetchCount),
[],
function (PDOStatement $statement) {
while (true) {
$statement->execute();
$row = $statement->fetch();
if (empty($row)) {
return;
}
yield $row;
while ($row = $statement->fetch()) {
yield $row;
}
}
});
}
private function bench(
Cursor $cursor = null,
array $pdoStatementOptions = [],
callable $fetcher
) {
$pdo = new PDO('pgsql:dbname=bench', 'postgres', '', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
try {
$sql = 'SELECT * FROM users ORDER BY id';
if ($cursor) {
$pdo->beginTransaction();
$statement = $pdo->prepare(sprintf('DECLARE %s SCROLL CURSOR WITH HOLD FOR %s', $cursor->name(), $sql));
} else {
$statement = $pdo->prepare($sql, $pdoStatementOptions);
}
$statement->setFetchMode(PDO::FETCH_ASSOC);
$bench = new Benchmark();
$bench->output('before execution');
$statement->execute();
$bench->output('after execution');
if ($cursor) {
$statement = $pdo->prepare(sprintf('FETCH FORWARD %d FROM %s', $cursor->fetchCount(), $cursor->name()));
}
foreach ($fetcher($statement, $bench) as $row) {
$id = $row['id'];
if ($id % 100000 === 0) {
$bench->output($id . ' fetched');
}
}
} finally {
if ($cursor) {
$pdo->rollBack();
}
unset($pdo);
}
}
}
echo '1) select and fetch =============' . PHP_EOL;
(new UserRepository())->select();
echo PHP_EOL;
echo '2) cursor and fetch 1 =============' . PHP_EOL;
(new UserRepository())->selectWithCursor(1);
echo PHP_EOL;
echo '3) cursor and fetch 100 =============' . PHP_EOL;
(new UserRepository())->selectWithCursor(100);
echo PHP_EOL;
echo '4) cursor and fetch 10,000 =============' . PHP_EOL;
(new UserRepository())->selectWithCursor(10000);
echo PHP_EOL;
echo '5) cursor and fetch 100,000 =============' . PHP_EOL;
(new UserRepository())->selectWithCursor(100000);
echo PHP_EOL;
echo '6) cursor and fetch 1,000,000 =============' . PHP_EOL;
(new UserRepository())->selectWithCursor(1000000);
echo PHP_EOL;
echo '7) default and fetchAll() =============' . PHP_EOL;
(new UserRepository())->selectAndFetchAll();
echo PHP_EOL;
//echo 'PDO::CURSOR_SCROLL =============' . PHP_EOL;
//(new UserRepository())->select([PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
1) select and fetch =============
[ 0ms] before execution: RSS[ 13,088]kb / memory_get_usage[ 364]kb
[ 843ms] after execution: RSS[ 108,708]kb / memory_get_usage[ 364]kb
[ 880ms] 100000 fetched: RSS[ 108,708]kb / memory_get_usage[ 369]kb
[ 917ms] 200000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 955ms] 300000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 992ms] 400000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,028ms] 500000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,066ms] 600000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,102ms] 700000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,140ms] 800000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,177ms] 900000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
[ 1,215ms] 1000000 fetched: RSS[ 108,824]kb / memory_get_usage[ 369]kb
2) cursor and fetch 1 =============
[ 0ms] before execution: RSS[ 13,500]kb / memory_get_usage[ 364]kb
[ 146ms] after execution: RSS[ 13,500]kb / memory_get_usage[ 364]kb
[ 1,933ms] 100000 fetched: RSS[ 13,500]kb / memory_get_usage[ 369]kb
[ 3,681ms] 200000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 5,480ms] 300000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 7,239ms] 400000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 8,983ms] 500000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 10,731ms] 600000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 12,471ms] 700000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 14,214ms] 800000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 15,958ms] 900000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 17,710ms] 1000000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
3) cursor and fetch 100 =============
[ 0ms] before execution: RSS[ 13,512]kb / memory_get_usage[ 364]kb
[ 145ms] after execution: RSS[ 13,512]kb / memory_get_usage[ 364]kb
[ 272ms] 100000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 398ms] 200000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 524ms] 300000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 650ms] 400000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 778ms] 500000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 904ms] 600000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 1,030ms] 700000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 1,156ms] 800000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 1,282ms] 900000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
[ 1,413ms] 1000000 fetched: RSS[ 13,512]kb / memory_get_usage[ 369]kb
4) cursor and fetch 10,000 =============
[ 0ms] before execution: RSS[ 13,512]kb / memory_get_usage[ 364]kb
[ 149ms] after execution: RSS[ 13,512]kb / memory_get_usage[ 364]kb
[ 258ms] 100000 fetched: RSS[ 14,272]kb / memory_get_usage[ 369]kb
[ 367ms] 200000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 477ms] 300000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 587ms] 400000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 697ms] 500000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 809ms] 600000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 918ms] 700000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 1,030ms] 800000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 1,140ms] 900000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
[ 1,249ms] 1000000 fetched: RSS[ 14,356]kb / memory_get_usage[ 369]kb
5) cursor and fetch 100,000 =============
[ 0ms] before execution: RSS[ 14,356]kb / memory_get_usage[ 364]kb
[ 150ms] after execution: RSS[ 14,356]kb / memory_get_usage[ 364]kb
[ 268ms] 100000 fetched: RSS[ 22,720]kb / memory_get_usage[ 369]kb
[ 387ms] 200000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 505ms] 300000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 623ms] 400000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 742ms] 500000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 862ms] 600000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 980ms] 700000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 1,098ms] 800000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 1,218ms] 900000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
[ 1,336ms] 1000000 fetched: RSS[ 22,944]kb / memory_get_usage[ 369]kb
6) cursor and fetch 1,000,000 =============
[ 0ms] before execution: RSS[ 22,944]kb / memory_get_usage[ 364]kb
[ 144ms] after execution: RSS[ 22,944]kb / memory_get_usage[ 364]kb
[ 992ms] 100000 fetched: RSS[ 108,776]kb / memory_get_usage[ 369]kb
[ 1,029ms] 200000 fetched: RSS[ 108,776]kb / memory_get_usage[ 369]kb
[ 1,065ms] 300000 fetched: RSS[ 108,776]kb / memory_get_usage[ 369]kb
[ 1,101ms] 400000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,138ms] 500000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,175ms] 600000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,212ms] 700000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,248ms] 800000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,285ms] 900000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
[ 1,322ms] 1000000 fetched: RSS[ 108,848]kb / memory_get_usage[ 369]kb
7) default and fetchAll() =============
[ 0ms] before execution: RSS[ 13,516]kb / memory_get_usage[ 364]kb
[ 839ms] after execution: RSS[ 108,852]kb / memory_get_usage[ 365]kb
[ 1,179ms] after fetchAll: RSS[ 563,512]kb / memory_get_usage[ 455,016]kb
[ 1,203ms] 100000 fetched: RSS[ 563,804]kb / memory_get_usage[ 455,016]kb
[ 1,222ms] 200000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,248ms] 300000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,272ms] 400000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,292ms] 500000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,314ms] 600000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,334ms] 700000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,357ms] 800000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,383ms] 900000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
[ 1,405ms] 1000000 fetched: RSS[ 563,832]kb / memory_get_usage[ 455,016]kb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment