1 / 36

MySQL Database Administration

MySQL Database Administration. By Will Mayall NW Startups 05-Jan-2011. Filesystem Check. The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks! Keep a daily log of the size of ibdata1 to predict the disk consumption rate.

oakley
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 NW Startups 05-Jan-2011

  2. Filesystem Check • The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks! • Keep a daily log of the size of ibdata1 to predict the disk consumption rate. • ls –ltr /var/lib/mysql > LS_MYSQL_05JAN2011.doc • Also keep track of all the filesystems. • df –h > DF_H_05JAN2011.doc

  3. The Ugly Truth • To shrink a shared InnoDBtablespace:1. Stop the Application. Backup *all* InnoDB tables with mysqldump.2. Drop all of the InnoDB tables. Stop MySQL.3. Physically delete the ibdata1 file at the filesystem interface.4. Start MySQL Server, which recreates a new, small tablespace file.5. Restore all your InnoDB tables using the mysqldump, which expands the ibdata1 tablespace file as needed.

  4. 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;

  5. Verify Database Sizes 5-jan-2011

  6. 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. 5-jan-2001

  7. 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 db4 --count

  8. Run the mysqltuner.pl Daily • The mysqltuner.pl Perl Script can be used to tune the MySQL Configuration file. • ./mysqltuner.pl --host HOSTNAME--user --pass --forcemem 4000 --forceswap 2000

  9. >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> • >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ • >> Run with '--help' for additional options and output filtering • [--] Performing tests on ssiakrlnxsai06:3306 • Please enter your MySQL administrative login: wmayall • Please enter your MySQL administrative password: • [--] Assuming 4000 MB of physical memory • [--] Assuming 2000 MB of swap space • -------- General Statistics -------------------------------------------------- • [--] Skipped version check for MySQLTuner script • [OK] Currently running supported MySQL version 5.1.44-enterprise-gpl-pro • -------- Storage Engine Statistics ------------------------------------------- • [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster • [--] Data in MyISAM tables: 0B (Tables: 1) • [--] Data in InnoDB tables: 2G (Tables: 209) • [!!] Total fragmented tables: 209 • -------- Performance Metrics ------------------------------------------------- • [--] Up for: 41d 12h 37m 39s (8M q [2.480 qps], 342K conn, TX: 87B, RX: 38B) • [--] Reads / Writes: 43% / 57% • [--] Total buffers: 41.0M global + 2.7M per thread (500 max threads) • [OK] Maximum possible memory usage: 1.4G (35% of installed RAM) • [OK] Slow queries: 0% (1K/8M) • [OK] Highest usage of available connections: 30% (153/500) • [OK] Key buffer size / total MyISAM indexes: 8.0M/105.0K • [OK] Key buffer hit rate: 99.9% (4M cached / 4K reads) • [!!] Query cache is disabled • [OK] Sorts requiring temporary tables: 0% (185 temp sorts / 32K sorts) • [!!] Joins performed without indexes: 24595 • [OK] Temporary tables created on disk: 4% (33K on disk / 699K total) • [!!] Thread cache is disabled • [!!] Table cache hit rate: 0% (64 open / 202K opened) • [OK] Open file limit used: 0% (6/2K) • [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) • [!!] InnoDB data size / buffer pool: 2.4G/8.0M • -------- Recommendations ----------------------------------------------------- • General recommendations: • Run OPTIMIZE TABLE to defragment tables for better performance • Enable the slow query log to troubleshoot bad queries • Adjust your join queries to always utilize indexes • Set thread_cache_size to 4 as a starting value • Increase table_cache gradually to avoid file descriptor limits • Variables to adjust: • query_cache_size (>= 8M) • join_buffer_size (> 128.0K, or always use indexes with joins) • thread_cache_size (start at 4) • table_cache (> 64) • innodb_buffer_pool_size (>= 2G)

  10. 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

  11. Run mysqlreport To Identify Problems • The mysqlreport Perl Script can be used to help identify problems. It currently lives on sun3 in my home directory. • Run it at least Daily so you have history of the Database Engine Status. • For more information on mysqlreport visit: • http://hackmysql.com/mysqlreportguide#temp_report • /home/wmayall/mysqlreport-3.5/mysqlreport --user wUSER –password PASS --host HOSTNAME --port 3306

  12. Run mytop to Identify Queries • The mytop Perl Script lives on sun3 and is a real time monitor of the MySQL SHOW FULL PROCESSLIST command. The –s option is for number of seconds, 1 is the fastest, pressing the f key will Freeze the screen and you can capture any DML that is currently in the PROCESSLIST. • mytop --host HOSTNAME--db DATABASE--user USER --pass PASS -s 1 • Thread 343078 was executing following query: • SELECT * FROM GL_Detail_CurPeriod WHERE GL_Detail_CurPeriod.Year_Period< '201104' And GL_Detail_CurPeriod.TransactionNo= '040087‘ • -- paused. press any key to resume or (e) to explain --

  13. Database Indexing • Taking the query from the previous page you can run an Explain Plan as follows: • Explain SELECT * FROM GL_Detail_CurPeriod WHERE GL_Detail_CurPeriod.Year_Period< '201104' And GL_Detail_CurPeriod.TransactionNo= '040087‘\G • *************************** 1. row *************************** • id: 1 • select_type: SIMPLE • table: GL_Detail_CurPeriod • type: range • possible_keys: Period_Account,Period_TransType,TransactionNo • key: Period_Account • key_len: 6 • ref: NULL • rows: 1 • Extra: Using where • 1 row in set (0.00 sec)

  14. Identifying Indexes • The MySQL Optimizer chose the Period_Account Index for the above query. • Using the CREATE TABLE command, you can verify if the optimizer choose the BEST Index: • show create table GL_Detail_CurPeriod\G • PRIMARY KEY (`GLDetailID`), • KEY `Period_Account` (`Year_Period`,`GLAccount`), • KEY `Period_TransType` (`Year_Period`,`TransType`,`SubType`), • KEY `TransactionNo` (`TransactionNo`,`Year_Period`), • KEY `GLPairID` (`GLPairID`)

  15. Identifying Indexes Continued • The MySQL Optimizer will CHOOSE the Period_Account Index because there were no Records where the Year_Period < ‘201104’ AND TransactionNo = ‘040087’ • This is Probably a User Error, they should have used <= or just = for the Year_Period. • Therefore, in the example above, MySQL would CHOOSE: GL_Detail_CurPeriod.Year_Periodinstead 0f the TransactionNo Index.

  16. 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.

  17. 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.

  18. ext3 vs ext4

  19. IOSTAT -d • iostat –d 2 20 • Linux 2.6.18-194.3.1.el5 (sun3) 01/05/2011 • Device: tpsBlk_read/s Blk_wrtn/s Blk_readBlk_wrtn • sda 14.74 34.90 657.39 125671896 2367081118 • sda1 0.00 0.00 0.00 4008 70 • sda2 14.74 34.90 657.39 125664816 2367081048 • sdb 52.76 126.00 594.84 453707252 2141843080 • sdb1 52.76 126.00 594.84 453682084 2141840696 • dm-0 82.79 34.11 654.87 122836906 2357997664 • dm-1 0.41 0.78 2.52 2823440 9083384 • dm-2 79.12 125.98 506.98 453605050 1825482272 • dm-3 65.79 39.87 506.98 143557370 1825482272 • dm-6 0.00 0.00 0.00 104 8 • dm-7 0.30 0.00 2.43 184 8753896 • dm-4 0.00 0.00 0.00 64 8 • dm-5 0.08 0.00 0.62 104 2246000

  20. IOSTAT -cx • iostat -cx 2 20 • avg-cpu: %user %nice %system %iowait %steal %idle • 8.31 0.06 4.41 0.78 0.00 86.44

  21. 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 wa st • 0 0 195676 32156 91552 2179016 0 1 42 325 3 5 8 4 86 1 0

  22. TOP • You need to know MySQL is NOT Parallel. It will only use one CPU. • top • top - 11:51:29 up 41 days, 16:17, 205 users, load average: 0.51, 0.95, 1.04 • Tasks: 1052 total, 2 running, 1050 sleeping, 0 stopped, 0 zombie • Cpu(s): 4.6%us, 7.2%sy, 0.0%ni, 87.3%id, 0.5%wa, 0.2%hi, 0.2%si, 0.0%st • Mem: 4307708k total, 4272308k used, 35400k free, 92480k buffers • Swap: 2064376k total, 195676k used, 1868700k free, 2173336k cached • PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND • 2328 root 19 0 408m 20m 3352 S 4.3 0.5 632:25.87 lsassd • 2235 root 16 0 26404 1552 1188 R 2.6 0.0 591:17.20 vmware-guestd • 16404 wam 16 0 29084 2844 1488 S 2.0 0.1 2:55.76 top

  23. NETSTAT • Netstat can be used to help Identify who is accessing the database remotely. • netstat -ntp |grep :3306

  24. 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. It is always good practice to monitor performance using mysqlreport, mytop, show global status, and the other Unix commands discussed earlier.

  25. 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.

  26. 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.

  27. 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.

  28. 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.

  29. 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.

  30. 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.

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

  32. 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

  33. 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

  34. 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

  35. Scripts Continued • Check mysqlreport • #!/bin/sh • #./run_loop_mysqlreport.ksh.sh > LOOP_MYSQLREPORT.doc & • x=1 • while [ $x -le 60 ] • do • echo "START RUN TIME" • date • /home/wmayall/scripts/run_mysqlreport_dl4prod.ksh • x=$(( $x + 1 )) • echo "END RUN TIME“ • date • sleep 60 • done

  36. References: • http://www.mysql.com/ • http://hackmysql.com/ • http://forum.percona.com/ • http://www.tuxfiles.org/linuxhelp/fstab.html • http://jeremy.zawodny.com/mysql/mytop/ • http://www.wikipedia.org/

More Related