Re: Performance (was: The New Slashdot Setup (includes MySql server))

I know I am going to regret believing that I will actually make any
difference, but I am going to shoot myself anyway.

I am writing this more for the new PostgreSQL members who were not
around last time than in any belief it will make a difference on the
MySQL end.

> Hi,
>
> Mike Mascari:
> >
> > 1. alter_rename_table = no
> >
> > The syntax in PostgreSQL is ALTER TABLE x RENAME TO y;
> >
> They say "alter table crash_q rename crash_q1".
>
> What does the official standard say (assuming any exists) -- is the "to"
> optional or not?

I don't see any RENAME in the SQL92 spec. Now, how hard is it to do a
'man alter_table' and see what it says at the top of the screen?

>
> > 2. atomic_updates = no
> >
> > Huh? Besides being paranoid about fsync()'ing transactions how is
> > a transaction based MVCC not atomic with respect to updates?
> >
> That's a misnomer. They actually mean this:
>
> create table crash_q (a integer not null);
> create unique index crf on crash_q(a);
>
> insert into crash_q values (2);
> insert into crash_q values (3);
> insert into crash_q values (1);
> update crash_q set a=a+1;

Poorly named, huh? How do you think it got such a name? This item was
on the crashme tests before TRANSACTION was on there? Can you explain
how a very exotic issue got on there year(s) before transactions.
Transactions got on there only because I complained.

>
> > 3. automatic_rowid = no
> >
> > The description simply says Automatic rowid. Does this apply to
> > query result sets or to the underlying relation? If the latter,
> > PostgreSQL has, of course, an OID for every tuple in the
> > database.
> >
> I'll have them fix that. MySQL calls them "_rowid" and apparently tests
> only for these.

Well, I don't see _rowid in the SQL spec either, so we are both
non-standard here, though I believe our OID is SQL3.

>
> > 4. binary_items = no
> >
> > Read up on large objects...
> >
> ... with an ... erm ... let's call it "nonstandard" ... interface.

Yes.

>
> > 5. connections = 32
> >
> > This, should, of course be +32, since PostgreSQL can easily
> > handle hundreds of simultaneous connections.
> >
> The testing code (Perl) looks like this, and it bombs after the 32nd
> connection.
>
> for ($i=1; $i < $max_connections ; $i++)
> {
> if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
> { PrintError => 0})))
> {
> print "Last connect error: $DBI::errstr\n" if ($opt_debug);
> last;
> }
> $dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
> print "." if ($opt_debug);
> push(@connect,$dbh);
> }
> print "$i\n";
>
> I do not know where that limit comes from.
> It might be the DBI interface to PostgreSQL, or a runtime limit.
>
> Anyway, $max_connections has the value to 1000.

You have to recompile the backend to increase it. Not on the client
end. See FAQ.

>
> > 6. create_table_select = no
> >
> > Again. PostgreSQL supports CREATE TABLE AS SELECT (i.e. Oracle),
> > and SELECT INTO syntax.
>
> Test code:
> create table crash_q SELECT * from crash_me;
>
> Again, is the "AS" optional or not?

man create_table. That is all it takes. There is not standard for
this. It is from Oracle. Is their AS optional? Does it really matter?

>
> > 7. except = no
> >
> > PostgreSQL has had both INTERSECT and EXCEPT since 6.5.0 (albeit
> > they're slow).
> >
> Looking at the test, we see it doing this:
>
> create table crash_me (a integer not null,b char(10) not null);
> insert into crash_me (a,b) values (1,'a');
> create table crash_me2 (a integer not null,b char(10) not null, c integer);
> insert into crash_me2 (a,b,c) values (1,'b',1);
> select * from crash_me except select * from crash_me2;
>
> For what it's worth, there is at least one database which doesn't
> have this restriction (i.e., that the number of columns must be
> identical) (namely SOLID).
>
> So this test needs to be split into two. I'll do that.

So you test EXCEPT by having a different number of columns. I can see
it now, "Hey we don't have EXCEPT. PostgreSQL does it, but they can't
handle a different number of columns. Let's do only that test so we
look equal."

>
> > I'm starting to get very tired of this. I don't see why
> > PostgreSQL users are obligated to get MySQL tests correct. And
> > I'm only 15% through the list...
> >
> _Somebody_ has to get these things right. I'm not suggesting that it's
> any obligation of yours specifically, but somebody's gotta do it, and
> (IMHO) it can only be done by somebody who already knows _something_
> about the database to be tested.
>
> > Bottom line...either the test writers are ignorant or deceptive.
>
> Or the tests are just badly written. Or they're too old and suffer from
> severe bit rot.
>
>
> For what its worth, I do NOT think the people who wrote these tests
> are either ignorant or deceptive. Most, if not all, of these tests
> are OK when checked against at least one SQLish database.

In looking at each of these items, it is impossible for me to believe
that the tests were not written by either very ignorant people ("I can't
run 'man') or very deceptive people ("Let's make ourselves look good.").

If you view this from outside the MySQL crowd, can you see how we would
feel this way? This is just a small example of the volumes of reasons
we have in believing this.

If you are going to publish things about other databases on your web
site, you had better do a reasonable job to see that is it accurate and
fair. If it can't be done, take it off. Don't leave it up and have it
be wrong, and ignore people in the past who tell you it is wrong.

It never has been fair, and I suspect never will be, because this is
hashed around every year with little change or acknowledgement.

So, yea, we have an attitude. We are usually nice folks, so if the
majority of us have a bad attitude, there must be some basis for that
feeling, and I can tell you, the majority of us do have a bad attitude
on the topic.

I know the MySQL folks don't have a bad attitude about us, and you know,
they don't because we never did anything like this Crashme to them. But
actually, we are tired of being pushed by an ignorant/deceptive crashme
test, and we are starting to push back. But, you can be sure we will
never stoop to the level of the crashme test.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026