Re: Need help to decide Mysql vs Postgres

> I am all for postgres at this point, however just want to know why I am
> getting opposite results !!! Both DBs are on the same machine

> Why do you say "opposite results" ?

Please pardon my ignorance, but from whatever I had heard, mysql was
supposedly always faster than postgres !!!! Thats why I was so surprised !!
I will definately post the "analyze query" thing by end of today ...

Thanks for all your helps !!
Amit

In the last exciting episode, ashah(at)tagaudit(dot)com (Amit V Shah) wrote:
>> I am all for postgres at this point, however just want to know why I am
>> getting opposite results !!! Both DBs are on the same machine
>
>> Why do you say "opposite results" ?
>
> Please pardon my ignorance, but from whatever I had heard, mysql was
> supposedly always faster than postgres !!!! Thats why I was so
> surprised !! I will definately post the "analyze query" thing by
> end of today ...

There is a common "use case" where MySQL(tm) using the "MyISAM"
storage manager tends to be quicker than PostgreSQL, namely where you
are submitting a lot of more-or-less serial requests of the form:

select * from some_table where id='some primary key value';

If your usage patterns differ from that, then "what you heard" won't
necessarily apply to your usage.
--
output = ("cbbrowne" "@" "acm.org")
http://linuxdatabases.info/info/rdbms.html
The difference between a child and a hacker is the amount he flames
about his toys. -- Ed Schwalenberg

> Please pardon my ignorance, but from whatever I had heard, mysql was
> supposedly always faster than postgres !!!! Thats why I was so surprised
> !!

I heard a lot of this too, so much it seems common wisdom that postgres
is slow... well maybe some old version was, but it's getting better at
every release, and the 8.0 really delivers... I get the feeling that the
PG team is really working and delivering improvements every few months,
compare this to MySQL 5 which has been in beta for as long as I can
remember.
Also, yes, definitely mysql is faster when doing simple selects like
SELECT * FROM table WHERE id=constant, or on updates with few users, but
once you start digging... it can get a thousand times slower on some joins
just because the optimizer is dumb... and then suddenly 0.2 ms for MySQL
versus 0.3 ms for postgres on a simple query doesn't seem that attractive
when it's 2 ms on postgres versus 2 seconds on mysql for a not so
complicated one like pulling the first N rows from a join ordered by...
PG is considered slower than mysql also because many people don't use
persistent connections, and connecting postgres is a lot slower than
connecting MySQL... But well, persistent connections are easy to use and
mandatory for performance on any database anyway so I don't understand why
the fuss.

> I will definately post the "analyze query" thing by end of today ...
>
> Thanks for all your helps !!
> Amit
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>

On 6/6/2005 2:12 PM, PFC wrote:

>
>> Please pardon my ignorance, but from whatever I had heard, mysql was
>> supposedly always faster than postgres !!!! Thats why I was so surprised
>> !!
>
> I heard a lot of this too, so much it seems common wisdom that postgres
> is slow... well maybe some old version was, but it's getting better at
> every release, and the 8.0 really delivers...

The harder it is to evaluate software, the less often people reevaluate
it and the more often people just "copy" opinions instead of doing an
evaluation at all.

Today there are a gazillion people out there who "know" that MySQL is
faster than PostgreSQL. They don't know under what circumstances it is,
or what the word "circumstances" means in this context anyway. When you
ask them when was the last time they actually tested this you get in
about 99% of the cases an answer anywhere between 3 years and infinity
(for all those who never did). The remaining 1% can then be reduced to
an insignificant minority by asking how many concurrent users their test
simulated.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Christopher Browne wrote:
>
> There is a common "use case" where MySQL(tm) ...
>
> select * from some_table where id='some primary key value';
>
> If your usage patterns differ from that...

However this is a quite common use-case; and I wonder what the
best practices for postgresql is for applications like that.

I'm guessing the answer is PGMemcache?
(http://people.freebsd.org/~seanc/pgmemcache/pgmemcache.pdf)
... with triggers and listen/notify to manage deletes&updates
and tweaks to the application code to look to memcached for
those primary_key=constant queries?

If that is the answer, I'm curious if anyone's benchmarked
or even has qualitative "yeah, feels very fast" results for
such an application for the common mysql use case.

I did my own evaluation a few months back, because postgres was not cutting
it for me.
I found that postgres 8.0 (was what I was using at the time, now on 8.0.2)
out performed mysql on a optiplex with 2gig meg of memory. I had postgres
and mysql loaded and would run one server at a time doing testing.
My tests included using aqua studios connection to both databases and .asp
page using odbc connections. There was not a huge difference, but I had
significant time in postgres and it was a little faster, so I just took new
approaches (flattened views,eliminated outer joins etc) to fixing the
issues.

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

jfradkin(at)wazagua(dot)com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Jan Wieck
Sent: Monday, June 06, 2005 1:55 PM
To: PFC
Cc: Amit V Shah; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres

On 6/6/2005 2:12 PM, PFC wrote:

>
>> Please pardon my ignorance, but from whatever I had heard, mysql was
>> supposedly always faster than postgres !!!! Thats why I was so surprised

>> !!
>
> I heard a lot of this too, so much it seems common wisdom that
postgres
> is slow... well maybe some old version was, but it's getting better at
> every release, and the 8.0 really delivers...

The harder it is to evaluate software, the less often people reevaluate
it and the more often people just "copy" opinions instead of doing an
evaluation at all.

Today there are a gazillion people out there who "know" that MySQL is
faster than PostgreSQL. They don't know under what circumstances it is,
or what the word "circumstances" means in this context anyway. When you
ask them when was the last time they actually tested this you get in
about 99% of the cases an answer anywhere between 3 years and infinity
(for all those who never did). The remaining 1% can then be reduced to
an insignificant minority by asking how many concurrent users their test
simulated.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> Christopher Browne wrote:
>> There is a common "use case" where MySQL(tm) ...
>> select * from some_table where id='some primary key value';

> However this is a quite common use-case; and I wonder what the
> best practices for postgresql is for applications like that.

Setting up a prepared statement should be a noticeable win for that sort
of thing. Also of course there are the usual tuning issues: have you
picked an appropriate shared_buffers setting, etc.

regards, tom lane

> My tests included using aqua studios connection to both databases and
> .asp
> page using odbc connections.

Performance also depends a lot on the driver.
For instance, the PHP driver for MySQL is very very fast. It is also very
dumb, as it returns everything as a string and doesn't know about quoting.
For Python it's the reverse : the MySQL driver is slow and dumb, and the
postgres driver (psycopg 2) is super fast, handles all quoting, and knows
about type conversions, it will automatically convert a Python List into a
postgres Array and do the right thing with quoting, and it works both ways
(ie you select a TEXT[] you get a list of strings all parsed for you). It
knows about all the postgres types (yes even numeric <=> python Decimal)
and you can even add your own types. That's really cool, plus the
developer is a friendly guy.

------------------ in psql :
test=> CREATE TABLE typetests ( id SERIAL PRIMARY KEY, iarray INTEGER[]
NULL, narray NUMERIC[] NULL, tarray TEXT[] NULL,vnum NUMERIC NULL, vint
INTEGER NULL, vtext TEXT NULL) WITHOUT OIDS;
NOTICE: CREATE TABLE will create implicit sequence "typetests_id_seq" for
serial column "typetests.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"typetests_pkey" for table "typetests"
CREATE TABLE

------------------- in Python :
data = {
'myiarray' : [1,5,8,6],
'mytarray' : ['hello','world'],
'mynarray' : [Decimal("1.23"),Decimal("6.58")],
'mynum' : Decimal("66.66"),
'myint' : 555,
'mytext' :u "This is an Unicode String Портал по изучению иностранных"
}
cursor.execute( """INSERT INTO typetests
(iarray,narray,tarray,vnum,vint,vtext)
VALUES
(%(myiarray)s,%(mynarray)s,%(mytarray)s,%(mynum)s,%(myint)s,%(mytext)s)""",
data );

------------------ in psql :
test=> SELECT * FROM typetests;
id | iarray | narray | tarray | vnum | vint | vtext
----+-----------+-------------+---------------+-------+------+-----------
4 | {1,5,8,6} | {1.23,6.58} | {hello,world} | 66.66 | 555 | This is an
Unicode String Портал по изучению иностранных
(1 ligne)

------------------- in Python :

cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for elem in row:
print type(elem), elem

------------------- output :

<type 'int'> 4
<type 'list'> [1, 5, 8, 6]
<type 'list'> [Decimal("1.23"), Decimal("6.58")]
<type 'list'> ['hello', 'world']
<class 'decimal.Decimal'> 66.66
<type 'int'> 555
<type 'str'> This is an Unicode String Портал по изучению иностранных

------------------- in Python :

cursor = db.cursor(cursor_factory = psycopg.extras.DictCursor)
cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for key, value in row.items():
print key, ":", type(value), value

------------------- output :

iarray : <type 'list'> [1, 5, 8, 6]
tarray : <type 'list'> ['hello', 'world']
vtext : <type 'str'> This is an Unicode String Портал по изучению
иностранных
id : <type 'int'> 4
vnum : <class 'decimal.Decimal'> 66.66
vint : <type 'int'> 555
narray : <type 'list'> [Decimal("1.23"), Decimal("6.58")]

------------------- Timings :

Time to execute SELECT * FROM typetests and fetch the results, including
type conversions :

Plain query : 0.279 ms / request
Prepared query : 0.252 ms / request

(not that bad ! Pentium-M 1600 MHz laptop with local postgres).

Just doing SELECT id FROM typetests gives 0.1 ms for executing query and
fetching the result.

Who said Postgres was slow on small queries ?

* PFC <lists(at)boutiquenumerique(dot)com> wrote:

<snip>
> For Python it's the reverse : the MySQL driver is slow and dumb,
> and the postgres driver (psycopg 2) is super fast, handles all quoting,
> and knows about type conversions, it will automatically convert a
> Python List into a postgres Array and do the right thing with quoting,
> and it works both ways (ie you select a TEXT[] you get a list of
> strings all parsed for you). It knows about all the postgres types (yes
> even numeric <=> python Decimal) and you can even add your own types.
> That's really cool, plus the developer is a friendly guy.

Is there anything similar for java ?

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------

On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote:
> * PFC <lists(at)boutiquenumerique(dot)com> wrote:
>
> <snip>
> > For Python it's the reverse : the MySQL driver is slow and dumb,
> > and the postgres driver (psycopg 2) is super fast, handles all quoting,
> > and knows about type conversions, it will automatically convert a
> > Python List into a postgres Array and do the right thing with quoting,
> > and it works both ways (ie you select a TEXT[] you get a list of
> > strings all parsed for you). It knows about all the postgres types (yes
> > even numeric <=> python Decimal) and you can even add your own types.
> > That's really cool, plus the developer is a friendly guy.
>
> Is there anything similar for java ?
>

The postgres JDBC driver is very good-- refer to pgsql-jdbc mailing list
or look at jdbc.postgresql.org. I've had only limited experience with
the mysql jdbc driver, but it seemed servicable enough, if you can live
with their licensing and feature set.

Linux(Debian) + Java + PostgreSQL = Fastest

2005/7/8, Mark Lewis <mark(dot)lewis(at)mir3(dot)com>:
> On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote:
> > * PFC <lists(at)boutiquenumerique(dot)com> wrote:
> >
> > <snip>
> > > For Python it's the reverse : the MySQL driver is slow and dumb,
> > > and the postgres driver (psycopg 2) is super fast, handles all quoting,
> > > and knows about type conversions, it will automatically convert a
> > > Python List into a postgres Array and do the right thing with quoting,
> > > and it works both ways (ie you select a TEXT[] you get a list of
> > > strings all parsed for you). It knows about all the postgres types (yes
> > > even numeric <=> python Decimal) and you can even add your own types.
> > > That's really cool, plus the developer is a friendly guy.
> >
> > Is there anything similar for java ?
> >
>
> The postgres JDBC driver is very good-- refer to pgsql-jdbc mailing list
> or look at jdbc.postgresql.org. I've had only limited experience with
> the mysql jdbc driver, but it seemed servicable enough, if you can live
> with their licensing and feature set.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Atte

Moises Alberto Lindo Gutarra
Consultor y Desarrollador Java / Open Source
TUMI Solutions SAC
Tel: +51.13481104
Cel: +51.197366260
MSN : mlindo(at)tumisolutions(dot)com