On Tue, Dec 21, 2010 at 11:09, Michael Ben-Nes <michael@xxxxxxxxxxx> 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.takeba@xxxxxxx', '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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
