Last active
December 18, 2020 15:05
-
-
Save shin1x1/27db7768218ab3b4557a3dce4f639faa to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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]); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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