Re: Integrity on large sites
От | PFC |
---|---|
Тема | Re: Integrity on large sites |
Дата | |
Msg-id | op.tssjrsc3cigqcu@apollo13 обсуждение исходный текст |
Ответ на | Re: Integrity on large sites (Scott Ribe <scott_ribe@killerbytes.com>) |
Ответы |
Re: Integrity on large sites
(Ron Johnson <ron.l.johnson@cox.net>)
Re: Integrity on large sites ("Alexander Staubo" <alex@purefiction.net>) |
Список | pgsql-general |
> 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)
В списке pgsql-general по дате отправления:
Следующее
От: "Harpreet Dhaliwal"Дата:
Сообщение: Vacuum DB in Postgres Vs similar concept in other RDBMS