使ってみれば便利なPercona Serverも、導入前は結構慎重に検証した覚えがあります。
特にベンチマークをとって極端な性能劣化がないか、とか、設定やステータスの差異をたくさん調べました。今回はSHOWの差異について書いていきます。ほぼ本家MySQLとPercona Serverの情報を表示して比べただけの内容ですが、導入するか悩んでる方には参考になるかもしれません。
SHOW ENGINES
本家MySQLのdebパッケージとの比較してもInnoDB行のCommentしか差異がないのでPercona Serverだけ載せておきます。表示名はInnoDBでも中身はXtraDBであることを主張しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | !| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |
SHOW PLUGINS
InnoDBプラグインが16個追加されている以外は一緒です。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
mysql> SHOW PLUGINS; +--------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +--------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | +| INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +| XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +--------------------------------+----------+--------------------+---------+---------+ |
SHOW VARIABLES
主に追加された設定項目のリストで、少しだけDefault/Min値が変わったのがあるので備考を書いておきました。気になる項目は Percona Documentation のサイドバーから検索してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
mysql> SHOW GLOBAL VARIABLES; +-------------------------------------------+-----------------------------+ | Variable_name | Value | +-------------------------------------------+-----------------------------+ +| enforce_storage_engine | | +| expand_fast_index_creation | OFF | +| fast_index_creation | ON | +| have_response_time_distribution | YES | +| innodb_adaptive_flushing_method | keep_average | +| innodb_adaptive_hash_index_partitions | 1 | +| innodb_blocking_buffer_pool_restore | OFF | +| innodb_buffer_pool_restore_at_startup | 0 | +| innodb_buffer_pool_shm_checksum | ON | +| innodb_buffer_pool_shm_key | 0 | !| innodb_buffer_pool_size | 33554432 | Min 5M -> 32M +| innodb_checkpoint_age_target | 0 | +| innodb_corrupt_table_action | assert | +| innodb_dict_size_limit | 0 | +| innodb_doublewrite_file | | +| innodb_fake_changes | OFF | +| innodb_fast_checksum | OFF | +| innodb_flush_neighbor_pages | none | +| innodb_ibuf_accel_rate | 100 | +| innodb_ibuf_active_contract | 1 | +| innodb_ibuf_max_size | 16760832 | +| innodb_import_table_from_xtrabackup | 0 | +| innodb_kill_idle_transaction | 0 | +| innodb_lazy_drop_table | 0 | +| innodb_log_block_size | 4096 | +| innodb_merge_sort_block_size | 1048576 | +| innodb_page_size | 16384 | !| innodb_purge_threads | 1 | Default 0 -> 1 +| innodb_read_ahead | none | +| innodb_recovery_stats | OFF | +| innodb_recovery_update_relay_log | OFF | +| innodb_show_locks_held | 10 | +| innodb_show_verbose_locks | 0 | +| innodb_stats_auto_update | 1 | +| innodb_stats_update_need_lock | 1 | +| innodb_thread_concurrency_timer_based | OFF | +| innodb_use_global_flush_log_at_trx_commit | ON | +| innodb_use_sys_stats_table | OFF | +| log_slow_admin_statements | OFF | +| log_slow_filter | | +| log_slow_rate_limit | 1 | +| log_slow_rate_type | session | +| log_slow_slave_statements | OFF | +| log_slow_sp_statements | ON | +| log_slow_verbosity | microtime,query_plan,innodb | +| log_warnings_suppress | | +| optimizer_fix | ON | +| query_cache_strip_comments | OFF | +| query_response_time_range_base | 10 | +| query_response_time_stats | OFF | +| slow_query_log_timestamp_always | OFF | +| slow_query_log_timestamp_precision | second | +| slow_query_log_use_global_control | | +| thread_statistics | OFF | +-------------------------------------------+-----------------------------+ |
SHOW STATUS
こちらもたくさん追加されていて、InnoDBの新ステータスは Show InnoDB Status にページはあるのですが、ほとんどが説明はありません。例えばベンチマークをとりながら young/old 関係を見ていると、なるほどこういう意味か、とわかってきて便利ではあるのですが、確信して説明できるものじゃないので自己満足程度に留めています。。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
mysql> SHOW GLOBAL STATUS; +------------------------------------------+-------------+ | Variable_name | Value | +------------------------------------------+-------------+ +| Com_show_client_statistics | 0 | +| Com_show_index_statistics | 0 | +| Com_show_slave_status_nolock | 0 | +| Com_show_table_statistics | 0 | +| Com_show_temporary_tables | 0 | +| Com_show_thread_statistics | 0 | +| Com_show_user_statistics | 0 | +| Flashcache_enabled | OFF | +| Innodb_adaptive_hash_cells | 69221 | +| Innodb_adaptive_hash_heap_buffers | 0 | +| Innodb_adaptive_hash_hash_searches | 0 | +| Innodb_adaptive_hash_non_hash_searches | 26 | +| Innodb_background_log_sync | 1 | +| Innodb_buffer_pool_pages_LRU_flushed | 0 | +| Innodb_buffer_pool_pages_made_not_young | 0 | +| Innodb_buffer_pool_pages_made_young | 0 | +| Innodb_buffer_pool_pages_old | 0 | +| Innodb_checkpoint_age | 0 | +| Innodb_checkpoint_max_age | 169134982 | +| Innodb_checkpoint_target_age | 163849514 | +| Innodb_deadlocks | 0 | +| Innodb_dict_tables | 9 | +| Innodb_history_list_length | 7 | +| Innodb_ibuf_discarded_delete_marks | 0 | +| Innodb_ibuf_discarded_deletes | 0 | +| Innodb_ibuf_discarded_inserts | 0 | +| Innodb_ibuf_free_list | 0 | +| Innodb_ibuf_merged_delete_marks | 0 | +| Innodb_ibuf_merged_deletes | 0 | +| Innodb_ibuf_merged_inserts | 0 | +| Innodb_ibuf_merges | 0 | +| Innodb_ibuf_segment_size | 2 | +| Innodb_ibuf_size | 1 | +| Innodb_lsn_current | 2701785672 | +| Innodb_lsn_flushed | 2701785672 | +| Innodb_lsn_last_checkpoint | 2701785672 | +| Innodb_master_thread_1_second_loops | 1 | +| Innodb_master_thread_10_second_loops | 0 | +| Innodb_master_thread_background_loops | 1 | +| Innodb_master_thread_main_flush_loops | 1 | +| Innodb_master_thread_sleeps | 1 | +| Innodb_max_trx_id | 3328 | +| Innodb_mem_adaptive_hash | 557984 | +| Innodb_mem_dictionary | 183866 | +| Innodb_mem_total | 34471936 | +| Innodb_mutex_os_waits | 1 | +| Innodb_mutex_spin_rounds | 30 | +| Innodb_mutex_spin_waits | 1 | +| Innodb_oldest_view_low_limit_trx_id | 3328 | +| Innodb_purge_trx_id | 0 | +| Innodb_purge_undo_no | 0 | +| Innodb_current_row_locks | 0 | +| Innodb_s_lock_os_waits | 2 | +| Innodb_s_lock_spin_rounds | 60 | +| Innodb_s_lock_spin_waits | 2 | +| Innodb_x_lock_os_waits | 0 | +| Innodb_x_lock_spin_rounds | 0 | +| Innodb_x_lock_spin_waits | 0 | +| binlog_commits | 0 | +| binlog_group_commits | 0 | +------------------------------------------+-------------+ |
SHOW ENGINE INNODB STATUS
参考ページはこちらTRANSACTIONS
本番運用中だとTRANSACTIONSの表示が長くなりすぎて、途中で切れてそれ以降の項目を見ることができない場合がありましたが、Percona Serverでは最後尾に移動されたため、TRANSACTIONS以外が見れないということはなくなりました。TRANSACTION section was moved to the end of the output, so that important information is not overlooked when the there is a large amount of it.
LOG
チェックポイント関連が表示されるようになりました。|
1 2 3 4 5 6 7 8 9 10 11 12 |
--- LOG --- Log sequence number 2701785672 Log flushed up to 2701785672 Last checkpoint at 2701785672 +Max checkpoint age 169134982 +Checkpoint age target 163849514 +Modified age 0 +Checkpoint age 0 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second |
BUFFER POOL AND MEMORY
Internal hash tables と Buffer pool size, bytes が追加されています。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 34471936; in additional pool allocated 0 +Internal hash tables (constant factor + variable factor) + Adaptive hash index 557984 (553768 + 4216) + Page hash 35384 (buffer pool 0 only) + Dictionary cache 183866 (139952 + 43914) + File system 83992 (82672 + 1320) + Lock system 84192 (83816 + 376) + Recovery system 0 (0 + 0) Dictionary memory allocated 43914 Buffer pool size 2047 +Buffer pool size, bytes 33538048 Free buffers 1840 Database pages 207 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 207, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 207, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] |
ROW OPERATIONS
OLDEST VIEW項目が追加されました。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB + ---OLDEST VIEW--- + Normal read view + Read view low limit trx n:o 500 + Read view up limit trx id 500 + Read view low limit trx id 500 + Read view individually stored trx ids: + ----------------- Main thread process no. 5518, id 1199630672, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s |
普段はあまり関わらないものがほとんどですが、ベンチマークや本番で窮地に陥った時、原因の究明に役立つものも少なからずありますので、頭の片隅にでも置いとく程度に知っておくといつか幸せになれると思います。
