What can we learn from MySQL?

Bruce Momjian wrote:

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

MySQL was my first introduction to SQL databases (I had dabbled with
Clipper and Foxpro years earlier, but only for a couple of months and
had forgotten most of it by then). So practically all I knew about SQL
and RDBMS I got from the MySQL manual. IIRC, MySQL has a chapter for
beginners, on how to create your first database and tables, how to
insert a record, etc.

I see that the Pg manual already has that. Good.

The problem is that, since MySQL was my only SQL database I knew for a
long time, I didn't know that an RDBMS can be [much] more than what
MySQL was/is. I could only do simple SELECTs (no JOINs, let alone
subselect since MySQL doesn't support it) but found it sufficient, since
I did most of the hard work from Perl/PHP (for example, doing an
adjacency tree query by several SELECTs and combining the results myself
from the client side).

I didn't know squat about stored procedures or triggers or check
constraints. I had no idea what a foreign key is -- and when MySQL
manual says it's not necessary, slow, and evil, I believed it.

I never bothered checking out other databases until I started reading
more about transactions, reliability, Date/Codd, and other more
theoretical stuffs. Only then I started trying out Interbase, Firebird,
SAPDB, DB2, Oracle, and later Pg.

So in my opinion, as long as the general awareness about RDBMS (on what
tasks/responsibilities it should do, what features it generally has to
have, etc) is low, people will be looking at MySQL as "good enough" and
will not be motivated to look around for something better. As a
comparison, I'm always amazed by people who use Windows 95/98/Me. They
find it normal/"good enough" that the system crashes every now and then,
has to be rebooted every few hours (or every time they install
something). They don't know of anything better.

So perhaps the direction of advocacy should be towards increasing that
awareness?

--
dave

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

Questions I have are:

I have already told Bruce at length about the single most common
complaint in the phpPgAdmin lists and in the IRC channel: the inability
to change column types. I think we should listen to the punters on that
one.

Also, how about a new section in the manual: PostgreSQL for MySQL users
and PostgreSQL for Oracle users?

Chris

Bruce Momjian wrote:

Here is a blog about a recent MySQL conference with title, "Why MySQL
Grew So Fast":

http://www.oreillynet.com/pub/wlg/4715

and a a Slashdot discussion about it:

http://developers.slashdot.org/article.pl?sid=04/04/20/2229212&mode=nested&tid=137&tid=185&tid=187&tid=198

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

Questions I have are:

o Are we marketing ourselves properly?
o Are we focused enough on ease-of-use issues?
o How do we position ourselves against a database that some
say is "good enough" (MySQL), and another one that some
say is "too much" (Oracle)
o Are our priorities too technically driven?

Do we care enough about interoperability?

When I ask about non-standard complience of Pg (turning unquoted
identifiers to lowercase instead of uppercase, violating the SQL
standard, and requring an expensive rewrite of clients), and I get the
answer "uppercase is ugly", I think something is wrong.

To be fair, I got a fair amount of legitimate problems with MIGRATING to
standard compliency. I find these issues legitimate, though solveable.
Getting a "we prefer lowercase to the standard", however, means to me
that even if I write a patch to start migration, I'm not likely to get
it in.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/

On Thu, 2004-04-22 at 21:09, Bruce Momjian wrote:

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

One common thing people talk about is ease of use. However that puzzles
me, since it takes all of (about) three commands to install postgresql
and log into the database for the first time. I use debian, so it
amounts to "apt-get install", su to "postgres" and "psql template1".

I suspect some of the difficulties lie in how distributions of linux (a
large part of the pg user base) present postgres.

Postgres is a server system, and the only real interface it provides is
SQL, and "CREATE TABLE" and other simple commands are the same as in
MySQL. How can it be easier to use?

I think debian does a great job, do other distros make it so easy?

Here are some ideas at the distribution level:

o Ask a question during install to create a database other than
template0 and template1.
o If it's a GUI-based distro, install and lauch pgadmin3 and
provide some icons.
o If it's a GUI distro, lauch a browser to a special tutorial
at the distributors website so that users can see instantly
how to start doing things without going through the install
instructions or instructions about how to create a new
database. It should show pgadmin3 and show how to execute
queries (and provide the same simple examples you see in any
SQL tutorial), and then later show how to use psql from the
command line.
o If it's being installed from the command line, maybe have a
final question "Launch PgSQL quickstart?" that would run a
script to provide instructions interactively, like saying
"Now type createdb ... this will make a starting database",
"now type psql ...", "now type CREATE TABLE ...".
o If you look at windows packages, even servers, there's always
an interface to click on and then "browse around". This
interface detects the fact that something is installed and
allows you to administer it somehow.

Those are just ideas. MySQL doesn't do any of that, so I can't really
see how people say it's easier.

One very legitimate concern is the PostgreSQL site's search
functionality. I really like php.net, and I think MySQL tries to make
their website like PHP's. That requires manpower, and we've already
discussed that on this list.

Overall, I think that the PostgreSQL project has done everything very
well. It compiles very nicely and provides a lot of tools. Beyond that,
we just need adoption from application developers, distributions who
spend more time making PostgreSQL easy to use, and ISPs.

I don't know what you mean by "too technically driven". I guess the
release process of postgresql isn't very marketing friendly. Often what
happens with development being as open as it is here, is that people get
excited about a set of new features, and not all of them make it in to
the next release. From the outside it looks like postgres is slow and
not providing many new features. Maybe we should throw old features into
the press release just to remind people that postgres has had the
features for years :) (i.e. "7.5 has native win32 support, and yes, it
still has foreign keys and stored procedures, and yes, they even work in
win32!" might work, assuming of course that win32 is ready for release
at that time).

Regards,
Jeff Davis

I have already told Bruce at length about the single most common
complaint in the phpPgAdmin lists and in the IRC channel: the inability
to change column types. I think we should listen to the punters on that
one.

Maybe a simple and less dangerous option would be to add a column of the
new type, rename the old column and hide it, and select from the old
column and cast it to the new column? Is that a feasible implementation?
If I really need to change a column, that's what I'd do.

It's certainly less dangerous than MySQL. I remember a horror story
watching my column silently cast itself to a bunch of blank values in
MySQL. In postgres you'd just be able to undo it or fix the problem
since the data is still there. I also understand that MySQL has to copy
the entire table or something insane like that, so we could use the
opportunity to brag about how much more efficient we are for large
tables.

Regards,
Jeff Davis

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

Questions I have are:

o Are we focused enough on ease-of-use issues?

There are two issues here : ease-of-use for admin and basic users.

I recognize my focus on the later as someone using pg as a teaching tool.

Having a correct SQL implementation, referential integrity and
transactions is an important issue when explaining DB concepts.
That's why I could not have used mysql.

Having some help/hint/advices/caveat provided for basic users would help.
But some of the change I submitted require a lot of changes, especially in
the parser, hence are rejected.

I also submit patch to try to fix some "surprises" (there is != but not
==, non-user tables are in stat_.._user_tables viewa...) I had while using
pg.

My agenda is quite hard to get thru the hacker/patch lists. Maybe
because the patches I sent are not really good enough, but also because
it is not a real focus of postgres developers.

On for former point, admin ease-of-use, A little story a few month ago.

I succeeded in advising production people here to switch some applications
from a mysql database, which was working perfectly, to a postgres
database. A few weeks later, the performances were desastrous. 30 seconds
to get an answer to a simple select on a 1500 entries tables. After
investigation, the problems were:

- no vacuum, although there were daily "DELETE FROM tables;"
to empty all the data and reload from another source.

- no analyze, because the admin did not know about it.

- very small "shared_buffers" setting (16 the minimum thanks to FreeBSD
default installation...).

With mysql, you don't need to vacuum analyze, and I think the memory
management maybe more or less "automatic".

I think that the default configuration should have some "automagic"
features so that reasonnable values are chosen depending on the available
resources, which would allow basic users not to care about it.

memory_management = auto/manual...

You also need to have a basic standalone binary port to windows. I wish I
could allow simply my students to use pg on their home computers. Well, it
does not work that simply, you need cygwin at the time, and I haven't seen
the windows binary available for download from the pg download page.

o How do we position ourselves against a database that some
say is "good enough" (MySQL), and another one that some
say is "too much" (Oracle)

"free and serious".

o Are our priorities too technically driven?

Not bad if other agendas can also get through.

--
Fabien Coelho - coelho@cri.ensmp.fr

I think debian does a great job, do other distros make it so easy?

On Mac OS X, there's Marc Liyanage's binary package, which is about as
simple as it gets to install. But PostgreSQL also compiles pretty
nicely out of the box.

So I think it is really more a question of being known and known in a
good light.

Also on Mac OS X Server, MySQL is included by default. Has anyone tried
to lobby Apple about including PostgreSQL too?

Cheers.

---------------
Francois

Home page: http://www.monpetitcoin.com/

"If liberty means anything at all, it means the right to tell people
what they do not want to hear." - George Orwell

On Fri, Apr 23, 2004 at 01:05:21PM +0700, David Garamond wrote:

So in my opinion, as long as the general awareness about RDBMS (on what
tasks/responsibilities it should do, what features it generally has to
have, etc) is low, people will be looking at MySQL as "good enough" and
will not be motivated to look around for something better. As a
comparison, I'm always amazed by people who use Windows 95/98/Me. They
find it normal/"good enough" that the system crashes every now and then,
has to be rebooted every few hours (or every time they install
something). They don't know of anything better.

Agree. People don't know that an RDBMS can be more better.

A lot of users think speed is the most important thing. And they check
the performance of SQL server by "time mysql -e "SELECT..." but they
don't know something about concurrency or locking.

BTW, is the current MySQL target (replication, transactions, ..etc)
what typical MySQL users expect? I think they will lost users who love
classic, fast and simple MySQL. The trade with advanced SQL servers is
pretty full. I don't understand why MySQL developers want to leave
their current possition and want to fight with PostgreSQL, Oracle, DB2
.. etc.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

On Fri, 23 Apr 2004, Shachar Shemesh wrote:

When I ask about non-standard complience of Pg (turning unquoted
identifiers to lowercase instead of uppercase, violating the SQL
standard, and requring an expensive rewrite of clients), and I get the
answer "uppercase is ugly", I think something is wrong.

I would love if someone fixed pg so that one can get the standard
behaviour. It would however have to be a setting that can be changed so we
are still backward compatible.

that even if I write a patch to start migration, I'm not likely to get
it in.

Just changing to uppercase would break old code so such a patch should not
just be commited. But would people stop a patch that is backward
compatible (in the worst case a setting during initdb)? I'm not so sure
they will.

--
/Dennis Bj�rklund

Karel Zak wrote:

On Fri, Apr 23, 2004 at 01:05:21PM +0700, David Garamond wrote:

So in my opinion, as long as the general awareness about RDBMS (on what
tasks/responsibilities it should do, what features it generally has to
have, etc) is low, people will be looking at MySQL as "good enough" and
will not be motivated to look around for something better. As a
comparison, I'm always amazed by people who use Windows 95/98/Me. They
find it normal/"good enough" that the system crashes every now and then,
has to be rebooted every few hours (or every time they install
something). They don't know of anything better.

Agree. People don't know that an RDBMS can be more better.

A lot of users think speed is the most important thing. And they check
the performance of SQL server by "time mysql -e "SELECT..." but they
don't know something about concurrency or locking.

Even worse: They benchmark "SELECT 1+1" one million times.
The performance of "SELECT 1+1" has NOTHING to do with the REAL
performance of a database.
Has anybody seen the benchmarks on MySQL??? They have benchmarked
"CREATE TABLE" and so forth. This is the most useless thing I have ever
seen.

It is so annoying _ I had to post it ;).

Regards,

Hans

BTW, is the current MySQL target (replication, transactions, ..etc)
what typical MySQL users expect? I think they will lost users who love
classic, fast and simple MySQL. The trade with advanced SQL servers is
pretty full. I don't understand why MySQL developers want to leave
their current possition and want to fight with PostgreSQL, Oracle, DB2
.. etc.

Karel

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Hi!

Jeff Davis wrote:

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

One common thing people talk about is ease of use. However that puzzles
me, since it takes all of (about) three commands to install postgresql
and log into the database for the first time. I use debian, so it
amounts to "apt-get install", su to "postgres" and "psql template1".

"Ease of use" == "I would like to install it on my precious-s-s win*
without this horrible Cygwin"

No more, no less.

One very legitimate concern is the PostgreSQL site's search
functionality. I really like php.net, and I think MySQL tries to make
their website like PHP's. That requires manpower, and we've already
discussed that on this list.

"discussed" is good, but where's that "manpower"?

Rather than contining with the answers that you and the others have
contributed to the thread I'm going to respond to this first posting.

The first link that you've quoted is a mixture of fact that I can agree
with and an excuse to express an ego.

I was first introduced to pc based databases back around when DBase came
out. DBase II was not that good but DBase-III+ was what set the measure
for everything else that came onto the PC, including oracle. Ironically
the blog that you've listed is a demonstration of the success of the
human desire to justify ones value system by converting others. What
drove the development of sql related technology has less to do with
technology and more to do with psychology and human nature.

In my opinion MySQL became popular because of the internet (no news
there). I still remember the days when a person could get paid $300 for
composing one html page. People get spoilt making this kind of money so
when companies like microsoft started putting out products that
simplified html composing, like frontpage, thus allowing less skilled
children onto the market, the html page composers saw the writing on the
wall and started hunting for the next 'evolution' which was of course
dynamic pages.

Netscape was king in those days and javascript made a great impression
on the community. What a concept to be able to change the way a page
looked and acted. So now the emphasis was instead of making money on a
static page it would be a dynamic one.

The more popular a technology is the higher the elite must raise the bar
to continue making gobs of money. So as more people jumped on the
bandwagon we had to look at other ways of expanding the word 'dynamic'.
Enter databased information.

Meanwhile, as html was getting more complicated we had taken our
classically trained views of programming languages and applied it to
server side includes and to javascript. But javascript is such a bitch
so cgi remained important until larry showed up and changed the paradign
by looking at the person rather than the language (remember larry's a
linguist) and we stared using perl.

So now people started using databased technology on the internet.
Remember, I speak of the grass roots and not that very small minority of
DBA's that had a real understanding of databases. MySQL became popular,
it "bragged" (this is the point folks) that you could attach your
programming language to it and get good results. And for what it's is
worth, this was true. People hate to change and need to justify their
decisions in life with the money they make so programming stayed outside
of the database for a long time giving MySQL time to evolve.

The reason why MySQL remains relevant because it grew up at rate that
the grass roots expectations grew.

PostgreSQL is different, it was never part of this movement because of
its roots. Using this view of history I would argue that Pg is the
newcomer and MySQL is the veteran.

If we want to reach the 'popular' masses then we need to consider the
psychology of the internet ... go after the grass roots by convincing
them that their jobs are on the line if they don't learn proper
relational theory. Show them! Give them examples!

There's three phases to software development:
- programming
- debugging
- documentation

You old timers have done the first two. Now it's time to address the
last one. And if you don't agree with what I say it may not perhaps be
so much because I'm wrong but because you have not invested your
self-image into that activity. Success is about figuring out what
everybody wants before they do. And the measure of success is not
technology its money.

Bruce Momjian wrote:

Dennis Bjorklund <db@zigo.dhs.org> writes:

On Fri, 23 Apr 2004, Shachar Shemesh wrote:

When I ask about non-standard complience of Pg (turning unquoted
identifiers to lowercase instead of uppercase, violating the SQL
standard, and requring an expensive rewrite of clients), and I get the
answer "uppercase is ugly", I think something is wrong.

I would love if someone fixed pg so that one can get the standard
behaviour. It would however have to be a setting that can be changed so we
are still backward compatible.

Yes. There have been repeated discussions about how to do this, but
no one's come up with a solution that seems workable. See the archives
if you care.

For the foreseeable future, backwards compatibility is going to trump
standards compliance on this point. That doesn't mean we don't care
about compliance; it does mean that it is not the *only* goal.

I find it a bit odd to be debating this point in this thread, seeing
that one of the big lessons I draw from MySQL is "standards compliance
does not matter"...

regards, tom lane

On Apr 23, 2004, at 8:35 AM, Christopher Kings-Lynne wrote:

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.
Questions I have are:

I have already told Bruce at length about the single most common
complaint in the phpPgAdmin lists and in the IRC channel: the
inability to change column types. I think we should listen to the
punters on that one.

Also, how about a new section in the manual: PostgreSQL for MySQL
users and PostgreSQL for Oracle users?

Hello Bruce, Chris and everyone,
So far I have offered free PHP5/ PostgreSQL hosting to around 800
developers that signed up on dotgeek.org
I gathered a number of feedback.

Overall many PHP developers are extremely impressed by PostgreSQL but
they never had the chance/found a reason to try it.

The issues are related mainly to the syntax. Here MySQL, by using non
standards systems, is making the move not that easy to many developers.

Marketing is an important point, so is being able to let the highest
number of people to try PostgreSQL and see the difference.
Another problem is, as far as I can say, their easier to search and
more user friendly manual. I know that Alexey is working on that so I
will think about a way
to contribute directly. Users (and monitored) comments are a must IMHO.

Cheers
David Costa

There are two issues here : ease-of-use for admin and basic users.

On for former point, admin ease-of-use, A little story a few month ago.

I succeeded in advising production people here to switch some applications
from a mysql database, which was working perfectly, to a postgres
database. A few weeks later, the performances were desastrous. 30 seconds
to get an answer to a simple select on a 1500 entries tables. After
investigation, the problems were:

- no vacuum, although there were daily "DELETE FROM tables;"
to empty all the data and reload from another source.

- no analyze, because the admin did not know about it.

My goal is to have pg_autovacuum integrated into the backend for 7.5. I
don't know if it will default to being turned on or off, I'm sure that
will be a discussion, but if it is defaulted to on, then this whole
problem of having to train newbies about vacuum should just go away.

Matthew

Christopher Kings-Lynne wrote:

My question is, "What can we learn from MySQL?" I don't know there is
anything, but I think it makes sense to ask the question.

Questions I have are:

I have already told Bruce at length about the single most common
complaint in the phpPgAdmin lists and in the IRC channel: the inability
to change column types. I think we should listen to the punters on that
one.

Yea, I will push that for 7.5.

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

My goal is to have pg_autovacuum integrated into the backend for 7.5.

I know about that, and that would be a good thing.

I hope so!

I don't know if it will default to being turned on or off, I'm sure that
will be a discussion, but if it is defaulted to on, then this whole
problem of having to train newbies about vacuum should just go away.

Yes. I really thing that it should be on by default, because those who
will need it more than others are those who will not know about tuning
configuration parameters. As I understand the requirements from
pg_autovacuum, it means that some statistics will have to be on by default
as well.

I think it's premature to have this conversation. I need to get something
done / working before we dicuss optimal configuration. That said, I also
agree that if it's good enough, it should be on by default.

Good luck;-)

Thanks, I'll need it....

Matthew

ps: I'm hoping to have time to work on this starting in May. I haven't
really done any development on pg_autovacuum beyond bug fixing what is
already in CVS, so.... If someone out there wants to work on it, don't
wait for me, I won't be offended :-) I just want to see it up and
running.

Am Freitag, 23. April 2004 06:09 schrieb Bruce Momjian:

o Are we marketing ourselves properly?
o Are we focused enough on ease-of-use issues?
o How do we position ourselves against a database that some
say is "good enough" (MySQL), and another one that some
say is "too much" (Oracle)
o Are our priorities too technically driven?

Success is not measured by absolute number of installations. You can measure
success by having enough users so that the project can continue, enough users
so you can make a living, more satisfied users than unsatisfied ones, more
heavy-duty installations than personal database-driven websites, and by
having a product that you feel good about. The only way to position
ourselves is as the relational database with the best price/performance
ration (price = TOC, performance = features + speed). And the only way to
achieve any of these goals is by focussing on technology and ease of use.
For the crowd out there, PostgreSQL is an exciting and growing topic. That's
more important than the installation count.

Dear Matthew,

My goal is to have pg_autovacuum integrated into the backend for 7.5.

I know about that, and that would be a good thing.

I don't know if it will default to being turned on or off, I'm sure that
will be a discussion, but if it is defaulted to on, then this whole
problem of having to train newbies about vacuum should just go away.

Yes. I really thing that it should be on by default, because those who
will need it more than others are those who will not know about tuning
configuration parameters. As I understand the requirements from
pg_autovacuum, it means that some statistics will have to be on by default
as well.

Good luck;-)

--
Fabien Coelho - coelho@cri.ensmp.fr