Postgres performance comments from a MySQL user

On Wed, 11 Jun 2003, Jay O'Connor wrote:

Some backs-story. I'm in the process of converting our internal file based
data storage to an RDBMS. After looking a bit at PostgreSQL and MySQL, I
chose Postgresql. My boss has heard of MySQL and has not heard of
PostgreSQL and every now and then ahe make allusions that we shuold be
using MySQL.

One comment he got from the architect of another web site is as follows

If we were to start again
from scratch now, I'd still use InnoDB over postgres unless the
performance picked up with postgres recently.

Keep in mind our application is very write-heavy so your numbers may
be different. Does postgres still keep the old row versions in the
primary-key B-Tree? If it does I doubt performance improved much for
write-heavy apps, that was a very poor design decision by them. InnoDB
takes the Oracle route of moving old row versions to a seperate
on-disk data structure.

Does what he say make sense? If so, has the situation changed? BNasically,
I need something intelligent to say to my boss to either counter or
mitigate his perception.

Well, one big thing for update (or delete/insert) heavy apps is to make
sure to set the free space map to a reasonable size and vacuum frequently.
If he's only tried older PostgreSQL servers, he may not realize that
vacuums can be run concurrently with queries. There were still index
related bloat problems, but IIRC those should be going away in 7.4.

"Jay O'Connor" <joconnor@cybermesa.com> writes:

One comment he got from the architect of another web site is as follows

As best I can tell, this comment is based on ancient information.
Postgres has gotten very substantially faster over the years ... and
it also helps a lot to know something about how to tune it (the
out-of-the-box settings are excessively conservative). I would not
put a lot of stock in hearsay evaluations of performance, especially
not from someone who hasn't tested recent PG releases.

regards, tom lane

We are currently in the middle of a process of moving from SQL Server 7
to PostgreSQL. We are running PostgreSQL 7.3.2 *untuned* on a 384M
single CPU machine and it beats a 4 CPU server with 2GB of memory
running SQL Server 7. This application is using stored procedures
returning result sets quite heavily and I was extremely and positively
surprised from PostgreSQL's performance.

Bottom line is that you have to try it yourself and see how it performs
in your environment and in your application.

Avi

On Wednesday, Jun 11, 2003, at 11:49 America/Chicago, Tom Lane wrote:

Could you clarify are you moving from MySQL or Microsoft SQL 7.0?

-----Original Message-----
From: Avi Schwartz [mailto:avi@CFFtechnologies.com]
Sent: Wednesday, June 11, 2003 7:21 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres performance comments from a MySQL user

We are currently in the middle of a process of moving from SQL Server 7
to PostgreSQL. We are running PostgreSQL 7.3.2 *untuned* on a 384M
single CPU machine and it beats a 4 CPU server with 2GB of memory
running SQL Server 7. This application is using stored procedures
returning result sets quite heavily and I was extremely and positively
surprised from PostgreSQL's performance.

Bottom line is that you have to try it yourself and see how it performs
in your environment and in your application.

Avi

On Wednesday, Jun 11, 2003, at 11:49 America/Chicago, Tom Lane wrote:

"Jay O'Connor" <joconnor@cybermesa.com> writes:

One comment he got from the architect of another web site is as
follows

As best I can tell, this comment is based on ancient information.
Postgres has gotten very substantially faster over the years ... and
it also helps a lot to know something about how to tune it (the
out-of-the-box settings are excessively conservative). I would not
put a lot of stock in hearsay evaluations of performance, especially
not from someone who hasn't tested recent PG releases.

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

From Microsoft SQL Server 7. We looked briefly at MySQL but realized
that the features we need will not be out until at least version 5
while PostgreSQL has them now.

Avi

On Wednesday, Jun 11, 2003, at 21:34 America/Chicago, Maksim Likharev
wrote:

My I ask on what operations PG better than MSSQL?

cause out of my observations only spatial data types,
could influence a choice between MSSQL and PG.

Thank you.

-----Original Message-----
From: Avi Schwartz [mailto:avi@CFFtechnologies.com]
Sent: Wednesday, June 11, 2003 8:12 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres performance comments from a MySQL user

From Microsoft SQL Server 7. We looked briefly at MySQL but realized
that the features we need will not be out until at least version 5
while PostgreSQL has them now.

Avi

On Wednesday, Jun 11, 2003, at 21:34 America/Chicago, Maksim Likharev
wrote:

Could you clarify are you moving from MySQL or Microsoft SQL 7.0?

-----Original Message-----
From: Avi Schwartz
Sent: Wednesday, June 11, 2003 7:21 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres performance comments from a MySQL user

We are currently in the middle of a process of moving from SQL Server

7

to PostgreSQL.

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

The biggest improvement we noticed was in two areas. We have some very
complex selects that join 7-8 tables which perform 30-40% faster under
PGSQL. The second thing that performs better are the above mentioned
result set returning stored procedures.

BTW, the reason we started looking at PSQL is not due to performance,
this just came as a pleasant surprise. The real reason is in the fact
the we are 90% linux in our server environment and the only Windows
component is SQL Server 7. SS7 does not play nicely with Linux and
lacks any tools to use from the Linux environment. Windows itself is
hard to manage remotely and since the production environment is
co-located it became a real issue.

Avi

On Wednesday, Jun 11, 2003, at 22:24 America/Chicago, Maksim Likharev
wrote:

How about adding the mesage about tuning to the:

1/ 'don't kill the postmaster message at the bottom of emails'
2/ The install messages.
3/ The build messages.
4/ The login screen for the superuser of the databse?

Justin Clift wrote:

Hi Dave,

Are we able to get more messages added to the list of mailing list end tags, so we
can mention something about server tuning or such?

The rest of these things will probably have to be done in the PG source code itself.

:-)

Regards and best wishes,

Justin Clift

Dennis Gearon wrote:

How about adding the mesage about tuning to the:

1/ 'don't kill the postmaster message at the bottom of emails'
2/ The install messages.
3/ The build messages.
4/ The login screen for the superuser of the databse?

Justin Clift wrote:

Joseph Shraibman wrote:

Justin Clift wrote:

The _very first_ thing to do with any PostgreSQL installation is bump
up the memory settings (at least "sort_mem" and "shared_buffers") in
the postgresql.conf and restart it.

Tell him to test it with decent settings (try about 4000 for each as
an initial start),

Perhaps that should be in the message that 'make install' echoes and in
comments in the conf file itself?

Probably it's a good idea to have some mention of this, as even though we should alter the source to higher defaults for our next release, there are potentially
people that would read a message like this and go "wow, didn't know that", then tune their existing installations as well.

With Solaris, 4000 for those memory settings is a good place to start and it's generally fine to just leave them at that unless there's a definite reason for
performance tuning. No idea with other OS's.

Anyone feel like submitting a patch to alter the default settings to a higher mark? Don't think it's been done yet, and it'd be a shame to forget it before
feature freeze time of the next release.

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

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

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

I am _definitely_ not trying to sell MSSQL Server, but the version of TDS (Tabular Data Stream) that MSSQL Server users has been pretty thoroughly reverse engineered by the FreeTDS (http://www.freetds.org) group. I use it to access Sybase servers in production but have used it against MSSQL Server 7 with good results.

Ooohh. ... I just had a fantasy about a migration tool that uses FreeTDS to bring over tables and data...

Ian

Avi Schwartz <avi@CFFtechnologies.com> 06/11/03 08:38PM >>>

The biggest improvement we noticed was in two areas. We have some very
complex selects that join 7-8 tables which perform 30-40% faster under
PGSQL. The second thing that performs better are the above mentioned
result set returning stored procedures.

BTW, the reason we started looking at PSQL is not due to performance,
this just came as a pleasant surprise. The real reason is in the fact
the we are 90% linux in our server environment and the only Windows
component is SQL Server 7. SS7 does not play nicely with Linux and
lacks any tools to use from the Linux environment. Windows itself is
hard to manage remotely and since the production environment is
co-located it became a real issue.

Avi

On Wednesday, Jun 11, 2003, at 22:24 America/Chicago, Maksim Likharev
wrote:

My I ask on what operations PG better than MSSQL?

cause out of my observations only spatial data types,
could influence a choice between MSSQL and PG.

Thank you.

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

I think that feature, plus filling in some of the holes in the object model, like more complete inheritance, would make Postgres VERY much more useful, not that it isn't today. It just would have awider audience.

Sven Koehler wrote:

OK, so how do we handle things like converting a varchar to a timestamp
with time zone? What if one of the rows has invalid date syntax? Do we
convert the column anyway, or throw the whole change out with an error?

being a developer instead of DBA makes me think little about the danger
of losing data when you change column type. But, I think you're right,
very BAD things could happen with your data if the database silently
convert / truncate your data when you change the column type.
As far as I remember when working with Oracle, it allows you to change
the data as long as you don't lose or corrupt your data. So, for
example, changing varchar(20) to varchar(40) should be ok, but the
reverse might not be ok. It'd be nice if it allows you to change the
type from varchar(40) to varchar(20) if you don't have any data that is
larger than varchar(20). I don't know how much complexity that will add,
though. well that's just some idea from me.

- reynard

On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote:

<snip>

It'd be nice if it allows you to change the type from varchar(40)
to varchar(20) if you don't have any data that is larger than varchar(20).

Yeah, and then an application comes in and wants to write more than
20 chars ...

well, i dont like such size limitations at all - i'm using "text" instead.
what about efficiency ? does it bring _anything_ to limit the size
of varchar fields ?

I don't know how much complexity that will add, though. well that's
just some idea from me.

hmm, i dont think, its really needed. if you really want to do that,
you should also think _very carefully_ 'bout what you're doing.
and so you can type the 3 more statements to create a new table,
copy the data, drop the old table and rename the new one.

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/

On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote:

As far as I remember when working with Oracle, it allows you to change
the data as long as you don't lose or corrupt your data. So, for
example, changing varchar(20) to varchar(40) should be ok, but the
reverse might not be ok. It'd be nice if it allows you to change the
type from varchar(40) to varchar(20) if you don't have any data that is
larger than varchar(20). I don't know how much complexity that will add,
though. well that's just some idea from me.

It's not _that_ hard IMHO. It's just that no one has ever bothered to
code it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El dia que dejes de cambiar dejaras de vivir"

It'd be nice if it allows you to change the type from varchar(40)
to varchar(20) if you don't have any data that is larger than varchar(20).

Yeah, and then an application comes in and wants to write more than
20 chars ...

why should i define a column as varchar(20) and should than want to
write more data than that?
i'd only define it as varchar(20) if i'm sure that 20 chars are enough.

well, i dont like such size limitations at all - i'm using "text" instead.
what about efficiency ? does it bring _anything_ to limit the size
of varchar fields ?

well - than use text instead of varchar.

On Sat, Jun 14, 2003 at 10:21:16PM +0200, Sven K?hler wrote:

<snip>

Yeah, and then an application comes in and wants to write more than
20 chars ...

why should i define a column as varchar(20) and should than want to
write more data than that?
i'd only define it as varchar(20) if i'm sure that 20 chars are enough.

Perhaps there's still some code which still expects longer fields ?
especially in larger applications w/ many developers/admis this can
be really dangerous.

well, if you really want to do this, an perl script which creates
a new table and copies the data could also suit your needs.

we shouldnt make the postmaster codebase too big. each single line
can contain many errors.

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/

Hi,

Reynard Hilman wrote:

OK, so how do we handle things like converting a varchar to a
timestamp with time zone? What if one of the rows has invalid date
syntax? Do we convert the column anyway, or throw the whole change
out with an error?

being a developer instead of DBA makes me think little about the danger
of losing data when you change column type. But, I think you're right,
very BAD things could happen with your data if the database silently
convert / truncate your data when you change the column type.
As far as I remember when working with Oracle, it allows you to change
the data as long as you don't lose or corrupt your data. So, for
example, changing varchar(20) to varchar(40) should be ok, but the
reverse might not be ok. It'd be nice if it allows you to change the
type from varchar(40) to varchar(20) if you don't have any data that is
larger than varchar(20). I don't know how much complexity that will add,
though. well that's just some idea from me.

This of course would be convenient, but otoh it would add a great value
of complexity to the backend for a rarely used feature. At least it
should be rarely used :)
For the rare cases where someone has to change column type, maybe its
more easy if (s)he does this by hand - thus not complaining to the
overloaded core-developers about data loss or unexpected results.
The only thing which is a bit complicated is the disabling/enabling
trigger sequence which can be borrowed from pg_dump output.
The remaining part is possible to put all in one transaction;
or at least the delete rows/copy back part:
create table ... as select .... from original ...;
delete from original;
alter table original create column (with new type)
alter table remove old column
insert into original select * from temptable

Regards
Tino

why should i define a column as varchar(20) and should than want to
write more data than that?
i'd only define it as varchar(20) if i'm sure that 20 chars are enough.

Perhaps there's still some code which still expects longer fields ?
especially in larger applications w/ many developers/admis this can
be really dangerous.

well - you assume that i don't know the needs of my app.
i know what i do, so i know why i change a column to varchar(20).