PostgreSQL is much faster than MySQL, only when...

And ofcourse, you ran ANALYZE before doing any timings, right?

On Tue, Nov 25, 2003 at 01:08:55PM +0100, Marek Lewczuk wrote:

Hello,
I have changed DB from MySQL to PostgreSQL. When I have run my
application on PostgreSQL it was disaster - it was much slower than
MySQL...

I have tried to change PG configuration file etc.. no luck. After many
long days of thinking what is wrong I have made several tests with
"EXPLAIN" statement, and to my amusement there was many SeqScan - MySQL
didn't show that things. I have made some changes in PG db structure
(new indexes etc) and then I have made another test: PostgreSQL time -
10ms, MySQL time - 1 sec. I've tried to change the structure in MySQL
but it didn't change the results.

So my advise for all users which think that MySQL is faster than PG -
re-check your DB structure, check all your querys using EXPLAIN... maybe
new indexes should be added etc.

Regards,
Marek

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

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

U�ytkownik Martijn van Oosterhout napisa�:

And ofcourse, you ran ANALYZE before doing any timings, right?

Of course.

U�ytkownik terry@greatgulfhomes.com napisa�:

You said:
"When I have run my application on PostgreSQL it was disaster"

I think you meant:
"When I have run my application on MySQL it was disaster"

Well, my english isn't perfect or it was simple mistake... but what I
wanted to say is that my application wasn't working well with PostgreSQL
before the db structure has been changed (new indexes). After that it
was much better...

Cross-posted to "comp.databases.postgresql.advocacy" because
PostgreSQL could be very helpful to MySQL DBAs who wish to optimize their
databases -- just convert it to PostgreSQL on a test system and use EXPLAIN
and ANALYZE to identify the weaknesses.

Even if these MySQL DBAs don't use PostgreSQL for anything else, at
the very least it will increase awareness of the product. =)

Hello,
I have changed DB from MySQL to PostgreSQL. When I have run my
application on PostgreSQL it was disaster - it was much slower than
MySQL...

I have tried to change PG configuration file etc.. no luck. After many
long days of thinking what is wrong I have made several tests with
"EXPLAIN" statement, and to my amusement there was many SeqScan - MySQL
didn't show that things. I have made some changes in PG db structure
(new indexes etc) and then I have made another test: PostgreSQL time -
10ms, MySQL time - 1 sec. I've tried to change the structure in MySQL
but it didn't change the results.

So my advise for all users which think that MySQL is faster than PG -
re-check your DB structure, check all your querys using EXPLAIN... maybe
new indexes should be added etc.

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:

Użytkownik terry@greatgulfhomes.com napisał:

You said:
"When I have run my application on PostgreSQL it was disaster"

I think you meant:
"When I have run my application on MySQL it was disaster"

Well, my english isn't perfect or it was simple mistake... but what I
wanted to say is that my application wasn't working well with PostgreSQL
before the db structure has been changed (new indexes). After that it
was much better...

Yes. I think the gist of your post was "out of the box postgresql
performed like garbage compared to mysql, but then i spent some time
tweaking and tuning, taking advantage of indexes, and now it performs so
quickly that i am unable to make any changes within mysql to match
postgresql's performance"

I'm curious as to what type of application you run and what first
prompted you to switch to postgresql?

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:

Yes. I think the gist of your post was "out of the box postgresql
performed like garbage compared to mysql, but then i spent some time
tweaking and tuning, taking advantage of indexes, and now it performs so
quickly that i am unable to make any changes within mysql to match
postgresql's performance"

I would put it this way. 'Applications designed for mysql does not perform good
when ported to postgresql when porting means "just make it work with postgresql"'.

Rather obvious isn't it?

Shridhar

U�ytkownik Robert Treat napisa�:

On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:

Yes. I think the gist of your post was "out of the box postgresql
performed like garbage compared to mysql, but then i spent some time
tweaking and tuning, taking advantage of indexes, and now it performs so
quickly that i am unable to make any changes within mysql to match
postgresql's performance"

Exactly. But as I wrote before - tweaking and tuning wasn't so
important... You cannot use the same db structure in both databases -
even if you think that all indexes, keys are well for MySQL it could be
not sufficient for PostgreSQL. Many people use the same structure and
they see that PostgreSQL is slower... so I just wanted to tell them,
that it is necessary to re-check structure which will be used with PG.

I'm curious as to what type of application you run and what first
prompted you to switch to postgresql?

It's analysis system for automotive market. There are many complicated
querys, with many joins... PostgreSQL is much more flexible, more
powerful and more stable than MySQL. There are also many other features
which are just not available in MySQL...

I'm curious as to what type of application you run and

what first

prompted you to switch to postgresql?

I would like to add my 2 cents to this although I have not
been asked by anyone about this :-)

If I remember correctly the fact about 7 years ago...
when PostgreSQL already supported
- sub-query
- transaction
- triggers
- stored procedures (functions)
, mySQL did not have any of these critical and essential
capabilities required by any serious bussiness applications.

When I noticed that fact, I have never turned my head back
to mySQL again since then. Why? Because I thought I would
take a train instead of a plane (if it indeed is), when I am
100% sure that the plane will not fly due to bad wether.

Regards,

CN

How does the performance compare using models mimicked from Oracle? Is
there any information anywhere on that?

Marek Lewczuk wrote:

U�ytkownik Robert Treat napisa�:

On Tue, 2003-11-25 at 08:07, Marek Lewczuk wrote:

Yes. I think the gist of your post was "out of the box postgresql
performed like garbage compared to mysql, but then i spent some time
tweaking and tuning, taking advantage of indexes, and now it performs so
quickly that i am unable to make any changes within mysql to match
postgresql's performance"

Exactly. But as I wrote before - tweaking and tuning wasn't so
important... You cannot use the same db structure in both databases -
even if you think that all indexes, keys are well for MySQL it could be
not sufficient for PostgreSQL. Many people use the same structure and
they see that PostgreSQL is slower... so I just wanted to tell them,
that it is necessary to re-check structure which will be used with PG.

I'm curious as to what type of application you run and what first
prompted you to switch to postgresql?

It's analysis system for automotive market. There are many complicated
querys, with many joins... PostgreSQL is much more flexible, more
powerful and more stable than MySQL. There are also many other features
which are just not available in MySQL...

Robert Treat

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

http://www.postgresql.org/docs/faqs/FAQ.html

--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----

Barbara Lindsey wrote:

How does the performance compare using models mimicked from Oracle? Is
there any information anywhere on that?

From reports we have received, we are usually +/- 10% compared to Oracle
on most queries.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hi all;

Since we are on the topic of what prompted us to use PostgreSQL, I figured I
would share my experiences as well, and some additional thoughts that I had.

I chose PostgreSQL about 2 years ago when I realized that the application I
was building needed something more robust than MySQL. I found the learning
curve to be a bit steep at first, but eventually got the hang of it and have
developed a real appreciation for PostgreSQL. Unlike many members here I
won't say I would never go back to MySQL, because there are some
circumstances where I would (where I need an RDBMS-like interface but not an
RDBMS, f. ex. MySQL's heap tables could be very useful for real-time control
systems). However, I am actually seeing less and less value in MySQL as I
become more familiar with the extensibility features in PostgreSQL.

Anyway one additional thought I had was that it should be possible to write
parsers for text files in PL/PERLu and then plug those in as views. In this
way, files such as /etc/passwd or even /var/log/messages could be used as if
they were tables in the database. Information from these tables could then
be selected into other tables, presented directly to an application, etc.
While it mught be possible to do the same for MySQL, it would be much more
work. Imagine being able to run a query such as:

select * from logs.iptables
where proto = 'TCP' and td_entered > '2003-01-01' and port =
(select port from sysinfo.services where service='ssh');

in order to get every hit against the ssh service on the firewall in the
current year. Of course, I imagine, it would take some time to run ;-) But
for generating reports of system activirt, I would not assume this to be a
problem.

Next question-- any ideas how one could generate something like MySQL's heap
tables (maybe in shared memory?) within PostgreSQL?

Best WIshes,
Chris Travers

Chris Travers wrote:

Anyway one additional thought I had was that it should be possible to write
parsers for text files in PL/PERLu and then plug those in as views. In this
way, files such as /etc/passwd or even /var/log/messages could be used as if
they were tables in the database. Information from these tables could then
be selected into other tables, presented directly to an application, etc.
While it mught be possible to do the same for MySQL, it would be much more

Copy can import delimited data with user specified delimiter. May be you can
upload the files in batch to database periodically.

Of course if you need multiple delimiter parsing, then you need to do something
about it. Sed/AWK scripts probably..

Next question-- any ideas how one could generate something like MySQL's heap
tables (maybe in shared memory?) within PostgreSQL?

Pardon the dumb question(Used mysql seriously onced 2 years back.). What is heap
table? What do you need that postgresql does not offer? Just trying to understand..

Shridhar

Since we are on the topic of what prompted us to use PostgreSQL, I
figured I would share my experiences as well, and some additional
thoughts that I had.

I chose PostgreSQL about 2 years ago when I realized that the
application I was building needed something more robust than MySQL. I
found the learning curve to be a bit steep at first, but eventually got
the hang of it and have developed a real appreciation for PostgreSQL.
Unlike many members here I won't say I would never go back to MySQL,
because there are some circumstances where I would (where I need an
RDBMS-like interface but not an RDBMS, f. ex. MySQL's heap tables could
be very useful for real-time control systems). However, I am actually
seeing less and less value in MySQL as I become more familiar with the
extensibility features in PostgreSQL.

I'm moving from Oracle 8i to PostgreSQL. I don't really do much fancy
stuff in SQL now, but I do use views a little bit and so MySQL just isn't
an option for me.

I just finished reading another book on PostgreSQL so I can imagine
why you like it so much. The only two things I'd really like to see in
PostgreSQL are:

1. Support for "CONNECT BY" (useful for family trees), which I
understand is being worked on now. The only twist is that I'd also like to
see a specialized index option for supporting this as this is something
that Oracle can't do -- my vision of such an index file is that it would be
quite different from others in that it would be designed to increase the
speed of traversing through the tree. I'm not convinced that Oracle's
solution to this is optimal in this regard.

2. Moving to table spaces (PostgreSQL version 8 maybe?) rather
than just storing a whole bunch of files in a single directory. Oracle's
implementation is nice because tables, indexes, etc., can span multiple
table spaces, and there are great performance optimization and scalability
advantages that otherwise just aren't possible without them. I read in
another thread (approx. 2 months old) earlier this evening that some folks
would like to see OIDs deprecated, and if this is the case then the sub-
directories under "base/" will obviously need a different naming mechanism,
so instead of re-thinking this perhaps it would be a good opportunity for
the PostgreSQL team to look at the possibility of implementing things
within table spaces.

Anyway one additional thought I had was that it should be possible to
write parsers for text files in PL/PERLu and then plug those in as
views. In this way, files such as /etc/passwd or even /var/log/messages
could be used as if they were tables in the database. Information from
these tables could then be selected into other tables, presented
directly to an application, etc. While it mught be possible to do the
same for MySQL, it would be much more work. Imagine being able to run a
query such as:

select * from logs.iptables
where proto = 'TCP' and td_entered > '2003-01-01' and port (select
port from sysinfo.services where service='ssh');

in order to get every hit against the ssh service on the firewall in the
current year. Of course, I imagine, it would take some time to run ;-)
But for generating reports of system activirt, I would not assume this
to be a problem.

A very interesting idea, but my feeling is that pure PERL is best
suited for dealing with flat text files.

Next question-- any ideas how one could generate something like MySQL's
heap tables (maybe in shared memory?) within PostgreSQL?

I have no idea. Anyone else?

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Hi;

I am trying to determine if there is anything I can do with MySQL that I
can't do with PostgreSQL. HEAP tables are the last one I can't figure out.

HEAP tables are MySQL tables which reside in memory and are visible across
sessions. For obvious reasons, this violates the D in ACID.

In many cases this can be very usefull-- for example if I am building a
system where I want the simplicity of a SQL-like interface with the low
latency of memory-only devices. WOuld I have to run the entire db in a
ramdisk, or can anyone think of a way to do something like this?

It has occurred to me that when table spaces are supported, then this would
be much easier (have a HEAP table space sitting on a ramdisk).

Best WIshes,
Chris Travers

Chris Travers wrote:

HEAP tables are MySQL tables which reside in memory and are visible across
sessions. For obvious reasons, this violates the D in ACID.

Postgresql depend upon two level of buffers. One is it's own buffers and others
are OS buffer cache. So when you say update the table, the relevant portion is
fetched in postgresql buffers and updated. OS does the physical fetch for
postgresql and keeps a copy in it's own buffer cache.

Even if postgresql does not keep them in it's own buffers after a certain time,
most probably OS buffer cache will. So effectively it is cached in RAM. However
other IO operations might need memory and it might get reused under load.

Postgresql does not have a special name for it. It goes for every operation it
makes. So if your table is small enough, it is already cached in the RAM by all
chances.

There should be no performance difference between small tables in postgresql and
heap tables in mysql.

In many cases this can be very usefull-- for example if I am building a
system where I want the simplicity of a SQL-like interface with the low
latency of memory-only devices. WOuld I have to run the entire db in a
ramdisk, or can anyone think of a way to do something like this?

No. Just build a normal table in postgresql. That's it. And the bright side of
it, it satisfy D.

Hope this helps. I would be interested in numbers that say postgresql is slower
than mysql heap tables. (You can force postgresql to load entire table by doin
select * from table. Of course the table is expected to be small enough.. Then
compare the results. It will always be slow first time..)

Shridhar

Hi All,

I've just been reading an article in PHP Architect magazine
(http://www.phparch.com) which is the cover story for October called
"Migrating from MySQL to PostgreSQL". I must say that this is a highly
compelling article, especially for me, and is aimed at programmers that
aren't necessarilly SQL experts or DBAs. For instance, like many PHP
Web developers who use MySQL instead of flat files to store stuff!
Instead of using a DB as a powerful tool. This article presents reasons
as to why a more standards compliant DB is good for programmers, and why
in some cases MySQL can be less of a friend to programmers than perhaps
PostgreSQL.

I honestly believe that if the advocates of PostgreSQL wrote an article
or case study along the lines of this article, it would go a long way to
attracting many more programmers. In my experience all of the articles
and tutorials are written from the perspective of why PG is a better DB
as a DB. Rather than emphasise aspects like "PG is great because you
can move complicated code like this <insert complicated PHP/Perl code
here> ..... normally dealt programatically to your DB which can be both
faster and applied to any other programmers (VB, Java) that you are
sharing the important enterprise data with. I've not seen anything in
articles aimed at PHP/MySQL users saying, "Hey, look at how these
triggers can make your life soooo much easier" or "Hey, look at how
cascading can save you oh so much coding" or "Hey look at all this
programmatical logic that can be put into queries just by writing your
own functions"

I have recently compared the PostgreSQL users to the Debian users (meant
as a complement) by the fact that they are in general highly
knowledgable of thier own subject and peripheral subjects too. They are
passionate and well versed, and happy to nudge people in the direction
of enlightenment without spoonfeeding them. But in the same way, the
advocacy (IMHO) falls into the same boat as Debian. There is a certain
self-assuredness that PostgreSQL is a far superior product and if
someone can't see how obvious that is then maybe PG isn't for them (a
little harsh I know but I'm trying to illustrate a point).

My point is that there are thousands, tens of thousands of programmers
out there, that need to know why and how PG is so great. My eyes have
now been fully opened by this article, and got rid of my nagging feeling
that there was something great about PG that I "Just wasn't grasping,
and couldn't put my finger on". Maybe the advocacy team should be
aiming for all those programmers that desperately need PG, but don't
know it yet, and probably don't have time to garner enough DB experience
to understand why they need it!

Sadly the PHP Architect article is not free, I bought the electronic
magazine for about $2, but believe it's worth every penny and more.

Just my 2 cents.

Apologies if the PG articles ARE out there and please notice that my
comments do not say that they don't exist, but that I have never seen them.

Cheers

Tony.

On 27/11/2003 08:08 Chris Travers wrote:

Hi;

I am trying to determine if there is anything I can do with MySQL that I
can't do with PostgreSQL. HEAP tables are the last one I can't figure
out.

HEAP tables are MySQL tables which reside in memory and are visible
across
sessions. For obvious reasons, this violates the D in ACID.

In many cases this can be very usefull-- for example if I am building a
system where I want the simplicity of a SQL-like interface with the low
latency of memory-only devices. WOuld I have to run the entire db in a
ramdisk, or can anyone think of a way to do something like this?

It has occurred to me that when table spaces are supported, then this
would
be much easier (have a HEAP table space sitting on a ramdisk).

If your table is that small, you could just cache it in your application.

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

HEAP tables in MySQL can be basically used as a network-aware SQL interface
for shared memory segments. I was more looking at the possibility of using
these for coordinating various processes on different systems on a network.

If there is not much performance difference between reasonably small tables
and HEAP tables in MySQL, then I could simply create a simple database and
use that. I was just wondering if there was any other tricks that could be
used, aside from functions written in C ;-)
----- Original Message -----
From: "Paul Thomas" <paul@tmsl.demon.co.uk>
To: "pgsql-general @ postgresql . org" <pgsql-general@postgresql.org>
Sent: Thursday, November 27, 2003 5:02 PM
Subject: Re: PostgreSQL, MySQL, etc., was Re: [GENERAL] PostgreSQL is much
faster than MySQL, only when...

On 27/11/2003 08:08 Chris Travers wrote:

Hi;

I am trying to determine if there is anything I can do with MySQL that I
can't do with PostgreSQL. HEAP tables are the last one I can't figure
out.

HEAP tables are MySQL tables which reside in memory and are visible
across
sessions. For obvious reasons, this violates the D in ACID.

In many cases this can be very usefull-- for example if I am building a
system where I want the simplicity of a SQL-like interface with the low
latency of memory-only devices. WOuld I have to run the entire db in a
ramdisk, or can anyone think of a way to do something like this?

It has occurred to me that when table spaces are supported, then this
would
be much easier (have a HEAP table space sitting on a ramdisk).

If your table is that small, you could just cache it in your application.

--
Paul Thomas

+------------------------------+--------------------------------------------
-+

| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |

+------------------------------+--------------------------------------------
-+

This should be pretty easy. Look into PostgreSQL's rule system. You
should just need a lot of "do instead" functions for the table.

Jon

On Thu, 27 Nov 2003, Chris Travers wrote: