Re: Need help to decide Mysql vs Postgres

От: Rod Taylor
Тема: Re: Need help to decide Mysql vs Postgres
Дата: ,
Msg-id: 1118074949.709.18.camel@home
(см: обсуждение, исходный текст)
Ответ на: Re: Need help to decide Mysql vs Postgres  (Amit V Shah)
Список: pgsql-performance

Скрыть дерево обсуждения

Need help to decide Mysql vs Postgres  (Amit V Shah, )
 Re: Need help to decide Mysql vs Postgres  ("Joshua D. Drake", )
 Re: Need help to decide Mysql vs Postgres  (Amit V Shah, )
  Re: Need help to decide Mysql vs Postgres  ("Joel Fradkin", )
  Re: Need help to decide Mysql vs Postgres  ("Joshua D. Drake", )
   Re: Need help to decide Mysql vs Postgres  (Philip Hallstrom, )
 Re: Need help to decide Mysql vs Postgres  (Josh Berkus, )
 Re: Need help to decide Mysql vs Postgres  (Amit V Shah, )
  Re: Need help to decide Mysql vs Postgres  ("Steinar H. Gunderson", )
  Re: Need help to decide Mysql vs Postgres  (Josh Berkus, )
 Re: Need help to decide Mysql vs Postgres  (PFC, )
 Re: Need help to decide Mysql vs Postgres  ( (), )
 Re: Need help to decide Mysql vs Postgres  (Bill Ewing, )
  Re: Need help to decide Mysql vs Postgres  (Christopher Kings-Lynne, )
  Re: Need help to decide Mysql vs Postgres  (PFC, )
 Re: Need help to decide Mysql vs Postgres  (Jeffrey Tenny, )
  Re: Need help to decide Mysql vs Postgres  (Tobias Brox, )
   Re: Need help to decide Mysql vs Postgres  (Michael Fuhr, )
 Re: Need help to decide Mysql vs Postgres  (Amit V Shah, )
  Re: Need help to decide Mysql vs Postgres  (Alvaro Herrera, )
  Re: Need help to decide Mysql vs Postgres  (Rod Taylor, )
  Re: Need help to decide Mysql vs Postgres  (PFC, )
 Re: Need help to decide Mysql vs Postgres  (Amit V Shah, )
  Re: Need help to decide Mysql vs Postgres  (Christopher Browne, )
   Re: Need help to decide Mysql vs Postgres  (Ron Mayer, )
    Re: Need help to decide Mysql vs Postgres  (Tom Lane, )
  Re: Need help to decide Mysql vs Postgres  (PFC, )
   Re: Need help to decide Mysql vs Postgres  (Jan Wieck, )
    Re: Need help to decide Mysql vs Postgres  ("Joel Fradkin", )
     Re: Need help to decide Mysql vs Postgres  (PFC, )
      Re: Need help to decide Mysql vs Postgres  (Enrico Weigelt, )
       Re: Need help to decide Mysql vs Postgres  (Mark Lewis, )
        Re: Need help to decide Mysql vs Postgres  (Moises Alberto Lindo Gutarra, )

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



В списке pgsql-performance по дате сообщения:

От: Mark Rinaudo
Дата:
Сообщение: Re: Postgresql on an AMD64 machine
От: Neil Conway
Дата:
Сообщение: Re: Postgresql on an AMD64 machine