Integrity on large sites

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

Some large sites don't even use data types!

http://www.thestar.com/News/article/189175

"in some cases the field for the social insurance number was instead
filled in with a birth date."

(search the archives for "OT: Canadian Tax Database")

Cheers,
Stuart.

Naz Gassiep wrote:

I'm working in a project at the moment that is using MySQL, and people
keep making assertions like this one:

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

This sounds to me like MySQLish. A large DB working with no RI would
give me nightmares. Is it really true that large sites turn RI off to
improve performance,

i know from having worked in the shop that handles it that the databases
used in processing of NYS Personal Income Tax (Informix) most assuredly
use referential integrity.

anything else would be suicide.

certain shops do turn it off for large databases. that doesn't make it a
good idea,
or the industry norm.

richard

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/22/07 21:12, Naz Gassiep wrote:

I'm working in a project at the moment that is using MySQL, and people
keep making assertions like this one:

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

This sounds to me like MySQLish. A large DB working with no RI would
give me nightmares. Is it really true that large sites turn RI off to
improve performance, or is that just a MySQL thing where it gets turned
off just because MySQL allows you to turn it off and improve
performance? Can you even turn RI off in PostgreSQL? Does Oracle, DB2 or
MSSQL allow you to turn it off? Am I just being naive in thinking that
everyone runs their DBs with RI in production?

Allow you to turn it off???

RI as in foreign keys or RI as in primary keys?

FKs are not implemented on our big transactional systems that use
Rdb/VMS. Originally this was because the extra load would slow down
a system that needed every ounce of speed back on late 1990s technology.

Now we have (some) faster hardware, but even higher posting volumes.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGU6gRS9HxQb37XmcRAjSnAJwN8XhCxsHyeJHqxzi/k0Dj6O8fVACdGxrd
R1hfrTh9ifDivr51AGt1NNQ=
=CSLd
-----END PGP SIGNATURE-----

Not using foreign keys makes sense for some applications. WORM
applications where you know you are loading accurate data, for
example. Or times when it doesn't matter if an application bug
corrupts your data.

But if you care about your data and if you can't trust your client to
edit it correctly, you'd better have referential integrity. Size is
irrelevant to that equation.

On May 22, 2007, at 7:12 PM, Naz Gassiep wrote:

----- Original Message Follows -----
From: "Stuart Cooper" <stuart.cooper@gmail.com>

"*Really* big sites don't ever have referential
integrity. Or if the few spots they do (like with
financial transactions) it's implemented on the

application level (via, say, optimistic locking), never the
database level."

Some large sites don't even use data types!

http://www.thestar.com/News/article/189175

"in some cases the field for the social insurance number
was instead filled in with a birth date."

But the fact that they don't use data types, or that some
big sites supposedly may not use referential integrity does
not provide justification that doing without is a Good
Thing. The Canadian Tax article, to any competent systems
admin, would provide incredibly strong justification FOR
using typed and validated data and referential integrity.
Anyone who has to be concerned with the integrity and
validity of their data, which should be the case everywhere
-- otherwise why bother collecting it -- has to enforce
those aspects, and RDBMS are built to do that. "Turning it
off" doesn't seem like a good way to address performance
issues. Buy bigger/better hardware and adjust configuration
settings. Data integrity has to be the first and fundamental
concern. Performance is irrelevant if you can't trust the
data -- would having answers faster be of any use if the
answers were not reliable?

"*Really* big sites don't ever have referential
integrity. Or if the few spots they do (like with
financial transactions) it's implemented on the

application level (via, say, optimistic locking), never the
database level."

Sure, but in the forum benchmark I just did, when using MyISAM, with no
reference integrity checks, at the end of the benchmark, there is an
impressive number of records with broken foreign key relations... when the
user kills his HTTP connection or reloads at the wrong moment, and the
script is interrupted, or killed by an exception or whatever, boom.

PFC wrote:

"*Really* big sites don't ever have referential
integrity. Or if the few spots they do (like with
financial transactions) it's implemented on the

application level (via, say, optimistic locking), never the
database level."

Sure, but in the forum benchmark I just did, when using MyISAM, with
no reference integrity checks, at the end of the benchmark, there is an
impressive number of records with broken foreign key relations... when
the user kills his HTTP connection or reloads at the wrong moment, and
the script is interrupted, or killed by an exception or whatever, boom.

One assumes you mean implicit foreign key relations, since MyISAM doesn't
enforce them (hence the reason they're "broken", potentiated by the lack of
transaction support).

Sadly, there is a market for wrong answers faster.

--
Lew

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

Pure, utter, unadulterated bullshit. Speaking as someone who had years of
experience with Sybase SQL Server before either MySQL or PostgreSQL were
even created...

Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL partisans, that
it's never done in the database level, is just wrong. Whether it's a direct
deception, iow speaker knows it to be false, or an indirect deception, iow
speaker is implying a level of expertise he does not possess, either way I
would categorize it as a lie.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Naz Gassiep wrote:

I'm working in a project at the moment that is using MySQL, and people
keep making assertions like this one:

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

This sounds to me like MySQLish. A large DB working with no RI would
give me nightmares. Is it really true that large sites turn RI off to
improve performance, or is that just a MySQL thing where it gets turned
off just because MySQL allows you to turn it off and improve
performance? Can you even turn RI off in PostgreSQL? Does Oracle, DB2 or
MSSQL allow you to turn it off? Am I just being naive in thinking that
everyone runs their DBs with RI in production?

Someone's been drinking the MySQL 3.23 kool aide.

1: The bigger the amount of data you have to store, the more likely you
are to NEED referential integrity to make sure it's not getting all
messed up. Not just financial data either. What about applications
like trouble ticketing systems? Can you imagine having tickets go
orphan in a system to keep track of issues? What about parts inventory
systems? Hospital medication tracking? Transportation scheduling?
Fantasy Football? All of those systems are likely to need RI to make
sure that the data inside them stays coherent. We don't want to have
two customers thinking they have the same quarterback / taxi /
penicillen dosage / broken network router / water pump etc...

2: Handling RI in the application doesn't scale. If everything you do
requires you to check in the app, lock the whole table to prevent race
conditions, and then commit, you'll never scale to any real number of
users. You can have reliability and performance if you do RI in the
database. You only get to pick one if you're gonna do RI in the
application.

3: Of course you can turn off RI in PostgreSQL. Either remove the FK
triggers or disable them db wide. You can the same thing in Oracle as
well. This is normally done during maintenance windows to allow data
that is known to be coherent to be imported quickly. Doing so while
processing transactions is suicidal.

On 5/23/07, Naz Gassiep <naz@mira.net> wrote:

"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."

It's not just the big ones. Try using Ruby on Rails -- and its ORM,
ActiveRecord -- at some point, and you will notice the rampant
ignorance of referential integrity. ActiveRecord bears signs of having
been designed for MySQL.

For example, you need a plugin to add programmatic support for
foreign-key declarations to your schema code, and foreign key
relationships have to be explicitly defined using directives such as
"has_many". The unit test framework assumes it can delete rows in any
order, irrespective of foreign-key references. And so on.

Interestingly, ActiveRecord's support for polymorphic object
associations -- which allow you define a reference to an object in an
arbitrary table -- violates RI *per definition*. There's no support
for setting up the check constraints that would be appropriate for
such attributes. All the more annoying, since such associations are
extremely useful.

Alexander.

Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL partisans,

Live from the front :

This freshly created database has had to endure a multithreaded query
assault for about 2 hours.
It gave up.

TABLE `posts` (
`post_id` int(11) NOT NULL auto_increment,
`topic_id` int(11) NOT NULL,
etc...

mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
| 591257 |
+--------------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
ERROR 1062 (23000): Duplicate entry '591257' for key 1

mysql> CHECK TABLE posts;
+-------------------+-------+----------+-----------------------------+
| Table             | Op    | Msg_type | Msg_text                    |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning  | Table is marked as crashed  |
| forum_bench.posts | check | error    | Found 588137 keys of 588135 |
| forum_bench.posts | check | error    | Corrupt                     |
+-------------------+-------+----------+-----------------------------+

mysql> REPAIR TABLE posts;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| forum_bench.posts | repair | status   | OK       |
+-------------------+--------+----------+----------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'post_time' doesn't have a default value |
+---------+------+------------------------------------------------+

mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
| 591257 |
+--------------+

mysql> SELECT count(*) FROM posts UNION ALL SELECT sum( topic_post_count )
FROM topics;
+----------+
| count(*) |
+----------+
| 588137 |
| 588145 |
+----------+

mysql> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT topic_id
FROM posts);
+----------+
| count(*) |
+----------+
| 11583 |
+----------+

(Note : there cannot be a topic without a post in it, ha !)

Try Postgres :

forum_bench=> SELECT count(*) FROM posts UNION ALL SELECT
sum( topic_post_count ) FROM topics;
count
--------
536108
536108
(2 lignes)

forum_bench=> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT
topic_id FROM posts);
count
-------
0
(1 ligne)

Scott Marlowe wrote:

2: Handling RI in the application doesn't scale. If everything you do
requires you to check in the app, lock the whole table to prevent race
conditions, and then commit, you'll never scale to any real number of
users. You can have reliability and performance if you do RI in the
database. You only get to pick one if you're gonna do RI in the
application.

the other risk for RI in the app is the possibility of incompatible
implementation
across different app versions or different apps that access the same data.

not at all a fun place to be, that.

richard

On Wed, May 23, 2007 at 12:12:52PM +1000, Naz Gassiep wrote:

give me nightmares. Is it really true that large sites turn RI off to
improve performance,

You can't "turn it off", but you can "not use it". And I suppose
there are shops where they don't use it; after all, you can make any
computer system arbitrarily fast if the answer doesn't have to be
right.

By the way, the idea that application-level checks will make your
data integrity cheaper is the sort of idea that application
programmers who don't know anything about databases like to flog.
There are of course occasions where this is trivially true (e.g., you
require two pieces of data in a command, so you error before talking
to the database if in your parse stage they're not both there). But
any non-trivial integrity check is automatically going to impose
database queries, and anyone who claims the application programmer
can magically make the round trip cheaper than doing the same
operation inside the database is, bluntly, talking nonsense.

Worse, by moving the checking code out to the application, you also
move the maintenance of all that checking code out into the
application, where two different programmers can implement these
one-off checks in subtly different ways, introducing strange,
hard-to-troubleshoot data anomalies that take days to puzzle out and
fix. Then someone in senior management asks why the database didn't
just catch this on its own, at which point you re-implement all those
foreign keys while _still_ paying the cost of the client-side
validation code (which never gets ripped out), which means that the
whole thing ends up operating _slower_ than any other possible
implementation. I Have Been In That Meeting.

The whole reason we're using relational databases is so that the
relations can be queried _and maintained_. Databases vendors didn't
invent foreign keys in order to slow down their systems so they could
have angry customers. They implemented them in order to protect
their customers' data from bugs in application code. If your data is
worth storing, it's surely worth storing correctly.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/23/07 12:48, PFC wrote:

Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL partisans,

Live from the front :

This freshly created database has had to endure a multithreaded
query assault for about 2 hours.
It gave up.

TABLE `posts` (
`post_id` int(11) NOT NULL auto_increment,
`topic_id` int(11) NOT NULL,
etc...

mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
| 591257 |
+--------------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
ERROR 1062 (23000): Duplicate entry '591257' for key 1

mysql> CHECK TABLE posts;
+-------------------+-------+----------+-----------------------------+
| Table             | Op    | Msg_type | Msg_text                    |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning  | Table is marked as crashed  |
| forum_bench.posts | check | error    | Found 588137 keys of 588135 |
| forum_bench.posts | check | error    | Corrupt                     |
+-------------------+-------+----------+-----------------------------+

mysql> REPAIR TABLE posts;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| forum_bench.posts | repair | status   | OK       |
+-------------------+--------+----------+----------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'post_time' doesn't have a default value |
+---------+------+------------------------------------------------+

What version of that pathetic RDBMS is this?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVLLGS9HxQb37XmcRAgpiAJ4nHpdGXL5HFdosWvkIy16CEyXiSwCgjqtB
qYgCmePqgZkGCpdJ/JAFLoE=
=P7OR
-----END PGP SIGNATURE-----

On 5/23/07, PFC <lists@peufeu.com> wrote:

+-------------------+-------+----------+-----------------------------+
| Table             | Op    | Msg_type | Msg_text                    |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning  | Table is marked as crashed  |
| forum_bench.posts | check | error    | Found 588137 keys of 588135 |
| forum_bench.posts | check | error    | Corrupt                     |
+-------------------+-------+----------+-----------------------------+

I find it hard to believe that this is MySQL's fault and not some
problem with your setup. Granted, MySQL is a pretty bad database, but
it's not *that* bad -- your example implies that heavily MyISAM-based
(you don't say whether this is MyISAM or InnoDB) sites such as
Slashdot and Flickr should be falling over every hour.

Alexander.

What version of that pathetic RDBMS is this?

MySQL 5.0.40, on gentoo Linux, Core 2 Duo.

The table in question takes about 100 inserts/deletes and 600 selects per
second.
MyISAM isn't able to finish the benchmark. Actually, I have to run REPAIR
TABLE every 20 minutes, since it corrupts.

I find it hard to believe that this is MySQL's fault and not some
problem with your setup.

Yeah, me too.
Is it a MyISAM bug, a gentoo bug, a hardware bug ? Who knows.
Go into bugs.mysql.com and search for "corrupt" or "corruption"
Postgres, InnoDB and memtest86 are perfectly happy on this machine.

However, I have sometimes upgraded MySQL on websites, and found it to
crash repeatedly, then had to downgrade it.

I submitted this one, consider it my pet bug :
http://bugs.mysql.com/bug.php?id=28534

On May 23, 2007, at 12:54 PM, Scott Ribe wrote:

"*Really* big sites don't ever have referential integrity. Or if
the few
spots they do (like with financial transactions) it's implemented
on the
application level (via, say, optimistic locking), never the
database level."

Pure, utter, unadulterated bullshit. Speaking as someone who had
years of
experience with Sybase SQL Server before either MySQL or PostgreSQL
were
even created...

Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL
partisans, that
it's never done in the database level, is just wrong. Whether it's
a direct
deception, iow speaker knows it to be false, or an indirect
deception, iow
speaker is implying a level of expertise he does not possess,
either way I
would categorize it as a lie.

I concur with the claim of organic fertilizer.

I got into a rather spicy argument at the only RAILS conference I
went to. They have this mentality that with Rails you don't need to
put in RI on the database because you can always run exists? checks
right before you do the insert to ensure integrity of your data. Not
only does this apply to Referential Integrity, but also unique
values. I was damn near screaming at them over the stupidity of such
a notion.

My experience is based on working at a rather large company that has
a really huge Oracle database.
When they designed it, they passed up on all Referential integrity
and all unique constraints.
After five years, we have tables that are >60% duplicate records and
the database is coming to a standstill.
And there is no known method in sight on being able to fix this one.

Bottom line, if the DBA or anyone says we can't support RI or UNIQUE
because of the performance overhead... I would be inclined to look
for another DBA.
But I have to admit. I am extremely opinionated about this as I'm
the guy who does most of the performance and metric reporting using
these horrid tables.
it does provide infinite job security, but it's hardly worth it in
the long run.

Alexander Staubo wrote:

On 5/23/07, PFC <lists@peufeu.com> wrote:

+-------------------+-------+----------+-----------------------------+
| Table             | Op    | Msg_type | Msg_text                    |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning  | Table is marked as crashed  |
| forum_bench.posts | check | error    | Found 588137 keys of 588135 |
| forum_bench.posts | check | error    | Corrupt                     |
+-------------------+-------+----------+-----------------------------+

I find it hard to believe that this is MySQL's fault and not some
problem with your setup. Granted, MySQL is a pretty bad database, but
it's not *that* bad -- your example implies that heavily MyISAM-based
(you don't say whether this is MyISAM or InnoDB) sites such as
Slashdot and Flickr should be falling over every hour.

I'm not going to comment on who's fault it is, but the OP quoted 100
updates and 600 selects per *second*. I can't imagine Flickr or Slashdot
(which is heavily csched for reading) are under anything like that sort
of constant load.

Regards, Dave.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/23/07 20:29, Tom Allison wrote:
[snip]

Bottom line, if the DBA or anyone says we can't support RI or UNIQUE
because of the performance overhead... I would be inclined to look for
another DBA.
But I have to admit. I am extremely opinionated about this as I'm the
guy who does most of the performance and metric reporting using these
horrid tables.
it does provide infinite job security, but it's hardly worth it in the
long run.

We must be the exception to the rule.

In July 2005 we did a major long-weekend unload-reload archive of
our big (400M row) toll tables. There was no RI on the tables,
either PK or FK.

When reloading the "keep" data, I created a PK (fortunately the
hashed/clustered design of the table means that PK enforcement is
almost zero-cost) and loaded the data.

There were ZERO duplicates.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVR0MS9HxQb37XmcRAsozAKC0TCPBjj0cO58SEHfZ0JDoMdWTUQCeNDLq
Fa0x3oDJGTllIZ65dgdTUiY=
=Kqex
-----END PGP SIGNATURE-----