[root@cluster1 ~]# pt-mysql-summary -password password # Percona Toolkit MySQL Summary Report ####################### System time | 2013-11-10 14:13:27 UTC (local TZ: GMT +0000) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== /data/mysql/ 0 0 /data/mysql/mysql.sock # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | No # Report On Port 3306 ######################################## User | root@localhost Time | 2013-11-10 14:13:26 (GMT) Hostname | cluster1 Version | 5.5.34-log Percona XtraDB Cluster (GPL), wsrep_23.7.6.r3936 Built On | Linux i686 Started | 2013-11-10 14:03 (up 0+00:09:58) Databases | 4 Datadir | /data/mysql/ Processes | 3 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | /data/mysql/mysql.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 Sleep 2 0 1250 600 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 1 1 0 0 system user 2 0 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 2 0 0 0 localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 3 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 0 0 0 sleeping 1 1 0 0 wsrep aborter idle 1 0 0 0 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 12 secs Aborted_connects 300 Bytes_received 900000 10 175 Bytes_sent 17500000 200 2000 Com_select 7000 1 Com_show_binlogs 300 Com_show_databases 300 Com_show_engine_status 300 Com_show_master_status 300 Com_show_plugins 300 Com_show_processlist 300 Com_show_slave_status 300 Com_show_status 700 Com_show_variables 450 Connections 6000 1 Created_tmp_disk_tables 3000 Created_tmp_files 900 Created_tmp_tables 20000 5 Flush_commands 150 Handler_read_first 450 Handler_read_rnd_next 450000 5 40 Handler_write 450000 5 40 Innodb_adaptive_hash_cells 300000000 3500 Innodb_adaptive_hash_non_hash_searches 2500 Innodb_background_log_sync 150 Innodb_buffer_pool_bytes_data 700000000 8000 Innodb_buffer_pool_bytes_dirty 2250000 25 Innodb_buffer_pool_read_requests 150000 1 Innodb_buffer_pool_reads 40000 Innodb_buffer_pool_write_requests 150 Innodb_checkpoint_age 22500 Innodb_checkpoint_max_age 15000000000 175000 Innodb_checkpoint_target_age 15000000000 175000 Innodb_data_fsyncs 450 Innodb_data_read 1000000000 12500 Innodb_data_reads 45000 Innodb_data_writes 450 Innodb_data_written 225000 2 Innodb_dict_tables 1250 Innodb_history_list_length 2250 Innodb_ibuf_segment_size 300 Innodb_ibuf_size 150 Innodb_log_writes 150 Innodb_lsn_current 600000000 7000 Innodb_lsn_flushed 600000000 7000 Innodb_lsn_last_checkpoint 600000000 7000 Innodb_master_thread_1_second_loops 150 Innodb_master_thread_background_loops 150 Innodb_master_thread_main_flush_loops 150 Innodb_master_thread_sleeps 150 Innodb_max_trx_id 1250000 15 Innodb_mem_adaptive_hash 1250000000 15000 Innodb_mem_dictionary 300000000 3500 Innodb_mem_total 80000000000 900000 Innodb_oldest_view_low_limit_trx_id 1250000 15 Innodb_os_log_fsyncs 450 Innodb_os_log_written 70000 Innodb_pages_read 40000 Innodb_read_views_memory 9000 Innodb_descriptors_memory 1250000 15 Innodb_s_lock_os_waits 300 Innodb_s_lock_spin_rounds 9000 Innodb_s_lock_spin_waits 300 Open_table_definitions 5000 Opened_files 30000 5 Opened_table_definitions 5000 Opened_tables 5000 Queries 15000 3 Questions 15000 3 Select_scan 2250 Slow_queries 600 Table_locks_immediate 5000 Threads_created 450 Uptime 90000 1 1 wsrep_protocol_version 600 wsrep_last_committed 700000 8 wsrep_received 900 wsrep_received_bytes 150000 1 wsrep_local_state 600 wsrep_cluster_conf_id 700 wsrep_cluster_size 450 wsrep_local_index 300 # Table cache ################################################ Size | 100 Usage | 25% # Key Percona Server features ################################ Table & Index Stats | Disabled Multiple I/O Threads | Enabled Corruption Resilient | Enabled Durable Replication | Disabled Import InnoDB Tables | Disabled Fast Server Restarts | Disabled Enhanced Logging | Disabled Replica Perf Logging | Disabled Response Time Hist. | Disabled Smooth Flushing | Enabled HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Unknown # Percona XtraDB Cluster ##################################### Cluster Name | data_cluster Cluster Address | gcomm:// Cluster Size | 3 Cluster Nodes | 192.168.0.102:3306,192.168.0.103:3306,192.168.0.101:3306 Node Name | cluster1 Node Status | Primary SST Method | xtrabackup-v2 Slave Threads | 1 Ignore Split Brain | false Ignore Quorum | false gcache Size | 128M gcache Directory | /data/mysql/ gcache Name | /data/mysql//galera.cache # Plugins #################################################### InnoDB compression | ACTIVE # Query cache ################################################ query_cache_type | OFF Size | 0.0 Usage | 0% HitToInsertRatio | 0% # Schema ##################################################### Would you like to mysqldump -d the schema and analyze it? y/n y There are 4 databases. Would you like to dump all, or just one? Type the name of the database, or press Enter to dump all of them. Database Tables Views SPs Trigs Funcs FKs Partn mysql 24 test 2 Database MyISAM CSV InnoDB mysql 22 2 test 2 Database BTREE mysql 31 test 1 c t s e l d i t s t b v b m t h i e n o a n i m e i a l e i a m t u n t t n a x g r o d m r e m g e y l t i c b i e s b t i l n h u t l i n i t a m a o m t n r t m b e t e p x t Database === === === === === === === === === === === === === === === mysql 59 10 6 78 5 4 26 3 3 6 8 5 4 4 2 test 2 # Noteworthy Technologies #################################### Full Text Indexing | No Geospatial Types | No Foreign Keys | No Partitioning | No InnoDB Compression | No SSL | No Explicit LOCK TABLES | No Delayed Insert | No XA Transactions | No NDB Cluster | No Prepared Statements | No Prepared statement count | 0 # InnoDB ##################################################### Version | 5.5.34-rel32.0 Buffer Pool Size | 512.0M Buffer Pool Fill | 0% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 64.0M = 128.0M Log Buffer Size | 8M Flush Method | O_DIRECT Flush Log At Commit | 1 XA Support | ON Checksums | ON Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 500 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 154 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 16 Read Views | 1 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites Pending I/O Flushes | 0 buf pool, 0 log Transaction States | 1xnot started # MyISAM ##################################################### Key Cache | 32.0M Pct Used | 10% Unflushed | 0% # Security ################################################### Users | 6 users, 0 anon, 0 w/o pw, 0 old pw Old Passwords | OFF # Binary Logging ############################################# Binlogs | 19 Zero-Sized | 0 Total Size | 10.9M binlog_format | ROW expire_logs_days | 14 sync_binlog | 1 server_id | 1 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 3/3 default_storage_engine | InnoDB flush_time | 0 init_connect | SET NAMES utf8 init_file | sql_mode | ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION join_buffer_size | 128k sort_buffer_size | 2M read_buffer_size | 128k read_rnd_buffer_size | 256k bulk_insert_buffer | 0.00 max_heap_table_size | 32M tmp_table_size | 32M max_allowed_packet | 16M thread_stack | 192k log | OFF log_error | /data/mysql/mysql-error.log log_warnings | 1 log_slow_queries | ON log_queries_not_using_indexes | ON log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/my.cnf [mysql] port = 3306 socket = /data/mysql/mysql.sock default-character-set = utf8 [client] socket = /data/mysql/mysql.sock default-character-set = utf8 [mysqld] user = mysql default_storage_engine = InnoDB socket = /data/mysql/mysql.sock pid_file = /data/mysql/mysql.pid key_buffer_size = 32M myisam_recover = FORCE,BACKUP max_allowed_packet = 16M max_connect_errors = 1000000 skip_name_resolve sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY sysdate_is_now = 1 innodb = FORCE innodb_strict_mode = 1 datadir = /data/mysql/ log_bin = /data/mysql/mysql-bin expire_logs_days = 14 sync_binlog = 1 tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 query_cache_size = 0 max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 table_definition_cache = 1024 table_open_cache = 100 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 512M log_error = /data/mysql/mysql-error.log log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /data/mysql/mysql-slow.log collation-server = utf8_unicode_ci init-connect = 'SET NAMES utf8' character-set-server = utf8 wsrep_provider = /usr/lib/libgalera_smm.so wsrep_cluster_address = gcomm://192.168.0.101,192.168.0.102,192.168.0.103 binlog_format = ROW innodb_locks_unsafe_for_binlog = 1 innodb_autoinc_lock_mode = 2 wsrep_node_address = 192.168.0.101 wsrep_sst_method = xtrabackup-v2 wsrep_cluster_name = data_cluster wsrep_sst_auth = "sstuser:password" # The End ####################################################