MYSQLにもMSSQLやOracleのようにProfilerが無いか以前にも検証したのですが、
暫く現場を離れていて忘れていたのでセルフリマインドの為に再確認です。
MYSQL Profiling

Queryの実行プランは、その他DBと同じく実行プランの確認とインデックスの有無で対応可能ですが、
サーバーパラメーターのチューニングにPROFILEを利用してどのDB処理が遅いが確認する事が出来ます。
必要に応じてmy.cnfや動的パラメーターを変更しましょう。

Oracle (例)
EXPLAIN PLAN FOR select * from テーブル名;

MYSQL (例)
EXPLAIN select * from テーブル名;

mysql> explain select * from orders where o_id = 100;
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 71933 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

mysql>

PROFILEのONとOFFについて


mysql> SET profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> SET profiling=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>

mysql> show variables like 'profiling_history_size';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling_history_size | 15    |
+------------------------+-------+
1 row in set (0.00 sec)

mysql>


実際に実行してみると、Source_functionとDurationでどの処理が、
どの位内部処理に時間がかかっているか確認出来る。

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

[省略]

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| tpcc |
+——————–+
5 rows in set (0.00 sec)

mysql> show profiles;
+———-+————+——————-+
| Query_ID | Duration | Query |
+———-+————+——————-+
| 1 | 0.00636075 | show local status |
| 2 | 0.00027875 | set profiling=1 |
| 3 | 0.01078700 | show local status |
| 4 | 0.01107825 | show databases |
+———-+————+——————-+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE SOURCE;
+——————–+———-+———————–+—————+————-+
| Status | Duration | Source_function | Source_file | Source_line |
+——————–+———-+———————–+—————+————-+
| starting | 0.001011 | NULL | NULL | NULL |
| Opening tables | 0.000583 | open_tables | sql_base.cc | 4838 |
| System lock | 0.000057 | mysql_lock_tables | lock.cc | 299 |
| init | 0.000062 | mysql_select | sql_select.cc | 2554 |
| optimizing | 0.000026 | optimize | sql_select.cc | 863 |
| statistics | 0.000055 | optimize | sql_select.cc | 1054 |
| preparing | 0.000041 | optimize | sql_select.cc | 1076 |
| executing | 0.003726 | exec | sql_select.cc | 1823 |
| Sending data | 0.000117 | exec | sql_select.cc | 2365 |
| end | 0.000026 | mysql_select | sql_select.cc | 2590 |
| query end | 0.000017 | mysql_execute_command | sql_parse.cc | 4439 |
| closing tables | 0.000007 | mysql_execute_command | sql_parse.cc | 4491 |
| removing tmp table | 0.000100 | free_tmp_table | sql_select.cc | 11180 |
| closing tables | 0.000015 | free_tmp_table | sql_select.cc | 11205 |
| freeing items | 0.004987 | mysql_parse | sql_parse.cc | 5639 |
| logging slow query | 0.000031 | log_slow_statement | sql_parse.cc | 1460 |
| cleaning up | 0.000218 | dispatch_command | sql_parse.cc | 1416 |
+——————–+———-+———————–+—————+————-+
17 rows in set (0.00 sec)

profile_mysql

Query_IDを指定して過去に実行したQuery詳細を確認

mysql> SHOW PROFILEs;
+———-+————+———————————————-+
| Query_ID | Duration | Query |
+———-+————+———————————————-+
| 1 | 0.00636075 | show local status |
| 2 | 0.00027875 | set profiling=1 |
| 3 | 0.01078700 | show local status |
| 4 | 0.01107825 | show databases |
| 5 | 0.00268775 | SHOW VARIABLES LIKE ‘tmp_table_size’ |
| 6 | 0.01079550 | show local status |
| 7 | 0.00259425 | show variables like ‘profiling_history_size’ |
+———-+————+———————————————-+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE SOURCE FOR QUERY 7;
+——————–+———-+———————–+—————+————-+
| Status | Duration | Source_function | Source_file | Source_line |
+——————–+———-+———————–+—————+————-+
| starting | 0.000268 | NULL | NULL | NULL |
| Opening tables | 0.000510 | open_tables | sql_base.cc | 4838 |
| System lock | 0.000055 | mysql_lock_tables | lock.cc | 299 |
| init | 0.000050 | mysql_select | sql_select.cc | 2554 |
| optimizing | 0.000018 | optimize | sql_select.cc | 863 |
| statistics | 0.000028 | optimize | sql_select.cc | 1054 |
| preparing | 0.000023 | optimize | sql_select.cc | 1076 |
| executing | 0.001354 | exec | sql_select.cc | 1823 |
| Sending data | 0.000089 | exec | sql_select.cc | 2365 |
| end | 0.000016 | mysql_select | sql_select.cc | 2590 |
| query end | 0.000010 | mysql_execute_command | sql_parse.cc | 4439 |
| closing tables | 0.000005 | mysql_execute_command | sql_parse.cc | 4491 |
| removing tmp table | 0.000080 | free_tmp_table | sql_select.cc | 11180 |
| closing tables | 0.000014 | free_tmp_table | sql_select.cc | 11205 |
| freeing items | 0.000058 | mysql_parse | sql_parse.cc | 5639 |
| logging slow query | 0.000008 | log_slow_statement | sql_parse.cc | 1460 |
| cleaning up | 0.000010 | dispatch_command | sql_parse.cc | 1416 |
+——————–+———-+———————–+—————+————-+
17 rows in set (0.01 sec)

mysql>

specific query

その他のProfileオプション

mysql> SHOW PROFILE ALL;
mysql> SHOW PROFILE BLOCK IO;
mysql> SHOW PROFILE CONTEXT SWITCHES;
mysql> SHOW PROFILE CPU;
mysql> SHOW PROFILE IPC;
mysql> SHOW PROFILE MEMORY;
mysql> SHOW PROFILE PAGE FAULTS;

other option

最後にシンプルに特定クエリーだけ確認

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from T_Archive_Log where id = 100;
+-----+---------------------+------------------------------------------------------------------+------------+------------+-------------+
| id  | Sdd                 | Rdd                                                              | Error_Code | conf_time  |        flag |
+-----+---------------------+------------------------------------------------------------------+------------+------------+-------------+
| 100 | admin@variable.jp   | 59AA9E550C91F84FCD7777713258CBA9E56655E6089F2657838AFFE48BDCC6F9 | 550        | 2011-10-12 |           0 |
+-----+---------------------+------------------------------------------------------------------+------------+------------+-------------+
1 row in set (0.01 sec)

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00117200 | select * from T_Archive_Log where id = 100 |
+----------+------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000789 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000022 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000016 |
| System lock                    | 0.000012 |
| Waiting for query cache lock   | 0.000005 |
| init                           | 0.000017 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000071 |
| preparing                      | 0.000013 |
| executing                      | 0.000003 |
| Sending data                   | 0.000154 |
| end                            | 0.000012 |
| query end                      | 0.000004 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000016 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
19 rows in set (0.02 sec)

mysql> SHOW PROFILE SOURCE FOR QUERY 1;
+--------------------------------+----------+-----------------------+---------------+-------------+
| Status                         | Duration | Source_function       | Source_file   | Source_line |
+--------------------------------+----------+-----------------------+---------------+-------------+
| starting                       | 0.000789 | NULL                  | NULL          |        NULL |
| Waiting for query cache lock   | 0.000005 | try_lock              | sql_cache.cc  |         454 |
| checking query cache for query | 0.000022 | send_result_to_client | sql_cache.cc  |        1561 |
| checking permissions           | 0.000005 | check_access          | sql_parse.cc  |        4745 |
| Opening tables                 | 0.000016 | open_tables           | sql_base.cc   |        4837 |
| System lock                    | 0.000012 | mysql_lock_tables     | lock.cc       |         299 |
| Waiting for query cache lock   | 0.000005 | try_lock              | sql_cache.cc  |         454 |
| init                           | 0.000017 | mysql_select          | sql_select.cc |        2554 |
| optimizing                     | 0.000010 | optimize              | sql_select.cc |         863 |
| statistics                     | 0.000071 | optimize              | sql_select.cc |        1054 |
| preparing                      | 0.000013 | optimize              | sql_select.cc |        1076 |
| executing                      | 0.000003 | exec                  | sql_select.cc |        1823 |
| Sending data                   | 0.000154 | exec                  | sql_select.cc |        2365 |
| end                            | 0.000012 | mysql_select          | sql_select.cc |        2590 |
| query end                      | 0.000004 | mysql_execute_command | sql_parse.cc  |        4434 |
| closing tables                 | 0.000009 | mysql_execute_command | sql_parse.cc  |        4486 |
| freeing items                  | 0.000016 | mysql_parse           | sql_parse.cc  |        5634 |
| logging slow query             | 0.000004 | log_slow_statement    | sql_parse.cc  |        1460 |
| cleaning up                    | 0.000005 | dispatch_command      | sql_parse.cc  |        1416 |
+--------------------------------+----------+-----------------------+---------------+-------------+
19 rows in set (0.00 sec)

mysql> SET profiling = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>

参考サイト:
MYSQL Profiling
MySQLのEXPLAINを徹底解説!!
プロファイリングで快適MySQLチューニング生活


システムのボトルネック発見の為に、参考になるサイトを確認していたらGDB,AWK,SORTなどの
普段の運用で利用するコマンドを利用してボトルネックを確認するシェルを作成している人がいたので
参考にさせていただきました。Profilerとして利用するのに良さそうです。

GDB
GDB, the GNU Project debugger, allows you to see what is going on `inside’ another
program while it executes — or what another program was doing at the moment it crashed.

#!/bin/bash
nsamples=1
sleeptime=0
pid=$(pidof mysqld)

for x in $(seq 1 $nsamples)
  do
    gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $pid
    sleep $sleeptime
  done | \
awk '
  BEGIN { s = ""; } 
  /Thread/ { print s; s = ""; } 
  /^\#/ { if (s != "" ) { s = s "," $4} else { s = $4 } } 
  END { print s }' | \
sort | uniq -c | sort -r -n -k 1,1

GDBがインストールされて無かったのでインストール

[root@CentOS64VM tools]# yum install gdb
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
* base: ftp.nara.wide.ad.jp
* extras: ftp.nara.wide.ad.jp
* updates: ftp.nara.wide.ad.jp
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package gdb.x86_64 0:7.2-56.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================
Package Arch Version Repository  Size
=====================================================================================
Installing:
gdb    x86_64 7.2-56.el6   base   2.3 M

Transaction Summary
=====================================================================================
Install 1 Package(s)

Total download size: 2.3 M
Installed size: 5.2 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 2.3 M

[省略]

Trying other mirror.
gdb-7.2-56.el6.x86_64.rpm           | 2.3 MB 00:13
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : gdb-7.2-56.el6.x86_64       1/1
Verifying : gdb-7.2-56.el6.x86_64 1/1

Installed:
gdb.x86_64 0:7.2-56.el6

Complete!
[root@CentOS64VM tools]#

GDBとスクリプトの準備が出来たので実行
Profiler

TPCC-MYSQLを実行して負荷をかけた状態でシステム状態を確認してみる。

[root@CentOS64VM tools]# ./poor_mans_profiler.sh
20
2 pthread_cond_wait@@GLIBC_2.3.2,os_cond_wait,reset_sig_count=2912),log_write_up_to,trx_commit_complete_for_mysql,innobase_commit,ha_commit_one_phase,he_command,mysql_parse,dispatch_command,do_handle_one_connection,handle_one_connection,pfs_spawn_thread,start_thread,clone
1 sigwait,signal_hand,pfs_spawn_thread,start_thread,clone
1 select,os_thread_sleep,srv_master_thread,start_thread,clone
1 pthread_cond_timedwait@@GLIBC_2.3.2,os_cond_wait_timed,time_in_usec=

こちらも合わせて確認:gdb -p `pidof mysqld`
How to debug lock (hang)

参考サイト
poor man’s profiler
漢(オトコ)のコンピュータ道
poormans-profiler
GDB: The GNU Project Debugger
ファイヤープロジェクト


MYSQL5.5.25にはオプティマイザーのバグがあったようで、
5.5.25をインストールした人は5.5.25aにアップグレードしてくださいとの事です。

Bug #65745
MySQL 5.5.25a is a replacement of MySQL 5.5.25, in that version a
regression bug was detected in the optimizer (Bug #65745).
That bug is specific to MySQL 5.5.25, other versions are not affected.

MySQL Community Server 5.5.25a has been released

MySQL 5.5.25a がリリースされました

改めてMYSQLのシステムテーブル(performance_schema)をレビューしてみます。

データベースサイズ

select table_schema, 
sum(data_length+index_length) /1024 /1024 as MB
from information_schema.tables group by table_schema 
order by sum(data_length+index_length) desc;

テーブルサイズ

select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;

MYSQL TABLE SIZE

PERFORMANCE_SCHEMA

mysql> show tables;
+———————————————-+
| Tables_in_performance_schema |
+———————————————-+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+———————————————-+
17 rows in set (0.01 sec)

mysql>

PERFORMANCE_SCHEMAを有効にしてパフォーマンスを確認

mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | OFF |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.00 sec)

mysql> set performance_schema = ON;
ERROR 1238 (HY000): Variable ‘performance_schema’ is a read only variable
mysql>

動的に設定変更出来ないようなので、/etc/my.cnfに以下のラインを追加して再起動。

————————————————————–
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=1500
————————————————————–

[root@CentOS64VM mysql]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL…….. SUCCESS!
[root@CentOS64VM mysql]#

mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 1500 |
| performance_schema_events_waits_history_size | 20 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.01 sec)

mysql>

テーブルが幾つかあるので、I/O関連のテーブルを2つだけ確認してみました。

file_summary_by_instance
File events summarized per file instance

SELECT SUBSTRING_INDEX(FILE_NAME, '/', -2) AS DATA_FILE, 
       COUNT_READ, 
       IFNULL(ROUND(SUM_NUMBER_OF_BYTES_READ/1024/1024/1024, 2), 0.00) AS READ_GB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ/COUNT_READ)/1024, 2), 0.00) AS AVG_READ_KB,
       COUNT_WRITE, 
       IFNULL(ROUND(SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024, 2), 0.00) AS WRITE_GB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_WRITE/COUNT_WRITE)/1024, 2), 0.00) AS AVG_WRITE_KB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE)/1024/1024/1024, 2), 0.00) AS TOTAL_GB, 
       IFNULL(100-((SUM_NUMBER_OF_BYTES_READ/(SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE))*100), 0.00) AS WRITE_PCT 
  FROM file_summary_by_instance 
 ORDER BY TOTAL_GB DESC LIMIT 10;

file_summary_by_instance
file_summary_by_instance

events_waits_summary_global_by_event_name
Wait events summarized per event name

SELECT EVENT_NAME,
       SUM_TIMER_WAIT/1000000000 WAIT_MS,
       COUNT_STAR
  FROM performance_schema.events_waits_summary_global_by_event_name 
 ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;

events_waits_summary_global_by_event_name

参考
MySQL 5.5新機能徹底解説
Tracking IO with PERFORMANCE_SCHEMA
Monitoring MySQL IO Latency with performance_schema
Tracking mutex locks in a process list, MySQL 5.5′s PERFORMANCE_SCHEMA
21.2.2. Performance Schema Startup Configuration
21.8. Performance Schema System Variables


tpcc-mysqlを利用してのMYSQLベンチマーク

MS SQLのTPCCベンチマークをMS調布オフィスにて検証させて頂いた事があるが、
MYSQLにもTPCCベンチマークツールがあるのを聞いたので検証してみました。
※perconaが提供しているMYSQLのTPCCベンチマークツールです。

BZRが必要という事なのでインストール

[root@CentOS64VM ~]# yum install bzr
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
 * base: www.ftp.ne.jp
 * extras: www.ftp.ne.jp
 * updates: www.ftp.ne.jp
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package bzr.x86_64 0:2.1.1-2.el6 will be installed
--> Processing Dependency: python-paramiko for package: bzr-2.1.1-2.el6.x86_64
--> Running transaction check
---> Package python-paramiko.noarch 0:1.7.5-2.1.el6 will be installed
--> Processing Dependency: python-crypto >= 1.9 for package: python-paramiko-1.7.5-2.1.el6.noarch
--> Running transaction check
---> Package python-crypto.x86_64 0:2.0.1-22.el6 will be installed
--> Finished Dependency Resolution


====================================================================================
Install       3 Package(s)

Total download size: 6.2 M
Installed size: 32 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 6.2 M
(1/3): bzr-2.1.1-2.el6.x86_64.rpm                | 5.3 MB     00:02
(2/3): python-crypto-2.0.1-22.el6.x86_64.rpm     | 159 kB     00:00
(3/3): python-paramiko-1.7.5-2.1.el6.noarch.rpm  | 728 kB     00:00
------------------------------------------------------------------------------------
====================================================================================

bazaarフォルダーを作成してtpcc-mysqlのソースをダウンロード

[root@CentOS64VM tools]# mkdir bazaar
[root@CentOS64VM tools]# ls -l
合計 59468
-r--r--r--. 1 root root 60883070  6月  9 10:59 2012 VMwareTools-8.8.4-743747.tar.gz
drwxr-xr-x. 2 root root     4096  7月 16 12:47 2012 bazaar
drwxr-xr-x. 7 root root     4096  6月  9 10:59 2012 vmware-tools-distrib
[root@CentOS64VM tools]#


[root@CentOS64VM bazaar]# bzr init
Created a standalone tree (format: 2a)
[root@CentOS64VM bazaar]# ls -la
合計 12
drwxr-xr-x. 3 root root 4096  7月 16 12:48 2012 .
drwxr-xr-x. 4 root root 4096  7月 16 12:47 2012 ..
drwxr-xr-x. 6 root root 4096  7月 16 12:48 2012 .bzr
[root@CentOS64VM bazaar]#

[root@CentOS64VM bazaar]# bzr branch lp:~percona-dev/perconatools/tpcc-mysql
You have not informed bzr of your Launchpad ID, and you must do this to
write to Launchpad or access private data.  See "bzr help launchpad-login".
Branched 42 revision(s).
[root@CentOS64VM bazaar]#


[root@CentOS64VM bazaar]# ls
tpcc-mysql
[root@CentOS64VM bazaar]# cd tpcc-mysql/
[root@CentOS64VM tpcc-mysql]# ls -l
合計 36
-rw-r--r--. 1 root root  749  7月 16 12:50 2012 README
-rw-r--r--. 1 root root 1621  7月 16 12:50 2012 add_fkey_idx.sql
-rw-r--r--. 1 root root  317  7月 16 12:50 2012 count.sql
-rw-r--r--. 1 root root 3105  7月 16 12:50 2012 create_table.sql
-rw-r--r--. 1 root root  763  7月 16 12:50 2012 drop_cons.sql
-rw-r--r--. 1 root root  477  7月 16 12:50 2012 load.sh
drwxr-xr-x. 2 root root 4096  7月 16 12:50 2012 schema2
drwxr-xr-x. 5 root root 4096  7月 16 12:50 2012 scripts
drwxr-xr-x. 2 root root 4096  7月 16 12:50 2012 src
[root@CentOS64VM tpcc-mysql]#

MYSQL関連ファイルのインストール
※バイナリー(Linux – Generic 2.6 (x86, 64-bit), Compressed TAR Archive)
を利用して予めMYSQLを設定したのですが、TPCCの検証でセグメントエラーが出てしまったので
MYSQLも検証の為だけに一度RPMからインストールしました。↓

【課題】
自分でMYSQLSERVER(コンパイル済みバイナリー)を設定して、
他のMYSQLパッケージをRPMでインストールした場合、実際に負荷をかけるtpcc_startの処理が失敗した。
エラーは、セグメンテーション違反(英語:segmentation fault)。
tpcc_loadの時は、Socket接続エラーが出たのでシンボリックリンクを作成して対応。
「ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock」
しかし、tpcc_startは失敗してしまったので、結局MYSQL ServerもRPMからインストールしたら問題無く処理された。
rpm -U --force MySQL-server-5.5.25a-1.linux2.6.x86_64.rpm
パスだけの問題?後日確認する。

[root@CentOS64VM tools]# ls -l MySQL-*
-rw-rw-r--. 1 root root 17644285  7月 16 13:54 2012 MySQL-client-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root  6460556  7月 16 13:00 2012 MySQL-devel-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root 67059427  7月 16 14:02 2012 MySQL-embedded-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root 53387152  7月 16 16:37 2012 MySQL-server-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root  2010686  7月 16 14:01 2012 MySQL-shared-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root  5175183  7月 16 14:02 2012 MySQL-shared-compat-5.5.25a-1.linux2.6.x86_64.rpm
-rw-rw-r--. 1 root root 41228968  7月 16 14:02 2012 MySQL-test-5.5.25a-1.linux2.6.x86_64.rpm
[root@CentOS64VM tools]#

MYSQL関連のパッケージをインストールしたので、TPCC-MYSQLのコンパイル

———————————————————————————
README
———————————————————————————

[root@CentOS64VM tpcc-mysql]# cat README
1. Build binaries
   * cd scr ; make
   ( you should have mysql_config available in $PATH)

2. Load data
   * create database
     mysqladmin create tpcc1000
   * create tables
     mysql tpcc1000 < create_table.sql
   * create indexes and FK ( this step can be done after loading data)
     mysql tpcc1000 < add_fkey_idx.sql
   * populate data
     - simple step
       tpcc_load localhost tpcc1000 root "" 1000
                 |hostname| |dbname| |user| |password| |WAREHOUSES|
     - load data in parallel
       check load.sh script

3. start benchmark
   * tpcc_start localhost tpcc1000 root "" 1000 32 10 10800
                |hostname| |dbname| |user| |password| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|


&#91;root@CentOS64VM tpcc-mysql&#93;#


&#91;root@CentOS64VM src&#93;# pwd
/home/tools/bazaar/tpcc-mysql/src
&#91;root@CentOS64VM src&#93;# make all
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O2 -g -I. `mysql_config --include`  -c main.c
cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
&#91;root@CentOS64VM src&#93;#
&#91;/SHELL&#93;

<a href="http://variable.jp/wp-content/uploads/2012/07/tpcc-mysql.jpg"><img src="http://variable.jp/wp-content/uploads/2012/07/tpcc-mysql.jpg" alt="tpcc-mysql" title="tpcc-mysql" width="735" height="498" /></a>

<strong>tpcc_loadがデータ生成プログラム</strong>
<strong>tpcc_startが負荷生成プログラム</strong>

[SHELL]
[root@CentOS64VM tpcc-mysql]# pwd
/home/tools/bazaar/tpcc-mysql
[root@CentOS64VM tpcc-mysql]# ls -l
合計 248
-rw-r--r--. 1 root root    749  7月 16 12:50 2012 README
-rw-r--r--. 1 root root   1621  7月 16 12:50 2012 add_fkey_idx.sql
-rw-r--r--. 1 root root    317  7月 16 12:50 2012 count.sql
-rw-r--r--. 1 root root   3105  7月 16 12:50 2012 create_table.sql
-rw-r--r--. 1 root root    763  7月 16 12:50 2012 drop_cons.sql
-rw-r--r--. 1 root root    477  7月 16 12:50 2012 load.sh
drwxr-xr-x. 2 root root   4096  7月 16 12:50 2012 schema2
drwxr-xr-x. 5 root root   4096  7月 16 12:50 2012 scripts
drwxr-xr-x. 2 root root   4096  7月 16 14:09 2012 src
-rwxr-xr-x. 1 root root  60273  7月 16 14:08 2012 tpcc_load
-rwxr-xr-x. 1 root root 153141  7月 16 14:09 2012 tpcc_start
[root@CentOS64VM tpcc-mysql]#

load

TPCC用のDBとユーザーの作成


mysql> create database tpcc;
Query OK, 1 row affected (0.01 sec)

mysql> grant all privileges on tpcc.* to tpcc@'%' identified by 'tpcc-mysql-pass';
Query OK, 0 rows affected (0.14 sec)

mysql>


[root@CentOS64VM tpcc-mysql]# mysql -u tpcc -p tpcc < create_table.sql
Enter password:
&#91;root@CentOS64VM tpcc-mysql&#93;# mysql -u tpcc -p tpcc < add_fkey_idx.sql
Enter password:
&#91;root@CentOS64VM tpcc-mysql&#93;#
&#91;/SHELL&#93;

<a href="http://variable.jp/wp-content/uploads/2012/07/tpcc-create-tables.jpg"><img src="http://variable.jp/wp-content/uploads/2012/07/tpcc-create-tables.jpg" alt="table" title="tpcc-create-tables" width="603" height="298" /></a>

<a href="http://variable.jp/wp-content/uploads/2012/07/tpcc-tables.jpg"><img src="http://variable.jp/wp-content/uploads/2012/07/tpcc-tables.jpg" alt="tables" title="tpcc-tables" width="494" height="296" /></a>
<strong>検証用データのロード</strong>

[SHELL]
[root@CentOS64VM tpcc-mysql]# ./tpcc_load
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************

 usage: tpcc_load [server] [DB] [user] [pass] [warehouse]
      OR
        tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

           * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
[root@CentOS64VM tpcc-mysql]#

tpcc_loadにはwarehouseというパラメータを付与する。
warehouse表のレコード数になります。
warehouse表のレコード数によってその他のテーブルのレコード数が決まるので、
この値がスケールファクターになります。
1 warehouse(以下WH)あたりデータベース容量が約100MB増加するとの事。


[root@CentOS64VM tpcc-mysql]# ./tpcc_load localhost tpcc tpcc tpcc-mysql-pass 2
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************
<Parameters>
     [server]: localhost
     [port]: 3306
     [DBname]: tpcc
       [user]: tpcc
       [pass]: tpcc-mysql-pass
  [warehouse]: 2
TPCC Data Load Started...
Loading Item
.................................................. 5000
.................................................. 10000
.................................................. 15000
.................................................. 20000
.................................................. 25000
.................................................. 30000

[省略]

Orders Done.
Loading Orders for D=8, W= 2
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=9, W= 2
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=10, W= 2
.......... 1000
.......... 2000
.......... 3000
Orders Done.

...DATA LOADING COMPLETED SUCCESSFULLY.
[root@CentOS64VM tpcc-mysql]#

load data

tpcc_startを実行して負荷をかけ、実際のベンチマーク開始。
※データを更新したりするので、数回実行するのであればバックアップを取得しておいた方が良い。

[root@CentOS64VM tpcc-mysql]$ ./tpcc_start -h
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
./tpcc_start: option requires an argument -- 'h'
Usage: tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file
[root@CentOS64VM tpcc-mysql]$ 


[root@CentOS64VM tpcc-mysql]# ./tpcc_start -h localhost -d tpcc -u tpcc -p tpcc-mysql-pass -w 2 -c 3 -r 60 -l 120
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value 'localhost'
option d with value 'tpcc'
option u with value 'tpcc'
option p with value 'tpcc-mysql-pass'
option w with value '2'
option c with value '3'
option r with value '60'
option l with value '120'
<Parameters>
     [server]: localhost
     [port]: 3306
     [DBname]: tpcc
       [user]: tpcc
       [pass]: tpcc-mysql-pass
  [warehouse]: 2
 [connection]: 3
     [rampup]: 60 (sec.)
    [measure]: 120 (sec.)


[省略]

STOPPING THREADS...

<Raw Results>
  [0] sc:1157  lt:891  rt:0  fl:0
  [1] sc:2047  lt:2  rt:0  fl:0
  [2] sc:206  lt:0  rt:0  fl:0
  [3] sc:205  lt:0  rt:0  fl:0
  [4] sc:148  lt:57  rt:0  fl:0
 in 120 sec.

<Raw Results2(sum ver.)>
  [0] sc:1157  lt:891  rt:0  fl:0
  [1] sc:2047  lt:2  rt:0  fl:0
  [2] sc:206  lt:0  rt:0  fl:0
  [3] sc:205  lt:0  rt:0  fl:0
  [4] sc:148  lt:57  rt:0  fl:0

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.37% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 56.49%  [NG] *
        Payment: 99.90%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 72.20%  [NG] *

<TpmC>
                 1024.000 TpmC
[root@CentOS64VM tpcc-mysql]#

tpcc結果

参考サイト:
tpcc-mysqlによるMySQLのベンチマーク
セグメンテーション違反


Windows7にてLinux環境を利用する為に、XPと同様にCoLinuxをインストールしようと思いましたが、
64bitにはインストール出来ないようでしたので、同じく無償で利用出来るVMWare Playerをインストールしました。

VMware Player
仮想マシンを最も容易に実行する手段
http://www.vmware.com/jp/products/desktop_virtualization/player/overview.html

VMware Player 製品ダウンロード センター

VMware-player-4.0.4-744019.exeの実行とインストール
install

インストールはシンプルで直ぐに終わります。
ディスク容量も必要なので、インストール先だけ予め決めておくと良いかと思います。

OSイメージのISOファイルは以下のCentOSのページからダウンロード
CentOS ISO Image
http://isoredirect.centos.org/centos/6/isos/x86_64/
Index of /pub/linux/centos/6.3/isos/x86_64

ISOイメージをダウンロードし終わったら、仮想システムのインストールを継続します。

ISOファイルの場所を指定
VM

OS
VM

メモリー、パーティションサイズの指定
MEM

基本設定完了と起動イメージ(ISO)の指定してOS設定直前まで完了
基本設定

ISO

イメージから起動してCentOSのインストール開始
インストール

インストール完了
Finish

CentOSをインストールして起動
CentOS

VMWARE TOOLSをCD MOUTしてファイルを取得してインストール。
VMwareTools-8.8.4-743747.tar.gz

[root@CentOS64VM vmware-tools-distrib]# ls -l
合計 244
-rw-r–r–. 1 root root 226388 6月 9 10:59 2012 FILES
lrwxrwxrwx. 1 root root 13 7月 15 21:51 2012 INSTALL -> ./doc/INSTALL
drwxr-xr-x. 2 root root 4096 6月 9 10:59 2012 bin
drwxr-xr-x. 2 root root 4096 6月 9 10:59 2012 doc
drwxr-xr-x. 4 root root 4096 6月 9 10:59 2012 etc
drwxr-xr-x. 2 root root 4096 6月 9 10:59 2012 installer
drwxr-xr-x. 16 root root 4096 6月 9 10:59 2012 lib
lrwxrwxrwx. 1 root root 31 7月 15 21:51 2012 vmware-install.pl -> ./bin/vmware-uninstall-tools.pl
[root@CentOS64VM vmware-tools-distrib]#

NAT設定で利用していたので、Internetコネクションを共有するNICを指定して
Windows7側で設定してあげる。(Colinuxと同様)Windows側とLinux側のセグメントを合わせてあげてから、
/etc/sysconfig/network-scripts/ifcfg-eth0ファイルの設定を変更してあげたら無事に利用する事が出来ました。

DEVICE=”eth0″
BOOTPROTO=”dhcp”
HWADDR=”xx:xx:xx:xx:xx:xx”
NM_CONTROLLED=”yes”
ONBOOT=”on”

参考にさせて頂いたサイト
http://gendosu.jp/?p=57
http://blog.asial.co.jp/691


FREE、TOPなどでもシステム状況を把握して問題解決やリソースのプランニングが可能だが、
他にもいくつか参考になるコマンドがあるのでメモ。

CPU使用率TOP10
ps auxw | sort -k3 -nr | head -n 10
cpu利用率

MEM使用率TOP10
ps auxw | sort -k4 -nr | head -n 10
mem利用率

パフォーマンス確認に便利なdstat
Usage: dstat [-afv] [options..] [delay [count]]

インストール
[root@ip-xx-xxx-xx-xxx ec2-user]# yum install dstat
Loaded plugins: fastestmirror, priorities, security, update-motd
Loading mirror speeds from cached hostfile
* amzn-main: packages.ap-northeast-1.amazonaws.com
* amzn-updates: packages.ap-northeast-1.amazonaws.com
amzn-main | 2.1 kB 00:00
amzn-updates | 2.3 kB 00:00
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package dstat.noarch 0:0.7.0-1.5.amzn1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================
Package Arch Version Repository Size
=======================================================================================
Installing:
dstat noarch 0.7.0-1.5.amzn1 amzn-main 182 k

Transaction Summary
=======================================================================================
Install 1 Package(s)

Total download size: 182 k
Installed size: 660 k
Is this ok [y/N]: y
Downloading Packages:
dstat-0.7.0-1.5.amzn1.noarch.rpm | 182 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : dstat-0.7.0-1.5.amzn1.noarch 1/1

Installed:
dstat.noarch 0:0.7.0-1.5.amzn1

Complete!
[root@ip-xx-xxx-xx-xxx ec2-user]#

dstat –top-bioで最もブロックI/Oしているプロセス確認。
dstat –top-ioで最もI/Oが多いプロセスを確認。

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-bio
—-most-expensive—-
block i/o process
init 66k 726B

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-io
—-most-expensive—-
i/o process
bash 82k 1351B
sshd: ec2-u 146B 248B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B

dstat –tcp でTCPコネクション確認
dstat –net でネットワーク確認

[ip-xx-xxx-xx-xxx]#dstat --tcp --net

net状態

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-cpu
-most-expensive-
cpu process
httpd 0.1
mysqld 1.0

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --net
-net/total-
recv send
0 0
80B 308B
40B 260B
40B 260B
40B 260B
40B 260B
5925B 35k
1710B 24k
4120B 37k
256B 298B
40B 154B
[root@ip-xx-xxx-xx-xxx ec2-user]#

CPUとネットの状況を同時に確認

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat -a
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 0 98 0 0 0| 92k 12k| 0 0 | 0 0 | 36 66
0 0 100 0 0 0| 0 0 | 80B 916B| 0 0 | 20 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 18 44
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 18 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 20 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 19 47
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 16 42
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 19 46
1 0 99 0 0 0| 0 0 | 40B 452B| 0 0 | 18 43
[root@ip-xx-xxx-xx-xxx ec2-user]#

-a, --all equals -cdngy (default)
all

dtatオプション

[ip-xx-xxx-xx-xxx]#dstat –help
Usage: dstat [-afv] [options..] [delay [count]]
Versatile tool for generating system resource statistics

Dstat options:
-c, –cpu enable cpu stats
-C 0,3,total include cpu0, cpu3 and total
-d, –disk enable disk stats
-D total,hda include hda and total
-g, –page enable page stats
-i, –int enable interrupt stats
-I 5,eth2 include int5 and interrupt used by eth2
-l, –load enable load stats
-m, –mem enable memory stats
-n, –net enable network stats
-N eth1,total include eth1 and total
-p, –proc enable process stats
-r, –io enable io stats (I/O requests completed)
-s, –swap enable swap stats
-S swap1,total include swap1 and total
-t, –time enable time/date output
-T, –epoch enable time counter (seconds since epoch)
-y, –sys enable system stats

–aio enable aio stats
–fs, –filesystem enable fs stats
–ipc enable ipc stats
–lock enable lock stats
–raw enable raw stats
–socket enable socket stats
–tcp enable tcp stats
–udp enable udp stats
–unix enable unix stats
–vm enable vm stats

–plugin-name enable plugins by plugin name (see manual)
–list list all available plugins

-a, –all equals -cdngy (default)
-f, –full automatically expand -C, -D, -I, -N and -S lists
-v, –vmstat equals -pmgdsc -D total

–bw, –blackonwhite change colors for white background terminal
–float force float values on screen
–integer force integer values on screen
–nocolor disable colors (implies –noupdate)
–noheaders disable repetitive headers
–noupdate disable intermediate updates
–output file write CSV output to file

delay is the delay in seconds between each update (default: 1)
count is the number of updates to display before exiting (default: unlimited)

[ip-xx-xxx-xx-xxx]#

上記オプションを適切に使い分けて、最適なシステム状況診断と
対応が出来るようにしておくと良さそうです。

dstat --cpu --mem --disk --page --int --load --net
general

dstat -N eth0
interface

dstat --time --proc --swap --sys --ipc
date

dstat --full
full

dstat --vmstat
vmstat


--integer 整数値を表示します
--nocolor カラー表示を無効にします
--noheaders ヘッダー表示を無効にします
--noupdate 仲介更新を無効にします
--output file 出力結果を CSV ファイルに書き出します

参考
Dstat: Versatile resource statistics tool


EC2で利用しているMYSQLのバージョンが低いので、
バグ対応とサイトレスポンス向上の為に5.5.25aにアップグレード。

mysql> select@@version;
+———–+
| @@version |
+———–+
| 5.5.20 |
+———–+
1 row in set (0.00 sec)

[ec2-user@ip-xx-xxx-xx-xxx src]$ uname -a
Linux ip-xx-xxx-xx-xxx x.x.xx-x.x.x.amzn1.x86_64 #1 SMP Thu Mar 22 08:00:08 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[ec2-user@ip-xx-xxx-xx-xxx src]$

MySQL 5.5.25a

D.1.2. Changes in MySQL 5.5.25a (2012-07-05)
Note

Due to MSI restrictions, the MSI packages of MySQL 5.5.25a will treat the version as 5.5.26 internally;
for example, as displayed by the Installation Wizard. MySQL itself reports the version as 5.5.25a;
for example, if you check the value of the VERSION() SQL function or the version system variable.

Bugs Fixed
A regression bug in the optimizer could cause excessive disk usage for UPDATE statements. (Bug #65745, Bug #14248833)

MySQL 5.5.25

D.1.3. Changes in MySQL 5.5.25 (2012-05-30)
Note

MySQL 5.5.25 is superseded by MySQL 5.5.25a due to a regression

    bug that can cause excessive disk usage

(for details, see Bug #65745). Current users of 5.5.25: Monitor disk usage and upgrade to 5.5.25a
as soon as that is made available. Users contemplating upgrades to 5.5.25: Defer and upgrade to 5.5.25a
instead when that is made available.

Functionality Added or Changed

Important Change: Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS)
may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege
was necessary to use either form of this statement.)
The –safe-mode server option now is deprecated and will be removed in MySQL 5.6.

Bugs Fixed

Performance: InnoDB:

    Improved the algorithm related to adaptive flushing.

This fix increases the rate of flushing in cases where compression is used and the
data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061)

InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE
statement for an InnoDB table could sometimes overlook rows recently committed by other
transactions. As explained in Section 14.3.9.2, “Consistent Nonlocking Reads”,
DML statements within a REPEATABLE READ transaction apply to rows committed by
other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)

InnoDB:

    The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be.

Its value should never exceed the value of Innodb_pages_written. (Bug #14000361, Bug #65030)

InnoDB: The error handling and message was improved for attempting to create a foreign key with
a column referencing itself. The message suggested a potential problem with the data dictionary,
when no such problem existed. (Bug #12902967)

InnoDB:

    The CHECK TABLE statement could fail for a large InnoDB table due to a timeout value of 2 hours.

For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB,
depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the
timeout less likely. It also makes InnoDB recognize the syntax CHECK TABLE QUICK, which avoids the
possibility of the timeout entirely. (Bug #11758510, Bug #50723)

Replication: It was theoretically possible for concurrent execution of more than one instance of
SHOW BINLOG EVENTS to crash the MySQL Server. (Bug #13979418)

Replication: Statements using AUTO_INCREMENT, LAST_INSERT_ID(), RAND(),
or user variables could be applied in the wrong context on the slave when
using statement-based replication and replication filtering server options
(see Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”). (Bug #11761686, Bug #54201)

References: See also Bug #11754117, Bug #45670, Bug #11746146, Bug #23894.

Replication: An INSERT into a table that has a composite primary key that includes an
AUTO_INCREMENT column that is not the first column of this composite key is not safe for
statement-based binary logging or replication. Such statements are now marked as unsafe
and fail with an error when using the STATEMENT binary logging format.
For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”,
as well as Section 16.4.1.1, “Replication and AUTO_INCREMENT”. Note

Tables using the InnoDB storage engine are not affected by this issue,
since InnoDB does not allow the creation of a composite key that includes an AUTO_INCREMENT column,
where this column is not the first column in the key.
(Bug #11754117, Bug #45670)
References: See also Bug #11761686, Bug #54201, Bug #11746146, Bug #23894.

SHOW TABLES was very slow unless the required information was already in the disk cache. (Bug #60961, Bug #12427262)

Download(最新のMYSQLをダウンロード)
http://dev.mysql.com/downloads/mysql/5.5.html#downloads

[root@ip-xx-xxx-xx-xxx src]# ls -l
total 181992
-rw-rw-r– 1 ec2-user ec2-user 186355822 Jul 7 08:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
[root@ip-xx-xxx-xx-xxx src]# tar zxvf mysql-5.5.25a-linux2.6-x86_64.tar.gz
mysql-5.5.25a-linux2.6-x86_64/docs/mysql.info
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_SRC
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_BIN
mysql-5.5.25a-linux2.6-x86_64/docs/ChangeLog
mysql-5.5.25a-linux2.6-x86_64/COPYING
mysql-5.5.25a-linux2.6-x86_64/README
mysql-5.5.25a-linux2.6-x86_64/INSTALL-BINARY
mysql-5.5.25a-linux2.6-x86_64/bin/myisam_ftdump
mysql-5.5.25a-linux2.6-x86_64/bin/myisamchk
mysql-5.5.25a-linux2.6-x86_64/bin/myisamlog
[省略]
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolve_stack_dump.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysqlman.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolveip.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysql-stress-test.pl.1
mysql-5.5.25a-linux2.6-x86_64/man/man8/mysqld.8
[root@ip-xx-xxx-xx-xxx src]#

[root@ip-xx-xxx-xx-xxx src]# mv mysql-5.5.25a-linux2.6-x86_64 /usr/local/
[root@ip-xx-xxx-xx-xxx src]# cd /usr/local/
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

シンボリックリンク作成
[root@ip-xx-xxx-xx-xxx local]# ln -s mysql-5.5.25a-linux2.6-x86_64/ mysql
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
lrwxrwxrwx 1 root root 30 Jul 7 09:30 mysql -> mysql-5.5.25a-linux2.6-x86_64/
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

既存MYSQLを停止
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

古いMYSQLのデータの場所を確認
[root@ip-xx-xxx-xx-xxx local]# cat /etc/my.cnf | grep data
datadir=/var/lib/mysql
[root@ip-xx-xxx-xx-xxx local]#

データベースデータを新しいMYSQLのデータフォルダーにコピー
[root@ip-xx-xxx-xx-xxx data]# cp -rp /var/lib/mysql/* /usr/local/mysql/data
[root@ip-xx-xxx-xx-xxx data]# ls -l
total 36892
-rw-rw—- 1 mysql mysql 27262976 Jul 7 09:33 ibdata1
-rw-rw—- 1 mysql mysql 5242880 Jul 7 09:33 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Jun 7 02:13 ib_logfile1
-rw-rw—- 1 mysql root 11922 Mar 31 15:37 ip-xx-xxx-xx-xxx.err
drwx—— 2 mysql mysql 4096 Mar 31 15:54 mysql
drwx—— 2 mysql mysql 4096 Mar 31 15:54 performance_schema
drwx—— 2 mysql mysql 4096 Mar 31 15:14 test
drwx—— 2 mysql mysql 4096 Jun 15 16:55 WP01
[root@ip-xx-xxx-xx-xxx data]#

MYSQL定義ファイルをバックアップ
[root@ip-xx-xxx-xx-xxx support-files]# mv /etc/my.cnf /etc/my.cnf.20120707
[root@ip-xx-xxx-xx-xxx support-files]#

新しくMYSQL定義ファイルをコピー
[root@ip-xx-xxx-xx-xxx support-files]# cp -p my-small.cnf /etc/my.cnf
[root@ip-xx-xxx-xx-xxx support-files]#

データフォルダー、Socketなどの場所を編集
[root@ip-xx-xxx-xx-xxx support-files]# vi /etc/my.cnf

新しいMYSQLのデータフォルダーなどに書き込めるようにオーナー変更
[root@ip-xx-xxx-xx-xxx local]# chown -R mysql:mysql mysql-5.5.25a-linux2.6-x86_64/
[root@ip-xx-xxx-xx-xxx local]#

新しいMYSQLを起動
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

システムデータベースのアップグレード
[root@ip-xx-xxx-xx-xxx data]# /usr/local/mysql/bin/mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: /usr/local/mysql/bin/mysql
Looking for ‘mysqlcheck’ as: /usr/local/mysql/bin/mysqlcheck
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
WP01.wp01_commentmeta OK
WP01.wp01_comments OK
WP01.wp01_geo_mashup_administrative_names OK
WP01.wp01_geo_mashup_location_relationships OK
WP01.wp01_geo_mashup_locations OK
WP01.wp01_links OK
WP01.wp01_mappress_maps OK
WP01.wp01_mappress_posts OK
WP01.wp01_options OK
WP01.wp01_postmeta OK
WP01.wp01_posts OK
WP01.wp01_searchmeter OK
WP01.wp01_searchmeter_recent OK
WP01.wp01_term_relationships OK
WP01.wp01_term_taxonomy OK
WP01.wp01_terms OK
WP01.wp01_usermeta OK
WP01.wp01_users OK
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
Running ‘mysql_fix_privilege_tables’…
OK
[root@ip-xx-xxx-xx-xxx data]#

サービス起動ファイルの変更
※通常は、そのままにしてあるが前回はRPMでインストールして今回はTARから展開して利用しているので
 SocketやPIDを変更するのが手間なので、そのままコピー

[root@ip-xx-xxx-xx-xxx local]# cd support-files/
[root@ip-xx-xxx-xx-xxx support-files]# pwd
/usr/local/mysql/support-files
[root@ip-xx-xxx-xx-xxx support-files]# cp -p mysql.server /etc/init.d/mysqld

mysql> select @@version;
+———–+
| @@version |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql> select version();
+———–+
| version() |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql>

MYSQL5.5.25a

アップグレードでinnodb_buffer_pool_sizeが多くなっていたので変更。

mysql> show variables like ‘%buffer%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———–+
14 rows in set (0.00 sec)

mysql> show variables like ‘%buffer%’;
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———-+
14 rows in set (0.00 sec)

mysql>

今回は久々にRPMでインストールしてあったMYSQLでアップグレードしたので、
/etc/init.d/mysqlの入れ替え、/etc/my.cnf、/etc/php.iniで少し時間かけてしまった。

PHP Socketの場所指定
MySQLに接続するときにエラー発生

パフォーマンスがいまいちなので、Wordpress用にQuery Cacheだけ設定しておいた。

mysql> SHOW VARIABLES LIKE ‘%query%’;
+——————————+————————————————-+
| Variable_name | Value |
+——————————+————————————————-+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/ip-xx-xxx-xx-xxx-slow.log |
+——————————+————————————————-+