How to quickly embrace the new environment when MySQL DBA is new

There are many methods. Here we will introduce lazy practices, that is, using tools.
However, in the production environment, can't programs be installed or swollen?
It doesn't matter. Our great percona has carefully considered the birds for us.

Here, we need to use three tools:

① Pt-Summary: Green and pure, installation-free, ready-to-use, and basic information about the OS where the MySQL server is located
② Pt-mysql-Summary: Local installation and remote collection of basic MySQL Server Information
③ Innotop: fine-grained MySQL server information analysis, allowing you to deeply observe and monitor MySQL server and remote monitoring.


About Pt-Summary, this tool almost clears the configuration of this server from the inside out,
The displayed parameters are also very clear. If you are interested, you can go to the official website and run them.


The following focuses on Pt-mysql-Summary and innotop.

First look at Pt-mysql-Summary

[MySQL @ odd ~] $ Pt-mysql-Summary -- user = David -- Password = 123 -- Host = 62.131.164.222 (I) Part 1 # percona toolkit MySQL Summary Report ############# ######### system time | 08:58:40 UTC (local TZ: CST + 0800) # instances ###################################### ########### Port Data Directory nice OOM socket ======================= ================================ 3306/home/MySQL/DATA 0 0/tmp/MySQL. sock -- previous two sections Shows the basic overview of MySQL instances-in fact, multiple instances can be listed here, but I only have one here (ii) Part 2 # MySQL executable ########### ################################ path to executable |/home/MySQL /MySQL/bin/mysqld has symbols | yes # report on port 3306 ######################### ############## user | David @ % time | 16:57:58 (CST) hostname | b7-version | 5.5.16-log source distribution built on | Linux x86_64 started | (Up 1 + 01: 31: 13) Databases | 6 datadir |/home/MySQL/data/processes | 20 connected, 2 running replication | is not a slave, has 0 slaves connected pidfile |/home/MySQL/data/IBD. PID (does not exist) -- this part is for MySQL instances (PS: 3306 here, because there are other instances) of course, it is more detailed than the previous section-there is a parameter that requires special attention, time refers to the current time of the MySQL server, rather than the system where the MySQL server is located time (iii) Part 3 # processlist ################################## ############## Command count (*) Working sum (time) max (time) -------------------------------- ------- ----------- query 1 1 1 0 sleep 20 0 35000 4000 user count (*) Working sum (time) max (time) ------------------------------ -------- ------- ----------- David 1 1 0 0 root 9 0 0 0 shaoyun 10 0 0 0 host count (*) Working sum (time) max (time) --------------------------------------------------- --- --------- 110.80.147.20 1 1 0 110.84.245.124 6 0 0 0 0 27.151.68.106 4 0 0 0 0 localhost 9 0 0 0 dB count (*) Working sum (time) max (time) ------------------------------ -------- ----------- Xilinx 15 0 0 0 null 5 1 0 0 state count (*) Working sum (time) max (time) ------------------------------ -------- ----------- 20 0 0 0 null 1 1 0 0 -- this part of content comes from show processlist. Let's take a look at it first. Show processlist without statistics: -- mysql> show processlist; + ------ + --------- + ------------------- + ------ + --------- + ------ + ------- + ------------------ + | ID | user | host | dB | command | time | state | info | + ------ + --------- + --------------------- + ------ + --------- + ------ + ------- + ---------------- + | 1508 | shaoyun | 130.84.225.124: 4373 | di-| sleep | 3523 | null | 1510 | shaoyun | 130.84.225.12 4: 4385 | di-| sleep | 2322 | null | 1513 | shaoyun | 27.161.68.106: 2585 | null | sleep | 7866 | null | 1514 | shaoyun | 27.161.68.106: 2586 | null | sleep | 7866 | null | 1522 | shaoyun | 27.161.68.106: 3107 | null | sleep | 6861 | null | 1529 | root | localhost | IBD | sleep | 4426 | null | 1557 | root | localhost: 59459 | b7-| sleep | 7 | null | 1579 | shaoyun | 165.77.122.42: 56966 | di-| sleep | 433 | null | 1580 | root | localhost | IBD | sleep | 239 | null | 1582 | shaoyun | 27.161.68.126: 5640 | di-| sleep | 164 | null | 1583 | root | localhost | null | query | 0 | null | show processlist | + ------ + --------- + ------------------- + ------ + --------- + ------ + ------- + ------------------ + -- it is not difficult to see that this is the statistics for each column (iv) Part 4 # status counters (wait 10 seconds) ############# ############ Variable per day per second 295 secsbytes_received 90000000 1000 600000000 7000 bytes_sent 6000 20000 2250 bytes 1 handler_rollback 80 handler_update 2250000 handler_write 800 bytes 25 bytes innodb_buffer_pool_write_requests 10000 7innodb_data_fsyncs 1250 innodb_data_read 15000000 175 100innodb_data_reads 900 innodb_data_writes 2000 1innodb_data_written 40000000 450 Jun 1250 Jun 150 Jun 1750 1innodb_log_writes 600 Jun 700 1000000 -- this part is taken from show global status and only collects the changes -- compared the value should be column 3, taken from two snapshots, divided by the change volume, calculated the change per second (v) Part 5 # Table cache ############## ############################ ###### Size | 64 usage | 100% -- displays the table cache size, usage indicates the percentage in use, and this is a myopia value (vi) Part 6 # plugins ####### ######################################## ##### InnoDB compression | active -- display the specified plug-in programs and their statuses (vii) # query cache ############### ################################# query_cache_type | on size | 0.0 usage | 0% hittoinsertratio | 0% -- this part is about the basic query cache information. Part 8 # schema ##################### ################################ wou Ld you like to mysqldump-d the schema and analyze it? Y/n ythere are 6 databases. wocould 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 di-61 65 jiradb 130 6 MySQL 22 Test Database InnoDB MyISAM di-61 jiradb 130 MySQL 22 Test Database btree 193 jiradb 274 MySQL 31 C t h I M r e S t a m p database ======= Xilinx 114 155 jiradb 1 346 MySQL 8 3 test -- this part is the data that mysqldump -- no-data runs -- first A small part specifies the number of tables, views, stored procedures, triggers, functions, and foreign key constraints in each database. The second part specifies the number of tables in each database using different storage engines. -- the third part indicates the number of index types in each database. -- the fourth part indicates the distribution of data types in each database, which may be ugly because the column is vertical. For example, the first column is Char collate. part 9 # noteworthy technologies ################################### # Full Text Indexing | no geospatial types | no foreign keys | Yes 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 -- this part shows whether some key technologies are used, such: full-text index, XA, etc. (10) part 10 # InnoDB #################################### ################ version | 1.1.8 buffer pool size | 128.0 m buffer pool fill | 10% buffer pool dirty | 0% file per table | on page size | 16 K Log File Size | 2*5.0 m = 10.0 m log buffer size | 8 m flush method | 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 | 0 InnoDB queue | 0 queries inside InnoDB, 0 queries in queue oldest transaction | 0 seconds history list Len | 1899 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 | 16 Xnot started -- this part shows the basic configuration of InnoDB -- buffer pool fill and buffer pool dirty are myopia values (11) part 2 # MyISAM #################################### ################ key cache | 16.0 m pct used | 20% unflushed | 0% -- this part is the MyISAM part (12) part 2 # Security #################################### ############## users | 9 users, 1 anon, 4 w/o PW, 9 old PW old passwords | on -- this part is extracted from the MySQL table of grants table -- it shows the number of users and various possible security configurations (13) part 2 # binary logging ################################### ######### binlogs | 12 zero-sized | 0 total size | 62.5 M binlog_format | mixed expire_logs_days | 0 sync_binlog | 0 server_id | 1 binlog_do_db | binlog_ignore_db | -- this part shows the configuration of binary logs (14) part 2 # noteworthy variables ################################### #### auto-Inc incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | SQL _mode | average | 128 K sort_buffer_size | 512 K read_buffer_size | 256 k limit | 512 K bulk_insert_buffer | 0.00 max_heap_table_size | 16 m tmp_table_size | 16 m max_allowed_packet | 1 m thread_stack | 256 k log | off log_error |/home/MySQL/log/alert. log log_warnings | 1 log_slow_queries | onlog_queries_not_using_indexes | off log_slave_updates | off -- this part shows several key server configurations (15) part 2 # configuration file ################################### ###### config file |/etc/My. CNF [client] Port = 3306 socket =/tmp/MySQL. sock [mysqld] Port = 3306 socket =/tmp/MySQL. sockbasedir =/home/MySQL/mysqldatadir =/home/MySQL/datalog-error =/home/MySQL/log/alert. loglog_slow_queries =/home/MySQL/log/slow. logskip-external-lockingkey_buffer_size = protocol = 1mtable_open_cache = 64sort_buffer_size = protocol = 1log-bin = mysql-binbinlog_format = mixedserver-id = 1sync_binlog = protocol = 2 [mysqldump] Protocol = 16 m [MySQL] No-auto-rehash [myisamchk] key_buffer_size = 20msort_buffer_size = 20mread_buffer = 2mwrite_buffer = 2 m [mysqlhotcopy] interactive-Timeout # The end ########## ######################################## # -- as you can see, this part is entirely from my. CNF. I will not go into details here.

In fact, after analyzing Pt-mysql-Summary, you have basically reached the root of the server you are about to manage.
The next innotop will give you some icing on the cake.

For more information, see code.google.com/p/innotop.

(I) Installation

# Perl-mcpan-eshell
CPAN> install time: hires
CPAN> install term: readkey
CPAN> install DBI
CPAN> install DBD: MySQL

# Wget https://innotop.googlecode.com/files/innotop-1.9.0.tar.gz
# Tar-zxv-F innotop-1.9.0.tar.gz
# Cd innotop-1.9.0
# Perl makefile. pl
# Make install


(Ii) Access

# Innotop-uroot-p123-h127.0.0.1


(Iii) Use

The first thing to do is to press? And innotop will teach you how to use it.



For example, if you want to query the SQL statement currently executed by MySQL, you can do this:
Press Q to enter the query list mode
Then, press E and enter the thread ID to display the execution plan.

By David Lin

2013-06-13

Good luck