mysql database administration l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
MySQL Database Administration PowerPoint Presentation
Download Presentation
MySQL Database Administration

Loading in 2 Seconds...

play fullscreen
1 / 36

MySQL Database Administration - PowerPoint PPT Presentation


  • 197 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'MySQL Database Administration' - oakley


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
mysql database administration

MySQL Database Administration

By Will Mayall

NW Startups

05-Jan-2011

filesystem check
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
the ugly truth
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.
check database sizes
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;
check the number of users
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

check the growth of tables
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
run the mysqltuner pl daily
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
slide9

>> 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)
check the global status
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
run mysqlreport to identify problems
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
run mytop to identify queries
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 --
database indexing
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)
identifying indexes
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`)
identifying indexes continued
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.
helpful unix commands
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.
ext3 vs ext4
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.
iostat d
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
iostat cx
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
vmstat
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
slide22
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
netstat
NETSTAT
  • Netstat can be used to help Identify who is accessing the database remotely.
  • netstat -ntp |grep :3306
mysql performance tuning
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.
innodb flush log at trx commit
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.
innodb log file size
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.
innodb buffer pool size
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.
table open cache
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.
query cache size
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.
thread cache size
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.
scripts for checking bottlenecks
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
scripts continued
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
scripts continued33
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
scripts continued34
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
scripts continued35
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
references
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/