1 / 40

MySQL Database Administration

MySQL Database Administration. By Will Mayall 02-Dec-2013. Filesystem Check. The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks! We opted to enable innodb_file_per_table to bypass ibdata1 file from growing out of control.

mea
Download Presentation

MySQL Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL Database Administration By Will Mayall 02-Dec-2013

  2. Filesystem Check • The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks! • We opted to enable innodb_file_per_table to bypass ibdata1 file from growing out of control. • However, the individual *.ibd files will grow, the optimize command will shrink the file, but will lock the table! • ls –ltr /apps/mysql_data/ > LS_MYSQL_02dec2013.doc • Also keep track of all the filesystems. • df –h > DF_H_02dec2013.doc

  3. Check Database Sizes • Check the size of the databases daily. • tee DATABASE_SIZES.doc • select sysdate(); • SELECT table_schema "Data Base Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" • FROM information_schema.TABLES • GROUP BY table_schema;

  4. Verify Database Sizes 02-Dec-2013

  5. Check the Number of Users • It’s a good idea to know your users as you are their client. Therefore, get a daily count of users and compare it from the pervious day. Make a note of new users. 03-Dec-2013

  6. Check the Growth of Tables • It’s a bad idea to do select count(*) of tables, but since this is a new application, knowing the table growth rate out weighs the performance hit. • mysqlshow -uUSER -p -t radius --count

  7. Check the Global Status • During peak traffic, checking the global status can help identify variables that are being exceeded. • echo "show global status\G" | mysql -uUSER -p

  8. Check the Global Variables • It is good to verify the variables in the my.cnf actually were picked up. • echo "show global variables;" | mysql -uUSER -p

  9. Show engine innodb status • For innodb tables, getting a 60 second snapshot of the status can help identify problems. • mysql> show engine innodb status\Gselect sleep(60); show engine innodb status\G • *************************** 1. row *************************** • Type: InnoDB • Name: • Status: • ===================================== • 131206 21:04:28 INNODB MONITOR OUTPUT • ===================================== • Per second averages calculated from the last 27 seconds • ----------------- • BACKGROUND THREAD • ----------------- • srv_master_thread loops: 47785 1_second, 47438 sleeps, 4253 10_second, 17623 background, 17623 flush • srv_master_thread log flush and writes: 50244 • ----------

  10. Database Table Status • MySQL gathers Metadata about the status of the Tables. • mysql> show table status from radius\G • *************************** 4. row *************************** • Name: radcheck • Engine: InnoDB • Version: 10 • Row_format: Compact • Rows: 1 • Avg_row_length: 16384 • Data_length: 16384 • Max_data_length: 0 • Index_length: 16384 • Data_free: 0 • Auto_increment: 2 • Create_time: 2013-11-17 01:30:32 • Update_time: NULL • Check_time: NULL • Collation: latin1_swedish_ci • Checksum: NULL • Create_options: • Comment:

  11. Database Index Status • MySQL gathers Metadata about the status of the Tables. • mysql> show index from radius.radacct\G • *************************** 1. row *************************** • Table: radacct • Non_unique: 0 • Key_name: PRIMARY • Seq_in_index: 1 • Column_name: radacctid • Collation: A • Cardinality: 15293918 • Sub_part: NULL • Packed: NULL • Null: • Index_type: BTREE • Comment: • Index_comment:

  12. Identifying Indexes • To check Indexes • mysql> show create table radacct\G • *************************** 1. row *************************** • Table: radacct • Create Table: CREATE TABLE `radacct` ( • `radacctid` bigint(21) NOT NULL AUTO_INCREMENT, • `acctsessionid` varchar(64) NOT NULL DEFAULT '', • `acctuniqueid` varchar(32) NOT NULL DEFAULT '', • `username` varchar(64) NOT NULL DEFAULT '', • `groupname` varchar(64) NOT NULL DEFAULT '', • `realm` varchar(64) DEFAULT '', • `nasipaddress` varchar(15) NOT NULL DEFAULT '', • `nasportid` varchar(15) DEFAULT NULL, • `nasporttype` varchar(32) DEFAULT NULL, • `acctstarttime` datetime DEFAULT NULL, • `acctstoptime` datetime DEFAULT NULL, • `acctsessiontime` int(12) DEFAULT NULL, • `acctauthentic` varchar(32) DEFAULT NULL, • `connectinfo_start` varchar(50) DEFAULT NULL, • `connectinfo_stop` varchar(50) DEFAULT NULL, • `acctinputoctets` bigint(20) DEFAULT NULL, • `acctoutputoctets` bigint(20) DEFAULT NULL, • `calledstationid` varchar(50) NOT NULL DEFAULT '', • `callingstationid` varchar(50) NOT NULL DEFAULT '', • `acctterminatecause` varchar(32) NOT NULL DEFAULT '', • `servicetype` varchar(32) DEFAULT NULL, • `framedprotocol` varchar(32) DEFAULT NULL, • `framedipaddress` varchar(15) NOT NULL DEFAULT '', • `acctstartdelay` int(12) DEFAULT NULL, • `acctstopdelay` int(12) DEFAULT NULL, • `xascendsessionsvrkey` varchar(10) DEFAULT NULL, • PRIMARY KEY (`radacctid`), • UNIQUE KEY `acctuniqueid` (`acctuniqueid`), • KEY `username` (`username`), • KEY `framedipaddress` (`framedipaddress`), • KEY `acctsessionid` (`acctsessionid`), • KEY `acctsessiontime` (`acctsessiontime`), • KEY `acctstarttime` (`acctstarttime`), • KEY `acctstoptime` (`acctstoptime`), • KEY `nasipaddress` (`nasipaddress`) • ) ENGINE=InnoDB AUTO_INCREMENT=23938867 DEFAULT CHARSET=latin1

  13. Understanding Queries • mysql> select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress; • +---------------+-----------------+--------------------------+ • | nasipaddress | count(username) | count(distinct username) | • +---------------+-----------------+--------------------------+ • | 10.1.0.10 | 6045699 | 126297 | • | 10.1.1.10 | 5778471 | 117264 | • | 10.1.2.10 | 1075127 | 65160 | • | 10.1.3.10 | 5187412 | 93745 | • | 10.1.4.10 | 4726818 | 107447 | • +---------------+-----------------+--------------------------+ • 5 rows in set (38.00 sec) • mysql> explain select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress; • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • | 1 | SIMPLE | radacct | index | NULL | nasipaddress | 17 | NULL | 15294970 | NULL | • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • 1 row in set (0.00 sec) • mysql>

  14. Forcing Indexes • You can force MySQL to use an Index and get a different Explain Plan. • mysql> explain select nasipaddress,count(username),count(distinct username) from radacctuse index (nasipaddress) group by nasipaddress; • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • | 1 | SIMPLE | radacct | index | NULL | nasipaddress | 17 | NULL | 15295535 | NULL | • +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+ • 1 row in set (0.00 sec) • mysql>

  15. Profiling a Query • mysql> set profiling=1; • Query OK, 0 rows affected, 1 warning (0.00 sec) • mysql> select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress; • +---------------+-----------------+--------------------------+ • | nasipaddress | count(username) | count(distinct username) | • +---------------+-----------------+--------------------------+ • | 10.1.0.10 | 6046041 | 126298 | • | 10.1.1.10 | 5778779 | 117265 | • | 10.1.2.10 | 1075229 | 65161 | • | 10.1.3.10 | 5187580 | 93745 | • | 10.1.4.10 | 4727107 | 107447 | • +---------------+-----------------+--------------------------+ • 5 rows in set (37.91 sec)

  16. Profiling a Query Continued • mysql> show profile for query 1; • +----------------------+-----------+ • | Status | Duration | • +----------------------+-----------+ • | starting | 0.000054 | • | checking permissions | 0.000007 | • | Opening tables | 0.000011 | • | init | 0.000014 | • | System lock | 0.000008 | • | optimizing | 0.000007 | • | statistics | 0.000019 | • | preparing | 0.000047 | • | Sorting result | 0.000005 | • | executing | 0.000004 | • | Sending data | 37.915050 | • | end | 0.000439 | • | removing tmp table | 0.000013 | • | end | 0.000007 | • | query end | 0.000011 | • | closing tables | 0.000016 | • | freeing items | 0.000027 | • | logging slow query | 0.000006 | • | cleaning up | 0.000015 | • +----------------------+-----------+ • 19 rows in set, 1 warning (0.00 sec) • mysql>

  17. Checking Replication • mysql> show slave status\G • *************************** 1. row *************************** • Slave_IO_State: Queueing master event to the relay log • Master_Host: mysql01-db • Master_User: repl • Master_Port: 3307 • Connect_Retry: 60 • Master_Log_File: blog.022416 • Read_Master_Log_Pos: 861798108 • Relay_Log_File: mysql02-relay-bin.002375 • Relay_Log_Pos: 55290803 • Relay_Master_Log_File: blog.022416 • Slave_IO_Running: Yes • Slave_SQL_Running: Yes • Replicate_Do_DB: • Replicate_Ignore_DB: • Replicate_Do_Table: • Replicate_Ignore_Table: • Replicate_Wild_Do_Table: • Replicate_Wild_Ignore_Table: • Last_Errno: 0 • Last_Error: • Skip_Counter: 0 • Exec_Master_Log_Pos: 861797661 • Relay_Log_Space: 189530366 • Until_Condition: None • Until_Log_File: • Until_Log_Pos: 0 • Master_SSL_Allowed: No • Master_SSL_CA_File: • Master_SSL_CA_Path: • Master_SSL_Cert: • Master_SSL_Cipher: • Master_SSL_Key: • Seconds_Behind_Master: 0 • Master_SSL_Verify_Server_Cert: No • Last_IO_Errno: 0 • Last_IO_Error: • Last_SQL_Errno: 0 • Last_SQL_Error: • Replicate_Ignore_Server_Ids: • Master_Server_Id: 991

  18. Repairing Replication • If Replication Breaks, you MIGHT be able to by-pass the error, otherwise you will have to rebuild Replication from the Master. • mysql>SET GLOBAL sql_slave_skip_counter = 1; • mysql>start slave; • mysql>show slave status\G

  19. Helpful Unix Commands • Get to know your filesystem (/etc/fstab), eg. ext3 vs ext4. • fs_spec- desc FS, fs_file – desc mount point, fs_vfstype – desc the type of FS, fs_mntops – desc mount option, fs_freq – desc determines FS dump, fs_passno – used by fsck • To increase I/O performance change the fs_mntops from the defaults. /dev/mapper/osvg-root / ext4 defaults 1 1 UUID=c96df9ac-787b-4388-9de9-74c292692f9b /boot ext3 defaults 1 2 /dev/mapper/osvg-opt /opt ext4 defaults 1 2 /dev/mapper/osvg-var /var ext4 defaults 1 2 /dev/mapper/appsvg-apps /apps ext4 defaults 1 2 /dev/sda2 /apps/full_backup ext4 defaults 1 2 tmpfs /dev/shmtmpfs defaults 0 0 devpts /dev/ptsdevptsgid=5,mode=620 0 0 sysfs /sys sysfs defaults 0 0 proc /procproc defaults 0 0 LABEL=swap swapswap defaults 0 0

  20. ext3 vs ext4 • ext3 • Introduced in 2001 ext3 supports journaling which improves speed. There are three levels of journaling for ext3 ” lowest, medium, highest ” risk check. • ext4 • With the stable release of ext4 in 2008, this becomes one of the best file system out there.  Transferring speed is really good, but it’s not depending on the file system itself, it also relies on hardware specifications, operating system, Kernel and many more dependencies.

  21. ext3 vs ext4

  22. IOSTAT -dx • iostat –dx 2 20 • Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-szavgqu-sz await svctm %util • sda 0.00 0.01 0.05 0.04 28.98 27.89 668.00 0.00 1.71 0.65 0.01 • dm-0 0.01 83.08 0.03 24.24 2.22 819.96 33.88 0.03 1.36 0.90 2.18 • dm-1 0.00 0.00 0.01 0.00 0.05 0.00 4.06 0.00 24.57 24.56 0.03 • dm-2 0.01 14.16 0.05 4.13 2.20 144.24 35.01 0.08 18.36 1.75 0.73 • dm-3 0.00 0.00 0.01 0.00 0.01 0.00 1.21 0.00 5.21 1.74 0.00 • dm-4 0.00 0.00 0.01 0.00 0.51 0.00 35.39 0.00 4.39 4.02 0.01 • dm-5 0.00 0.00 0.02 107.32 2.19 819.96 7.66 0.19 1.75 0.20 2.17 • dm-6 0.00 0.00 0.00 0.00 0.02 0.00 5.35 0.00 6.55 3.70 0.00 • dm-7 0.00 0.00 0.05 18.29 2.16 144.24 7.98 0.43 23.40 0.40 0.73 • dm-8 0.00 0.00 0.01 0.00 0.49 0.00 40.33 0.00 4.52 3.53 0.00 • dm-9 0.00 0.00 0.03 1.29 1.20 10.36 8.76 0.04 31.10 1.46 0.19 • sdf 0.00 0.00 0.03 2.20 1.12 72.19 32.97 0.01 3.59 0.56 0.12 • sdg 0.00 0.00 0.01 0.00 0.26 0.00 33.39 0.00 3.74 3.74 0.00 • sdh 0.00 0.00 0.01 14.27 1.13 410.12 28.78 0.01 0.98 0.71 1.02 • sdi 0.00 0.00 0.01 0.00 0.05 0.00 5.44 0.00 1.72 1.72 0.00 • dm-10 0.00 0.00 0.00 0.00 0.48 0.00 99.96 0.00 7.45 5.03 0.00 • dm-11 0.00 0.00 0.01 16.55 0.50 132.43 8.03 0.38 23.24 0.37 0.62 • dm-12 0.00 0.00 0.01 0.18 0.40 1.46 9.77 0.00 17.27 8.02 0.15 • dm-13 0.00 0.00 0.01 103.01 2.14 819.95 7.98 0.19 1.82 0.21 2.17 • sdb 0.00 0.00 0.00 1.03 0.03 30.27 29.41 0.00 3.86 0.48 0.05 • sdc 0.00 0.00 0.00 0.00 0.01 0.00 4.85 0.00 3.57 3.57 0.00 • sdd 0.00 0.00 0.00 6.67 0.01 192.74 28.89 0.01 0.84 0.61 0.40 • sde 0.00 0.00 0.00 0.00 0.01 0.00 8.00 0.00 1.66 1.66 0.00

  23. VMSTAT • vmstat 2 20 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wast 2 0 0 147798752 741408 101980384 0 0 1 16 0 0 0 0 99 0 0 0 0 0 147799344 741408 101980400 0 0 0 626 478 1977 0 0 100 0 0 0 0 0 147798640 741408 101980400 0 0 0 76 306 218 0 0 100 0 0 1 0 0 147799040 741408 101980400 0 0 0 0 145 185 0 0 100 0 0

  24. TOP • You need to know MySQL pre-allocates Memory at startup. • top • top - 22:03:56 up 69 days, 20:02, 1 user, load average: 0.34, 0.26, 0.18 • Tasks: 832 total, 1 running, 831 sleeping, 0 stopped, 0 zombie • Cpu(s): 0.1%us, 0.4%sy, 0.0%ni, 99.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st • Mem: 264493968k total, 116693676k used, 147800292k free, 741404k buffers • Swap: 8393944k total, 0k used, 8393944k free, 101977944k cached • PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND • 13299 root RT 0 458m 70m 42m S 4.3 0.0 982:15.40 corosync • 3761 william. 20 0 15560 1840 948 R 0.7 0.0 0:00.11 top • 58946 root 20 0 26216 1772 704 S 0.7 0.0 87:28.83 cmaperfd • 8163 root 20 0 106m 2460 1208 S 0.3 0.0 80:31.19 scsm • 15770 mysql 19 -1 22.8g 9.6g 7188 S 0.3 3.8 10178:15 mysqld • 1 root 20 0 19228 1528 1236 S 0.0 0.0 8:08.60 init

  25. NETSTAT • Netstat can be used to help Identify who is accessing the database remotely. • netstat -ntp |grep :3306 (No info could be read for "-p": geteuid()=1458 but you should be root.) tcp 0 0 10.65.105.137:3306 10.71.0.25:33876 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.75.0.24:45161 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.74.0.24:50678 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.72.0.25:56946 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.73.0.25:46129 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.65.103.183:51526 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.73.0.24:58631 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.71.0.24:34191 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.75.0.25:33685 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.74.0.25:41889 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.72.0.24:48392 ESTABLISHED - tcp 0 0 10.65.105.137:3306 10.65.103.182:46907 ESTABLISHED -

  26. MySQL Performance Tuning • The Default MySQL Configuration file is too small for most Applications. There are a handful of parameters that will make your life as a DBA have more time to surf the web instead of doing DBA work. The following changes should give you the most performance boost.

  27. MySQL Configuration File • The my.cnf file can live where ever you point it to. • PROD mysqlaaa01.prod:william.mayall[~/scripts]$ locate my.cnf • /apps/scripts/.my.cnf • /apps/scripts/my.cnf • /apps/scripts/copy_env/.my.cnf • /etc/my.cnf • /home/william.mayall/.my.cnf • /home/william.mayall/my.cnf • /home/william.mayall/my.cnf.05aug2013 • /home/william.mayall/copy_env/.my.cnf • /usr/my.cnf • Which my.cnf do you chose? • PROD mysqlaaa01.prod:william.mayall[~/scripts]$ ps -ef |grep mysql • root 15365 1 0 Sep30 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/usr/my.cnf --pid-file=/var/run/cluster/mysql/mysql:mysql_conf.pid --bind-address=10.65.105.137 • mysql 15770 15365 10 Sep30 ? 7-01:38:30 /usr/sbin/mysqld --defaults-file=/usr/my.cnf --basedir=/usr --datadir=/apps/mysql_data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --bind-address=10.65.105.137 --log-error=/var/log/mysql/mysql.er --pid-file=/var/run/cluster/mysql/mysql:mysql_conf.pid

  28. MySQL Configuration File • #cat /usr/my.cnf • /usr/my.cnf • [mysqld] • basedir=/usr • datadir=/apps/mysql_data • plugin-dir=/usr/lib64/mysql/plugin • user=mysql • log-error=/var/lib/mysql/mysql.err • pid-file=/var/lib/mysql/mysql.pid • ignore-db-dir=lost+found • log-bin=mysqld-bin • server-id=10 • max_allowed_packet=128M • max_connections=1000 • expire_logs_days=4 • binlog_cache_size = 8388608 • bulk_insert_buffer_size = 268435456 • innodb_buffer_pool_size = 17179869184 • innodb_flush_log_at_trx_commit = 2 • key_buffer_size = 2147483648 • max_heap_table_size = 33554432 • thread_cache_size = 30 • tmp_table_size = 33554432 • innodb_log_file_size = 134217728 • innodb_file_per_table = 1 • [mysqld_safe] • basedir=/usr • datadir=/apps/mysql_data • pid-file=/var/log/mysql/mysql.pid • log-error=/var/log/mysql/mysql.er

  29. innodb_flush_log_at_trx_commit • For I/O Bound performance changing this parameter from 1 to 2 will give you the best performance increase. • The Default value of 1 means each update transaction commits (or each statement outside of the transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications are OK with this value set to 2 which means do not flush log to the disk, but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not lose more than 1-2 seconds worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. The value set to 2 only causes data loss with full OS crash without battery backed up RAM or Disks.

  30. innodb_log_file_size • I’ve seen innodb_log_file_size to be the second best performance increaser. • Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increases recovery times so be careful. I normally use values 64M-512M depending on server size. The current size is 100M for db4, which is fine.

  31. innodb_buffer_pool_size • Again, the default of 8M is just too small. This is like the SGA for Oracle. Would you create an 8M SGA for a 2GB Oracle database? • It is best practices to cache the entire database, there is no reason not to IF you have enough RAM.

  32. key_buffer_size • The key_buffer_size is for MyISAM Tables, including TEMP & Memory Tables. • Tmp_table_size • Max_heap_size • The max_heap_size must be equal to or greater than the tmp_table_size.

  33. max_allowed_packet • The default of 1MB is too small, genereally make the max_allowed_packet 16MB – 256MB. • The Slave variables should be greater or equal to the Master’s variables.

  34. table_open_cache • This is a tricky one. You can actually see a performance hit if you get this wrong! • Increase it gradually over time, check “SHOW GLOBAL STATUS” check the Opened_tables value, you do NOT want Opened_tables increasing during peak times. • I suggest setting the value to 128 and go from there. It currently is set to 64 on db4.

  35. query_cache_size • The Query Cache will put often used queries into cache. • I noticed the user queries using the NO_CACHE Hint, but enabling cache could be of benefit to Ad-Hoc queries. • I suggest setting the value to 8M. It is currently disabled in db4.

  36. thread_cache_size • Thread creation/destructions can be expensive, which happens at each connect/disconnect. I normally set this value to at least 16. If the application has large jumps in the amount of concurrent connections and when I see fast growth of Threads_created variable I set it higher. The goal is not to have threads created in normal operation.

  37. Scripts for Checking Bottlenecks • Check iostat • #!/bin/sh • #./run_iostat.sh > IOSTAT_REPORT.doc & • x=1 • while [ $x -le 60 ] • do • echo "START RUN TIME" • date • /usr/bin/iostat -dx 2 20 • x=$(( $x + 1 )) • echo "END RUN TIME“ • date • sleep 60 • done

  38. Scripts Continued • Check vmstat • #!/bin/sh • #./run_vmstat.sh > VMSTAT_REPORT.doc & • x=1 • while [ $x -le 60 ] • do • echo "START RUN TIME" • date • /usr/bin/vmstat 2 20 • x=$(( $x + 1 )) • echo "END RUN TIME" • date • sleep 60 • done

  39. Scripts Continued • Show MySQL Status • #!/bin/sh • #./run_show_global_status.ksh > GLOBAL_STATUS_REPORT.doc & • x=1 • while [ $x -le 60 ] • do • echo "START RUN TIME" • date • echo "show global status\G" | mysql -uUSER –pPASSWD • x=$(( $x + 1 )) • echo "END RUN TIME" • date • sleep 60 • done

  40. Scripts Continued • Show MySQL Full Processlist • #!/bin/sh • #./run_show_processlistsh > PROCESSLIST_REPORT.doc & • x=1 • while [ $x -le 60 ] • do • echo "START RUN TIME" • date • echo "show full processlist;" | mysql -uUSER –pPASSWD • x=$(( $x + 1 )) • echo "END RUN TIME“ • date • sleep 1 • done

More Related