October 2007 Archive

SP/SF作成時に、中で利用するテーブル名が間違っていても、エラーを返すのは、実行時だけ？
って、不便。

http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es-5-0-30.html

InnoDB showed substandard performance with multiple queries running concurrently. (Bug#15815)

http://bugs.mysql.com/bug.php?id=15815

http://conferences.oreillynet.com/presentations/mysql07/InnoDB_kinoshita.pdf

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

#

Foreign keys. Partitioned tables do not support foreign keys. This means that:

1.

Definitions of tables employing user-defined partitioning may not contain foreign key references to other tables.
2.

No table definition may contain a foreign key reference to a partitioned table.

The scope of these restrictions includes tables that use the InnoDB storage engine.

InnoDBでPartitionする意味→

今までは大量のデータを、巨大なテーブルから削除する際に、セカンダリインデックスがバッファプールにフィットし無い場合に非常に遅くなっていたが、パーティショニングで解決する。

MyISAMと同じくPARTITION BYが使える。

mysql> show variables like 'log_output';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | TABLE |

+---------------+-------+

1 row in set (0.00 sec)

mysql> select @@system_time_zone;

+--------------------+

| @@system_time_zone |

+--------------------+

| 東京 (標準時) |

+--------------------+

1 row in set (0.00 sec)

SHOW INNODB STATUS walk through | MySQL Performance Blog

MySQL、グーグルが開発したコードを採用へ : ソフトウェア＆サービス - Computerworld.jp

innodb_flush_method=O_DIRECT
とか、
スレーブではinnodb_flush_log_at_trx_commit=0
とか、もいずれ検討しないと。

thread_cacheとか、cache系も整理して。

@@global.sql_mode
@@session.sql_mode
show global variables
show session variables

mysql> select @@session.sql_mode; +--------------------+ | @@session.sql_mode | +--------------------+ | | +--------------------+ 1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+----------------------------------------------------------------+
| @@global.sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show vaiables like 'sql_mode';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'vaiab
les like 'sql_mode'' at line 1
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'sql_mode';
+---------------+---------------------------------------------------------------
-+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
-+
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
+---------------+---------------------------------------------------------------
-+
1 row in set (0.00 sec)