To show that MySQL runs on top of Oracle Virtual Machine and Oracle Enterprise Linux would maximize the value for the customers by offering the lowest total cost of ownership, the highest performance, scalability and productivity. This Oracle LVM integrated demo includes several software/techinques from Oracle, they are MySQL Enterprise Monitor, MySQL Database, MySQL replication, MySQL Proxy, Oracle Virtual Machine, Oracle Virtual Machine Template and Oracle Linux. Those products would bring the following the users:
MySQL Enterprise Monitor helps MySQL DBA managing the database, improving performance, and increase DBA's skill. It maximize the SLA of the database and help the DBAs increase their productivity.
MySQL Proxy enable scale out solution by offering read-write splitting features in the most cost effective manner.
MySQL replication helps the user increase availability and capacity of the database in a lowest cost.
The user would enjoin the best performance by adopting MySQL on top of Oracle Enterprise Linux.
Oracle Virtual Machine template for MySQL increase the productivity of the DBA by per-configured/optimized MySQL database, and decrease the resource needed for deploying MySQL.
Oracle Virtual Machine would help the users with maximize HA from virtual machine layer, it helps MySQL application get zero down time, when migrating the virtual machine between different servers.
Demo Environment
The Servers of the Demo Environment
Install and Configure the Demo EnvironmentAs above graph shows, we apply 3 physical servers – ovmm, ov1, ovs2, ovmm contains OVM Mangaer, PHP pages that act as MySQL client application, and MySQL Enterprise Monitor, ovs1 and ovs2 need to be registered on Oracle Virtual Machine manager as virtual machine server, ovs2 contains one virtual machine for master database, and one virtual machine for first slave, ovs2 contains one virtual machine for 2nd slave database, and ovs2 also act as the backup server for the master database, we will demo the fail-over features by live migration on the Virtual Machine layer. The details of install OVM with HA please refer the document http://www.rachelp.nl/userfiles/file/Create_oraclevm_haserverpool.pdf and. There are several points need to take care of while installing the environment, I list them as below:
For high availability from the virtual machine layer, we need to configure HA options with OVM servers pool on OVM manager, and the servers that support HA need to attach to a shared storage, in this case, it is NFS, and we need to add read_write, sync, and no_root_squash options when doing NFS export, refer http://download.oracle.com/docs/cd/E15458_01/doc.22/e15440/toc.htm#BABDEFDG for the details.
MySQL virtual machine template will runs firewall when ever is started, we need to turned if off manually.
To enable replication and MySQL Enterprise Monitor Agent, the time lag between each virtual machine servers could not larger than a certain period, for say 15 seconds.
The available space on the shared storage need to larger than 70 GB.
For some cases when restart virtual machine the option file - /etc/my.cnf could reset to the default value.
Demo Flow
1 Show MySQL, OVM, Oracle Linux, Oracle Virtual Machine, Oracle Virtual Machine Template for MySLQ download address on Oracle eDelivery.
2 OVM/OEL Demo
- Navigate OVM interface.
Create/Initiate virtual machine with Oracle Virtual Machine Template for MySQL (the template was imported on the “Resource” tab).
3 MySQL Demo- Show slide 17 of MySQL technical presentation, explain the demo scenario to the audience
- Show MySQL master on 10.186.122.69
login MySQL master server, with username “root” and pssword “oracle” by
mysql -h 10.186.122.69 -P 3306 -u root -p oracle
use world;
show tables;
select code, name, continent, population, gnp from Country;
- Show MySQL slave 1 on 10.186.122.72 and slave 2 on 10.186.122.174
mysql -h 10.186.122.72 -P3306 -u root -p oracle
show slave status\G
use world;
show tables;
select code, name, continent, population, gnp from Country;
login MySQL slave 1 with usrname “root”, password “oracle”
mysql -h 10.186.122.174 -P3306 -u root -p oracle
show slave status\G
use world;
show tables;
select code, name, continent, population, gnp from Country;
- Show MySQL Proxy on ovmm.sg.oracle.com
ps -ef | grep proxy (show MySQL Proxy command)
- Show PHP demo application source code on ovmm.sg.oracle.com
high light the code of
$con = mysql_connect("127.0.0.1:4040", "root", "oracle");
show the audience that the database connection is through MySQL Proxy, and read-write splitting is accomplished by this service.
- Display phpQueryPage page from browser, the URL is below:
show audience the host name on the page, it will change to the other slave when the current slave server is down (it shows slave 1 at this moment)
shutdown slave 1 (shutdown virtual machine – slave 1 is an approach that close to the real case, but it take more time, shutdown database would be fast, it should be easier to keep the pace of demo)
show audience the host name on the page, it had changed to the slave 2 at this moment
- Demo the master slave replication feature by:
Query the data by type “HKG” in the “Code” field, and press enter
Insert one record by changing the “Code” field form “HKG” to “ABX”, and press “Insert” button, and query the new record out by type “ABX” on the “Code” filed then press “Query” button.
Back to phpQueryPage.php, the country code “ABX” shows on the query page
- Demo the live migration feature, and MySQL HA supported by the feature
Live migration from OVM interface, from OVM manager by pick “Live Migration” from the “More Action” list.
Show “ping Show the message sequence number leap, but the phpUpdatePage.php works fine after live migration, the user interface has no interruption for the migration on the virtual machine layer.
Chart 3. Read-write benchmark with transaction per second measurement
