SEO

Hi,
I was testing MySQL and PgSQL performances on my home box (amd athlon
64 3000, 1gig ddr ram, sata I hdd, win xp (x86)), select and insert
times seeemed identical with innoDB.

But when i try to query both using php, there's a huge difference even
for a funny query like "select 1"

Here's the code:

<?php
$mtime = microtime(true);

$pdo = new PDO('pgsql:host=localhost;dbname=test', "testacc", "pw");
for ($i = 0; $i < 10000; $i++)
{
$result = $pdo->query("Select "+$i);
}

echo microtime(true) - $mtime;
echo "<br>";

$mtime = microtime(true);

$pdo = new PDO('mysql:host=localhost;dbname=test', "testacc", "pw");
for ($i = 0; $i < 10000; $i++)
{
$result = $pdo->query("Select "+$i);
}

echo microtime(true) - $mtime;
echo "<br>";
?>

output is:
2.7696590423584
0.89393591880798

Nearly 3 times slower even w/o any table queries. But i could not
reproduce this by writing stored procs on both which selects 0-10000 in
a loop to a variable. results were almost same.
(I tried pg_ and mysqli_ functions too, results were not too different)

Is it mysql client libraries performs better? Or postgre stored procs
are 3x faster? I cannot understand, since there is not even an io
operation or any query planning stuff, what is the cause of this?

Thanks.

On Tue, Mar 28, 2006 at 05:01:45PM -0800, Antimon wrote:
> $pdo = new PDO('pgsql:host=localhost;dbname=test', "testacc", "pw");
> for ($i = 0; $i < 10000; $i++)
> {
> $result = $pdo->query("Select "+$i);
> }
> output is:
> 2.7696590423584

Ok, so that tells me that on this plain-vanilla hardware, you can
'only' do 3600 queries per second from PHP.

Who cares?

If you're actually trying to run that kind of volume on that kind of
hardware, you need to reconsider what you're doing.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Try factoring the connect time out of the test. My experience is the
connect is more expensive for Postgres than MySQL. With that out of the
way, I'd wager the times will be closer.
Regards,
Paul

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of Antimon
Sent: Tuesday, March 28, 2006 5:02 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL client api

Hi,
I was testing MySQL and PgSQL performances on my home box (amd athlon
64 3000, 1gig ddr ram, sata I hdd, win xp (x86)), select and insert
times seeemed identical with innoDB.

But when i try to query both using php, there's a huge difference even
for a funny query like "select 1"

Here's the code:

<?php
$mtime = microtime(true);

$pdo = new PDO('pgsql:host=localhost;dbname=test', "testacc", "pw");
for ($i = 0; $i < 10000; $i++)
{
$result = $pdo->query("Select "+$i);
}

echo microtime(true) - $mtime;
echo "<br>";

$mtime = microtime(true);

$pdo = new PDO('mysql:host=localhost;dbname=test', "testacc", "pw");
for ($i = 0; $i < 10000; $i++)
{
$result = $pdo->query("Select "+$i);
}

echo microtime(true) - $mtime;
echo "<br>";
?>

output is:
2.7696590423584
0.89393591880798

Nearly 3 times slower even w/o any table queries. But i could not
reproduce this by writing stored procs on both which selects 0-10000 in
a loop to a variable. results were almost same.
(I tried pg_ and mysqli_ functions too, results were not too different)

Is it mysql client libraries performs better? Or postgre stored procs
are 3x faster? I cannot understand, since there is not even an io
operation or any query planning stuff, what is the cause of this?

Thanks.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 2006-03-28 at 19:01, Antimon wrote:
> Hi,
> I was testing MySQL and PgSQL performances on my home box (amd athlon
> 64 3000, 1gig ddr ram, sata I hdd, win xp (x86)), select and insert
> times seeemed identical with innoDB.
>
> But when i try to query both using php, there's a huge difference even
> for a funny query like "select 1"
>
> Here's the code:
>
> <?php
> $mtime = microtime(true);
>
> $pdo = new PDO('pgsql:host=localhost;dbname=test', "testacc", "pw");
> for ($i = 0; $i < 10000; $i++)
> {
> $result = $pdo->query("Select "+$i);
> }
>
> echo microtime(true) - $mtime;
> echo "<br>";
>
> $mtime = microtime(true);
>
> $pdo = new PDO('mysql:host=localhost;dbname=test', "testacc", "pw");
> for ($i = 0; $i < 10000; $i++)
> {
> $result = $pdo->query("Select "+$i);
> }
>
> echo microtime(true) - $mtime;
> echo "<br>";
> ?>
>
> output is:
> 2.7696590423584
> 0.89393591880798
>
> Nearly 3 times slower even w/o any table queries. But i could not
> reproduce this by writing stored procs on both which selects 0-10000 in
> a loop to a variable. results were almost same.
> (I tried pg_ and mysqli_ functions too, results were not too different)
>
> Is it mysql client libraries performs better? Or postgre stored procs
> are 3x faster? I cannot understand, since there is not even an io
> operation or any query planning stuff, what is the cause of this?

MySQL's client libs, connection speed, and parser are known to be quite
fast. PostgreSQL's aren't exactly slow, but they have more to do. As
you start running actual queries, you'll see the advantage start to
disappear, and by the time you're running queries full of subselects and
unions, you'll notice the it's the tortoise and the hare all over again.

As someone else mentioned, factor out the connection time. But really,
this test is fairly bogus. Unless you're writing an app that has a lot
of "select 1" in it that is.

At least test something fairly realistic. Maybe some kind of thing like
having 10,000,000 rows, pick a number at random from 1 to 10,000,000 and
use it to select one row at a time. Better yet, make a union view with
a subselect on a function, create a functional index for the underlying
tables, and see if your outside where clause gets pushed down inside the
query by the planner.

Run updates in the background at the same time.

Pull the power plug whilst doing it, see which database comes back up.

Just benchmark what you're really gonna use the database for, cause if
you pick one or the other from this test, you might as well flip a coin.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I know this is not even a test but i thought it might be a client
library issue, not server itself.
Well it is not, i tried it on .net with npgsql which is a .net client
library (Not using libpq). Results are same. Connect time does not have
much effect by the way.

On 28 Mar 2006 17:01:45 -0800, Antimon <antimon@gmail.com> wrote:
> Nearly 3 times slower even w/o any table queries. But i could not
> reproduce this by writing stored procs on both which selects 0-10000 in
> a loop to a variable. results were almost same.
> (I tried pg_ and mysqli_ functions too, results were not too different)
>
> Is it mysql client libraries performs better? Or postgre stored procs
> are 3x faster? I cannot understand, since there is not even an io
> operation or any query planning stuff, what is the cause of this?

Yes, it is the case that raw select performance for pgsql drastically
improves with prepares statements as much as 2-3x. In my experience
postgresql is just a bit faster with prepared statements and
substantially slower without. There is a little bit of pain in
setting them up properly in a pooling web environment but it is worth
it if you want top performance.

Does anybody know if php uses the parameterized flavors of the C API?
That's another huge optimization over PQexec.

I agree with Jim in that it't probably fast enough anyways.

Merlin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, 2006-03-29 at 02:45, Antimon wrote:
> I know this is not even a test but i thought it might be a client
> library issue, not server itself.
> Well it is not, i tried it on .net with npgsql which is a .net client
> library (Not using libpq). Results are same. Connect time does not have
> much effect by the way.

Just so you understand, there was a period of time when MySQL AB put out
benchmark after benchmark like this to "prove" that MySQL was faster
than PostgreSQL. Of course, they never compared anything moderately
complex, since they knew they'd lose.

Is it a fair comparison of say, a mainframe and a workstation to compare
the boot up times? Not really, unless your particular application is
gonna be rebooting the mainframe a lot. It's a small, narrow channel to
compare on, and most the time it doesn't mean a lot.

That's all. No personal attack meant against you, sorry if it came
across that way. It's just many pgsql folks still smart from that era,
and it seems to come back to haunt us every so often.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hi,
I'm sorry about being understood like i was trying to compare pgsql
with mysql. I was trying stuff, did this and saw that huge difference
(even it is not bad alone, but comparing to mysql), and thought that
might be some library issue causing slow reads from server. I don't
need any rdbms to be faster on selecting a constant integer. My point
was the library, not server performance.

On Wed, 2006-03-29 at 12:19, Antimon wrote:
> Hi,
> I'm sorry about being understood like i was trying to compare pgsql
> with mysql. I was trying stuff, did this and saw that huge difference
> (even it is not bad alone, but comparing to mysql), and thought that
> might be some library issue causing slow reads from server. I don't
> need any rdbms to be faster on selecting a constant integer. My point
> was the library, not server performance.

No need to apologize, honest. This discussion brings up some valid
points, even if the benchmark is not necessarily a valid method for
choosing the database.

PostgreSQL is generally "heavier" than MySQL. A large portion of this
is that PostgreSQL is generally busy making sure things are done right
first, and fast second. In MySQL, it's the other way around.

I wonder how your test would work if you did something a little more
complex (like the pick 1 row in a million scenario) and did in parallel
for say 5, 10, 20, 50 clients at a time. That would like give you some
idea of how well connectivity and small sql select statements scale on
each system.

Generally, pgsql folks consider the single user scenario to not be that
important, and ask themselves "so what happens when 50 people do this at
the same time?" Again, MySQL tends to be the opposite.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Just noticed,
On windows, these results are produced.
But on linux, postgresql performs great. So postgre has a bad windows
integration than mysql. Well since it supports win32 for a long time,
it makes sense.
I did some "multi client" tests and postgre could not even catch mysql
so i decided to go test it on my linux box. It seems that the
performance issue is about windows version.