interesting PHP/MySQL thread

Joe,

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2

I particularly liked this post:
(http://marc.theaimsgroup.com/?l=php-dev&m=105621207500778&w=2)

Boy, Monty's making friends all over, ain't he?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

Joe,

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2

I particularly liked this post:
(http://marc.theaimsgroup.com/?l=php-dev&m=105621207500778&w=2)

Boy, Monty's making friends all over, ain't he?

[ CC to general.]

[ MySQL changes client library to GPL.]

This is _very_ interesting, and not surprising. MySQL got users to
adopt MySQL, then they change the client license to get users to
purchase commercial, non-GPL licenses.

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Notice the second URL mentions Interbase before PostgreSQL, which I find
curious.

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

I think most people would agree that a large part of MySQL's audience has come from the bundling of MySQL libraries with PHP. Getting PostgreSQL to fill this void would be a very positive development.

If concerns about licensing are a major driver here, I would think that PostgreSQL's simple Berkeley license would compare very favorably to the tangled web of Interbase history of corporate intrigue and community forking.

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: "Joe Conway" <mail@joeconway.com>; "Advocacy (PostgreSQL)" <pgsql-advocacy@postgresql.org>; "PostgreSQL-general" <pgsql-general@postgresql.org>
Sent: Sunday, June 22, 2003 5:59 PM
Subject: Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread

Hi,

3.2.23 was a LONG time ago. One thing I like about mysql is that they are
constantly making major improvements. I have asked this before, where is
Replication with PostgreSQL? If there was a system that could handle more
than one master without hacking, I would seriously look into switching to
PostgreSQL again. Currently mysql can't handle more than one master cleanly.
Lack of built in Replication is the main thing that continues to keep us
from using PostgreSQL. All of the little, "baby can't learn how to program
or write SQL functions" don't do crap for me, but Replication is a large
part of our network structure, we can't do without it and we certainly don't
want to use a third party product. Patching together tools like what happens
with a qmail install is not a system I want to be responsible for. And yet I
like qmail a great deal, and I like what I have seen of PostgreSQL.

Thanks,

Eric

At 10:19 PM 6/22/03 -0400, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&amp;m=105621066832429&amp;w=2

Hoo boy. Did you catch the part about

and the MySQL 3.2.23
library can't connect to MySQL 4.1 servers, rendering it broken.

Backwards compatibility must not be a consideration over there...

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Indeed. What can we do exactly?

regards, tom lane

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

(250) 655 - 9513 (PST Time Zone)

"Inquiry is fatal to certainty." -- Will Durant

Bruce Momjian wrote:

Josh Berkus wrote:

Joe,

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&amp;m=105621066832429&amp;w=2

I particularly liked this post:
(http://marc.theaimsgroup.com/?l=php-dev&amp;m=105621207500778&amp;w=2)

Boy, Monty's making friends all over, ain't he?

[ CC to general.]

[ MySQL changes client library to GPL.]

This is _very_ interesting, and not surprising. MySQL got users to
adopt MySQL, then they change the client license to get users to
purchase commercial, non-GPL licenses.

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Not surprising at all. And I think to make the big touting that MySQL
will continue to support open source and bla, bla, and then putting this
pretty severe change into the better hidden fine print is a good example
how $19.5M affect someones ethics.

To clearify, we need to encourage the PHP developer community to
encourage the PHP user community to switch to PostgreSQL.

What I'm worried about is exactly the people who adopted MySQL already.
The change to another database will be painfull no matter what. How many
of them will be willing to give another open source database a shot?

Notice the second URL mentions Interbase before PostgreSQL, which I find
curious.

That's simply alphabetical, don't try to interpret something into it.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&amp;m=105621066832429&amp;w=2

Hoo boy. Did you catch the part about

and the MySQL 3.2.23
library can't connect to MySQL 4.1 servers, rendering it broken.

Backwards compatibility must not be a consideration over there...

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Indeed. What can we do exactly?

regards, tom lane

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Indeed. What can we do exactly?

I think we need to use this opportunity to convince the PHP folks to level
the playing field, rather than playing favourites with ANY one database...

Chris

To clearify, we need to encourage the PHP developer community to
encourage the PHP user community to switch to PostgreSQL.

What I'm worried about is exactly the people who adopted MySQL already.
The change to another database will be painfull no matter what. How many
of them will be willing to give another open source database a shot?

If you assume that cost is one of the factors in going with an open
source database, what are their choices?

I know it took me a while to convince the CIO on the project I'm working
on that PostgreSQL was an improvement over MySQL. He's slowly coming
around as I start to show him what I am doing with the much richer
PostgreSQL feature set, but the performance of 7.3 compared to MySQL is
likely to remain a bit of a sticking point, because some queries are
taking 2-3 times as long on the same platform with the same data.

If the data entry folks, who are probably about to get a look at a portion
of the application that is still using the MySQL engine, get used to the
search times there, when we switch the whole thing over to PostgreSQL we
may get complaints if searches that used to take 3-4 seconds are now
taking 10-12 seconds. (Have others noticed that 7 seconds seems to be
a threshold point for users reacting to query times?)

MySQL also does case independent text comparisions, and apparently ONLY
case-insensitive comparisons.
--
Mike Nolan

On Sun, 22 Jun 2003 nolan@celery.tssi.com wrote:

MySQL also does case independent text comparisions, and apparently ONLY
case-insensitive comparisons.

Is this a good thing? Doesn't sound like it to me, but figured I'd ask :)

I know it took me a while to convince the CIO on the project I'm working
on that PostgreSQL was an improvement over MySQL. He's slowly coming
around as I start to show him what I am doing with the much richer
PostgreSQL feature set, but the performance of 7.3 compared to MySQL is
likely to remain a bit of a sticking point, because some queries are
taking 2-3 times as long on the same platform with the same data.

If the data entry folks, who are probably about to get a look at a portion
of the application that is still using the MySQL engine, get used to the
search times there, when we switch the whole thing over to PostgreSQL we
may get complaints if searches that used to take 3-4 seconds are now
taking 10-12 seconds. (Have others noticed that 7 seconds seems to be
a threshold point for users reacting to query times?)

Whoa, something's not right. Could you please send along an EXPLAIN
ANALYZE after doing a VACUUM ANALYZE of your query that's taking 3-4x
longer? Something smells very strange here because my experience has
been quite the opposite... I can understand 0.05ms longer per
connection in setup overhead (fork() vs new thread) , but this seems
like way too much... I wonder if you couldn't benefit from the use of
a cursor if you're returning a large dataset. -sc

http://developer.postgresql.org/docs/postgres/sql-declare.html
http://developer.postgresql.org/docs/postgres/sql-fetch.html
http://developer.postgresql.org/docs/postgres/sql-close.html

--
Sean Chittenden

on that PostgreSQL was an improvement over MySQL. He's slowly coming
around as I start to show him what I am doing with the much richer
PostgreSQL feature set, but the performance of 7.3 compared to MySQL is
likely to remain a bit of a sticking point, because some queries are
taking 2-3 times as long on the same platform with the same data.

The only conceivable reason for that is poor query optimisation on the part
of the database and query designers...

If the data entry folks, who are probably about to get a look at a portion
of the application that is still using the MySQL engine, get used to the
search times there, when we switch the whole thing over to PostgreSQL we
may get complaints if searches that used to take 3-4 seconds are now
taking 10-12 seconds. (Have others noticed that 7 seconds seems to be
a threshold point for users reacting to query times?)

Huh? Tsearch in postgresql's contrib dir is as fast as if not faster than
MySQL's FULLTEXT indexes in my experience... Don't tell me you're just
using LIKE comparisions? I have searches using tsearch over 30000 food
brands and decriptions that take about 100ms.

MySQL also does case independent text comparisions, and apparently ONLY
case-insensitive comparisons.

What do you mean 'independent test comparisons'?

Chris

On Sun, Jun 22, 2003 at 10:19:20PM -0400, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Interesting thread (php-dev subj: removing bundled libmysql):
http://marc.theaimsgroup.com/?l=php-dev&amp;m=105621066832429&amp;w=2

Note the comment on
http://marc.theaimsgroup.com/?l=php-dev&amp;m=105624024116515&amp;w=2 :

Georg Richter wrote:

Unbelievable.
Guess the postgres guys are licking their chops over this.

We need to use this opportunity to encourage PHP folks to switch to
PostgreSQL.

Indeed. What can we do exactly?

Probably the Postgres client library (that'd be libpq) can be included
as part of the PHP distribution. With that, according to the thread,
the Postgres support could be built in by default. I can't find the PHP
license on their website though.

Better hurry. Sterling Hughes is proposing to enable SQlite support by
default; that move could be bad for the lobbying of activating Pg
support.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The Postgresql hackers have what I call a "NASA space shot" mentality.
Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)

On Sun, 22 Jun 2003 nolan@celery.tssi.com wrote:

MySQL also does case independent text comparisions, and apparently ONLY
case-insensitive comparisons.

Is this a good thing? Doesn't sound like it to me, but figured I'd ask :)

I think it is a classic case of thinking 'small'. :-)

The CIO on the project I'm working on thinks it is a good thing,
but he's coming from a MySQL environment, which he only learned in the
last year or so and he does not appear to have a lot of familiarity with
larger databases.

Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE,
but I can see how some people might think that 'NOLAN', 'Nolan' and
'nolan' should be considered as the same data.

BTW, I just tested it and MySQL does case folding on values in unique
indexes, too. (Well, at least it is consistent.)
--
Mike Nolan

Whoa, something's not right. Could you please send along an EXPLAIN
ANALYZE after doing a VACUUM ANALYZE of your query that's taking 3-4x
longer?

As luck would have it, I just finished the latest 'emergency' part of
the project, so I may have a day or so to play with this before the
CIO figures out I'm done. :-)

I'm hoping this turns out to be a tuning issue, as I'm still very much
of a rookie at tuning PostgreSQL.

I'll see if I can work something up. Should this go to the general
list or somewhere else?
--
Mike Nolan

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

Better hurry. Sterling Hughes is proposing to enable SQlite support by
default; that move could be bad for the lobbying of activating Pg
support.

SQlite? Sure, give it a try. (I was slightly astonished to compare
these two pages:
http://www.hwaci.com/sw/sqlite/omitted.html
http://www.hwaci.com/sw/sqlite/datatypes.html
At the very least, one would have to say that the author feels free
to define those parts of SQL he doesn't like as "not features". There
sure isn't anything on the former page to suggest that vast parts of
the SQL spec are being ignored per the latter page.)

SQlite is even less competition from our point of view than MySQL is
... if the PHP guys think their users will be satisfied with SQlite,
let them try it for awhile.

I'd be happy if PHP would adopt a database-neutral stance, ie, nothing
in particular bundled into their core distribution. That might not be
compatible with their project goals though. Anyone have a feeling about
how important it is to them to have bundled DB support? Maybe we could
talk them into bundling more than one DB interface --- if they put both
PG and SQlite support into their distro, that'd be fine with me too.

regards, tom lane

On Sun, 22 Jun 2003 23:57:10 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'd be happy if PHP would adopt a database-neutral stance, ie, nothing
in particular bundled into their core distribution. That might not be
compatible with their project goals though. Anyone have a feeling about
how important it is to them to have bundled DB support?

i'm not really a member of the "php community", but i have used it quite a
lot, and the database features are a key component, they're a big part of
why you use php. i'd think that having some db support bundled in their
core is very important to them.

richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security

Whoa, something's not right. Could you please send along an
EXPLAIN ANALYZE after doing a VACUUM ANALYZE of your query that's
taking 3-4x longer?

As luck would have it, I just finished the latest 'emergency' part
of the project, so I may have a day or so to play with this before
the CIO figures out I'm done. :-)

I'm hoping this turns out to be a tuning issue, as I'm still very
much of a rookie at tuning PostgreSQL.

I'll see if I can work something up. Should this go to the general
list or somewhere else?

Definitely performance@. -sc

--
Sean Chittenden

3.2.23 was a LONG time ago. One thing I like about mysql is that they are
constantly making major improvements.

What?? What "major" improvements? They haven't had a major improvement for
years! Their roadmap has had "foreign key support" on it back when I first
started using it 5 years ago!!!

In that same time, PostgreSQL has added:

* write-ahead log
* full subselect support
* almost all sql92 constructs
* schemas
* domains
* constraints
* triggers
* rules
* casts, conversions
* full support for different encodings
* prepared queries
* dependency tracking
* set-returning-functions
* table statistics based query planner
* full transaction support
* unlimited field sizes, all of it can be indexed instead of the first n
bytes!
* thousands of nameless improvements
* information_schema
* hash aggregates
* PLUS we already had all the "new" stuff that MySQL is adding (eg. GIS,
rtree indexes)

And what has MySQL done? Replication and built-in full text indexing?
UNIONs as well - hooray! Big deal.

I have asked this before, where is
Replication with PostgreSQL? If there was a system that could handle more
than one master without hacking, I would seriously look into switching to
PostgreSQL again. Currently mysql can't handle more than one master

cleanly.

Lack of built in Replication is the main thing that continues to keep us
from using PostgreSQL. All of the little, "baby can't learn how to

program

or write SQL functions" don't do crap for me, but Replication is a large
part of our network structure, we can't do without it and we certainly

don't

want to use a third party product. Patching together tools like what

happens

with a qmail install is not a system I want to be responsible for. And yet

I

like qmail a great deal, and I like what I have seen of PostgreSQL.

Replication is a big feature, and it happens to be one that PostgreSQL
doesn't really have. If you NEED it, then PostgreSQL might not be for you.
However, the entire .org domain is run of replicated PostgreSQL servers
using eRserver I think. Why don't you want to use a 3rd party product?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

3.2.23 was a LONG time ago. One thing I like about mysql is that they are
constantly making major improvements.

What?? What "major" improvements? They haven't had a major improvement for
years! Their roadmap has had "foreign key support" on it back when I first
started using it 5 years ago!!!
In that same time, PostgreSQL has added:
[long list]

I think a few of these were there more than five years ago. But yeah,
the notion that PG isn't "constantly making major improvements" is
laughable.

I'd be interested to see an unbiased comparison of how much each project
has gotten done in the last several years. I'm certainly not qualified
to say what MySQL has gotten done ...

regards, tom lane