MySQL's "crashme" (was Re: Performance)

"Matthias Urlichs" <smurf(at)noris(dot)net> writes:
>> Hmm. And then who's job is it to take someone else's work and make it
>> accurate? If the shoe were on the other foot: if I generated a
>> benchmark suite and features list, and it contained major and numerous
>> inaccuracies, who would you expect to be responsible (or at least feel
>> responsible) for correcting/updating/improving it? 'Twould be me imho.
>>
> Umm, there's still a difference between saying (a) "it's broken, fix
> it", (b) "here's my analysis as to what exactly is broken, can you fix
> it", and (c) "here's a patch that fixes it".

Good luck. Close analysis of the crashme test leaves an extremely bad
taste in the mouth: there are just too many cases where it's clearly
designed as a pro-MySQL advertising tool and not an honest attempt to
describe reality. Shall we consider details?

> Attached is the current crashme output. "crash_me_safe" is off only
> because of the fact that some tests go beyond available memory.
> There's no sense in testing how far you can push a "SELECT a from b where
> c = 'xxx(several megabytes worth of Xes)'" query when the size fo a TEXT
> field is limited to 32k.

I would not like to see us labeled "crashme unsafe" merely because
someone is too impatient to let the test run to conclusion. But there's
a more interesting problem here: using stock crashme and Postgres 7.0,
on my system it's crashme that crashes and not Postgres! The crashme
Perl script is a huge memory hog and runs into the kernel's process-size
limit long before the connected backend does. To get it to run to
completion, I have to reduce the thing's limit on the longest query it
will try:

*** crash-me~ Sat May 20 12:28:11 2000
--- crash-me Sat May 20 13:21:11 2000
***************
*** 104,110 ****
#

$max_connections="+1000"; # Number of simultaneous connections
! $max_buffer_size="+16000000"; # size of communication buffer.
$max_string_size="+8000000"; # Enough for this test
$max_name_length="+512"; # Actually 256, but ...
$max_keys="+64"; # Probably too big.
--- 104,110 ----
#

$max_connections="+1000"; # Number of simultaneous connections
! $max_buffer_size="+1000000"; # size of communication buffer.
$max_string_size="+8000000"; # Enough for this test
$max_name_length="+512"; # Actually 256, but ...
$max_keys="+64"; # Probably too big.

A few months ago I was able to use max_buffer_size = +2000000, but
crashme 1.43 seems to be an even worse memory hog than its predecessors.
At this setting, the Perl process tops out at about 114Mb while the
connected backend grows to 66Mb. (I run with a process limit of 128Mb.)
To be fair, this could be Perl's fault more than crashme's. I'm using
Perl 5.005_03 ... anyone know if more recent versions use less memory?

Now, on to some specific complaints:

> alter_drop_col=no # Alter table drop column

While our ALTER TABLE support is certainly pretty weak, it should be
noted that this test will continue to fail even when we have ALTER TABLE
DROP COLUMN, because crashme is testing for a non-SQL-compliant syntax.

> alter_rename_table=no # Alter table rename table

We have ALTER TABLE RENAME ... but not under the syntax crashme is
testing. Since SQL92 doesn't specify a syntax for RENAME, there's no
absolute authority for this --- but a quick check of the comparative
crashme results at http://www.mysql.com/crash-me-choose.htmy shows that
*none* of the major commercial DBMSs "pass" this test. Rather curious
that crashme uses a MySQL-only syntax for this test, no?

> atomic_updates=no # atomic updates

What's actually being tested here is whether the DBMS will let you do
"update crash_q set a=a+1" in a table with a unique index on "a" and
consecutive pre-existing values. In other words, is the uniqueness
constraint checked on a per-tuple-update basis, or deferred to end of
transaction? It's fair to blame Postgres for not supporting a deferred
uniqueness check, but this test is extremely misleadingly labeled.
A person who hadn't examined the guts of crashme would probably think
it tests whether concurrent transactions see each others' results
atomically.

> automatic_rowid=no # Automatic rowid

Test is actually looking for a system column named "_rowid". Our OIDs
serve the same purpose, and I believe there are equivalent features in
many other DBMSes. Again, MySQL is the only "passer" of this test,
which says more about their level of standardization than other
people's.

> binary_items=no # binary items (0x41)

We have binary literals (per the test name) and hex literals (what
it actually appears to be testing). Unfortunately for us, ours are
SQL92-compliant syntax, and what crashme is looking for isn't.

> comment_#=no # # as comment
> comment_--=yes # -- as comment
> comment_/**/=yes # /* */ as comment
> comment_//=no # // as comment

It'd be helpful to the reader if they indicated which two of these
conventions are SQL-compliant ... of course, that might expose the
fact that MySQL isn't ...

> connections=32 # Simultaneous connections

Should probably be noted that this is just the default limit (chosen to
avoid creating problems on small systems) and can easily be raised at
postmaster start time.

> crash_me_safe=no # crash me safe

I get "yes", and I'd *really* appreciate it if you not submit this
misleading statement.

> create_table_select=no # create table from select

This is looking for "create table crash_q SELECT * from crash_me",
which again appears to be a MySQL-only syntax. We have the same feature
but we want "AS" in front of the "SELECT". Dunno how other DBMSs do it.

> date_zero=no # Supports 0000-00-00 dates

Note this is not checking to see if the date format yyyy-mm-dd is
accepted, it's checking to see if the specific value '0000-00-00'
is accepted. Haven't these people heard of NULL? Another test that
only MySQL "passes".

> except=no # except

This test is checking:
create table crash_me (a integer not null,b char(10) not null);
create table crash_me2 (a integer not null,b char(10) not null, c integer);
select * from crash_me except select * from crash_me2;
Postgres rejects it with
ERROR: Each UNION | EXCEPT | INTERSECT query must have the same number of columns.
Unsurprisingly, hardly anyone else accepts it either.

> except_all=no # except all

While we do not have "except all", when we do this test will still fail
for the same reason as above.

> func_extra_not=no # Function NOT in SELECT

What they are looking for here is "SELECT NOT 0", which Postgres rejects
as a type violation. SQL-compliant "NOT FALSE" would work.

BTW, while I haven't got the patience to go through the function list in
detail, quite a few functions that we actually have are shown as "not
there" because of type resolution issues. For example they test exp()
with "select exp(1)" which fails because of ambiguity about whether
exp(float8) or exp(numeric) is wanted. This will get cleaned up soon,
but it's not really a big problem in practice...

> having_with_alias=no # Having on alias

Again, how curious that MySQL is the only DBMS shown as passing this
test. Couldn't be because it violates SQL92, could it?

> insert_select=no # insert INTO ... SELECT ...

We would pass this test if the crashme script weren't buggy: it fails
to clean up after a prior test that creates a crash_q table with
different column names. The prior test is testing "drop table if
exists", which means the only way to be shown as having this
SQL-standard feature is to implement the not-standard "if exists".

> intersect=no # intersect
> intersect_all=no # intersect all

See above comments for EXCEPT.

> logical_value=1 # Value of logical operation (1=1)

A rather odd result, considering that what Postgres actually returns for
"SELECT (1=1)" is 't'. But showing the correct answer isn't one of
crashme's highest priorities...

> minus_neg=no # Calculate 1--1

Another case where "passing" the test means accepting MySQL's version of
reality instead of SQL92's. All the SQL-compliant DBMSs think -- is a
comment introducer, so "select a--1 from crash_me" produces an error ...
but not in MySQL ...

> quote_ident_with_"=no # " as identifier quote (ANSI SQL)
> quote_ident_with_[=no # [] as identifier quote
> quote_ident_with_`=no # ` as identifier quote

Here at least they admit which variant is ANSI ;-). Postgres doesn't
pass because we think 'select "A" from crash_me' should look for a
column named upper-case-A, but the column is actually named
lower-case-a. We are not conforming to the letter of the SQL standard
here --- SQL says an unquoted name should be mapped to all upper case,
not all lower case as we do it, which is how the column got to be named
that way. We're closer than MySQL though...

> select_string_size=+16208 # constant string size in SELECT

I got 1048567 here, roughly corresponding to where I set max_buffer_size.
Not sure why you get a smaller answer.

> select_table_update=no # Update with sub select

We certainly have update with sub select. What they're looking for is
the non-SQL-compliant syntax
update crash_q set crash_q.b=
(select b from crash_me where crash_q.a = crash_me.a);
It works in Postgres if you remove the illegal table specification:
update crash_q set b=
(select b from crash_me where crash_q.a = crash_me.a);

> type_sql_bit=yes # Type bit
> type_sql_bit(1_arg)=yes # Type bit(1 arg)
> type_sql_bit_varying(1_arg)=yes # Type bit varying(1 arg)

It should probably be noted that we only have syntax-level support for
BIT types in 7.0; they don't actually work. The test is not deep enough
to notice that, however.

General comments:

It appears that they've cleaned up their act a little bit. The last
time I examined crashme in any detail, there was an even longer list
of tests that checked for standard features but were careful to use a
nonstandard variant so they could claim that other people failed to
have the feature at all.

More generally, it's difficult to take seriously a test method and
presentation method that puts more weight on how many variant spellings
of "log()" you accept than on whether you have subselects. (I count
five entries versus two.)

One could also complain about the very large number of tests that are
checking features that are non-SQL if not downright SQL-contradictory,
but are listed simply as bullet points with no pro or con. A naive
reader would think that green stars are always good; they are not,
but how are you to tell without a copy of the SQL spec in hand?

Finally, the test coverage seems to have been designed with an eye
towards giving MySQL as many green stars as possible, not towards
exercising the most important features of SQL. It would be interesting
to see considerably more coverage of subselects, for example, and I
expect that'd turn up shortcomings in a number of products including
Postgres. But it won't happen as long as crashme is a tool of, by, and
for MySQL partisans (at least not till MySQL has subselects, whereupon
the test coverage will no doubt change).

Just FYI, I attach a diff between what you presented and what I get from
running the current crashme. I don't understand exactly what's causing
the small differences in the values of some of the size limits.
Perhaps it is a side effect of using a different max_buffer_size, but
it seems really weird.

regards, tom lane