Transcript MySQL Database Administration

By Will Mayall
[email protected]
24-SEP-2014
1
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_24sep2014.doc
Also keep track of all the filesystems.
df –h > DF_H_24sep2014.doc
2
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;
3
Verify Database Sizes
24-sep-2014
Data Base Name
Data Base Size in MB
Free Space in
MB
MY_DB
7770.32812500
7.00000000
performance_sche
ma
0.00000000
0.00000000
information_sche
ma
0.00781250
0.00000000
mysql
0.77073860
0.09775925
4
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.
24-sep-2014
QUERY
select count(*) from mysql.user;
COUNT
21
5
Check for Missing Passwords
 mysql> select User,Password,Host from
mysql.user where nullif(Password, '')
is NULL;
 +------+----------+-----------+
 | User | Password | Host






|
+------+----------+-----------+
| root |
| localhost |
| root |
| 127.0.0.1 |
| root |
| ::1
|
+------+----------+-----------+
3 rows in set (0.00 sec)
 mysql>
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`),




































13
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)
14
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>
























15
Replication
 Row Based vs Statement Based Replication
 The Default is Statement Based Replication which
replays the exact SQL statement on the Slave. This
is generally much slower than row based
replication.
 Row Based Replication inserts the returned values
of the query executed on the Master.
16
Checking Replication

mysql> show slave status\G

*************************** 1.
Slave_IO_State:
Master_Host:
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File:
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running:
Slave_SQL_Running:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition:
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed:
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert:
Last_IO_Errno:



































row ***************************
Queueing master event to the relay log
mysql01-db
repl
3307
60
blog.022416
861798108
mysql02-relay-bin.002375
55290803
blog.022416
Yes
Yes
0
0
861797661
189530366
None
0
No
0
No
0
17
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
18
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
UUID=c96df9ac-787b-4388-9de9-74c292692f9b /boot
/dev/mapper/osvg-opt
/opt
ext4
/dev/mapper/osvg-var
/var
ext4
/dev/mapper/appsvg-apps /apps
ext4
/dev/sda2
/apps/full_backup
ext4
tmpfs
/dev/shm
tmpfs
devpts
/dev/pts
devpts
sysfs
/sys
sysfs
proc
/proc
proc
LABEL=swap
swap
swap
defaults
ext3
defaults
defaults
defaults
defaults
defaults
gid=5,mode=620
defaults
defaults
defaults
1 1
defaults
1 2
1 2
1 2
1 2
0 0
0 0
0 0
0 0
0 0
1 2
19
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.
20
ext3 vs ext4
Ext3
ext4
Stands For
Third Extended
Filesystem
Fourth Extended
Filesystem
Original OS
Linux
Linux
Max Volume Size
256TB
1 EB
Max File Size
16GB – 2TB
16TB
Max Filename
Length
254 bytes
256 bytes
Journaling
Yes
Yes
21
IOSTAT -dx

iostat –dx 1 20
























Device:
sda
dm-0
dm-1
dm-2
dm-3
dm-4
dm-5
dm-6
dm-7
dm-8
dm-9
sdf
sdg
sdh
sdi
dm-10
dm-11
dm-12
dm-13
sdb
sdc
sdd
sde
rrqm/s
0.00
0.01
0.00
0.01
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
wrqm/s
0.01
83.08
0.00
14.16
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
r/s
0.05
0.03
0.01
0.05
0.01
0.01
0.02
0.00
0.05
0.01
0.03
0.03
0.01
0.01
0.01
0.00
0.01
0.01
0.01
0.00
0.00
0.00
0.00
w/s
0.04
24.24
0.00
4.13
0.00
0.00
107.32
0.00
18.29
0.00
1.29
2.20
0.00
14.27
0.00
0.00
16.55
0.18
103.01
1.03
0.00
6.67
0.00
rsec/s
28.98
2.22
0.05
2.20
0.01
0.51
2.19
0.02
2.16
0.49
1.20
1.12
0.26
1.13
0.05
0.48
0.50
0.40
2.14
0.03
0.01
0.01
0.01
wsec/s avgrq-sz avgqu-sz
27.89
668.00
0.00
819.96
33.88
0.03
0.00
4.06
0.00
144.24
35.01
0.08
0.00
1.21
0.00
0.00
35.39
0.00
819.96
7.66
0.19
0.00
5.35
0.00
144.24
7.98
0.43
0.00
40.33
0.00
10.36
8.76
0.04
72.19
32.97
0.01
0.00
33.39
0.00
410.12
28.78
0.01
0.00
5.44
0.00
0.00
99.96
0.00
132.43
8.03
0.38
1.46
9.77
0.00
819.95
7.98
0.19
30.27
29.41
0.00
0.00
4.85
0.00
192.74
28.89
0.01
0.00
8.00
0.00
await
1.71
1.36
24.57
18.36
5.21
4.39
1.75
6.55
23.40
4.52
31.10
3.59
3.74
0.98
1.72
7.45
23.24
17.27
1.82
3.86
3.57
0.84
1.66
svctm
0.65
0.90
24.56
1.75
1.74
4.02
0.20
3.70
0.40
3.53
1.46
0.56
3.74
0.71
1.72
5.03
0.37
8.02
0.21
0.48
3.57
0.61
1.66
%util
0.01
2.18
0.03
0.73
0.00
0.01
2.17
0.00
0.73
0.00
0.19
0.12
0.00
1.02
0.00
0.00
0.62
0.15
2.17
0.05
0.00
0.40
0.00
22
VMSTAT
 vmstat 1 20



















procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---r b
swpd
free
buff cache
si
so
bi
bo
in
cs us sy id wa
2 0
0 16705084
388 21434300
0
0
229
212
0
0 6 1 93 0
2 0
0 16657148
388 21434288
0
0
0
19 3436 3598 5 1 95 0
2 0
0 16657512
388 21434284
0
0
0
84 1897 1033 5 0 95 0
2 0
0 16657496
388 21434284
0
0
0
20 1298 955 4 0 95 0
4 0
0 16787144
388 21449080
0
0
0
492 2077 1281 5 1 94 0
1 0
0 16772468
384 21449096
0
0 1260 61007 6352 7445 1 3 96 0
17 0
0 17017212
388 21165932
0
0 5914 5017 42771 54745 17 10 72 1
10 0
0 17015168
388 21167964
0
0 1332 3589 33222 36175 27 8 65 0
6 0
0 17008832
388 21159076
0
0
420
564 31538 31344 31 4 65 0
8 0
0 16976264
388 21160504
0
0 6940 1132 22048 27035 21 4 75 0
8 0
0 16885008
388 21194688
0
0 17796
0 12144 5351 26 4 70 0
5 0
0 16951516
388 21179876
0
0 1888
5 10102 4534 20 4 76 0
4 0
0 16963556
388 21182392
0
0 2393 3579 9982 5255 12 3 85 0
4 0
0 16962976
388 21200324
0
0 18460
524 5825 2901 9 4 87 0
6 0
0 16950652
388 21201276
0
0
936
452 5669 3554 11 5 84 0
5 0
0 16919420
388 21219484
0
0 16796
0 8565 3730 13 4 83 0
2 0
0 16926496
388 21219432
0
0
56
207 5534 2509 13 5 82 0
23
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
13299
3761
58946
8163
15770
1



USER
root
william.
root
root
mysql
root
PR
RT
20
20
20
19
20
NI
0
0
0
0
-1
0
VIRT
458m
15560
26216
106m
22.8g
19228
RES SHR S %CPU %MEM
TIME+ COMMAND
70m 42m S 4.3 0.0 982:15.40 corosync
1840 948 R 0.7 0.0
0:00.11 top
1772 704 S 0.7 0.0 87:28.83 cmaperfd
2460 1208 S 0.3 0.0 80:31.19 scsm
9.6g 7188 S 0.3 3.8 10178:15 mysqld
1528 1236 S 0.0 0.0
8:08.60 init
24
Checking the mysqld Threads

wmayall@db50:~$ ps -ef |grep mysqld
wmayall 19235 32139 0 08:46 pts/17
00:00:00 grep --color=auto mysqld
root
29659
1 0 Sep23 pts/13
00:00:00 /bin/sh /opt/mysql/server-5.6/bin/mysqld_safe --datadir=/mysql --pidfile=/mysql/db50.pid
mysql
30487 29659 99 Sep23 pts/13
14:45:21 /opt/mysql/server-5.6/bin/mysqld --basedir=/opt/mysql/server-5.6 -datadir=/mysql --plugin-dir=/opt/mysql/server-5.6/lib/plugin --user=mysql --log-error=/mysql/db50.err --pid-file=/mysql/db50.pid
--socket=/tmp/mysql.sock --port=3306
wmayall@db50:~$

wmayall@db50:~$ top -p 30487 –H

top - 08:49:16 up 209 days, 17:38, 11 users, load average: 14.38, 5.90, 4.22
Tasks: 182 total, 24 running, 158 sleeping,
0 stopped,
0 zombie
Cpu(s): 58.5%us, 31.1%sy, 0.0%ni, 10.2%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 65943808k total, 49776144k used, 16167664k free,
388k buffers
Swap: 10485756k total,
0k used, 10485756k free, 21920948k cached

























PID
30807
18728
30787
10166
10762
16599
10186
10457
30803
30805
10462
30750
10174
16569
30815
10038
USER
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
mysql
PR
20
20
20
20
20
20
20
20
20
20
20
20
20
20
20
20
NI
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
VIRT
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
32.9g
RES
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
7.8g
SHR
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
12m
S %CPU %MEM
R
90 12.4
R
90 12.4
R
89 12.4
R
89 12.4
R
89 12.4
R
88 12.4
S
88 12.4
R
86 12.4
R
86 12.4
S
85 12.4
S
85 12.4
R
84 12.4
R
84 12.4
R
84 12.4
R
84 12.4
R
84 12.4
TIME+
4:53.30
4:06.05
8:56.86
1:45.46
3:39.19
1:57.14
1:43.29
0:59.81
3:46.43
3:47.04
2:02.88
4:21.46
3:18.21
0:51.66
8:30.47
2:27.83
COMMAND
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
mysqld
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
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
tcp
0
0 10.65.105.137:3306
but you should be root.)
10.71.0.25:33876
10.75.0.24:45161
10.74.0.24:50678
10.72.0.25:56946
10.73.0.25:46129
10.65.103.183:51526
10.73.0.24:58631
10.71.0.24:34191
10.75.0.25:33685
10.74.0.25:41889
10.72.0.24:48392
10.65.103.182:46907
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED
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 --defaultsfile=/usr/my.cnf --basedir=/usr --datadir=/apps/mysql_data --plugin28
dir=/usr/lib64/mysql/plugin --user=mysql --bind-address=10.65.105.137 --log-
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
Back_log=400
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.
 Usually ¾ the Size of RAM, if the server is only using
mysqld.
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.
 If you can, make it as large as all combined Indexes
plus tmp_table_size and max_heap_size.
 If possible, make it as large as all combined Indexes.
33
key_cache_block_size
 MyISAM uses the Operating Block Size,
which is normally 4096 bytes.
 MySQL Default is usually 1024 Bytes.
 To speed up large MyISAM queries
increasing the key_cache_block_size to
4096 will increase performance.
34
back_log
 If you are experiencing web service
thrashing (high loads), increase the
back_log value higher than or equal to
400.
 Drawbacks are the web server will
accept new connections, and the
database server can become saturated.
35
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.
36
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.
37
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.
38
Cache Hit Rates

























How to calculate cache hit rates
mysql> show global status like 'Qc%';
+-------------------------+-----------+
| Variable_name
| Value
|
+-------------------------+-----------+
| Qcache_free_blocks
| 3671
|
| Qcache_free_memory
| 679977496 |
| Qcache_hits
| 3188949
|
| Qcache_inserts
| 3120018
|
| Qcache_lowmem_prunes
| 301732
|
| Qcache_not_cached
| 1066818
|
| Qcache_queries_in_cache | 153203
|
| Qcache_total_blocks
| 314518
|
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> show global status like 'Com_select%';
+---------------+---------+
| Variable_name | Value
|
+---------------+---------+
| Com_select
| 5130462 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> select 3188949/(3188949+5130462);
+---------------------------+
| 3188949/(3188949+5130462) |
39
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.
40
Scripts for Checking Bottlenecks















Check iostat
#!/bin/bash
#Stolen from Will Mayall 25-Sep-2014
#./run_iostat.sh > IOSTAT_REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START RUN TIME"
date
/usr/bin/iostat -dx 1 20
x=$(( $x + 1 ))
echo "END RUN TIME“
date
sleep 60
done
41
Scripts Continued















Check vmstat
#!/bin/bash
#Stolen from Will Mayall 25-Sep-2014
#./run_vmstat.sh > VMSTAT_REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START RUN TIME"
date
/usr/bin/vmstat 1 20
x=$(( $x + 1 ))
echo "END RUN TIME"
date
sleep 60
done
42
Scripts Continued















Show MySQL Status
#!/bin/bash
#Stolen from Will Mayall 25-Sep-2014
#./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
43
Scripts Continued















Show MySQL Full Processlist
#!/bin/bash
#Stolen from Will Mayall 25-Sep-2014
#./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
44
Questions?
45