Thread: Need help to decide Mysql vs Postgres
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
> > 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/
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:jd@commandprompt.com] Sent: Tuesday, May 24, 2005 1:15 PM To: Amit V Shah Cc: 'pgsql-performance@postgresql.org' 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/
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 jfradkin@wazagua.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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Amit V Shah Sent: Tuesday, May 24, 2005 12:22 PM To: 'Joshua D. Drake' Cc: 'pgsql-performance@postgresql.org' 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:jd@commandprompt.com] Sent: Tuesday, May 24, 2005 1:15 PM To: Amit V Shah Cc: 'pgsql-performance@postgresql.org' 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
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
> - 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
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/
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 josh@agliodbs.com www.agliodbs.com Ph: 415-752-2500 Fax: 415-752-2387 2166 Hayes Suite 200 San Francisco, CA
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.
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:jd@commandprompt.com] > Sent: Tuesday, May 24, 2005 1:15 PM > To: Amit V Shah > Cc: 'pgsql-performance@postgresql.org' > 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/
>> 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
In article <200505241037.49050.josh@agliodbs.com>, Josh Berkus <josh@agliodbs.com> 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/
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 ------------------------------------------------------------------------
> 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
If you want something more "embedded" in your application, you could
consider :
http://firebird.sourceforge.net/
http://hsqldb.sourceforge.net/
http://sqlite.org/
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
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:jeffrey.tenny@comcast.net] Sent: Monday, June 06, 2005 11:51 AM To: pgsql-performance@postgresql.org 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 majordomo@postgresql.org)
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/)
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:jeffrey.tenny@comcast.net] > Sent: Monday, June 06, 2005 11:51 AM > To: pgsql-performance@postgresql.org > 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 majordomo@postgresql.org) > > > ---------------------------(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 > --
> 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" ?
> 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
[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
In the last exciting episode, ashah@tagaudit.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 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/
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.
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@Yahoo.com #
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@wazagua.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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jan Wieck Sent: Monday, June 06, 2005 1:55 PM To: PFC Cc: Amit V Shah; pgsql-performance@postgresql.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@Yahoo.com # ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Ron Mayer <rm_pg@cheapcomplexdevices.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@boutiquenumerique.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@metux.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@boutiquenumerique.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.lewis@mir3.com>: > On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote: > > * PFC <lists@boutiquenumerique.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@tumisolutions.com