MySQL Replication vs mysqlslap on EC2

Getting familiar with any product means either jumping in the deep end and hoping the weight of new knowledge is not enough to sink you or taking incremental steps walking from the shallow end towards the deep end (of knowledge in depth).

So as I proceed towards a robust test of MySQL Cluster on EC2, I thought it would be useful to see how MySQL holds up against a mysqlslap load simulation as a standard alone database and in a MySQL replication Master->Slave setup.
You can see my results for the standard databases in this post.

So the next step was to setup a standard master-slave replication. I have done this before, but it is always useful to keep the documentation handy. You will find the outline and how to setup MySQL replication from MySQL’s website.

Setting up MySQL replication on EC2 was as simple as firing up two instances of the same AMI with MySQL 5.1 installed and making the necessary changes to the master and slave my.cnf files.
Whilst you could have foregone the process of rebuilding the slave database from a master backup, it is always good practice to run though the steps. You could foregone this step as the database is actually the same on both instances as it is the same AMI.

Once the replication was configured and running I run the same tests as I ran the other day using mysqlslap.

Comments:

  • The slave database never had any trouble even when the master was struggling until I increased the size of the auto-generated table significantly which suggests that the network bandwidth became the bottleneck rather than the IO or CPU bandwidth.
  • MySQL started with the medium my.cnf sample and modified max_connections couldn’t handle more than 750 concurrent sessions without mysql complaining on a lack of resources and causing mysqlslap to die.
  • At 500 concurrent sessions, the MySQL EC2 node was tapped out. Load spiked to 180 at one point.

As I mentioned this was the first step and using a simulated auto-generated workload. I am reviewing the new sample database called Sakila, provided by MySQL.

As I have time I am planning on building this series into a full blown performance test of MySQL on EC2. Essentially I am hoping that this work will allow people to understand exactly how MySQL scales on EC2 so they can resource their expected workload with confidence or alternatively know how to use mysqlslap to produce their own performance and scaling benchmarks.

Next up will be another run of MySQL 5.1 NDB cluster vs mysqlslap.

Have Fun

Paul

Previous Articles:
http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html

Here is the screen dump of my session



cat /etc/hosts

127.0.0.1 localhost
# Mysql replication
10.255.71.159 mysql-master
10.255.83.177 mysql-slave

Much sure my.cnf is set appropriately and restart MySQL:

service mysql.server restart
Shutting down MySQL.. [ OK ]
Starting MySQL [ OK ]

Setup MySQL replication:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create the replication user and grant replication privileges:

mysql> create user myslave IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

Lock the tables during the backup to get a consistent backup:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

After the backup is complete unlock the tables again:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Grab the master status info

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 488
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Backup the master database using mysqldump

mysqldump --all-databases --lock-all-tables -u root -p >dbdump.db
Enter password:

Copy the files to slave:

scp dbdump.db mysql-slave:/mnt
dbdump.db 100% 392KB 391.8KB/s 00:00

Set the Master setting on the slave:

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_PORT=3306,
MASTER_USER='myslave',
MASTER_PASSWORD='53cr37',
MASTER_LOG_POS=488;

mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37',
-> MASTER_LOG_POS=488;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 488
Relay_Log_File: domU-12-31-35-00-52-43-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
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: 488
Relay_Log_Space: 423
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:
1 row in set (0.00 sec)

Running mysqlslap load simulation tests:

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 \
--user=root --password

cat /tmp/mysqlslap.csv

blackhole,mixed,0.024,0.012,0.072,1,100
blackhole,mixed,0.039,0.020,0.083,25,4
blackhole,mixed,0.072,0.030,0.091,50,2
blackhole,mixed,0.094,0.051,0.115,100,1
myisam,mixed,0.026,0.012,0.075,1,100
myisam,mixed,0.057,0.026,0.091,25,4
myisam,mixed,0.061,0.033,0.124,50,2
myisam,mixed,0.109,0.052,0.119,100,1


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --password

cat /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.192,0.165,0.231,1,1000
blackhole,mixed,0.227,0.179,0.295,25,40
blackhole,mixed,0.253,0.188,0.312,50,20
blackhole,mixed,0.283,0.203,0.345,100,10
myisam,mixed,0.255,0.194,0.318,1,1000
myisam,mixed,0.282,0.205,0.354,25,40
myisam,mixed,0.302,0.275,0.362,50,20
myisam,mixed,0.357,0.306,0.403,100,10


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_q10000.csv

blackhole,mixed,2.145,2.051,2.244,1,10000
blackhole,mixed,2.210,2.107,2.275,25,400
blackhole,mixed,2.128,1.953,2.240,50,200
blackhole,mixed,2.420,2.085,3.031,100,100
myisam,mixed,2.651,2.618,2.751,1,10000
myisam,mixed,2.710,2.637,2.821,25,400
myisam,mixed,2.698,2.637,2.788,50,200
myisam,mixed,2.901,2.599,3.741,100,100

mysqlslap --concurrency=1,25,50,100,150 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql --csv=/tmp/mysqlslap_bigtab_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_bigtab_q10000.csv

blackhole,mixed,2.958,2.877,3.054,1,10000
blackhole,mixed,2.993,2.946,3.219,25,400
blackhole,mixed,2.982,2.897,3.088,50,200
blackhole,mixed,3.247,2.902,3.788,100,100
blackhole,mixed,3.262,2.966,4.075,150,66
myisam,mixed,3.540,3.442,3.628,1,10000
myisam,mixed,3.577,3.516,3.652,25,400
myisam,mixed,3.608,3.537,3.661,50,200
myisam,mixed,3.647,3.508,3.720,100,100
myisam,mixed,4.298,3.608,5.188,150,66

Setting the max_connections setting higher so I can test more than 150
concurrent sessions:


mysql> set global max_connections = 1024;
Query OK, 0 rows affected (0.00 sec)

mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
--auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password

Sample of the top from the master with mysqlslap running:

top - 08:08:23 up 1:10, 2 users, load average: 161.43, 59.07, 26.07
Tasks: 2056 total, 100 running, 1949 sleeping, 0 stopped, 7 zombie
Cpu(s): 62.8% us, 34.6% sy, 0.0% ni, 0.0% id, 1.6% wa, 0.0% hi, 1.0% si
Mem: 1740944k total, 1548008k used, 192936k free, 20684k buffers
Swap: 917496k total, 0k used, 917496k free, 1323268k cached

mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
> --auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
> --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
> --number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,1943137703,190133822,450874686,1121980228,'YhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQW9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAju','ZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQLKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8s','gGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0X','EufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E873FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52Zb','YGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQzZc0l4Qi9fT8HrX4S4CoilYbACNSi0mhPMZARzlcWEcg6zkQ3o6eafDcrS4QSq7u5Z4YYh0vwSbuRB') ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)

tail -f /tmp/mysqlslap_1000c_bigtab_q10000.csv

blackhole,mixed,2.717,2.641,2.830,1,10000
blackhole,mixed,2.730,2.644,2.839,25,400
blackhole,mixed,2.668,2.482,2.747,50,200
blackhole,mixed,2.955,2.723,3.481,100,100
blackhole,mixed,2.969,2.853,3.288,250,40
blackhole,mixed,3.849,3.061,4.874,500,20
blackhole,mixed,5.514,3.760,8.081,1000,10
myisam,mixed,3.099,2.937,3.160,1,10000
myisam,mixed,3.142,3.055,3.263,25,400
myisam,mixed,3.217,3.141,3.268,50,200
myisam,mixed,3.217,3.110,3.319,100,100
myisam,mixed,4.256,3.319,5.561,250,40
myisam,mixed,4.306,3.781,7.740,500,20

Checking the table is ok after that failure

[root@domU-12-31-35-00-40-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49254
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlslap |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysqlslap
Database changed
mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| t1 |
+---------------------+
1 row in set (0.00 sec)

mysql> check table t1 quick;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| mysqlslap.t1 | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.05 sec)


mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)

cat /tmp/mysqlslap_750c_bigtab_q10000.csv

blackhole,mixed,3.682,3.053,4.811,500,20
blackhole,mixed,6.265,3.496,10.161,750,13
myisam,mixed,5.009,3.662,7.803,500,20

mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_write_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1413946291,2008175571,806084542,1096535128,1877357308,2039695798,
1522423739,1247119446,677182057,1658524254,
'4uoCZg37QkwQxLB0Jl7CkWw6qGxpFQbJLFYmvRlDBig13jtESsJCEFA4Nz...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1338459946,2107889310,393787969,1623810840,1403227682,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
Killed

tail -f /tmp/mysqlslap_write_750c_bigtab_q10000.csv

blackhole,write,3.736,2.935,5.308,500,20
blackhole,write,5.025,3.172,7.826,750,13
myisam,write,4.769,3.630,6.532,500,20

Advertisements

MySQL 5.1 NDB Cluster Replication on EC2

As I mentioned previously once the MySQL Cluster was setup, the next thing to test was setting up a MySQL NDB Cluster Replication i.e. Master Cluster and Slave Cluster.

You would use this type of setup in situations where you want availability and redundancy.
So even if your multiple (n+1) management nodes and data nodes and SQL/API nodes die on one site, you can redirect traffic or load to a replicated site.

Essentially the (row based) replication is done via a pair of NDB SQL nodes, with the master and slave configuration similar to the plain vanilla variety of MySQL replication. I would suggest getting that working first before attempting this, just so you gain some familiarity with how MySQL replication works.

The main differences from normal replication was the creation of the slave NDB Cluster from the clone of the Master and the way to check where the NDB apply is up to, so you can set the slave correctly.
The steps to do this setup start in the MySQL documentation.

I used two pairs of multi-purpose nodes (Management, Data and MySQL combined) to setup the MySQL 5.1 NDB Clusters replication. So it was a 2 data node Master cluster and a 2 data node Slave cluster.
Here are my high level steps:

  1. Configure /etc/hosts. Added mysql-ndb-master and mysql-ndb-slave IP addresses
  2. Configure the configure.ini to reflect the number of data nodes and MySQL nodes required.
  3. On either management node issue the START BACKUP command to create a backup.
  4. Copy all files from all NDB data nodes to one of the Slave management nodes.
  5. Restore the NDB database in order by data node_id.
  6. Configure the Master /etc/my.cnf and restart the Master MySQL node.
  7. Configure the Slave /etc/my.cnf and restart the Slave MySQL node.
  8. Verify that SHOW MASTER STATUS and SHOW SLAVE STATUS are correct.

Once that was done the Cluster replication was up and running. I then proceeded to run mysqlslap on the master SQL node to quickly test the replication and it was fine under light load. (more on that later).

As per normal I have dumped my work out so that you can see the same stuff I did whilst I was setting up.

Have Fun

Paul

Previous articles in the series:
MySQL 5.1 NDB Cluster – Part 1.
MySQL 5.1 NDB Cluster – Part 2.
MySQL 5.1 NDB Cluster – Testing redundancy.
MySQL 5.1 NDB Cluster – sample configuration files.



ndb_mgm> show
Connected to Management Server at: mysql-ndb_mgmd1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @10.255.39.130 (Version: 5.1.20, Nodegroup: 0, Master)
id=2 @10.255.7.34 (Version: 5.1.20, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=3 @10.255.39.130 (Version: 5.1.20)
id=4 @10.255.7.34 (Version: 5.1.20)

[mysqld(API)] 2 node(s)
id=5 @10.255.7.34 (Version: 5.1.20)
id=6 @10.255.39.130 (Version: 5.1.20)

Original 2 data node configure.ini file

[root@domU-12-31-35-00-20-74 ~]# cat /usr/local/mysql-cluster/EC2-configure-2node.ini
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 64
MaxNoOfConcurrentOperations= 128
DataMemory= 256M
IndexMemory= 256M
Diskless= false
TimeBetweenWatchDogCheck= 30000
DataDir= /mnt/mysql-cluster/data
MaxNoOfOrderedIndexes= 500
MaxNoOfAttributes= 1000
TimeBetweenGlobalCheckpoints= 500
NoOfFragmentLogFiles= 4
FragmentLogFileSize=12M
DiskPageBufferMemory= 64M
ODirect= 1
# the following parametes just function as a small regression
# test that the parameter exists
InitialNoOfOpenFiles= 27

#
# Increase timeouts to cater for slow test-machines
# (possibly running several tests in parallell)
#
HeartbeatIntervalDbDb= 30000
HeartbeatIntervalDbApi= 30000
#TransactionDeadlockDetectionTimeout= 7500

[ndbd]
HostName= mysql-ndbd1 # hostname is a valid network adress

[ndbd]
HostName= mysql-ndbd2 # hostname is a valid network adress

[ndb_mgmd]
HostName= mysql-ndb_mgmd1 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
[ndb_mgmd]
HostName= mysql-ndb_mgmd2 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[mysqld]

[mysqld]

Create REPLICATION user on master MySQL node

create user myslave IDENTIFIED BY '53cr37';

GRANT REPLICATION SLAVE
ON *.* TO 'myslave'@'mysql-ndb-slave'
IDENTIFIED BY '53cr37';

mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-ndb-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Hosts file on each Master and slave node

cat /etc/hosts

127.0.0.1 localhost
# Mysql Cluster data node
10.255.46.240 mysql-ndbd1
10.255.19.225 mysql-ndbd2
# Mysql Cluster mgm node
10.255.46.240 mysql-ndb_mgmd1
10.255.19.225 mysql-ndb_mgmd2
# Mysql replication
10.255.46.240 mysql-ndb-master
10.255.11.159 mysql-ndb-slave

Master MYSQL node my.cnf file

[root@domU-12-31-35-00-29-02 BACKUP]# cat /etc/my.cnf
# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
log-bin=row
server-id=1

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Slave /etc/my.cnf file

cat /etc/my.cnf

# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
master-host='mysql-ndb-master'
master-port=3306
master-user='myslave'
master-password='53cr37'
server_id=2

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Checking slave status

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status\G
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-ndb-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37';
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: domU-12-31-35-00-0E-51-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Run backup on master cluster using START BACKUP from ndb_mgm command line

ndb_mgm> start backup
Connected to Management Server at: mysql-ndb_mgmd1:1186
Waiting for completed, this may take several minutes
Node 1: Backup 1 started from node 3
Node 1: Backup 1 started from node 3 completed
StartGCP: 6681 StopGCP: 6684
#Records: 2053 #LogRecords: 0
Data: 33888 bytes Log: 0 bytes

Copy backup to slave from all data nodes

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.1.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.1.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.1.ctl 100% 6832 6.7KB/s 00:00

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.2.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.2.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.2.ctl 100% 6832 6.7KB/s 00:00

Checking databases are all on SLAVE, otherwise use CREATE SCHEMA

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| world |
+--------------------+
4 rows in set (0.00 sec)

Slave

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| world |
+--------------------+
4 rows in set (0.02 sec)

Restore the Backup onto the slave

ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/


ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 1
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 2
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 1
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

Check apply status on slave

SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;


SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE epoch > 1110
ORDER BY epoch ASC LIMIT 1;



mysql> SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;
+---------------------+
| @latest:=MAX(epoch) |
+---------------------+
| 1110 |
+---------------------+
1 row in set (0.09 sec)

mysql> SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
-> @pos:=Position
-> FROM mysql.ndb_binlog_index
-> WHERE epoch > 1110
-> ORDER BY epoch ASC LIMIT 1;
Empty set (0.00 sec)

Restart MASTER and SLAVE MySQL nodes to make sure /etc/my.cnf is ok

service mysql.server restart


On master:

mysql> show master status\G
*************************** 1. row ***************************
File: row.000001
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On Slave:

[root@domU-12-31-35-00-04-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: row.000001
Read_Master_Log_Pos: 106
Relay_Log_File: domU-12-31-35-00-04-51-relay-bin.000003
Relay_Log_Pos: 245
Relay_Master_Log_File: row.000001
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: 106
Relay_Log_Space: 417
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:
1 row in set (0.00 sec)

Running mysqlslap to test

mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password


mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 --number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password
Enter password:
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '88' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '36' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '38' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '73' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '55' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
Killed

cat /tmp/mysqlslap.csv
ndb,mixed,0.129,0.102,0.165,1,100
ndb,mixed,0.035,0.011,0.067,1,10
ndb,mixed,0.004,0.001,0.031,1,1

Concurrency settings were too low, setting higher

[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 2048
MaxNoOfConcurrentOperations= 4096

Restart the cluster.

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10 --user=root --password


[root@domU-12-31-35-00-29-02 BACKUP]# cat /tmp/mysqlslap.csv
ndb,mixed,0.053,0.012,0.075,1,10
ndb,mixed,1.202,1.040,1.321,25,0
ndb,mixed,2.482,2.386,2.596,50,0
ndb,mixed,10.239,9.398,11.006,100,0

MySQL vs MySQLSlap

In preparation for some more intense testing on the MySQL 5.1 Cluster, I started up a plain vanilla MySQL 5.1 database with MyISAM and INNODB enabled.

I am going to test out various configurations of the cluster ie. 2 data node, 4 data node, multiple SQL/API nodes using a new MySQL 5.1 load emulation tool called mysqlslap.

I read some decent entries on the net about other people who have used it as well, here and here.
You can capture your own sql either dumping the sql from your app, using tcpdump to capture the sql on the wire, or setting the general query log (requires restart) to capture all queries or patching mysql so the slow query log can go below the 1 sec threshold.

Anyway on with the show. This was not really a tuning exercise, I just used the sample medium my.cnf located in /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-medium.cnf, which frankly is probably too small.

Here are results for the plain standalone MySQL database on EC2. I have included the full help of mysqlslap as the MySQL documentation is missing detail about the auto-generate-sql options.

Have Fun

Paul



mysqlslap --help
mysqlslap Ver 0.9 Distrib 5.1.20-beta, for pc-linux-gnu (i686)
Copyright (C) 2005 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Run a query multiple times against the server

Usage: mysqlslap [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf
The following groups are read: mysqlslap client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-?, --help Display this help and exit.
-a, --auto-generate-sql
Generate SQL where not supplied by file or command line.
--auto-generate-sql-add-autoincrement
Add autoincrement to auto-generated tables.
--auto-generate-sql-execute-number=#
Set this number to generate a set number of queries to
run.

--auto-generate-sql-guid-primary
Add GUID based primary keys to auto-generated tables.
--auto-generate-sql-load-type=name
Load types are mixed, update, write, key, or read.
Default is mixed

--auto-generate-sql-secondary-indexes=#
Number of secondary indexes to add auto-generated tables.
--auto-generate-sql-unique-query-number=#
Number of unique queries auto tests
--auto-generate-sql-unique-write-number=#
Number of unique queries for
auto-generate-sql-write-number
--auto-generate-sql-write-number=#
Number of rows to insert to used in read and write loads
(default is 100).

-C, --compress Use compression in server/client protocol.
-c, --concurrency=name
Number of clients to simulate for query to run.
--create=name File or string to use create tables.
--create-schema=name
Schema to run tests in.
--csv[=name] Generate CSV output to named file or to stdout if no file
is named.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'.
-F, --delimiter=name
Delimiter to use in SQL statements supplied in file or
command line.
-e, --engine=name Storage engine to use for creating the table.
-h, --host=name Connect to host.
-i, --iterations=# Number of times to run the tests.
-x, --number-char-cols=name
Number of VARCHAR columns to create table with if
specifying --auto-generate-sql
-y, --number-int-cols=name
Number of INT columns to create table with if specifying
--auto-generate-sql.
--number-of-queries=#
Limit each client to this number of queries (this is not
exact).
--only-print This causes mysqlslap to not connect to the databases,
but instead print out what it would have done instead.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection.
--post-query=name Query to run or file containing query to run after
executing.
--pre-query=name Query to run or file containing query to run before
executing.
--preserve-schema Preserve the schema from the mysqlslap run, this happens
unless --auto-generate-sql or --create are used.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --query=name Query to run or file containing query to run.
-s, --silent Run program in silent mode - no output.
-S, --socket=name Socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-u, --user=name User for login if not current user.
-v, --verbose More verbose output; You can use this multiple times to
get even more verbose output.
-V, --version Output version information and exit.

Running tests, progressively increasing the number of queries

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 --user=root \
--password

cat mysqlslap.csv

blackhole,mixed,0.021,0.009,0.069,1,100
blackhole,mixed,0.030,0.017,0.080,25,4
blackhole,mixed,0.044,0.025,0.087,50,2
blackhole,mixed,0.080,0.043,0.108,100,1
myisam,mixed,0.017,0.011,0.072,1,100
myisam,mixed,0.053,0.022,0.087,25,4
myisam,mixed,0.062,0.031,0.096,50,2
myisam,mixed,0.101,0.046,0.117,100,1
innodb,mixed,0.023,0.022,0.029,1,100
innodb,mixed,0.083,0.077,0.090,25,4
innodb,mixed,0.098,0.078,0.158,50,2
innodb,mixed,0.229,0.167,0.265,100,1


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root \
--password

cat /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.161,0.089,0.209,1,1000
blackhole,mixed,0.167,0.154,0.215,25,40
blackhole,mixed,0.193,0.163,0.233,50,20
blackhole,mixed,0.249,0.183,0.284,100,10
myisam,mixed,0.215,0.172,0.237,1,1000
myisam,mixed,0.234,0.185,0.252,25,40
myisam,mixed,0.280,0.250,0.339,50,20
myisam,mixed,0.300,0.270,0.359,100,10
innodb,mixed,0.350,0.236,0.674,1,1000
innodb,mixed,0.425,0.375,0.655,25,40
innodb,mixed,0.424,0.355,0.499,50,20
innodb,mixed,0.640,0.587,0.668,100,10

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_q10000.csv

blackhole,mixed,1.678,1.591,1.777,1,10000
blackhole,mixed,1.673,1.608,1.737,25,400
blackhole,mixed,1.739,1.675,1.899,50,200
blackhole,mixed,1.971,1.707,2.424,100,100
myisam,mixed,2.145,2.098,2.232,1,10000
myisam,mixed,2.185,2.056,2.464,25,400
myisam,mixed,2.179,2.060,2.319,50,200
myisam,mixed,2.370,2.151,3.008,100,100
innodb,mixed,3.541,3.077,3.989,1,10000
innodb,mixed,4.005,3.513,4.793,25,400
innodb,mixed,4.329,4.080,5.172,50,200
innodb,mixed,6.438,6.212,6.614,100,100


Oracle 11G on EC2 using silent install

The buzz around the Oracle community was the upcoming release of the new Oracle database.
No code names required, everyone knew it would be 11 something, Alex Gorbachev even ran a poll on the new letter.

Oracle decided to continue down the Grid path, hence the new version was Oracle 11g.

The software is officially released to more than privileged beta testers. You can get it here.

So given I had a spare moment before I start on the MySQL 5.1 Cluster replication build. I decided to download the software and give it a whirl.
As I have mentioned in the past, once you get a hang for using response files you will never go back to the slow java based GUI installer, especially over slow links.

Make sure you read the Linux install guide, the main thing is to get the software packages, users and kernel parameter setup.

I have attached the following process I used to install Oracle 11g on EC2.

Have Fun

Paul



Get the software, accept the license on the website and then copy your
cookie.txt file to the EC2 instance.



wget --load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
--07:32:09-- http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
=> `linux_11gR1_database.zip'
Resolving download.oracle.com... 208.111.133.54, 208.111.133.53, 208.111.133.52
Connecting to download.oracle.com|208.111.133.54|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1,844,533,232 (1.7G) [application/zip]

100%[====================================================================================>] 1,844,533,232 8.03M/s ETA 00:00

07:36:05 (7.47 MB/s) - `linux_11gR1_database.zip' saved [1844533232/1844533232]


Nice option provided by Alex Gorbachev

cat pkgs

binutils
compat-libstdc++
elfutils-libelf
elfutils-libelf-devel
glibc
glibc-common
glibc-devel
gcc
gcc-c++
libaio-devel
libaio
libgcc
libstdc++
libstdc++-devel
make
sysstat
unixODBC
unixODBC-devel

Use RPM to determine what packages are installed and versions

for pkg in `cat pkgs` ; do rpm -q $pkg --queryformat '%{NAME} %{VERSION} %{ARCH}\n' ; done

binutils 2.15.92.0.2 i386
package compat-libstdc++ is not installed
elfutils-libelf 0.97.1 i386
package elfutils-libelf-devel is not installed
glibc 2.3.4 i686
glibc-common 2.3.4 i386
glibc-devel 2.3.4 i386
gcc 3.4.6 i386
package gcc-c++ is not installed
package libaio-devel is not installed
libaio 0.3.105 i386
libgcc 3.4.6 i386
libstdc++ 3.4.6 i386
package libstdc++-devel is not installed
make 3.80 i386
package sysstat is not installed
package unixODBC is not installed
package unixODBC-devel is not installed

Create a shell script to install packages


cat install_pkgs


yum install binutils \
compat-libstdc++ \
elfutils-libelf \
elfutils-libelf-devel \
glibc \
glibc-common \
glibc-devel \
gcc \
gcc-c++ \
libaio-devel \
libaio \
libgcc \
libstdc++ \
libstdc++-devel \
make \
sysstat \
unixODBC \
unixODBC-devel

./install_pkgs

Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
No Match for argument: compat-libstdc++
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc to pack into transaction set.
glibc-2.3.4-2.36.i686.rpm 100% |=========================| 126 kB 00:00
---> Package glibc.i686 0:2.3.4-2.36 set to be updated
---> Downloading header for gcc-c++ to pack into transaction set.
gcc-c%2B%2B-3.4.6-8.i386. 100% |=========================| 36 kB 00:00
---> Package gcc-c++.i386 0:3.4.6-8 set to be updated
---> Downloading header for binutils to pack into transaction set.
binutils-2.15.92.0.2-22.i 100% |=========================| 41 kB 00:00
---> Package binutils.i386 0:2.15.92.0.2-22 set to be updated
---> Package libaio-devel.i386 0:0.3.105-2 set to be updated
---> Package unixODBC-devel.i386 0:2.2.11-1.RHEL4.1 set to be updated
---> Downloading header for gcc to pack into transaction set.
gcc-3.4.6-8.i386.rpm 100% |=========================| 43 kB 00:00
---> Package gcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for glibc-devel to pack into transaction set.
glibc-devel-2.3.4-2.36.i3 100% |=========================| 94 kB 00:00
---> Package glibc-devel.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for glibc-common to pack into transaction set.
glibc-common-2.3.4-2.36.i 100% |=========================| 646 kB 00:01
---> Package glibc-common.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for libstdc++ to pack into transaction set.
libstdc%2B%2B-3.4.6-8.i38 100% |=========================| 34 kB 00:00
---> Package libstdc++.i386 0:3.4.6-8 set to be updated
---> Package sysstat.i386 0:5.0.5-15.0.1.el4 set to be updated
---> Downloading header for elfutils-libelf to pack into transaction set.
elfutils-libelf-0.97.1-4. 100% |=========================| 7.4 kB 00:00
---> Package elfutils-libelf.i386 0:0.97.1-4 set to be updated
---> Downloading header for libgcc to pack into transaction set.
libgcc-3.4.6-8.i386.rpm 100% |=========================| 34 kB 00:00
---> Package libgcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for libstdc++-devel to pack into transaction set.
libstdc%2B%2B-devel-3.4.6 100% |=========================| 67 kB 00:00
---> Package libstdc++-devel.i386 0:3.4.6-8 set to be updated
---> Downloading header for elfutils-libelf-devel to pack into transaction set.
elfutils-libelf-devel-0.9 100% |=========================| 7.4 kB 00:00
---> Package elfutils-libelf-devel.i386 0:0.97.1-4 set to be updated
---> Package unixODBC.i386 0:2.2.11-1.RHEL4.1 set to be updated
--> Running transaction check
--> Processing Dependency: glibc-headers = 2.3.4-2.36 for package: glibc-devel
--> Processing Dependency: elfutils-libelf = 0.97.1-3 for package: elfutils
--> Processing Dependency: glibc = 2.3.4-2.25 for package: glibc-headers
--> Processing Dependency: cpp = 3.4.6-8 for package: gcc
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc-headers to pack into transaction set.
glibc-headers-2.3.4-2.36. 100% |=========================| 127 kB 00:00
---> Package glibc-headers.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for elfutils to pack into transaction set.
elfutils-0.97.1-4.i386.rp 100% |=========================| 12 kB 00:00
---> Package elfutils.i386 0:0.97.1-4 set to be updated
---> Downloading header for cpp to pack into transaction set.
cpp-3.4.6-8.i386.rpm 100% |=========================| 36 kB 00:00
---> Package cpp.i386 0:3.4.6-8 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
elfutils-libelf-devel i386 0.97.1-4 base 52 k
gcc-c++ i386 3.4.6-8 base 2.4 M
libaio-devel i386 0.3.105-2 base 9.9 k
libstdc++-devel i386 3.4.6-8 base 8.6 M
sysstat i386 5.0.5-15.0.1.el4 update 104 k
unixODBC i386 2.2.11-1.RHEL4.1 base 829 k
unixODBC-devel i386 2.2.11-1.RHEL4.1 base 772 k
Updating:
binutils i386 2.15.92.0.2-22 base 2.8 M
elfutils-libelf i386 0.97.1-4 base 38 k
gcc i386 3.4.6-8 base 4.4 M
glibc i686 2.3.4-2.36 base 5.9 M
glibc-common i386 2.3.4-2.36 base 16 M
glibc-devel i386 2.3.4-2.36 base 1.9 M
libgcc i386 3.4.6-8 base 63 k
libstdc++ i386 3.4.6-8 base 282 k
Updating for dependencies:
cpp i386 3.4.6-8 base 1.6 M
elfutils i386 0.97.1-4 base 136 k
glibc-headers i386 2.3.4-2.36 base 585 k

Transaction Summary
=============================================================================
Install 7 Package(s)
Update 11 Package(s)
Remove 0 Package(s)
Total download size: 46 M
Is this ok [y/N]: y
Downloading Packages:
(1/18): glibc-2.3.4-2.36. 100% |=========================| 5.9 MB 00:16
(2/18): gcc-c++-3.4.6-8.i 100% |=========================| 2.4 MB 00:06
(3/18): binutils-2.15.92. 100% |=========================| 2.8 MB 00:07
(4/18): libaio-devel-0.3. 100% |=========================| 9.9 kB 00:00
(5/18): unixODBC-devel-2. 100% |=========================| 772 kB 00:02
(6/18): gcc-3.4.6-8.i386. 100% |=========================| 4.4 MB 00:12
(7/18): glibc-devel-2.3.4 100% |=========================| 1.9 MB 00:05
(8/18): glibc-headers-2.3 100% |=========================| 585 kB 00:01
(9/18): elfutils-0.97.1-4 100% |=========================| 136 kB 00:00
(10/18): glibc-common-2.3 100% |=========================| 16 MB 00:44
(11/18): libstdc++-3.4.6- 100% |=========================| 282 kB 00:00
(12/18): sysstat-5.0.5-15 100% |=========================| 104 kB 00:00
(13/18): elfutils-libelf- 100% |=========================| 38 kB 00:00
(14/18): libgcc-3.4.6-8.i 100% |=========================| 63 kB 00:00
(15/18): libstdc++-devel- 100% |=========================| 8.6 MB 00:23
(16/18): elfutils-libelf- 100% |=========================| 52 kB 00:00
(17/18): unixODBC-2.2.11- 100% |=========================| 829 kB 00:02
(18/18): cpp-3.4.6-8.i386 100% |=========================| 1.6 MB 00:04
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : libgcc ####################### [ 1/29]
Updating : glibc-common ####################### [ 2/29]
Updating : glibc ####################### [ 3/29]
Stopping sshd:[ OK ]
Starting sshd:[ OK ]
Updating : binutils ####################### [ 4/29]
Updating : elfutils-libelf ####################### [ 5/29]
Updating : glibc-headers ####################### [ 6/29]
Updating : glibc-devel ####################### [ 7/29]
Updating : libstdc++ ####################### [ 8/29]
Installing: libstdc++-devel ####################### [ 9/29]
Updating : elfutils ####################### [10/29]
Installing: unixODBC ####################### [11/29]
Updating : cpp ####################### [12/29]
Updating : gcc ####################### [13/29]
Installing: gcc-c++ ####################### [14/29]
Installing: libaio-devel ####################### [15/29]
Installing: unixODBC-devel ####################### [16/29]
Installing: sysstat ####################### [17/29]
Installing: elfutils-libelf-devel ####################### [18/29]
Cleanup : glibc ####################### [19/29]
Cleanup : binutils ####################### [20/29]
Cleanup : gcc ####################### [21/29]
Cleanup : glibc-devel ####################### [22/29]
Cleanup : glibc-headers ####################### [23/29]
Cleanup : elfutils ####################### [24/29]
Cleanup : glibc-common ####################### [25/29]
Cleanup : libstdc++ ####################### [26/29]
Cleanup : elfutils-libelf ####################### [27/29]
Cleanup : libgcc ####################### [28/29]
Cleanup : cpp ####################### [29/29]

Installed: elfutils-libelf-devel.i386 0:0.97.1-4 gcc-c++.i386 0:3.4.6-8 libaio-devel.i386 0:0.3.105-2 libstdc++-devel.i386 0:3.4.6-8 sysstat.i386 0:5.0.5-15.0.1.el4 unixODBC.i386 0:2.2.11-1.RHEL4.1 unixODBC-devel.i386 0:2.2.11-1.RHEL4.1
Updated: binutils.i386 0:2.15.92.0.2-22 elfutils-libelf.i386 0:0.97.1-4 gcc.i386 0:3.4.6-8 glibc.i686 0:2.3.4-2.36 glibc-common.i386 0:2.3.4-2.36 glibc-devel.i386 0:2.3.4-2.36 libgcc.i386 0:3.4.6-8 libstdc++.i386 0:3.4.6-8
Dependency Updated: cpp.i386 0:3.4.6-8 elfutils.i386 0:0.97.1-4 glibc-headers.i386 0:2.3.4-2.36
Complete!

Checking compat-libstdc++ issue


yum search compat-libstdc

Searching Packages:
Setting up repositories
Reading repository metadata in from local files


compat-libstdc++-33.i386 3.2.3-47.3 base
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386 2.96-132.7.2 base
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


compat-libstdc++-33.i386 3.2.3-47.3 installed
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386 2.96-132.7.2 installed
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


Checking what environment variables are set as the Oracle unix user:

env|grep ORA

ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=/home/oracle/product/10.2.0/db_1

Make the new software ORACLE_HOME:

mkdir -p /home/oracle/product/11.1.0/db_1

Check kernel parameters:

cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1


# Oracle Specific

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Unzip the downloaded file:

unzip linux_11gR1_database.zip

...


Copy and edit a response file:

cp directory_unzipped/database/install/response/ 11G_SE.rsp

diff se.rsp 11G_SE.rsp

36c36
FROM_LOCATION="/mnt/downloads/database/stage/products.xml"
44c44


> ORACLE_BASE="/home/oracle"
52c52


> ORACLE_HOME="/home/oracle/product/11.1.0/db_1"
60c60


> ORACLE_HOME_NAME="Ora11gR1HOME1"
92c92
SHOW_SPLASH_SCREEN=FALSE
409c409


> s_globalDBName="TRAIN"
416c416


> s_dbSid="TRAIN"
563,564c563,564



> s_superAdminSamePasswd="xxxxxx"
> s_superAdminSamePasswdAgain="xxxxxx"
709c709


> n_configurationOption=3

Unset ORACLE_HOME:

[oracle@domU-12-31-36-00-25-E2 response]$ export ORACLE_HOME=
[oracle@domU-12-31-36-00-25-E2 ~]$ export LD_LIBRARY_PATH=
[oracle@domU-12-31-36-00-25-E2 response]$ env|grep ORA
ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=

Run the install using -silent -responseFile options:

./runInstaller -silent -responseFile /mnt/downloads/database/install/response/11G_SE.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB. Actual 6433 MB Passed
Checking swap space: must be greater than 150 MB. Actual 895 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-08-11_08-21-09AM. Please wait ...[oracle@domU-12-31-36-00-25-E2 database]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

You can find the log of this install session at:
/home/oracle/oraInventory/logs/installActions2007-08-11_08-21-09AM.log
.................................................................................................... 100% Done.


Loading Product Information
.............................................................................................................. 100% Done.


Starting execution of Prerequisites...
Total No of checks: 14

Performing check for CertifiedVersions
Checking operating system requirements ...
Expected result: One of enterprise-4,enterprise-5,redhat-4,redhat-5,SuSE-10,asianux-2,asianux-3
Actual Result: redhat-4
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Packages
Checking operating system package requirements ...
Checking for make-3.80; found make-1:3.80-6.EL4-i386. Passed
Checking for binutils-2.15.92.0.2; found binutils-2.15.92.0.2-22-i386. Passed
Checking for gcc-3.4.5; found gcc-3.4.6-8-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386. Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386. Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-8-i386. Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-4-i386. Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-15.0.1.el4-i386. Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-8-i386. Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-8-i386. Passed
Checking for unixODBC-2.2.11; found unixODBC-2.2.11-1.RHEL4.1-i386. Passed
Checking for unixODBC-devel-2.2.11; found unixODBC-devel-2.2.11-1.RHEL4.1-i386. Passed
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Kernel
Checking kernel parameters
Checking for semmsl=250; found semmsl=250. Passed
Checking for semmns=32000; found semmns=32000. Passed
Checking for semopm=100; found semopm=100. Passed
Checking for semmni=128; found semmni=128. Passed
Checking for shmmax=536870912; found shmmax=536870912. Passed
Checking for shmmni=4096; found shmmni=4096. Passed
Checking for shmall=2097152; found shmall=2097152. Passed
Checking for file-max=65536; found file-max=65536. Passed
Checking for VERSION=2.6.9; found VERSION=2.6.16-xenU. Passed
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000. Passed
Checking for rmem_default=4194304; rmem_default=1048576. Failed <<<< rmem_max="4194304;" rmem_max="1048576." wmem_default="262144;" wmem_default="262144." wmem_max="262144;" wmem_max="262144." atleast="2.3.4-2.19" passed
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The kernel parameters do not meet the minimum requirements (see above).
Recommendation: Perform operating system specific instructions to update the kernel parameters.

========================================================
Performing check for GLIBC
Checking Recommended glibc version
Expected result: ATLEAST=2.3.4-2.19
Actual Result: 2.3.4-2.36
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for TotalMemory
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 1700MB
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for SwapSpace
Checking available swap space requirements ...
Expected result: 2550MB
Actual Result: 895MB
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The system does not have the required swap space.
Recommendation: Make more swap space available to perform the install.

========================================================
Performing check for DetectIfDHCPAssignedIP
Checking Network Configuration requirements ...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.
Recommendation: Oracle supports installations on systems with DHCP-assigned public IP addresses. However, the primary network interface on the system should be configured with a static IP address in order for the Oracle Software to function properly. See the Installation Guide for more details on installing the software on systems configured with DHCP.

========================================================
Performing check for CheckPathForOtherOracleHomes
Checking PATH environment variable...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The installer has detected that the PATH environment variable includes other ORACLE_HOME locations in it.
Recommendation: Restart the installer after removing other ORACLE_HOME locations from PATH environment variable.

========================================================
Performing check for CheckTempDiskSpace
Checking for sufficient diskspace in TEMP location...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CheckLdLibraryPath
Checking LD_LIBRARY_PATH environment variable...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleBase
Validating ORACLE_BASE location (if set) ...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleHomeSpace
Checking Oracle Home path for spaces...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for DetectAnyInvalidASMHome
Checking for proper system clean-up....
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CompatibilityChecks_DB
Checking for Oracle Home incompatibilities ....
Actual Result: NEW_HOME
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
PrereqChecks complete


Analyzing dependencies
.................................................................................................................. 100% Done.


-----------------------------------------------------------------------------
Summary
Global Settings
Source: /mnt/downloads/database/stage/products.xml
Oracle Base: /home/oracle
Oracle Home: /home/oracle/product/11.1.0/db_1 (Ora11gR1HOME1)
Installation Type: Standard Edition
Product Languages
English
Space Requirements
/ Required 3.38GB (includes 224MB temporary) : Available 6.14GB
New Installations (116 products)
Oracle Database 11g 11.1.0.6.0
Enterprise Edition Options 11.1.0.6.0
Oracle Database 11g 11.1.0.6.0
Oracle Text 11.1.0.6.0
Oracle Net Services 11.1.0.6.0
Oracle Enterprise Manager Console DB 11.1.0.5.0
Oracle Net Listener 11.1.0.6.0
HAS Files for DB 11.1.0.6.0
Oracle Call Interface (OCI) 11.1.0.6.0
Enterprise Manager Agent 10.2.0.3.1
Oracle Programmer 11.1.0.6.0
Oracle Database Gateway for ODBC 11.1.0.6.0
Oracle JVM 11.1.0.6.0
Database Configuration and Upgrade Assistants 11.1.0.6.0
Oracle XML Development Kit 11.1.0.6.0
Generic Connectivity Common Files 11.1.0.6.0
Oracle Multimedia 11.1.0.6.0
Oracle Multimedia Locator 11.1.0.6.0
Oracle Internet Directory Client 11.1.0.6.0
Oracle Database Utilities 11.1.0.6.0
Secure Socket Layer 11.1.0.6.0
PL/SQL 11.1.0.6.0
Oracle Recovery Manager 11.1.0.6.0
Oracle Net 11.1.0.6.0
Assistant Common Files 11.1.0.6.0
Installation Common Files 11.1.0.6.0
Enterprise Manager plugin Common Files 11.1.0.5.0
Oracle LDAP administration 11.1.0.6.0
SQL*Plus 11.1.0.6.0
HAS Common Files 11.1.0.6.0
Oracle Help for the Web 2.0.14.0.0
Oracle UIX 2.2.20.0.0
Precompiler Common Files 11.1.0.6.0
Oracle Clusterware RDBMS Files 11.1.0.6.0
Cluster Verification Utility Common Files 11.1.0.6.0
Oracle Wallet Manager 11.1.0.6.0
Oracle Security Developer Tools 11.1.0.6.0
XML Parser for Java 11.1.0.6.0
Enterprise Manager Minimal Integration 11.1.0.6.0
Oracle Database User Interface 2.2.13.0.0
SQL*Plus Files for Instant Client 11.1.0.6.0
Oracle ODBC Driver 11.1.0.6.0
Required Support Files 11.1.0.6.0
Database SQL Scripts 11.1.0.6.0
OLAP SQL Scripts 11.1.0.6.0
PL/SQL Embedded Gateway 11.1.0.6.0
Oracle Globalization Support 11.1.0.6.0
Character Set Migration Utility 11.1.0.6.0
Oracle Locale Builder 11.1.0.6.0
Secure Socket Layer 11.1.0.6.0
Oracle Java Client 11.1.0.6.0
Oracle JDBC/THIN Interfaces 11.1.0.6.0
Oracle Multimedia Client Option 11.1.0.6.0
Oracle Universal Connection Pool 11.1.0.6.0
Oracle Notification Service 11.1.0.5.0
Oracle Code Editor 1.2.1.0.0I
Oracle Ultra Search Server Rdbms 11.1.0.6.0
Oracle Help For Java 4.2.9.0.0
Oracle Containers for Java 11.1.0.6.0
JAccelerator (COMPANION) 11.1.0.6.0
Database Workspace Manager 11.1.0.6.0
SQLJ Runtime 11.1.0.6.0
Oracle Core Required Support Files 11.1.0.6.0
Platform Required Support Files 11.1.0.6.0
Oracle Ice Browser 5.2.3.6.0
Oracle Application Express 11.1.0.6.0
Oracle SQL Developer 11.1.0.6.0
Oracle JDBC Server Support Package 11.1.0.6.0
regexp 2.1.9.0.0
Enterprise Manager Common Files 10.2.0.3.1
Installation Plugin Files 11.1.0.6.0
Oracle JDBC/OCI Instant Client 11.1.0.6.0
XML Parser for Oracle JVM 11.1.0.6.0
Oracle XML Query 11.1.0.6.0
Oracle Starter Database 11.1.0.6.0
Sample Schema Data 11.1.0.6.0
Precompiler Required Support Files 11.1.0.6.0
Parser Generator Required Support Files 11.1.0.6.0
Oracle Multimedia Locator RDBMS Files 11.1.0.6.0
Oracle Globalization Support 11.1.0.6.0
Oracle Multimedia Annotator 11.1.0.6.0
Oracle Multimedia Java Advanced Imaging 11.1.0.6.0
Oracle Database 11g Multimedia Files 11.1.0.6.0
Agent Required Support Files 10.2.0.3.1
Oracle 11g Warehouse Builder Server 11.1.0.6.0
Oracle Ultra Search Server 11.1.0.6.0
Oracle Ultra Search Middle-Tier 11.1.0.6.0
Oracle Ultra Search Common Files 11.1.0.6.0
Perl Interpreter 5.8.3.0.4
RDBMS Required Support Files 11.1.0.6.0
Oracle Display Fonts 9.0.2.0.0
RDBMS Required Support Files for Instant Client 11.1.0.6.0
Enterprise Manager Agent Core Files 10.2.0.3.1
Enterprise Manager Common Core Files 10.2.0.3.1
Enterprise Manager Grid Control Core Files 10.2.0.3.1
Enterprise Manager Database Plugin -- Agent Support 11.1.0.5.0
Enterprise Manager Database Plugin -- Management Service Support 11.1.0.5.0
Enterprise Manager Repository Core Files 10.2.0.3.1
Enterprise Manager Database Plugin -- Repository Support 11.1.0.5.0
Provisioning Advisor Framework 10.2.0.3.1
XDK Required Support Files 11.1.0.6.0
Oracle RAC Required Support Files-HAS 11.1.0.6.0
SQL*Plus Required Support Files 11.1.0.6.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Bali Share 1.1.18.0.0
Buildtools Common Files 11.1.0.6.0
Oracle Net Required Support Files 11.1.0.6.0
SSL Required Support Files for InstantClient 11.1.0.6.0
LDAP Required Support Files 11.1.0.6.0
Oracle ODBC Driverfor Instant Client 11.1.0.6.0
Oracle Configuration Manager 10.2.6.0.0
Oracle Universal Installer 11.1.0.6.0
Oracle One-Off Patch Installer 11.1.0.6.0
Installer SDK Component 11.1.0.6.0
Sun JDK 1.5.0.1.1
-----------------------------------------------------------------------------


Installation in progress (Sat Aug 11 08:21:39 EDT 2007)
............................................................... 7% Done.
............................................................... 14% Done.
............................................................... 21% Done.
............................................................... 29% Done.
............................................................... 36% Done.
............................................................... 43% Done.
............................................................... 50% Done.
............................................................... 58% Done.
............................................................... 65% Done.
............................................................... 72% Done.
............................................................... 80% Done.
........................ 82% Done.
Install successful

Linking in progress (Sat Aug 11 08:27:00 EDT 2007)
. 83% Done.
Link successful

Setup in progress (Sat Aug 11 08:29:03 EDT 2007)
.................................. 100% Done.
Setup successful

End of install phases.(Sat Aug 11 08:29:16 EDT 2007)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/home/oracle/product/11.1.0/db_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts

The installation of Oracle Database 11g was successful.
Please check '/home/oracle/oraInventory/logs/silentInstall2007-08-11_08-21-09AM.log' for more details.

cd /home/oracle/product/11.1.0/db_1
./root.sh



MySQL 5.1 NDB Cluster on EC2 configuration files

As I mentioned in the MySQL 5.1 NDB Cluster testing article I was going to provide the configuration files I used for setting up the cluster.

There are three main files for setting up a MySQL cluster

  1. configure.ini : The MySQL cluster management configuration file.
  2. /etc/my.cnf: Standard MySQL configuration file, required for data and SQL/API nodes.
  3. /etc/hosts: Local names resolution file required on all cluster nodes.

Specifically for EC2. I use names rather ip addresses and use /etc/hosts to locally resolve those names to specific ip addresses. This means you will have to change the ip addresses to be correct before you launch the cluster.
To get the local ip address you can use a command: nslookup `hostname`

These configuration files will enable you to have 2 management nodes on the hosts:

  1. mysql-ndb_mgmd1
  2. mysql-ndb_mgmd2

Two data nodes on the hosts:

  1. mysql-ndbd1
  2. mysql-ndbd2

And eight MySQL nodes on any host, this is dangerous, so launch the cluster behind a firewall or on EC2 in a specific security group.

Have Fun

Paul

Here are my configuration files.

1. The configure.ini file, required on all management nodes.


[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 64
MaxNoOfConcurrentOperations= 128
DataMemory= 256M
IndexMemory= 256M
Diskless= false
TimeBetweenWatchDogCheck= 30000
DataDir= /mnt/mysql-cluster/data
MaxNoOfOrderedIndexes= 500
MaxNoOfAttributes= 1000
TimeBetweenGlobalCheckpoints= 500
NoOfFragmentLogFiles= 4
FragmentLogFileSize=12M
DiskPageBufferMemory= 64M
ODirect= 1
# the following parametes just function as a small regression
# test that the parameter exists
InitialNoOfOpenFiles= 27

#
# Increase timeouts to cater for slow test-machines
# (possibly running several tests in parallell)
#
HeartbeatIntervalDbDb= 30000
HeartbeatIntervalDbApi= 30000
#TransactionDeadlockDetectionTimeout= 7500

[ndbd]
HostName= mysql-ndbd1 # hostname is a valid network address

[ndbd]
HostName= mysql-ndbd2 # hostname is a valid network address

[ndb_mgmd]
HostName= mysql-ndb_mgmd1 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[ndb_mgmd]
HostName= mysql-ndb_mgmd2 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

[mysqld]

2. The /etc/my.cnf required on all data and SQL/API nodes.


# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server

3. The /etc/hosts file required on all hosts


# Mysql Cluster data node
10.255.14.177 mysql-ndbd1
10.255.31.160 mysql-ndbd2
# Mysql Cluster mgm node
10.255.14.177 mysql-ndb_mgmd1
10.255.31.160 mysql-ndb_mgmd2
# extra
127.0.0.1 localhost

Oracle RAC on NFS: Install clusterware

Continuing on with building an Oracle RAC cluster on NFS using Amazon EC2, following this build guide.

The setup will be two Oracle RAC nodes and one NFS node. This means that the NFS node is a Single Point Of Failure (SPOF) however this is not meant to be a production system rather a system to provide a environment to perform testing and learning.

I looked at various build guides for enabling High Availability NFS, essentially multiple nodes providing NFS using DRBD, however this requires a block device again. There is a new product released called ElasticDrive which provides a NBD driver for S3. I will be going back and using this product to see if I can get both Openfiler and OCFS2 working with it later.

A bit later…

I got to the point of installing the clusterware and it yet another EC2 related snag!
The lack of a 2nd ethernet device and the inability to setup a Virtual IP address (vip). Whilst I was able follow this post to fake another ethernet device by copying /etc/sysconfig/network-scripts/if-cfg-eth0 to if-cfg-eth0:1, the inability to setup up an virtual IP is going to kill this.

If you have been following this long list of posts on trying to get Oracle RAC working on EC2 this is pretty frustrating to say the least.
I thought about this for a while and did some reading of the EC2 forums. The next attempt I am going to run Qemu inside the Amazon VM (Virtual Machine). So a VM within a VM.
Qemu can convert VMware images to Qemu images so I can go back to using the good Oracle RAC on VMware install guides published by Howard Rogers at the Dizwell site.

Once I have the Oracle RAC install built under VMware I can convert that to run in EC2.

The bonus if this works is

  1. I can run multiple mini VMs inside one EC2 (Xen) VM.
  2. When Amazon get direct support for VMware happening I can reuse those VMware images directly.
  3. If I want to setup virtualization on another hosting services I am already halfway there.
  4. I can try pointing these Qemu Oracle RAC VMs at external shared disk or NFS and possibly run up a 4 node RAC cluster on one or two EC2 VMs

So that is where I am at. The work done so far with NFS is not lost as I mentioned. I have demostrated that you can provide more storage as required any application which requires more than the normal 150G /mnt mount point provided by EC2.

More later

Have Fun

Paul