Transcript MySQL Database Administration

By Will Mayall
NW Startups
05-Jan-2011
1
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
2
The Ugly Truth
 To shrink a shared InnoDB tablespace:
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.
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
5-jan-2011
Data Base Name
Data Base Size in MB
Free Space in MB
db1
0.18750000
450.00000000
db2
763.43750000
5025.00000000
db3
1499.75097656
5100.00000000
db4
1798.89062500
5100.00000000
information_schema
0.00781250
0.00000000
mysql
0.86225700
0.09775925
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.
5-jan-2001
QUERY
select count(*) from mysql.user;
COUNT
32
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 db4 --count
7
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
8








>> MySQLTuner 1.0.1 - Major Hayden <[email protected]>
>> 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)
9
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
10
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
11
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 --
12
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)
13
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`)
14
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_Period instead
0f the TransactionNo Index.
15
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.
fs_spec
fs_file
fs_vfstype
fs_mntops
fs_freq
fs_passno
/dev/VolGroup00/LogVol00
/
ext3
defaults
1
1
LABEL=/boot
/boot
ext3
defaults
1
2
tmpfs
/dev/shm
tmpfs
defaults
0
0
devpts
/dev/pts
devpts
gid=5,mode=620
0
0
sysfs
/sys
sysfs
defaults
0
0
proc
/proc
proc
defaults
0
0
/dev/VolGroup00/LogVol01
swap
swap
defaults
0
0
/dev/root_vg/SVOL
/sroot
ext3
defaults
1
2
16
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.
17
ext3 vs ext4
Ext3
ext4
Stands For
Third Extended
Filesystem
Fourth Extended
Filesystem
Original OS
Linux
Linux
Max Volume Size
256TB
1 EB Limited to 16TB
Max File Size
16GB – 2TB
16TB
Max Filename Length
254 bytes
256 bytes
Journaling
Yes
Yes
18
IOSTAT -d
 iostat –d 2 20
 Linux 2.6.18-194.3.1.el5 (sun3)














Device:
sda
sda1
sda2
sdb
sdb1
dm-0
dm-1
dm-2
dm-3
dm-6
dm-7
dm-4
dm-5
01/05/2011
tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
14.74
34.90
657.39 125671896 2367081118
0.00
0.00
0.00
4008
70
14.74
34.90
657.39 125664816 2367081048
52.76
126.00
594.84 453707252 2141843080
52.76
126.00
594.84 453682084 2141840696
82.79
34.11
654.87 122836906 2357997664
0.41
0.78
2.52 2823440 9083384
79.12
125.98
506.98 453605050 1825482272
65.79
39.87
506.98 143557370 1825482272
0.00
0.00
0.00
104
8
0.30
0.00
2.43
184 8753896
0.00
0.00
0.00
64
8
0.08
0.00
0.62
104 2246000
19
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
20
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
21
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
22
NETSTAT
 Netstat can be used to help Identify who is accessing
the database remotely.
 netstat -ntp |grep :3306
23
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.
24
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.
25
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.
26
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.
27
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.
28
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.
29
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.
30
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
31
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
32
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
33
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
34
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
35
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/
36