MYSQL5.6がリリースされてから2週間経過して色々と検証してくださる方がいて、
MYSQL5.6からはPerformance_SchemaがDefaultになっているので少しオーバーヘッドが高いのでは?
などVariableの値が増えているなど色々と出てきました。

Review of MySQL 5.6 Defaults Changes
MySQL 5.5 and 5.6 default variable values differences
Is MySQL 5.6 slower than MySQL 5.5 ?
Performance Schema overhead
MySQL5.6で増えたexplicit_defaults_for_timestamp

Performance検証は今後時間をとって行いますが、先ずはVariableの変更部分だけ調べてみました。

variableの値は”331 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)
mysql>

[root@HOME001 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > /home/mysql/5.5.29.txt
Enter password:
[root@HOME001 ~]#

variableの値は”435 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql>

[root@HOME002 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > 5.6.10.txt
Enter password:
[root@HOME002 ~]#

上記で取得した値をdiffコマンドで、増加・変更のあった値を調査してみる。

[root@HOME002 mysql]# diff -y -y --suppress-common-lines 5.5.29.txt 5.6.10.txt
                                                              > bind_address
                                                              > binlog_checksum
                                                              > binlog_max_flush_queue_time
                                                              > binlog_order_commits
                                                              > binlog_row_image
                                                              > binlog_rows_query_log_events
                                                              > core_file
                                                              > default_tmp_storage_engine
                                                              > disconnect_on_expired_password
engine_condition_pushdown                                     | end_markers_in_json
                                                              > enforce_gtid_consistency
                                                              > eq_range_index_dive_limit
                                                              > explicit_defaults_for_timestamp
                                                              > gtid_executed
                                                              > gtid_mode
                                                              > gtid_next
                                                              > gtid_owned
                                                              > gtid_purged
have_csv                                                      <
have_innodb                                                   <
have_ndbcluster                                               <
have_partitioning                                             <
                                                              > host_cache_size
                                                              > ignore_db_dirs
                                                              > innodb_adaptive_flushing_lwm
                                                              > innodb_api_bk_commit_interval
                                                              > innodb_api_disable_rowlock
                                                              > innodb_api_enable_binlog
                                                              > innodb_api_enable_mdl
                                                              > innodb_api_trx_level
                                                              > innodb_buffer_pool_dump_at_shutdown
                                                              > innodb_buffer_pool_dump_now
                                                              > innodb_buffer_pool_filename
                                                              > innodb_buffer_pool_load_abort
                                                              > innodb_buffer_pool_load_at_startup
                                                              > innodb_buffer_pool_load_now
                                                              > innodb_change_buffer_max_size
                                                              > innodb_checksum_algorithm
                                                              > innodb_cmp_per_index_enabled
                                                              > innodb_compression_failure_threshold_pct
                                                              > innodb_compression_level
                                                              > innodb_compression_pad_pct_max
                                                              > innodb_disable_sort_file_cache
                                                              > innodb_flush_log_at_timeout
                                                              > innodb_flush_neighbors
                                                              > innodb_flushing_avg_loops
                                                              > innodb_ft_aux_table
                                                              > innodb_ft_cache_size
                                                              > innodb_ft_enable_diag_print
                                                              > innodb_ft_enable_stopword
                                                              > innodb_ft_max_token_size
                                                              > innodb_ft_min_token_size
                                                              > innodb_ft_num_word_optimize
                                                              > innodb_ft_server_stopword_table
                                                              > innodb_ft_sort_pll_degree
                                                              > innodb_ft_user_stopword_table
                                                              > innodb_io_capacity_max
                                                              > innodb_lru_scan_depth
                                                              > innodb_max_dirty_pages_pct_lwm
                                                              > innodb_max_purge_lag_delay
                                                              > innodb_monitor_disable
                                                              > innodb_monitor_enable
                                                              > innodb_monitor_reset
                                                              > innodb_monitor_reset_all
                                                              > innodb_online_alter_log_max_size
                                                              > innodb_optimize_fulltext_only
                                                              > innodb_page_size
                                                              > innodb_print_all_deadlocks
                                                              > innodb_read_only
                                                              > innodb_sort_buffer_size
                                                              > innodb_stats_auto_recalc
                                                              > innodb_stats_persistent
                                                              > innodb_stats_persistent_sample_pages
                                                              > innodb_stats_transient_sample_pages
                                                              > innodb_sync_array_size
                                                              > innodb_undo_directory
                                                              > innodb_undo_logs
                                                              > innodb_undo_tablespaces
log                                                           <
                                                              > log_bin_basename
                                                              > log_bin_index
                                                              > log_bin_use_v1_row_events
log_slow_queries                                              | log_throttle_queries_not_using_indexes
                                                              > master_info_repository
                                                              > master_verify_checksum
max_long_data_size                                            <
                                                              > metadata_locks_hash_instances
                                                              > optimizer_trace
                                                              > optimizer_trace_features
                                                              > optimizer_trace_limit
                                                              > optimizer_trace_max_mem_size
                                                              > optimizer_trace_offset
                                                              > performance_schema_accounts_size
                                                              > performance_schema_digests_size
                                                              > performance_schema_events_stages_history_long_size
                                                              > performance_schema_events_stages_history_size
                                                              > performance_schema_events_statements_history_long_size
                                                              > performance_schema_events_statements_history_size
                                                              > performance_schema_hosts_size
                                                              > performance_schema_max_socket_classes
                                                              > performance_schema_max_socket_instances
                                                              > performance_schema_max_stage_classes
                                                              > performance_schema_max_statement_classes
                                                              > performance_schema_session_connect_attrs_size
                                                              > performance_schema_setup_actors_size
                                                              > performance_schema_setup_objects_size
                                                              > performance_schema_users_size
                                                              > pseudo_slave_mode
                                                              > relay_log_basename
                                                              > relay_log_info_repository
rpl_recovery_rank                                             | rpl_semi_sync_slave_enabled
rpl_semi_sync_master_enabled                                  | rpl_semi_sync_slave_trace_level
rpl_semi_sync_master_timeout                                  <
rpl_semi_sync_master_trace_level                              <
rpl_semi_sync_master_wait_no_slave                            <
                                                              > server_id_bits
                                                              > server_uuid
                                                              > slave_allow_batching
                                                              > slave_checkpoint_group
                                                              > slave_checkpoint_period
                                                              > slave_parallel_workers
                                                              > slave_pending_jobs_size_max
                                                              > slave_rows_search_algorithms
                                                              > slave_sql_verify_checksum
sql_big_tables                                                <
sql_low_priority_updates                                      <
sql_max_join_size                                             <
                                                              > ssl_crl
                                                              > ssl_crlpath
                                                              > table_open_cache_instances
                                                              > transaction_allow_batching
                                                              > tx_read_only
[root@HOME002 mysql]#

variables


MYSQL5.6がリリースされました!!

MYSQL5.6はこれまでのMYSQL5.5と比較してもパフォーマンスが良く、
レプリケーションも安定性と運用を考慮した改善がされているという
事でしたので、リリースを待っていました。

Web セミナー Web セミナー

MySQL Security Enhancements and Important Security Tips for ISVs & OEMs
Thursday, February 21, 2013  2013年02月22日 (金): 00:00 日本

MySQL 5.6 Performance & Scalability ~ Afina tu BD en este webinar gratuito!
Thursday, February 28, 2013  2013年03月01日 (金): 08:00 日本
  

MySQL Replication: An Introduction
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-introduction/

The whitepaper discusses:
Replication concepts
Replication enhancements in MySQL 5.6
Replication use-cases
Replication topologies
Replication monitoring and management

MySQL Replication Tutorial: Configuration, Provisioning and Management
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/

By reading this paper, you will be able to:
Configure and provision MySQL replication
Migrate to semi-synchronous replication
Administer and trouble-shoot MySQL replication

データベース管理者/開発者ガイド MySQL 5.6の新機能
http://www-jp.mysql.com/why-mysql/white-papers/whats-new-mysql-5-6-ja/

    以下抜粋

MySQL 5.6は、世界で最も普及しているオープンソース・データベースの、
これまでで最高のリリースです。次世代のWebシステムの実現や、組込みアプリケーション
およびサービスの構築 を可能にするために設計された先進的な新機能を提供します。
本書では、主要な機能と機能強化を項目ごとに紹介し、
詳細な技術情報や実装関連情報を提供する事例や参考資料を示します。本書によって、
以下に挙げるような、MySQL 5.6の以前のリリースより改善・強化した点を理解することができます。

パフォーマンスとスケーラビリティの向上
InnoDBストレージ・エンジンの改良によるトランザクション・スループットの向上
オプティマイザの改良によるクエリ実行時間と診断機能の改善
オンラインでのDDL/スキーマ操作によるアプリケーション可用性の向上
Memcached APIを用いたInnoDBへのNoSQLアクセスによる開発速度の向上
レプリケーションの改善による高いパフォーマンスと自己修復可能なクラスタ構成
パフォーマンス・スキーマの改良による性能統計情報管理と監視機能の改善
セキュリティの向上による容易なアプリケーション設計
その他の重要な機能強化


検証用のレプリケーションの環境にてスレーブのMYSQLを5.6にアップグレードしてみました。

レプリケーションの確認

マスター側にてデータをINSERTして確認。

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.12 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 1');
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 2');
Query OK, 1 row affected (0.44 sec)

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql>

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 5     |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.08 sec)

MYSQL5.5.29で追加インストールしたプラグインが
MYSQL5.6で有効になっていない可能性があるのでアップグレードの
タイミングで事前に無効にしておく。

[root@HOME002 mysql]# cp -p /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 mysql]# vi /etc/my.cnf
[root@HOME002 mysql]# diff /etc/my.cnf /etc/my.cnf.20130216
68c68
< #rpl_semi_sync_slave_enabled=1
---
> rpl_semi_sync_slave_enabled=1
[root@HOME002 mysql]#

[root@HOME002 mysql]# /etc/init.d/mysql stop
Shutting down MySQL.                                       [  OK  ]
[root@HOME002 mysql]#

MYSQL56-0

MYSQL5.6のインストール開始。
基本的には、TARの入れ替えとシンボリックリンクの張替えで対応してます。

[root@HOME002 src]# tar zxvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz
mysql-5.6.10-linux-glibc2.5-i686/COPYING
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess.conf
mysql-5.6.10-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld_multi
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlshow
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess
mysql-5.6.10-linux-glibc2.5-i686/bin/resolve_stack_dump
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqladmin
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld

省略.........

mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Memcache.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/File/Path.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Exec.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/ConfigFactory.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/CoreDump.pm
[root@HOME002 src]#

[root@HOME002 src]# mv mysql-5.6.10-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]#


[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 mysql mysql   27 12月 22 14:43 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxr-xr-x 13 root  root  4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 775 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]#


[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 root  root    33  2月 16 15:56 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxrwxr-x 13 mysql mysql 4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#


[root@HOME002 local]# cp -rp mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql/data/
[root@HOME002 local]#

[root@HOME002 bin]# ls -l /usr/local/mysql/bin/*up*
-rwxrwxr-x 1 mysql mysql 4471915  1月 23 02:13 /usr/local/mysql/bin/mysql_upgrade
[root@HOME002 bin]# /etc/init.d/mysql start
Starting MySQL......                                       [  OK  ]
[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME002 bin]#

upgrade

アップグレード後の確認。
OFFにしてあったSEMI-SYNCを再度ONに設定しても問題無いか確認して元に戻す。

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

[root@HOME002 bin]# vi /etc/my.cnf
[root@HOME002 bin]# diff /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 bin]#

[root@HOME002 bin]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 bin]#

レプリケーションの動作確認
マスターでデータをINSERTしてみる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('After Slave upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.43 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 1506
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 270
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1506
              Relay_Log_Space: 437
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

insert-after-slave-upgrade

エラーログの確認
特に問題なさそうですが、ログを確認するとバージョンアップしたので
幾つかオプションファイルの設定やレプリケーションの設定も変更した方が良さそうです。
マスターをアップグレードするタイミングで設定変更してみます。

130216 16:16:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-02-16 16:16:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-16 16:16:21 13756 [Note] Plugin 'FEDERATED' is disabled.
2013-02-16 16:16:21 b7fe96d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-02-16 16:16:21 13756 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-16 16:16:21 13756 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-02-16 16:16:21 13756 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-16 16:16:21 13756 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-16 16:16:21 13756 [Note] InnoDB: Using Linux native AIO
2013-02-16 16:16:21 13756 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-02-16 16:16:21 13756 [Note] InnoDB: Completed initialization of buffer pool
2013-02-16 16:16:21 13756 [Note] InnoDB: Highest supported file format is Barracuda.
2013-02-16 16:16:22 13756 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-16 16:16:22 13756 [Note] InnoDB: 1.2.10 started; log sequence number 1727822
2013-02-16 16:16:22 13756 [Note] Server hostname (bind-address): '*'; port: 3306
2013-02-16 16:16:22 13756 [Note] IPv6 is available.
2013-02-16 16:16:22 13756 [Note]   - '::' resolves to '::';
2013-02-16 16:16:22 13756 [Note] Server socket created on IP: '::'.
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@HOME001:3306' in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-02-16 16:16:22 13756 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-02-16 16:16:22 13756 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 1506, relay log '/usr/local/mysql/data/relay-bin.000006' position: 270
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: connected to master 'slave_user@HOME001:3306',replication started in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Note] Event Scheduler: Loaded 0 events
2013-02-16 16:16:22 13756 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master, maybe it is a *VERY OLD MASTER*. Error_code: 1193
[root@HOME002 data]#

show status

どれだけのデータが変更されているか把握する事で、
データベースをどのように調整したら良いかある程度デザインする上での指標になるかと思います。

データ追加頻度

mysql> show status like '%insert%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert             | 1     |INSERTコマンド実行数:再接続すると0にリセットされます
| Com_insert_select      | 0     |
| Delayed_insert_threads | 0     |
| Innodb_rows_inserted   | 1     |起動からのInnoDB Row Insert累計件数
| Qcache_inserts         | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('confirmation','Innodb_rows_inserted time 2');
Query OK, 1 row affected (0.42 sec)

mysql> show status like '%insert%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert             | 2     |
| Com_insert_select      | 0     |
| Delayed_insert_threads | 0     |
| Innodb_rows_inserted   | 2     |
| Qcache_inserts         | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)

mysql>

insert

更新頻度

mysql> show status like '%update%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_update          | 0     | UPDATEコマンド実行数:再接続すると0にリセットされます
| Com_update_multi    | 0     |
| Handler_update      | 0     | Update Row数:再接続すると0にリセットされます
| Innodb_rows_updated | 0     | 起動からのInnoDB Row Update累計件数
+---------------------+-------+
4 rows in set (0.00 sec)

mysql> select * from TABLE001 where id = 39;
+----+--------------+----------------------+
| id | title        | comment              |
+----+--------------+----------------------+
| 39 | confirmation | Com_update increment |
+----+--------------+----------------------+
1 row in set (0.00 sec)

mysql> update TABLE001 set comment = 'Innodb_rows_updated test1' where id = 39;
Query OK, 1 row affected (0.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show status like '%update%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_update          | 1     |
| Com_update_multi    | 0     |
| Handler_update      | 1     |
| Innodb_rows_updated | 1     |
+---------------------+-------+
4 rows in set (0.00 sec)

mysql> 

update

削除頻度

mysql> show status like '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 0     |
| Com_delete_multi    | 0     |
| Handler_delete      | 0     |
| Innodb_rows_deleted | 0     |
+---------------------+-------+
4 rows in set (0.00 sec)

mysql>  delete from TABLE001 where id > 40;
Query OK, 5 rows affected (0.44 sec)

mysql> show status like '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 1     | DELETEコマンド実行数:再接続すると0にリセットされます
| Com_delete_multi    | 0     | 
| Handler_delete      | 5     | DELETE Row数:再接続すると0にリセットされます
| Innodb_rows_deleted | 5     | 起動からのInnoDB Row Delete累計件数
+---------------------+-------+
4 rows in set (0.00 sec)

mysql>

delete

並列状態

mysql> show status like '%threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 0     |
| Threads_connected      | 2     | 取得した瞬間の値
| Threads_created        | 5     | 起動から作成されたスレッド数累計
| Threads_running        | 2     | 取得した瞬間の値
+------------------------+-------+
6 rows in set (0.00 sec)

mysql>

mysql> show processlist;
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User       | Host         | db   | Command     | Time | State                                                                 | Info             |
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  3 | slave_user | home002:4367 | NULL | Binlog Dump | 5203 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  5 | root       | localhost    | test | Query       |    0 | NULL                                                                  | show processlist |
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql>