Si tratta di un rpm da installare su Linux:
Le dipendenze che ho trovato necessarie sono :
rpm -i perl-Net-SSLeay-1.35-9.el6.x86_64.rpm
rpm -i perl-Net-LibIDN-0.12-3.el6.x86_64.rpm
rpm -i perl-IO-Socket-SSL-1.31-2.el6.noarch.rpm
rpm -i percona-toolkit-2.2.6-1.noarch.rpm
Di seguito tutti gli eseguibili che vengono installati con l'rpm percona-toolkit:
pt-archiver
pt-collect
pt-config-diff
pt-deadlock-logger
pt-diskstats
pt-duplicate-key-checker
pt-fifo-split
pt-find
pt-fk-error-logger
pt-heartbeat
pt-index-usage
pt-kill
pt-log-player
pt-mext
pt-mysql-summary
pt-online-schema-change
pt-pmp
pt-query-advisor
pt-query-digest
pt-show-grants
pt-sift
pt-slave-delay
pt-slave-find
pt-slave-restart
pt-stalk
pt-summary
pt-table-checksum
pt-table-sync
pt-tcp-model
pt-trend
pt-upgrade
pt-variable-advisor
pt-visual-explain
Riporto come esempio l'output del comando "pt-mysql-summary" :
/usr/bin/pt-mysql-summary --user=root --password=xxxx
System time | 2014-01-09 15:10:53 UTC (local TZ: CET +0100)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /mysql/mysql5.6/data/data 0 0 /tmp/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | em_agent@localhost
Time | 2014-01-09 16:10:53 (CET)
Hostname | MysqlVm01
Version | 5.6.14-log MySQL Community Server (GPL)
Built On | linux-glibc2.5 x86_64
Started | 2014-01-09 15:47 (up 0+00:23:40)
Databases | 5
Datadir | /mysql/mysql5.6/data/data/
Processes | 4 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /mysql/mysql5.6/admin/MysqlVm01.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
Sleep 3 0 600 350
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
em_agent 2 1 0 0
root 2 0 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
10.5.10.155 2 0 0 0
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 2 0 0 0
NULL 2 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
3 0 0 0
init 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 11 secs
Aborted_clients 60
Aborted_connects 900
Bytes_received 6000000 80 700
Bytes_sent 300000000000 3500000 6000
Com_admin_commands 1500
Com_change_db 250
Com_help 1250
Com_select 17500 2
Com_set_option 900
Com_show_binlogs 125
Com_show_databases 250
Com_show_engine_status 1500
Com_show_fields 3500
Com_show_master_status 125
Com_show_processlist 125
Com_show_slave_status 1500
Com_show_status 3000
Com_show_table_status 175
Com_show_tables 175
Com_show_variables 3000
Connections 1500 1
Created_tmp_disk_tables 9000 1
Created_tmp_files 350
Created_tmp_tables 70000 6
Flush_commands 60
Handler_commit 60
Handler_external_lock 40000 1
Handler_read_first 700
Handler_read_key 4000
Handler_read_next 1500
Handler_read_rnd_next 4000000 45 150
Handler_rollback 60
Handler_write 1750000 20 100
Innodb_buffer_pool_bytes_data 500000000 6000
Innodb_buffer_pool_pages_flushed 60
Innodb_buffer_pool_read_requests 450000 4
Innodb_buffer_pool_reads 30000
Innodb_buffer_pool_write_requests 60
Innodb_data_fsyncs 300
Innodb_data_read 600000000 7000
Innodb_data_reads 30000
Innodb_data_writes 300
Innodb_data_written 2000000 25
Innodb_dblwr_pages_written 60
Innodb_dblwr_writes 60
Innodb_log_writes 60
Innodb_os_log_fsyncs 175
Innodb_os_log_written 30000
Innodb_pages_read 30000
Innodb_pages_written 60
Innodb_rows_read 2000000 20
Innodb_num_open_files 1750
Innodb_available_undo_logs 8000
Key_read_requests 7000
Key_reads 1750
Open_table_definitions 7000
Opened_files 60000 4
Opened_table_definitions 7000
Opened_tables 100000 1
Qcache_not_cached 17500 2
Queries 35000 5
Questions 35000 5
Select_full_join 700
Select_range 60
Select_scan 25000 1
Slow_queries 60
Table_locks_immediate 20000
Table_open_cache_hits 40000
Table_open_cache_misses 35000
Table_open_cache_overflows 25000
Threads_created 300
Uptime 90000 1 1
# Table cache ################################################
Size | 431
Usage | 25%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | OFF
Size | 1.0M
Usage | 1%
HitToInsertRatio | 0%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB #####################################################
# MyISAM #####################################################
Key Cache | 1.0G
Pct Used | 20%
Unflushed | 0%
# Security ###################################################
Users | 6 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Binary Logging #############################################
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
join_buffer_size | 256k
sort_buffer_size | 64k
read_buffer_size | 256k
read_rnd_buffer_size | 1M
bulk_insert_buffer | 0.00
max_heap_table_size | 16M
tmp_table_size | 16M
max_allowed_packet | 4M
thread_stack | 256k
log |
log_error | /mysql/mysql5.6/admin/MysqlVm01.err
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
# Configuration File #########################################
Config File | /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld_safe]
socket = /tmp/mysql.sock
nice = 0
log-error = /mysql/mysql5.6/admin/MysqlVm01.err
pid-file = /mysql/mysql5.6/admin/MysqlVm01.pid
[mysqld]
user = mysql
pid-file = /mysql/mysql5.6/admin/MysqlVm01.pid
socket = /tmp/mysql.sock
port = 3306
basedir = /usr/local/mysql
datadir = /mysql/mysql5.6/data/data
log-error = /mysql/mysql5.6/admin/MysqlVm01.err
tmpdir = /tmp
character-set-server = utf8
innodb_stats_persistent = 1 # Also use ANALYZE TABLE for all tables periodically
innodb_read_io_threads = 16
innodb_write_io_threads = 4
table_open_cache_instances = 16
metadata_locks_hash_instances = 256
innodb_log_file_size = 2048M
innodb_checksum_algorithm = strict_crc32
innodb_buffer_pool_size = 2048M
innodb_data_home_dir = /mysql/mysql5.6/data/innodata
innodb_data_file_path = ibdata1:2048M:autoextend:max:4096M
innodb_log_group_home_dir = /mysql/mysql5.6/logs/innologs
key_buffer_size = 1024M
myisam_sort_buffer_size = 32M
sort_buffer_size = 32k
long_query_time = 1
log_queries_not_using_indexes = 0
slow-query-log
slow-query-log-file = /mysql/mysql5.6/admin/MysqlVm01-slow.log
read_buffer_size = 262144
read_rnd_buffer_size = 1M
sort_buffer_size = 64K
Ci sono arrivato cercando qualcosa che mi permettesse di identificare gli "unused index" su mysql ma sono sicuro che mi potrà essere anche utile per verificare ad esempio la consistenza dei dati in configurazione master e slave, oppure analizzare le query leggendole dallo slow query log file ecc.
Alla Prossima.
Ciaoao
No comments:
Post a Comment