Re: MySQL HandlerSocket - Is this possible in PG?

On Tue, Dec 21, 2010 at 11:09, Michael Ben-Nes <michael(at)epoch(dot)co(dot)il> wrote:
> Just stumbled on the following post:
> http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
>
> The post claim that MySQL can do more qps then MemCahed or any other NoSQL
> when doing simple queries like: SELECT * FROM table WHERE id=num;
>
> And I wonder if:
>
> 1. Currently, is it possbile to achive the same using PG 9.0.x
> 2. Is it possible at all?

I was curious what could be done currently, without any modifications
to PostgreSQL itself, so I ran a simple benchmark.

Table:
create table usr (user_id int primary key not null, user_name text not
null, user_email text not null, created timestamp not null);
insert into usr select generate_series(1, 1000000), 'Yukari Takeba',
'yukari(dot)takeba(at)dena(dot)jp', '2010-02-03 11:22:33';

<?php
$db = pg_connect('');
$res = pg_prepare($db, 'get_user', 'select user_name, user_email,
created from usr where user_id=$1');
$res = pg_query($db, 'begin');

$args = array();
for($i = 0; $i < 250000; $i++)
{
$args[0] = rand(1, 1000000);
$res = pg_execute($db, 'get_user', $args);
$row = pg_fetch_row($res);
}
?>

Each process does 250k queries, so when I run 4 in parallel it's 1M
queries total.

I'm running PostgreSQL 9.1alpha2, PHP 5.3.4, kernel 2.6.36.2 on Arch
Linux; AMD Phenom II X4 955.
The only tuning I did was setting shared_buffers=256M

Results:
% time php pg.php & time php pg.php &time php pg.php &time php pg.php & sleep 11
[1] 29792
[2] 29793
[3] 29795
[4] 29797
php pg.php 1,99s user 0,97s system 30% cpu 9,678 total
[2] done time php pg.php
php pg.php 1,94s user 1,06s system 30% cpu 9,731 total
[3] - done time php pg.php
php pg.php 1,92s user 1,07s system 30% cpu 9,746 total
[1] - done time php pg.php
php pg.php 2,00s user 1,04s system 31% cpu 9,777 total
[4] + done time php pg.php

So around 10 seconds to run the test in total.
These numbers aren't directly comparable to their test -- I tested
over a local UNIX socket, with PHP client on the same machine -- but
it's a datapoint nevertheless.

Bottom line, you can expect up to 100 000 QPS using pg_execute() on a
cheap quad-core gamer CPU. You won't be beating memcached with current
PostgreSQL, but I think it's a respectable result.

Regards,
Marti