MySQL Workshop II
OTN LA Tour 2015
Links: Account Details -
First Steps -
Initial Benchmark -
DBA 101 -
Production Ready MySQL -
MySQL Replication -
Upgrade to MySQL 5.6 -
Production Upgrade Planning -
VM Setup -
Installation -
Account Details
OS
- IP: 192.168.42.10
- User: dba
- Password: zookeeper
Unless otherwise stated, all commands are run using this dba account.
MySQL
MySQL Login details match OS login details.
First Steps
- Copy files from USB to local machine
- Install VirtualBox 5 (Available on USB)
- Configure Host Only Adapter (vboxnet0=192.168.42.1)
- Import Appliance (USB otntour2015.ova) DO NOT REINITIALIZE MAC ADDRESSES
- Start VM
- Set Hostname with provided name (new_hostname.sh)
$ ssh [email protected] $ sudo /usr/local/bin/new_hostname.sh <name> $ hostname $ sudo init 6
Create a snapshot of your VM labelled "MySQL 5.5" so you can restore to a MySQL 5.5 version.
Initial Benchmark Test
It is recommended you have at least 4 terminal windows open. This will help show the impact of various situations. On Mac I recommend iTerm 2 (included on USB).
Terminal 1
A window to look at overall system load. Run the following command.
$ vmstat 5
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 0 1543004 13444 87684 0 0 0 0 25 49 0 1 99 0 0 0 0 0 1543028 13452 87684 0 0 0 2 22 41 0 0 100 0 0 0 0 0 1543036 13452 87684 0 0 0 2 17 38 0 0 100 0 0 0 0 0 1542360 13460 87684 0 0 0 22 45 75 0 0 99 1 0 0 0 0 1540684 13468 87980 0 0 59 2 26 58 0 0 98 1 0 0 0 0 1540808 13468 87980 0 0 0 0 17 43 0 0 100 0 0 0 0 0 1540808 13468 87980 0 0 0 0 19 40 0 0 100 0 0 0 0 0 1540808 13468 87980 0 0 0 0 15 40 0 0 100 0 0 0 0 0 1538264 13468 89628 0 0 330 0 37 77 0 0 97 2 0
Terminal 2
A window to look at overall MySQL load. Run the following command.
$ mysqladmin -r -i 5 extended-status | grep -ie "com_insert " -ie "com_select" -ie "com_update " -ie "com_delete "
| Com_insert | 0 | | Com_select | 0 | | Com_update | 0 |
Terminal 3
An interactive MySQL Shell that we can run additional commands here. The following are SQL statements that will show some initial settings.
$ mysql
mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin'; SHOW GLOBAL VARIABLES LIKE 'sync_binlog'; SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
mysql> SELECT * FROM information_schema.global_variables WHERE variable_name IN ('log_bin', 'sync_binlog', 'innodb_flush_log_at_trx_commit');
mysql> SHOW PROCESSLIST;
... +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | +---------------+-------+ 1 row in set (0.00 sec) +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.00 sec) ... +--------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------------+----------------+ | SYNC_BINLOG | 0 | | LOG_BIN | OFF | | INNODB_FLUSH_LOG_AT_TRX_COMMIT | 1 | +--------------------------------+----------------+ 3 rows in set (0.00 sec)
Terminal 4
Our benchmarking terminal.
$ hostname $ sudo /usr/local/bin/newrelic.sh $ random_load.sh
Fri Jul 24 15:57:16 UTC 2015 (16) Benchmark Average number of seconds to run all queries: 0.106 seconds Minimum number of seconds to run all queries: 0.078 seconds Maximum number of seconds to run all queries: 0.400 seconds Number of clients running queries: 16 Average number of queries per client: 0 Fri Jul 24 15:58:51 UTC 2015 (49) ...
New Relic Monitoring
With Internet connectivity your VM will start reporting OS and MySQL statistics to New Relic. This is a SaaS Monitoring option. As the author of the MySQL Plugin I can provide a unlimited standard account for FREE via http://j.mp/newrelic-mysql for you to evaluate this at your organization.
- URL: https://rpm.newrelic.com/accounts/1049601/servers
- Login: [email protected]
- Password: zookeeper2015
Following login you should be able to see your hostname in the Servers and Plugins tabs.
Analysis
After everything is setup you should see the following in the various terminal windows.
OS load
System load, specifically Wait I/O (wa).
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 1 0 1287224 140084 144868 0 0 0 1382 617 33423 33 13 0 54 0 0 1 0 1283336 140224 144860 0 0 0 1410 722 33995 39 16 0 46 0 13 1 0 1281192 140236 144872 0 0 0 1587 716 26164 32 12 3 53 0 3 1 0 1286604 140520 144852 0 0 0 1319 669 26349 33 13 2 52 0 0 1 0 1282988 140520 144872 0 0 0 1270 594 29325 28 12 1 59 0
MySQL Load
Inserts/Updates/Selects occuring. The number represents the rate per 5 seconds.
| Com_insert | 2127 | | Com_select | 1927 | | Com_update | 0 | | Com_insert | 1696 | | Com_select | 1617 | | Com_update | 0 |
MySQL Processes
A large number of MySQL threads running.
mysql> show processlist; +-------+----------+-----------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 3 | dba | localhost | NULL | Query | 0 | NULL | show processlist | | 4 | newrelic | localhost:56777 | NULL | Sleep | 8 | | NULL | | 11966 | dba | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 | | 11967 | dba | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 | | 11968 | dba | localhost | mysqlslap | Query | 1 | update | INSERT INTO t1 VALUES (1154847471,2039507092,'A1yNIaru8HcCZaDEGC2X9KBCoNipoZayRODdy4N7ErBfQPtaTw5XGG | | 11969 | dba | localhost | mysqlslap | Query | 1 | query end | INSERT INTO t1 VALUES (1471079933,266531426,'a1vMmjn2ZKuaFxe9yR02BJeO5IKMg5o67bKlI7gjJQHbNokD7lGIwmj | | 11970 | dba | localhost | mysqlslap | Query | 1 | query end | INSERT INTO t1 VALUES (1471079933,266531426,'a1vMmjn2ZKuaFxe9yR02BJeO5IKMg5o67bKlI7gjJQHbNokD7lGIwmj | | 11971 | dba | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 | | 11972 | dba | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 | | 11973 | dba | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 | | 11974 | dba | localhost | mysqlslap | Query | 1 | update | INSERT INTO t1 VALUES (1154847471,2039507092,'A1yNIaru8HcCZaDEGC2X9KBCoNipoZayRODdy4N7ErBfQPtaTw5XGG | | 11975 | dba | localhost | mysqlslap | Query | 1 | query end | INSERT INTO t1 VALUES (1471079933,266531426,'a1vMmjn2ZKuaFxe9yR02BJeO5IKMg5o67bKlI7gjJQHbNokD7lGIwmj | ...
New Relic Metrics
The following clickable images provides a visual reference of what you should see for your server.
We will let this benchmarking run for a few minutes to produce graphs for everybody.
DBA 101
A default installation of MySQL 5.5 has a number of inherit weaknesses for being production ready. This VM is a stock MySQL 5.5 install from the Oracle YUM repo. Detailed installation steps can be found at the bottom of this page here.
User Security
There is none. The MySQL instance has no password.
$ sudo su - $ mysql -uroot
The following sets a password for the root user.
$ mysqladmin -uroot password 'zookeeper' $ mysql -uroot $ mysql -uroot -pzookeeper
We can re-run our first access to verify access now has more security.
$ mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) $ mysql -uroot -pzookeeper
We can store the user/password in a per user configuration file that is inspected for MySQL client programs. This means we do not need to enter the password each time.
$ echo "[client]
user=root
password=zookeeper" > ${HOME}/.my.cnf
$ mysql
Unsafe Privileges
MySQL 5.5 by default has unsafe permissions (users/schemas).
mysql> select host,user,password from mysql.user; mysql> show schemas;
+-----------+----------+-------------------------------------------+ | host | user | password | +-----------+----------+-------------------------------------------+ | localhost | root | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | | zoo | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | zoo | | | | localhost | newrelic | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | | 127.0.0.1 | newrelic | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | | localhost | dba | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | +-----------+----------+-------------------------------------------+
A new clean installation of MySQL 5.5 will look like:
mysql> select host,user,password from mysql.user; +-----------+----------+-------------------------------------------+ | host | user | password | +-----------+----------+-------------------------------------------+ | localhost | root | | | zoo | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | zoo | | | +-----------+----------+-------------------------------------------+ 6 rows in set (0.00 sec)
mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
MySQL provides a utility to perform the most basic security. This should always be run for a clean installation if no other automated processes are used.
$ mysql_secure_installation
... Enter current password for root (enter for none): Change the root password? [Y/n] n Remove anonymous users? [Y/n] y Disallow root login remotely? [Y/n] y Remove test database and access to it? [Y/n] y Reload privilege tables now? [Y/n] y
The user privileges however are still not clean (an artifact of setting 'root' password initially).
mysql> select host,user,password from mysql.user; +-----------+----------+-------------------------------------------+ | host | user | password | +-----------+----------+-------------------------------------------+ | localhost | root | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | | 127.0.0.1 | root | | | ::1 | root | | ... +-----------+----------+-------------------------------------------+
The following will provide the most secure access.
mysql> DELETE FROM mysql.user WHERE user='root' AND host != 'localhost'; mysql> FLUSH PRIVILEGES;
mysql> select host,user,password from mysql.user; +-----------+----------+-------------------------------------------+ | host | user | password | +-----------+----------+-------------------------------------------+ | localhost | root | *AC4BCA3DA84D9382139C10C5D1BFD55CFE70EE7C | +-----------+----------+-------------------------------------------+
The most recent DMR release of MySQL 5.7.8 no longer has a password column. See MySQL 5.7 : no more password column! for an explanation.
File System Security
Your data is not as protected as it should be.
$ ls -ld /var/lib/mysql/ drwxr-xr-x. 5 mysql mysql 4096 Jul 22 16:34 /var/lib/mysql/
$ ls -lh /var/lib/mysql/ total 37M -rw-rw----. 1 mysql mysql 26M Jul 22 16:35 ibdata1 -rw-rw----. 1 mysql mysql 5.0M Jul 22 16:35 ib_logfile0 -rw-rw----. 1 mysql mysql 5.0M Jul 22 16:35 ib_logfile1 drwx------. 2 mysql mysql 4.0K Jul 21 17:30 mysql drwx------. 2 mysql mysql 4.0K Jul 22 16:35 mysqlslap srwxrwxrwx. 1 mysql mysql 0 Jul 22 14:43 mysql.sock drwx------. 2 mysql mysql 4.0K Jul 21 17:30 performance_schema
Default distro installs do not lock down the datadir because they put a global readable file mysql.sock in the directory by default. This can be changed by configuration. This directory is also used for binary logs by default, demonstrated later.
You should now stop benchmarking and review New Relic statistics. This gap in load is useful for evaluating the next test.
Benchmark Basics
Mysqlslap is a utility that is included with the MySQL Binary. The random_load.sh script is using this.
$ mysqlslap --auto-generate-sql --verbose Benchmark Average number of seconds to run all queries: 0.125 seconds Minimum number of seconds to run all queries: 0.125 seconds Maximum number of seconds to run all queries: 0.125 seconds Number of clients running queries: 1 Average number of queries per client: 0 $ mysqlslap --concurrency=50 --iterations=10 --auto-generate-sql --verbose --no-drop Benchmark Average number of seconds to run all queries: 0.277 seconds Minimum number of seconds to run all queries: 0.136 seconds Maximum number of seconds to run all queries: 0.438 seconds Number of clients running queries: 50 Average number of queries per client: 0
mysqlslap will create it's own schema and defines a single table. We perform this check because this changes in MySQL 5.6.
$ sudo ls -lh /var/lib/mysql/mysqlslap/ total 16K -rw-rw----. 1 mysql mysql 65 Jul 22 16:36 db.opt -rw-rw----. 1 mysql mysql 8.6K Jul 22 16:36 t1.frm
sysbench has been pre-installed and configured on this VM. We will be using the examples found at http://www.lefred.be/?q=node/154, https://www.percona.com/blog/2014/09/02/using-sysbench-0-5-benchmark-mysql-whats-changed-latest-release/.
$ sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Threads started!
OLTP test statistics:
queries performed:
read: 0
write: 10000
other: 0
total: 10000
transactions: 0 (0.00 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 10000 (1215.34 per sec.)
other operations: 0 (0.00 per sec.)
General statistics:
total time: 8.2281s
total number of events: 10000
total time taken by event execution: 8.2112s
response time:
min: 0.12ms
avg: 0.82ms
max: 106.16ms
approx. 95 percentile: 1.03ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 8.2112/0.00
Monitoring Basics
$ tail -50 /var/log/mysqld.log ... 150724 15:51:08 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 150724 15:51:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150724 15:51:48 [Note] /usr/sbin/mysqld (mysqld 5.5.44) starting as process 2474 ... 150724 15:51:48 [Note] Plugin 'FEDERATED' is disabled. 150724 15:51:48 InnoDB: The InnoDB memory heap is disabled 150724 15:51:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150724 15:51:48 InnoDB: Compressed tables use zlib 1.2.3 150724 15:51:48 InnoDB: Using Linux native AIO 150724 15:51:48 InnoDB: Initializing buffer pool, size = 128.0M 150724 15:51:48 InnoDB: Completed initialization of buffer pool 150724 15:51:48 InnoDB: highest supported file format is Barracuda. 150724 15:51:49 InnoDB: Waiting for the background threads to start 150724 15:51:50 InnoDB: 5.5.44 started; log sequence number 1595675 150724 15:51:50 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150724 15:51:50 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150724 15:51:50 [Note] Server socket created on IP: '0.0.0.0'. 150724 15:51:50 [Note] Event Scheduler: Loaded 0 events 150724 15:51:50 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.44' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
$ mysql -e "SHOW PROCESSLIST" +-------+----------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------------+------+---------+------+-------+------------------+ | 2 | dba | localhost | NULL | Sleep | 1 | | NULL | | 4 | newrelic | localhost:56777 | NULL | Sleep | 44 | | NULL | | 29833 | dba | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +-------+----------+-----------------+------+---------+------+-------+------------------+ $ mysqladmin processlist +-------+----------+-----------------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------------+----+---------+------+-------+------------------+ | 2 | dba | localhost | | Sleep | 1 | | | | 4 | newrelic | localhost:56777 | | Sleep | 49 | | | | 29834 | dba | localhost | | Query | 0 | | show processlist | +-------+----------+-----------------+----+---------+------+-------+------------------+ $ mysqladmin -v processlist +-------+----------+-----------------+----+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------------+----+---------+------+-------+-----------------------+ | 2 | dba | localhost | | Sleep | 5 | | | | 4 | newrelic | localhost:56777 | | Sleep | 53 | | | | 29835 | dba | localhost | | Query | 0 | | show full processlist | +-------+----------+-----------------+----+---------+------+-------+-----------------------+
$ mysql -e "select * from information_schema.processlist;" +-------+----------+-----------------+------+---------+------+-----------+----------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +-------+----------+-----------------+------+---------+------+-----------+----------------------------------------------+ | 29836 | dba | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist | | 4 | newrelic | localhost:56777 | NULL | Sleep | 58 | | NULL | | 2 | dba | localhost | NULL | Sleep | 5 | | NULL | +-------+----------+-----------------+------+---------+------+-----------+----------------------------------------------+ $ mysql -e "select * from performance_schema.processlist;" ERROR 1146 (42S02) at line 1: Table 'performance_schema.processlist' doesn't existNOTE: We discuss performance_schema.processlist later.
$ mysql -e "SHOW ENGINE INNODB STATUS\G" *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 150722 16:44:27 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 45 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4950 1_second, 4941 sleeps, 492 10_second, 52 background, 52 flush srv_master_thread log flush and writes: 5213 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 500763, signal count 38652 --Thread 140666819790592 has waited at trx0undo.ic line 156 for 0.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7fef97d150c0 created in file buf0buf.c line 938 a writer (thread id 140666819790592) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.c line 1319 Last time write locked in file /pb2/build/sb_0-15194737-1430742730.11/rpm/BUILD/mysql-5.5.44/mysql-5.5.44/storage/innobase/include/trx0undo.ic line 156 --Thread 140666811803392 has waited at btr0pcur.c line 425 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x7fef97d2cac0 created in file buf0buf.c line 938 a writer (thread id 140666819790592) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3100 Last time write locked in file /pb2/build/sb_0-15194737-1430742730.11/rpm/BUILD/mysql-5.5.44/mysql-5.5.44/storage/innobase/row/row0ins.c line 2017 --Thread 140666890761984 has waited at row0ins.c line 2017 for 0.00 seconds the semaphore: X-lock on RW-latch at 0x7fef97d2cac0 created in file buf0buf.c line 938 a writer (thread id 140666819790592) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3100 Last time write locked in file /pb2/build/sb_0-15194737-1430742730.11/rpm/BUILD/mysql-5.5.44/mysql-5.5.44/storage/innobase/row/row0ins.c line 2017 --Thread 140666810472192 has waited at btr0pcur.c line 425 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x7fef97d2cac0 created in file buf0buf.c line 938 a writer (thread id 140666819790592) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3100 Last time write locked in file ...
Production Ready MySQL
A default MySQL installation is not ready for production usage.
Point in time recovery
If you care even remotely about your data you require the MySQL Binary logs to perform data recovery.
$ sudo vi /etc/my.cnf
NOTE: The MySQL configuration uses different settings, be sure to use [mysqld].
[mysqld] ... server-id=1 log-bin
NOTE: This is not a dynamic variable, a MySQL instance restart is required.
$ sudo service mysqld restart
At this time you should re-start your benchmarking and let it run for 5 minutes to evaluate the differences and before next change.
Durability (the D of ACID)
If you want proper durability you need to enable better flushing of data to disk.
mysql> SET GLOBAL sync_binlog=1;
NOTE: This is a dynamic variable, look at vmstat, mysqladmin and New Relic output to see impact.
NOTE: This is affected in MySQL 5.6 by the group commit. Discussed later.
Let this run for a few minutes and evaluate monitoring.
InnoDB log flushing
MySQL allows you to relax the flushing of the InnoDB transaction log file. This is a tradeoff many high volume websites that hold non-critical information consider to improve I/O. The rationale is to let the disk subsystem with appropriate BBRU cache to cater for a loss of writes up to 1 second. In a cloud/virtualized environment this is impossible to determine.
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ mysql> SHOW GLOBAL STATUS LIKE 'innodb_%fsyncs'; +------------------------------+--------+ | Variable_name | Value | +------------------------------+--------+ | Innodb_data_fsyncs | 785428 | | Innodb_data_pending_fsyncs | 2 | | Innodb_os_log_fsyncs | 770315 | | Innodb_os_log_pending_fsyncs | 1 | +------------------------------+--------+ 4 rows in set (0.00 sec)
mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Let this run for a few minutes and evaluate monitoring.
Binary Log Flushing
mysql> SET GLOBAL sync_binlog=0;
We can now reset the binary log flushing so we have maximum I/O throughput.
Let this run for a few minutes and evaluate monitoring.
You should now stop benchmarking and review New Relic statistics. This gap in load is useful for evaluating the next test.
MySQL Replication
Launch the second VM that will become a MySQL slave.
If the VM is an import appliance, then set the hostname to your given name first.
$ sudo /usr/local/bin/new_hostname.sh <name>
The following script will configure the OS.
$ sudo /usr/local/bin/slave.sh
This script will take care of the following to have a unique server.
- Reconfigure IP
- Reset HW MAC devices (needed for VM's)
- Change Hostname
After restarting, confirm you can ssh to 192.168.42.20
$ ssh [email protected]
Master Setup
On the master that is already configured with log-bin enabled a new MySQL user is needed for replication.
$ mysql mysql> CREATE USER [email protected] IDENTIFIED BY 'zoolander'; mysql> GRANT REPLICATION CLIENT ON *.* TO [email protected];
In order to connect to the master, you require the position of the binary logs based on the data that is being restored.
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysqld-bin.000002
Position: 349
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
To get the specific syntax you can cheat with mysqldump.
$ mysqldump --master-data --no-data --no-create-info mysql | grep CHANGE CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=349;
Slave Setup
On the slave we need to configure the MySQL instance to be in a replication topology.
$ sudo vi /etc/my.cnf
[mysqld] ... server-id=2
$ sudo service mysqld restart
$ ping -c 2 192.168.42.10
$ mysql -urepl -pzoolander -h192.168.42.10 -e "SHOW GRANTS"
$ mysql
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.42.10',
MASTER_USER='repl',
MASTER_PASSWORD='zoolander';
# The following will vary depending on your output
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=349;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
Replication setup will fail (intentionally)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting to reconnect after a failed registration on master
Master_Host: 192.168.42.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000002
Read_Master_Log_Pos: 349
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysqld-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1597
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'192.168.42.20' (using password: YES) (Errno: 1045)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Back on the master we correct the problem with the right permission.
mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected];
By the time you return to the slave, it should have automatically detected the correction and should have started replication.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.42.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000002
Read_Master_Log_Pos: 677
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 582
Relay_Master_Log_File: mysqld-bin.000002
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: 677
Relay_Log_Space: 739
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: 1
Troubleshooting
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
More Information
More reading can be found at MySQL Replication HowTo
Upgrade to MySQL 5.6
Working on the slave first, we are going to perform the start of a rolling upgrade, a popular technique for upgrading in which you can update the slave first, verify usage and operation and then failover to the slave.
We can config the yum repository for upgrade with:
$ sudo yum-config-manager --disable mysql55-community $ sudo yum-config-manager --enable mysql56-community $ yum info mysql-community-server
Installed Packages Name : mysql-community-server Arch : x86_64 Version : 5.5.44 Release : 2.el6 Size : 161 M Repo : installed From repo : mysql55-community ... Available Packages Name : mysql-community-server Arch : x86_64 Version : 5.6.25 Release : 2.el6 Size : 53 M Repo : mysql56-community Summary : A very fast and reliable SQL database server URL : http://www.mysql.com/ ...
WARNING: Be careful. There is mysql-server and mysql-community-server.
$ yum info mysql-server
Loaded plugins: security
Available Packages
Name : mysql-server
Arch : x86_64
Version : 5.1.73
Release : 5.el6_6
Size : 8.6 M
Repo : public_ol6_latest
Summary : The MySQL server and related files
URL : http://www.mysql.com
License : GPLv2 with exceptions
Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
: client/server implementation consisting of a server daemon (mysqld)
: and many different client programs and libraries. This package contains
: the MySQL server and some accompanying files and directories.
Without Internet Connectivity
Copy rpms from USB to 192.168.42.10:/tmp
# On host machine $ cd /local/path/to/usb $ scp rpm/*5-6* [email protected]:/tmp
# On VM cd /tmp sudo yum localinstall -y `ls *5.6*.rpm`
With Internet Connectivity
$ sudo yum update -y mysql-community-server
... ================================================================================================================================ Package Arch Version Repository Size ================================================================================================================================ Updating: mysql-community-server x86_64 5.6.25-2.el6 mysql56-community 53 M Updating for dependencies: mysql-community-client x86_64 5.6.25-2.el6 mysql56-community 18 M mysql-community-common x86_64 5.6.25-2.el6 mysql56-community 307 k mysql-community-libs x86_64 5.6.25-2.el6 mysql56-community 1.9 M mysql-community-libs-compat x86_64 5.6.25-2.el6 mysql56-community 1.6 M Transaction Summary ================================================================================================================================ Upgrade 5 Package(s) ... Updated: mysql-community-server.x86_64 0:5.6.25-2.el6 Dependency Updated: mysql-community-client.x86_64 0:5.6.25-2.el6 mysql-community-common.x86_64 0:5.6.25-2.el6 mysql-community-libs.x86_64 0:5.6.25-2.el6 mysql-community-libs-compat.x86_64 0:5.6.25-2.el6 Complete!
$ mysql -e "SELECT VERSION()" +-----------+ | VERSION() | +-----------+ | 5.6.25 | +-----------+
Upgraded right? Wrong!
Always, Always start with the MySQL error log when reviewing performance, startup, changes and upgrades.
$ tail -20 /var/log/mysqld.log ... 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'users' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure 2015-07-24 17:28:31 31214 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure 2015-07-24 17:28:31 31214 [Note] Event Scheduler: Loaded 0 events 2015-07-24 17:28:31 31214 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.25-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
The upgrade produced a large number of errors.
$ sudo mysql_upgrade
$ sudo mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck with default connection arguments Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck with default connection arguments Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck with default connection arguments Warning: Using a password on the command line interface can be insecure. OK
The actual process does not require sudo but the writing of the output to the default datadir requires mysql privileges or you get this message.
Could not create the upgrade info file '/var/lib/mysql/mysql_upgrade_info' in the MySQL Servers datadir, errno: 13This in turn is used to stop re-running of the script.
$ mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck This installation of MySQL is already upgraded to 5.6.25, use --force if you still need to run mysql_upgrade
Production Upgrade Planning
As with upgrading of any software a lot more is involved then just updating the software itself. Steps include:
- Backup your data (and software)
- Review release notes MySQL 5.6 Release Notes
- Review new features What Is New in MySQL 5.6
- Review possible incompatibilities Upgrading from MySQL 5.5 to 5.6
- Benchmark
- Test, Test and retest the process
MySQL 5.6 can be a drop in for MySQL 5.5 if using just existing functionality. There are at least two common compatibility issues.
- New Reserved Words
- TIMESTAMP default handling
Timestamp warning
In the error log you will see
2015-07-22 17:44:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
With MySQL 5.6 perhaps the most important changes are in the default values of several configuration variables. This can impact your system (both good and bad) if you already leave these configuration options presently as default. It can also cause a number of problems, in particular when you perform an online upgrade and restart the MySQL instance at a later time.
Recommended reading includes https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html, http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html, https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html, http://dev.mysql.com/doc/relnotes/mysql/5.6/en/index.html.
In this tutorial we are going to discuss three configuration changes and their relative impacts.
- innodb_file_per_table
- innodb_buffer_pool_instances
- innodb_log_file_size
In a well defined MySQL 5.5 environment you have likely configured these variables. In our tutorial, they are untuned.
innodb_file_per_table
To show the impact, we restart the MySQL Instance and run a simple benchmark.
$ sudo service mysqld restart $ mysql -e "DROP SCHEMA IF EXISTS mysqlslap" $ mysqlslap --auto-generate-sql --verbose --no-drop
$ sudo ls -lh /var/lib/mysql/mysqlslap/ total 112K -rw-rw----. 1 mysql mysql 65 Jul 24 18:18 db.opt -rw-rw----. 1 mysql mysql 8.5K Jul 24 18:18 t1.frm -rw-rw----. 1 mysql mysql 96K Jul 24 18:18 t1.ibd
MySQL has moved from using a default InnoDB tablespace to a per-table tablespace. At present our environment is actually a hybrid which is even worse.
innodb_buffer_pool_instances
To handle memory effectively for large buffer pools multiple pool instances should be used. In 5.5 this was optional, in 5.6 this is configured based on a number of variables.
innodb_log_file_size
In MySQL 5.5 a change in log file size required both a restart of the instance and the safe removal of prior log files. In 5.6 a restart will eliminate the second step. We can see this change easily with defaults, this shows 2 log files in the group of 48M each, eariler (in listing above) they were 5M.
$ sudo ls -lh /var/lib/mysql/ total 123M -rw-rw----. 1 mysql mysql 56 Jul 24 17:28 auto.cnf -rw-rw----. 1 mysql mysql 26M Jul 24 18:18 ibdata1 -rw-rw----. 1 mysql mysql 48M Jul 24 18:18 ib_logfile0 -rw-rw----. 1 mysql mysql 48M Jul 24 17:28 ib_logfile1 drwx------. 2 mysql mysql 4.0K Jul 24 17:31 mysql -rw-rw----. 1 mysql mysql 77K Jul 24 18:18 mysqld-bin.000001 -rw-rw----. 1 mysql mysql 20 Jul 24 17:28 mysqld-bin.index drwx------. 2 mysql mysql 4.0K Jul 24 18:18 mysqlslap srwxrwxrwx. 1 mysql mysql 0 Jul 24 17:28 mysql.sock -rw-r--r--. 1 root root 6 Jul 24 17:31 mysql_upgrade_info drwx------. 2 mysql mysql 4.0K Jul 24 17:31 performance_schema drwx------. 2 mysql mysql 4.0K Jul 24 16:43 sbtest
New Features
MySQL 5.6 offers numerous new and improved features, generally these are what drive upgrading software. While there is a long list, a few that are important are.
- Performance improvments with larger cores, concurrent connections and group commit.
- Vastly improved Performance schema
- Security improvments
Group Commit
In MySQL 5.5 benchmarking we demonstrated the impact of secure durability with sync_binlog=1. In MySQL 5.6 this is greatly improved with group commit functionality, and is generally considered minimual to now enable.
Rerun benchmarks with 0 and 1
The Performance Schema
While available in MySQL 5.5 (17 tables) there has been a great improvment in MySQL 5.6 (52 tables), and also in MySQL 5.7 of (73 tables). In addition the sys schema (https://github.com/MarkLeith/mysql-sys), which is included by default in MySQL 5.7 provides a great amount of user-friendly access to the internals and performance of MySQL. This one feature alone is worth the price of upgrade if you do not have dedicated, well training and experienced MySQL performance experts.
The 'sys' schema
$ cd $HOME/mysql-sys $ mysql < sys_56.sql
This has 82 view objects, a number duplicated as they have been re-introduced as x$ tables.
$ sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --max-requests=100000000 --num-threads=3 --report-interval=5 --report-checkpoints=10 run
$ mysqladmin -r -i 5 extended-status | grep -ie "com_insert " -ie "com_select" -e "com_update "
mysql> use sys;
mysql> select * from x$statement_analysis order by exec_count desc limit 1\G
*************************** 1. row ***************************
query: INSERT INTO sbtest1 ( id , k , c , pad ) VALUES (...)
db: sbtest
full_scan:
exec_count: 9781
err_count: 0
warn_count: 0
total_latency: 177157839304000
max_latency: 324741004000
avg_latency: 18112446000
lock_latency: 765344000000
rows_sent: 0
rows_sent_avg: 0
rows_examined: 0
rows_examined_avg: 0
rows_affected: 9781
rows_affected_avg: 1
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 55f1ac975cd97ea63cf29e3568c2daea
first_seen: 2015-07-24 18:46:14
last_seen: 2015-07-24 18:47:13
1 row in set (0.00 sec)
mysql> select * from x$io_global_by_file_by_bytes limit 10; +-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | +-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+ | /var/lib/mysql/ibdata1 | 533 | 10797056 | 20257.1407 | 1873 | 59179008 | 31595.8398 | 69976064 | 84.57 | | /var/lib/mysql/ib_logfile0 | 6 | 69632 | 11605.3333 | 20156 | 28941824 | 1435.8912 | 29011456 | 99.76 | | /var/lib/mysql/mysqld-bin.000002 | 0 | 0 | 0.0000 | 53107 | 24542484 | 462.1328 | 24542484 | 100.00 | | /var/lib/mysql/ib_logfile1 | 0 | 0 | 0.0000 | 9637 | 13744128 | 1426.1833 | 13744128 | 100.00 | | /var/lib/mysql/mysql/innodb_index_stats.ibd | 3 | 49152 | 16384.0000 | 2 | 32768 | 16384.0000 | 81920 | 40.00 | | /var/lib/mysql/mysql/innodb_table_stats.ibd | 3 | 49152 | 16384.0000 | 2 | 32768 | 16384.0000 | 81920 | 40.00 | | /var/lib/mysql/mysql/slave_master_info.ibd | 4 | 65536 | 16384.0000 | 0 | 0 | 0.0000 | 65536 | 0.00 | | /var/lib/mysql/mysql/slave_relay_log_info.ibd | 4 | 65536 | 16384.0000 | 0 | 0 | 0.0000 | 65536 | 0.00 | | /var/lib/mysql/mysql/slave_worker_info.ibd | 4 | 65536 | 16384.0000 | 0 | 0 | 0.0000 | 65536 | 0.00 | | /usr/share/mysql/english/errmsg.sys | 3 | 60110 | 20036.6667 | 0 | 0 | 0.0000 | 60110 | 0.00 | +-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+ 10 rows in set (0.00 sec)
mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=134883 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)
Demonstrating innodb_file_per_table
mysql> RENAME TABLE sbtest.sbtest1 TO sbtest.sbtestold; mysql> CREATE TABLE sbtest.sbtest1 lIKE sbtest.sbtestold; mysql> INSERT INTO sbtest.sbtest1 SELECT * FROM sbtest.sbtestold; mysql> SELECT * FROM x$io_global_by_file_by_bytes LIMIT 5; +-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+-----------+-----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | +-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+-----------+-----------+ | /var/lib/mysql/ibdata1 | 1582 | 27983872 | 17688.9204 | 2314 | 109199360 | 47190.7347 | 137183232 | 79.60 | | /var/lib/mysql/sbtest/sbtest1.ibd | 0 | 0 | 0.0000 | 2423 | 92831744 | 38312.7297 | 92831744 | 100.00 | | /var/lib/mysql/ib_logfile1 | 0 | 0 | 0.0000 | 15614 | 58245120 | 3730.3138 | 58245120 | 100.00 | | /var/lib/mysql/ib_logfile0 | 6 | 69632 | 11605.3333 | 25532 | 36644864 | 1435.2524 | 36714496 | 99.81 | | /var/lib/mysql/mysqld-bin.000002 | 0 | 0 | 0.0000 | 73279 | 33865662 | 462.1469 | 33865662 | 100.00 |
Demonstrating Read Load
$ sysbench --test=/usr/share/doc/sysbench/tests/db/select.lua --max-requests=100000000 --num-threads=3 --report-interval=5 --report-checkpoints=10 run
mysql> select * from x$io_by_thread_by_latency; +---------------------+-------+----------------+-------------+-------------------+---------------+-----------+----------------+ | user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id | +---------------------+-------+----------------+-------------+-------------------+---------------+-----------+----------------+ | page_cleaner_thread | 9895 | 38190618340902 | 21888 | 4246964379.0000 | 880958997000 | 18 | NULL | | io_handler_thread | 218 | 17744951960532 | 2071810692 | 81398862198.0000 | 471101104278 | 2 | NULL | | io_handler_thread | 109 | 13275830934252 | 637199010 | 121796613900.0000 | 443683989252 | 3 | NULL | | io_handler_thread | 91 | 11279843928180 | 450658872 | 123954328836.0000 | 964304912736 | 4 | NULL | | io_handler_thread | 79 | 9774581175846 | 541240308 | 123728875596.0000 | 1534082572836 | 5 | NULL | | dba@localhost | 3446 | 6920731195776 | 21888 | 2177024788.0000 | 599345019072 | 21 | 2 | | srv_master_thread | 519 | 6647367640554 | 21888 | 8032221576.0000 | 1850165177058 | 14 | NULL | | io_handler_thread | 182 | 2110964444010 | 283719096 | 11598705648.0000 | 227050558866 | 10 | NULL | | main | 1531 | 172523598030 | 21888 | 767181012.0000 | 65943403740 | 1 | NULL | | srv_purge_thread | 15 | 128080026 | 2077650 | 8538372.0000 | 13931370 | 12 | NULL | | signal_handler | 3 | 61053156 | 2600226 | 20351052.0000 | 42681600 | 19 | NULL | +---------------------+-------+----------------+-------------+-------------------+---------------+-----------+----------------+ 11 rows in set (0.12 sec)
Revisiting INNODB STATUS
In MySQL 5.5 there are 10 INFORMATION_SCHEMA.INNODB related tables. In MySQL 5.6 there are 28.
mysql> select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA' AND table_name like 'innodb%'; +----------------------------+ | table_name | +----------------------------+ | INNODB_LOCKS | | INNODB_TRX | | INNODB_SYS_DATAFILES | | INNODB_LOCK_WAITS | | INNODB_SYS_TABLESTATS | | INNODB_CMP | | INNODB_METRICS | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_FT_DELETED | | INNODB_BUFFER_PAGE_LRU | | INNODB_SYS_FOREIGN | | INNODB_SYS_COLUMNS | | INNODB_SYS_INDEXES | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_SYS_FIELDS | | INNODB_CMP_PER_INDEX_RESET | | INNODB_BUFFER_PAGE | | INNODB_CMPMEM | | INNODB_FT_INDEX_TABLE | | INNODB_FT_BEING_DELETED | | INNODB_SYS_TABLESPACES | | INNODB_FT_INDEX_CACHE | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_TABLES | | INNODB_BUFFER_POOL_STATS | | INNODB_FT_CONFIG | +----------------------------+ 28 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 4385437
trx_state: RUNNING
trx_started: 2015-07-24 18:59:48
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 19
trx_query: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5020, '29858002693-99284354604-97986798821-31184705126-26818546836-81812223368-49873972403-80374189540-93438477554-46359271769', '31504863415-81495921832-78032853060-81184376668-78200869529')
trx_operation_state: flushing log
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
InnoDB Buffer Pool hit ratio
mysql> select * from sys.innodb_buffer_stats_by_table; +---------------+----------------------+-----------+-----------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+----------------------+-----------+-----------+-------+--------------+-----------+-------------+ | sbtest | sbtest1 | 85.92 MiB | 75.66 MiB | 5499 | 5499 | 5499 | 364677 | | sbtest | sbtestold | 20.91 MiB | 18.70 MiB | 1338 | 1338 | 1338 | 96708 | | InnoDB System | SYS_FOREIGN | 32.00 KiB | 0 bytes | 2 | 2 | 2 | 0 | | InnoDB System | SYS_TABLES | 32.00 KiB | 1.43 KiB | 2 | 2 | 2 | 13 | | InnoDB System | SYS_COLUMNS | 16.00 KiB | 5.44 KiB | 1 | 1 | 1 | 84 | | InnoDB System | SYS_DATAFILES | 16.00 KiB | 424 bytes | 1 | 1 | 1 | 8 | | InnoDB System | SYS_FIELDS | 16.00 KiB | 916 bytes | 1 | 1 | 1 | 22 | | InnoDB System | SYS_TABLESPACES | 16.00 KiB | 416 bytes | 1 | 1 | 1 | 8 | | mysql | innodb_index_stats | 16.00 KiB | 1.73 KiB | 1 | 1 | 1 | 20 | | mysql | innodb_table_stats | 16.00 KiB | 244 bytes | 1 | 1 | 1 | 4 | | mysql | slave_master_info | 16.00 KiB | 0 bytes | 1 | 1 | 1 | 0 | | mysql | slave_relay_log_info | 16.00 KiB | 0 bytes | 1 | 1 | 1 | 0 | | mysql | slave_worker_info | 16.00 KiB | 0 bytes | 1 | 1 | 1 | 0 | +---------------+----------------------+-----------+-----------+-------+--------------+-----------+-------------+ 13 rows in set (1.17 sec)
Seeing all threads
mysql> SELECT * from processlist\G
*************************** 5. row ***************************
thd_id: 33
conn_id: 14
user: sbtest@localhost
db: sbtest
command: Query
state: freeing items
time: 0
current_statement: SELECT pad FROM sbtest1 WHERE id=5045
lock_latency: 34.00 us
rows_examined: 1
rows_sent: 1
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
last_wait: NULL
last_wait_latency: NULL
source: NULL
...
*************************** 21. row ***************************
thd_id: 12
conn_id: NULL
user: innodb/srv_purge_thread
db: NULL
command: NULL
state: NULL
time: NULL
current_statement: NULL
lock_latency: NULL
rows_examined: NULL
rows_sent: NULL
rows_affected: NULL
tmp_tables: NULL
tmp_disk_tables: NULL
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
last_wait: NULL
last_wait_latency: NULL
source: NULL
Index utilization
mysql> SELECT * FROM x$schema_index_statistics WHERE table_name='sbtest1'\G
*************************** 1. row ***************************
table_schema: sbtest
table_name: sbtest1
index_name: PRIMARY
rows_selected: 5707813
select_latency: 215928628194276
rows_inserted: 0
insert_latency: 0
rows_updated: 0
update_latency: 0
rows_deleted: 0
delete_latency: 0
*************************** 2. row ***************************
table_schema: sbtest
table_name: sbtest1
index_name: k_1
rows_selected: 0
select_latency: 0
rows_inserted: 0
insert_latency: 0
rows_updated: 0
update_latency: 0
rows_deleted: 0
delete_latency: 0
2 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE sbtest.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=540574 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)
mysql> set global innodb_flush_log_at_trx_commit=2; Query OK, 0 rows affected (0.00 sec) ... | Com_update | 0 | | Com_insert | 2755 | | Com_select | 33343 | | Com_update | 0 | | Com_insert | 2729 | | Com_select | 32998 | | Com_update | 0 | | Com_insert | 2744 | | Com_select | 34069 | | Com_update | 0 | | Com_insert | 6727 | | Com_select | 35064 | | Com_update | 0 | | Com_insert | 11733 | | Com_select | 26735 | | Com_update | 0 |
Take a snapshot of your VM before configuration replication. You can then clone this VM and launch a second instance.
VM Setup
Image Creation
- Download Oracle Linux 6.6
- Download VirtualBox 5
- Install VirtualBox 5
- Start VirtualBox
- Define Host-only Network
- VirtualBox|Preferences|Network
- Host-only Networks
- Add
- IPv4 Address: 192.168.42.1
- Disable DHCP Server
Installation Steps
Installation # Update base OS yum update yum upgrade -y # Disable IP Tables chkconfig iptables off chkconfig ip6tables off # Configure Networking sed -i "s/ONBOOT=no/ONBOOT=yes/" /etc/sysconfig/network-scripts/ifcfg-eth0 echo "DEVICE=eth1 BOOTPROTO=static IPADDR=192.168.42.10 NETMASK=255.255.255.0" > /etc/sysconfig/network-scripts/ifcfg-eth1 # Install MySQL cd /tmp wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm yum localinstall -y mysql-community-release-el6-5.noarch.rpm rm -f mysql-community-release-el6-5.noarch.rpm sudo yum-config-manager --disable mysql56-community sudo yum-config-manager --enable mysql55-community yum repolist enabled | grep mysql yum install -y mysql-community-server rpm -qa | grep mysql service mysqld start chkconfig mysqld on mysql -uroot -e "SELECT VERSION()" # Install Apache yum install -y httpd chkconfig httpd on echo "ServerName zoo" > /etc/httpd/conf.d/servername.conf service httpd start # Install New Relic rpm -Uvh http://download.newrelic.com/pub/newrelic/el5/i386/newrelic-repo-5-3.noarch.rpm yum install -y newrelic-sysmond nrsysmond-config --set license_key=1f50aab7a48cc9b66170ec2fc95289971c183186 /etc/init.d/newrelic-sysmond start LICENSE_KEY=1f50aab7a48cc9b66170ec2fc95289971c183186 bash -c "$(curl -sSL https://download.newrelic.com/npi/release/install-npi-linux-redhat-x64.sh)" cd /root/newrelic-npi # The following is an Interactive Installation ./npi install com.newrelic.plugins.mysql.instance # Patch script for later use sed -i 'i cd `dirname $0`' /root/newrelic-npi/npi mysql -e "CREATE USER newrelic@localhost IDENTIFIED BY 'zookeeper';GRANT PROCESS,REPLICATION CLIENT ON *.* TO newrelic@localhost;CREATE USER [email protected] IDENTIFIED BY 'zookeeper';GRANT PROCESS,REPLICATION CLIENT ON *.* TO [email protected];" # Install MySQL Utilities sudo yum install -y mysql-utilities # Non privileged account useradd -m -G mysql -s /bin/bash dba passwd dba umask 226 && echo "dba ALL=(ALL) NOPASSWD: ALL" > /etc/sudoers.d/dba mysql -e "CREATE USER dba@localhost IDENTIFIED BY 'zookeeper';GRANT ALL ON *.* TO dba@localhost WITH GRANT OPTION" yum install -y git-core su - dba git clone https://github.com/MarkLeith/mysql-sys echo "[client] user=$USERNAME password=zookeeper" > ${HOME}/.my.cnf mysql -e "SELECT USER()" cd /tmp wget http://www.lefred.be/files/sysbench-0.5-3.el6_.x86_64.rpm sudo rpm -Uvh sysbench-0.5-3.el6_.x86_64.rpm rm -f sysbench-0.5-3.el6_.x86_64.rpm sysbench mysql -e "CREATE USER sbtest@localhost; GRANT ALL ON sbtest.* TO sbtest@localhost;CREATE SCHEMA sbtest" sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua prepare sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua run