Обсуждение: Need help to decide Mysql vs Postgres

От:
Amit V Shah
Дата:

Hi all,

From whatever reading and surfing I have done, I have found that postgres is
good. Actually I myself am a fan of postgres as compared to mysql. However I
want to have some frank opinions before I decide something.  Following are
some of the aspects of my schema, and our concerns --

- We have around 150 tables on the DB
- We have lot of foreign keys between the tables
- Couple of tables are going to have around couple of hundereds of millions
of records (300 Million right now and would grow). Few of this tables are
fairly wide with around 32 columns, and have around 3-4 columns which are
foreign keys and refer to other tables
- Most of the DB usage is Selects. We would have some inserts but that would
be like a nightly or a monthly process


Our only concern with going with postgres is speed. I haven't done a speed
test yet so I can't speak. But the major concern is that the selects and
inserts are going to be much much slower on postgres than on mysql. I dont
know how true this is. I know this is a postgres forum so everyone will say
postgres is better but I am just looking for some help and advise I guess
!!!

I am not trying to start a mysql vs postgres war so please dont
misunderstand me .... I tried to look around for mysql vs postgres articles,
but most of them said mysql is better in speed. However those articles were
very old so I dont know about recent stage. Please comment !!!

Thanks,
Amit


От:
"Joshua D. Drake"
Дата:

>
> I am not trying to start a mysql vs postgres war so please dont
> misunderstand me .... I tried to look around for mysql vs postgres articles,
> but most of them said mysql is better in speed. However those articles were
> very old so I dont know about recent stage. Please comment !!!

It is my experience that MySQL is faster under smaller load scenarios.
Say 5 - 10 connections only doing simple SELECTS. E.g; a dymanic website.

It is also my experience that PostgreSQL is faster and more stable under
consistent and heavy load. I have customers you regularly are using up
to 500 connections.

Note that alot of this depends on how your database is designed. Foreign
keys slow things down.

I think it would be important for you to look at your overall goal of
migration. MySQL is really not a bad product "IF" you are willing to
work within its limitations.

PostgreSQL is a real RDMS, it is like Oracle or DB2 and comes with a
comparable feature set. Only you can decide if that is what you need.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

От:
Amit V Shah
Дата:

Hi Josh,

Thanks for the prompt reply !! Actually migration is inevitable. We have a
totally messed up schema, not normalized and stuff like that. So the goal of
the migration is to get a new and better normalized schema. That part is
done already. Now the decision point is, should we go with postgres or
mysql.

Thanks,
Amit

-----Original Message-----
From: Joshua D. Drake [mailto:]
Sent: Tuesday, May 24, 2005 1:15 PM
To: Amit V Shah
Cc: ''
Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres


>
> I am not trying to start a mysql vs postgres war so please dont
> misunderstand me .... I tried to look around for mysql vs postgres
articles,
> but most of them said mysql is better in speed. However those articles
were
> very old so I dont know about recent stage. Please comment !!!

It is my experience that MySQL is faster under smaller load scenarios.
Say 5 - 10 connections only doing simple SELECTS. E.g; a dymanic website.

It is also my experience that PostgreSQL is faster and more stable under
consistent and heavy load. I have customers you regularly are using up
to 500 connections.

Note that alot of this depends on how your database is designed. Foreign
keys slow things down.

I think it would be important for you to look at your overall goal of
migration. MySQL is really not a bad product "IF" you are willing to
work within its limitations.

PostgreSQL is a real RDMS, it is like Oracle or DB2 and comes with a
comparable feature set. Only you can decide if that is what you need.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


От:
"Joel Fradkin"
Дата:

I took an unbiased look and did some tests.
Objectively for me MYSQL was not an improvement for speed.
I had read the benchmarks in pcmagazine from a while back as well.

I did some tests using ODBC, and .net connections and also used aqua studios
(hooks up to both data bases) and found postgres a bit faster.

I did spend more time getting a feeling for setup on postgres, but I was at
a point of desperation as some queries were still too slow on postgres.

I ended up re-engineering my app to use simpler(flattened) data sets.
I still have a few I am working through, but all in all it is running better
then when I was on MSSQL, and MYSQL was just slower on the tests I did.

I loaded both MYSQL and postgres on both my 4 processor Dell running red hat
AS3 and Windows XP on a optiplex.

Joel Fradkin

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


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: 
[mailto:] On Behalf Of Amit V Shah
Sent: Tuesday, May 24, 2005 12:22 PM
To: 'Joshua D. Drake'
Cc: ''
Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres

Hi Josh,

Thanks for the prompt reply !! Actually migration is inevitable. We have a
totally messed up schema, not normalized and stuff like that. So the goal of
the migration is to get a new and better normalized schema. That part is
done already. Now the decision point is, should we go with postgres or
mysql.

Thanks,
Amit

-----Original Message-----
From: Joshua D. Drake [mailto:]
Sent: Tuesday, May 24, 2005 1:15 PM
To: Amit V Shah
Cc: ''
Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres


>
> I am not trying to start a mysql vs postgres war so please dont
> misunderstand me .... I tried to look around for mysql vs postgres
articles,
> but most of them said mysql is better in speed. However those articles
were
> very old so I dont know about recent stage. Please comment !!!

It is my experience that MySQL is faster under smaller load scenarios.
Say 5 - 10 connections only doing simple SELECTS. E.g; a dymanic website.

It is also my experience that PostgreSQL is faster and more stable under
consistent and heavy load. I have customers you regularly are using up
to 500 connections.

Note that alot of this depends on how your database is designed. Foreign
keys slow things down.

I think it would be important for you to look at your overall goal of
migration. MySQL is really not a bad product "IF" you are willing to
work within its limitations.

PostgreSQL is a real RDMS, it is like Oracle or DB2 and comes with a
comparable feature set. Only you can decide if that is what you need.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


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

               http://archives.postgresql.org


От:
Josh Berkus
Дата:

Amit,

> - We have lot of foreign keys between the tables

Do you need these keys to be enforced?   Last I checked, MySQL was still
having trouble with foriegn keys.

> - Most of the DB usage is Selects. We would have some inserts but that
> would be like a nightly or a monthly process

So transaction integrity is not a real concern?   This sounds like a data
warehouse; wanna try Bizgres?  (www.bizgres.org)

> Our only concern with going with postgres is speed. I haven't done a speed
> test yet so I can't speak. But the major concern is that the selects and
> inserts are going to be much much slower on postgres than on mysql. I dont
> know how true this is. I know this is a postgres forum so everyone will say
> postgres is better but I am just looking for some help and advise I guess

Well, the relative speed depends on what you're doing.   You want slow, try a
transaction rollback on a large InnoDB table ;-)   PostgreSQL/Bizgres will
also be implementing bitmapped indexes and table partitioning very soon, so
we're liable to pull way ahead of MySQL on very large databases.

> I am not trying to start a mysql vs postgres war so please dont
> misunderstand me .... I tried to look around for mysql vs postgres
> articles, but most of them said mysql is better in speed.

Also I'll bet most of those articles were based on either website use or
single-threaded simple-sql tests.   Not a read data warehousing situatiion.

It's been my personal experience that MySQL does not scale well beyond about
75GB without extensive support from MySQL AB.   PostgreSQL more easily scales
up to 200GB, and to as much as 1TB with tuning expertise.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

От:
Amit V Shah
Дата:

> - Most of the DB usage is Selects. We would have some inserts but that
> would be like a nightly or a monthly process

So transaction integrity is not a real concern?   This sounds like a data
warehouse; wanna try Bizgres?  (www.bizgres.org)

I took a look at this. I have a few concerns with bizgres though -- I am
using jetspeed portal engine and Hibernate as my O/R Mapping layer. I know
for sure that they dont support bizgres. Now the question is what difference
is there between bizgres and postgres ... I guess I will try to look around
the website more and find out, but if there is something you would like to
comment, that would be very helpful ...

Thanks,
Amit


От:
"Steinar H. Gunderson"
Дата:

On Tue, May 24, 2005 at 01:56:54PM -0400, Amit V Shah wrote:
> I took a look at this. I have a few concerns with bizgres though -- I am
> using jetspeed portal engine and Hibernate as my O/R Mapping layer.

If you have problems with performance, you might want to look into using JDBC
directly instead of using Hibernate. I know groups of people who are rather
less-than-happy with it performance-wise :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

От:
Josh Berkus
Дата:

Amit,

> I took a look at this. I have a few concerns with bizgres though -- I am
> using jetspeed portal engine and Hibernate as my O/R Mapping layer. I know
> for sure that they dont support bizgres. Now the question is what
> difference is there between bizgres and postgres ... I guess I will try to
> look around the website more and find out, but if there is something you
> would like to comment, that would be very helpful ...

Bizgres is PostgreSQL.   Just a different packaging of it, with some patches
which are not yet in the main PostgreSQL.   Also, it's currently beta.

--Josh

--
__Aglio Database Solutions_______________
Josh Berkus               Consultant
     www.agliodbs.com
Ph: 415-752-2500    Fax: 415-752-2387
2166 Hayes Suite 200    San Francisco, CA

От:
PFC
Дата:

    It's common knowledge, it seems, that MySQL without transactions will be
a lot faster than Postgres on Inserts. And on Updates too, that is, unless
you have more than a few concurrent concurrent connections, at which point
the MySQL full table lock will just kill everything. And you don't have
transactions, of course, and if something goes wrong, bye bye data, or
funky stuff happens, like half-commited transactions if a constraint is
violated in an INSERT SELECT, or you get 0 January 0000 or 31 February,
etc.
    I heard it said that MySQL with transactions (InnoDB) is slower than
postgres. I'd believe it... and you still get 00-00-0000 as a date for
free.
    But from your use case postgres doesn't sound like a problem, yours
sounds like a few big batched COPY's which are really really fast.

    And about SELECTs, this is really from an experience I had a few months
ago, from a e-commerce site... well, to put it nicely, MySQL's planner
don't know shit when it comes to doing anything a bit complicated. I had
this query to show the "also purchased" products on a page, and also a few
other queries, best buys in this category, related products, etc...,
nothing very complicated really, at worst they were 4-table joins... and
with 50K products MySQL planned it horrendously and it took half a second
! Seq scans every times... I had to split the query in two, one to get the
product id's, another one to get the products.
    I took the sql, put it in postgres with the usual changes (typenames,
etc...) but same indexes, same data... the query took half a millisecond.
Well... what can I say ?

    Also when you sit in front of the psql or mysql command line, it's an
entirely different experience. One is a pleasure to work with... the other
one is just a pain.


От:
"Joshua D. Drake"
Дата:

Amit V Shah wrote:
> Hi Josh,
>
> Thanks for the prompt reply !! Actually migration is inevitable. We have a
> totally messed up schema, not normalized and stuff like that. So the goal of
> the migration is to get a new and better normalized schema. That part is
> done already. Now the decision point is, should we go with postgres or
> mysql.

O.k. then I would ask myself this:

Would I trust my brand new data that I have put all this effort into,
that finally looks the way that I want it to look, to a database that
truncates information?

PostgreSQL is truly ACID compliant. Even if it is a little slower (which
under normal use I don't find to be the case) wouldn't the reliability
of PostgreSQL make up for say the 10% net difference in performance?

Sincerely,

Joshua D. Drake



>
> Thanks,
> Amit
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:]
> Sent: Tuesday, May 24, 2005 1:15 PM
> To: Amit V Shah
> Cc: ''
> Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres
>
>
>
>>I am not trying to start a mysql vs postgres war so please dont
>>misunderstand me .... I tried to look around for mysql vs postgres
>
> articles,
>
>>but most of them said mysql is better in speed. However those articles
>
> were
>
>>very old so I dont know about recent stage. Please comment !!!
>
>
> It is my experience that MySQL is faster under smaller load scenarios.
> Say 5 - 10 connections only doing simple SELECTS. E.g; a dymanic website.
>
> It is also my experience that PostgreSQL is faster and more stable under
> consistent and heavy load. I have customers you regularly are using up
> to 500 connections.
>
> Note that alot of this depends on how your database is designed. Foreign
> keys slow things down.
>
> I think it would be important for you to look at your overall goal of
> migration. MySQL is really not a bad product "IF" you are willing to
> work within its limitations.
>
> PostgreSQL is a real RDMS, it is like Oracle or DB2 and comes with a
> comparable feature set. Only you can decide if that is what you need.
>
> Sincerely,
>
> Joshua D. Drake
> Command Prompt, Inc.
>
>


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

От:
Philip Hallstrom
Дата:

>> Thanks for the prompt reply !! Actually migration is inevitable. We have a
>> totally messed up schema, not normalized and stuff like that. So the goal
>> of
>> the migration is to get a new and better normalized schema. That part is
>> done already. Now the decision point is, should we go with postgres or
>> mysql.

Coming in a little late, but you might find these links interesting... not
sure how up to date and/or accurate they are, but might give you some
things to look into.

http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html

От:
ellis@no.spam ()
Дата:

In article <>,
Josh Berkus <> wrote:

>So transaction integrity is not a real concern?

I know of all too many people that consider that to be
true. <sigh> They simply don't understand the problem.

--
http://www.spinics.net/linux/


От:
Bill Ewing
Дата:

A consultant did a project for us and chose MySQL.  We thought it was
cool that MySQL was free.

Turns out, MySQL costs over $500 (USD) if you are a commercial
organization like us!  Even worse, we have to formally transfer
licenses to customers and any further transfers must include
involvement of the MySQL organization.

Since we are a reputable organization, we diligently track the license
numbers- I make my mfg group log them, print them and include them in
the BOM of systems we ship.  Occassionally, I audit them to make sure
we are staying legal.  I spent many hours studying the MySQL license
agreements, I found ambiguitites and questions and called their rep
several times.  As usual, licenses punish the honest people.  What a
PITA.

The cost for us to do that work and tracking is hard to measure but is
certainly not free.

This prompted me to look around and find another open source database
that did not go over to the dark side and turn greedy.  Since Postgres
has true foreign key integrity enforcement and truly has a reputation
for being hardened and robust, it got our attention.

We are pretty close to choosing PostgreSQL 8.x.  Since we know and use
only Windows, there's still some learning curve and pain we are going
through.

Fortunately, there is a simple installer for windows.  The PGAdmin tool
that comes with PG looks decent and a company named EMS makes a decent
looking tool for about $195.

Trouble is, we are not DB admins.  We're programmers who love and know
java, JDBC and a few other languages.

So, our problem in installing is we don't know a cluster or SSL from a
hole in the ground.  Things get confusing about contexts- are we
talking about a user of the system or the database?  Yikes, do I need
to write down the 30+ character autogenerated password?

We just want to use JDBC, code SQL queries and essentially not care
what database is under us.  We would love to find a good tool that runs
as an Eclipse plug-in that lets us define our database, generate a
script file to create it and perhaps also help us concoct queries.

Our experience is that the many UNIX-ish thing about postgres are there
and we don't know UNIX.  This makes you realize how much you take for
granted about the OS you do know.  Of course, we'll learn, but postgres
people, if you're listening: good job, now take us a little farther and
we will be your most ardent supporters.

==Bill==



--
Bill Ewing
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------


От:
Christopher Kings-Lynne
Дата:

> So, our problem in installing is we don't know a cluster or SSL from a
> hole in the ground.  Things get confusing about contexts- are we
> talking about a user of the system or the database?  Yikes, do I need
> to write down the 30+ character autogenerated password?

No you don't need to write it down :)

> We just want to use JDBC, code SQL queries and essentially not care
> what database is under us.  We would love to find a good tool that runs
> as an Eclipse plug-in that lets us define our database, generate a
> script file to create it and perhaps also help us concoct queries.

Dunno if such a thing exists?

> Our experience is that the many UNIX-ish thing about postgres are there
> and we don't know UNIX.  This makes you realize how much you take for
> granted about the OS you do know.  Of course, we'll learn, but postgres
> people, if you're listening: good job, now take us a little farther and
> we will be your most ardent supporters.

Just ask questions on the lists, or get instant answers on #postgresql
on irc.freenode.org.

Cheers,

Chris

От:
PFC
Дата:



    If you want something more "embedded" in your application, you could
consider :

http://firebird.sourceforge.net/
http://hsqldb.sourceforge.net/
http://sqlite.org/

От:
Jeffrey Tenny
Дата:

Re: your JDBC wishes: Consider IBM Cloudscape (now Apache Derby) too,
which has an apache license.  It's all pure java and it's easy to get going.


As to MySql vs Postgres: license issues aside, if you have
transactionally complex needs (multi-table updates, etc), PostgreSQL
wins hands down in my experience.  There are a bunch of things about
MySQL that just suck for high end SQL needs. (I like my subqueries,
and I absolutely demand transactional integrity).

There are some pitfalls to pgsql though, especially for existing SQL
code using MAX and some other things which can really be blindsided
(performance-wise) by pgsql if you don't use the workarounds.


MySQL is nice for what I call "raw read speed" applications. But that
license is an issue for me, as it is for you apparently.


Some cloudscape info:
http://www-306.ibm.com/software/data/cloudscape/

Some info on pitfalls of MySQL and PostgreSQL, an interesting contrast:
http://sql-info.de/postgresql/postgres-gotchas.html
http://sql-info.de/mysql/gotchas.html


От:
Amit V Shah
Дата:

Hi all,

Thanks for your replies.

I ran a very prelimnary test, and found following results. I feel they are
wierd and I dont know what I am doing wrong !!!

I made a schema with 5 tables. I have a master data table with foreign keys
pointing to other 4 tables. Master data table has around 4 million records.
When I run a select joining it with the baby tables,

postgres -> returns results in 2.8 seconds
mysql -> takes around 16 seconds !!!! (This is with myisam ... with innodb
it takes 220 seconds)

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

Thanks,
Amit

-----Original Message-----
From: Jeffrey Tenny [mailto:]
Sent: Monday, June 06, 2005 11:51 AM
To: 
Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres


Re: your JDBC wishes: Consider IBM Cloudscape (now Apache Derby) too,
which has an apache license.  It's all pure java and it's easy to get going.


As to MySql vs Postgres: license issues aside, if you have
transactionally complex needs (multi-table updates, etc), PostgreSQL
wins hands down in my experience.  There are a bunch of things about
MySQL that just suck for high end SQL needs. (I like my subqueries,
and I absolutely demand transactional integrity).

There are some pitfalls to pgsql though, especially for existing SQL
code using MAX and some other things which can really be blindsided
(performance-wise) by pgsql if you don't use the workarounds.


MySQL is nice for what I call "raw read speed" applications. But that
license is an issue for me, as it is for you apparently.


Some cloudscape info:
http://www-306.ibm.com/software/data/cloudscape/

Some info on pitfalls of MySQL and PostgreSQL, an interesting contrast:
http://sql-info.de/postgresql/postgres-gotchas.html
http://sql-info.de/mysql/gotchas.html


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to )


От:
Alvaro Herrera
Дата:

On Mon, Jun 06, 2005 at 12:00:08PM -0400, Amit V Shah wrote:

> I made a schema with 5 tables. I have a master data table with foreign keys
> pointing to other 4 tables. Master data table has around 4 million records.
> When I run a select joining it with the baby tables,
>
> postgres -> returns results in 2.8 seconds
> mysql -> takes around 16 seconds !!!! (This is with myisam ... with innodb
> it takes 220 seconds)

PostgreSQL has an excellent query optimizer, so if you get a much better
execution time than MySQL in complex queries this isn't at all unexpected.

I assume the MySQL guys would tell you to rewrite the queries in certain
ways to make it go faster (just like the Postgres guys tell people to
rewrite certain things when they hit Postgres limitations.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"I would rather have GNU than GNOT."  (ccchips, lwn.net/Articles/37595/)

От:
Rod Taylor
Дата:

On Mon, 2005-06-06 at 12:00 -0400, Amit V Shah wrote:
> Hi all,
>
> Thanks for your replies.
>
> I ran a very prelimnary test, and found following results. I feel they are
> wierd and I dont know what I am doing wrong !!!
>
> I made a schema with 5 tables. I have a master data table with foreign keys
> pointing to other 4 tables. Master data table has around 4 million records.
> When I run a select joining it with the baby tables,
>
> postgres -> returns results in 2.8 seconds
> mysql -> takes around 16 seconds !!!! (This is with myisam ... with innodb
> it takes 220 seconds)

We said MySQL was faster for simple selects and non-transaction inserts
on a limited number of connections.

Assuming you rebuilt statistics in MySQL (myisamchk -a), I would presume
that PostgreSQLs more mature optimizer has come into play in the above 5
table join test by finding a better (faster) way of executing the query.

If you post EXPLAIN ANALYZE output for the queries, we might be able to
tell you what they did differently.

> 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

If possible, it would be wise to run a performance test with the
expected load you will receive. If you expect to have 10 clients perform
operation X at a time, then benchmark that specific scenario.

Both PostgreSQL and MySQL will perform differently in a typical real
load situation than with a single user, single query situation.

> -----Original Message-----
> From: Jeffrey Tenny [mailto:]
> Sent: Monday, June 06, 2005 11:51 AM
> To: 
> Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres
>
>
> Re: your JDBC wishes: Consider IBM Cloudscape (now Apache Derby) too,
> which has an apache license.  It's all pure java and it's easy to get going.
>
>
> As to MySql vs Postgres: license issues aside, if you have
> transactionally complex needs (multi-table updates, etc), PostgreSQL
> wins hands down in my experience.  There are a bunch of things about
> MySQL that just suck for high end SQL needs. (I like my subqueries,
> and I absolutely demand transactional integrity).
>
> There are some pitfalls to pgsql though, especially for existing SQL
> code using MAX and some other things which can really be blindsided
> (performance-wise) by pgsql if you don't use the workarounds.
>
>
> MySQL is nice for what I call "raw read speed" applications. But that
> license is an issue for me, as it is for you apparently.
>
>
> Some cloudscape info:
> http://www-306.ibm.com/software/data/cloudscape/
>
> Some info on pitfalls of MySQL and PostgreSQL, an interesting contrast:
> http://sql-info.de/postgresql/postgres-gotchas.html
> http://sql-info.de/mysql/gotchas.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to )
>
>
> ---------------------------(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
>
--


От:
PFC
Дата:

> postgres -> returns results in 2.8 seconds

    What kind of plan does it do ? seq scan on the big tables and hash join
on the small tables ?

> mysql -> takes around 16 seconds !!!! (This is with myisam ... with
> innodb it takes 220 seconds)

    I'm not surprised at all.
    Try the same Join query but with a indexed where + order by / limit on
the big table and you should get even worse for MySQL.
    I found 3 tables in a join was the maximum the MySQL planner was able to
cope with before blowing up just like you experienced.

> 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" ?

От:
Amit V Shah
Дата:

> 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


От:
Tobias Brox
Дата:

[Jeffrey Tenny - Mon at 11:51:22AM -0400]
> There are some pitfalls to pgsql though, especially for existing SQL
> code using MAX and some other things which can really be blindsided
> (performance-wise) by pgsql if you don't use the workarounds.

Yes, I discovered that - "select max(num_attr)" does a full table scan even
if the figure can be found easily through an index.

There exists a workaround:

  select num_attr from my_table order by num_attr desc limit 1;

will find the number through the index.

--
Tobias Brox, Tallinn

От:
Christopher Browne
Дата:

In the last exciting episode,  (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

От:
PFC
Дата:


> 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
>



От:
Michael Fuhr
Дата:

On Mon, Jun 06, 2005 at 08:25:08PM +0300, Tobias Brox wrote:
> [Jeffrey Tenny - Mon at 11:51:22AM -0400]
> > There are some pitfalls to pgsql though, especially for existing SQL
> > code using MAX and some other things which can really be blindsided
> > (performance-wise) by pgsql if you don't use the workarounds.
>
> Yes, I discovered that - "select max(num_attr)" does a full table scan even
> if the figure can be found easily through an index.

PostgreSQL 8.1 will be able to use indexes for MIN and MAX.

http://archives.postgresql.org/pgsql-committers/2005-04/msg00163.php
http://archives.postgresql.org/pgsql-committers/2005-04/msg00168.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

От:
Ron Mayer
Дата:

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.

От:
Jan Wieck
Дата:

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.                                  #
#==================================================  #

От:
"Joel Fradkin"
Дата:

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


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: 
[mailto:] On Behalf Of Jan Wieck
Sent: Monday, June 06, 2005 1:55 PM
To: PFC
Cc: Amit V Shah; 
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.                                  #
#==================================================  #

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to 


От:
Tom Lane
Дата:

Ron Mayer <> 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

От:
PFC
Дата:

> 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 ?












От:
Enrico Weigelt
Дата:

* PFC <> 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:     
---------------------------------------------------------------------
  Realtime Forex/Stock Exchange trading powered by postgresSQL :))
                                            http://www.fxignal.net/
---------------------------------------------------------------------

От:
Mark Lewis
Дата:

On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote:
> * PFC <> 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.




От:
Moises Alberto Lindo Gutarra
Дата:

Linux(Debian) + Java + PostgreSQL = Fastest

2005/7/8, Mark Lewis <>:
> On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote:
> > * PFC <> 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 :