Category Archives: mysql

Install a MySQL NDB Cluster using CentOS 6.2 with 2 MGMs/MySQL servers and 2 NDB nodes

I wrote a post few weeks back that my MySQL NDB cluster was already running. This is a follow-up post on how I did it.

Before I dug in, I read some articles first on the best practices for MySQL Cluster installations. One of the sources that I’ve read is this quite helpful presentation.

The plan was to setup the cluster with 6 components:

  • 2 Management nodes
  • 2 MySQL nodes
  • 2 NDB nodes

Based on the best practices, I only need 4 servers to accomplish this setup. With these tips in mind, this is the plan that I came up with:

  • 2 VMs (2 CPUs, 4GB RAM, 20GB drives ) – will serve as MGM nodes and MySQL servers
  • 2 Supermicro 1Us (4-core, 8GB RAM, RAID 5 of 4 140GB 10k rpm SAS) – will serve as NDB nodes
  • all servers will be installed with a minimal installation of CentOS 6.2
The servers will use these IP configuration
  • mm0 – 192.168.1.162 (MGM + MySQL)
  • mm1 – 192.168.1.211 (MGM + MySQL)
  • lbindb1 – 192.168.1.164 (NDB node)
  • lbindb2 – 192.168.1.163 (NDB node)

That’s the plan, now to execute…

I downloaded the binary packages from this mirror. If you want a different mirror, you can choose from the main download page. I only need these two:

To install the packages, I ran these commands in the respective servers

    mm0> rpm -Uhv --force MySQL-Cluster-server-gpl-7.2.5-1.el6.x86_64.rpm
    mm0> mkdir /var/lib/mysql-cluster
    mm1> rpm -Uhv --force MySQL-Cluster-server-gpl-7.2.5-1.el6.x86_64.rpm
    mm1> mkdir /var/lib/mysql-cluster
    lbindb1> rpm -Uhv --force MySQL-Cluster-server-gpl-7.2.5-1.el6.x86_64.rpm
    lbindb1> mkdir -p /var/lib/mysql-cluster/data
    lbindb2> rpm -Uhv --force MySQL-Cluster-server-gpl-7.2.5-1.el6.x86_64.rpm
    lbindb2> mkdir -p /var/lib/mysql-cluster/data

The mkdir commands will make sense in a bit…

My cluster uses these two configuration files:

  • /etc/my.cnf  – used in the NDB nodes and MySQL servers (both mm[01] and lbindb[01])
  • /var/lib/mysql-cluster/config.ini – used in the MGM nodes only (mm[01])

Contents of /etc/my.cnf:

[mysqld]
# Options for mysqld process:
ndbcluster # run NDB storage engine
ndb-connectstring=192.168.1.162,192.168.1.211 # location of management server

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=192.168.1.162,192.168.1.211 # location of management server

Contents of /var/lib/mysql-cluster/config.ini:

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Setting this to 1 for now, 3 ndb nodes
DataMemory=1024M # How much memory to allocate for data storage
IndexMemory=512M
DiskPageBufferMemory=1048M
SharedGlobalMemory=384M
MaxNoOfExecutionThreads=4
RedoBuffer=32M
FragmentLogFileSize=256M
NoOfFragmentLogFiles=6

[ndb_mgmd]
# Management process options:
NodeId=1
HostName=192.168.1.162 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files

[ndb_mgmd]
# Management process options:
NodeId=2
HostName=192.168.1.211 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files

[ndbd]
# lbindb1
HostName=192.168.1.164 # Hostname or IP address
DataDir=/var/lib/mysql-cluster/data # Directory for this data node's data files

[ndbd]
# lbindb2
HostName=192.168.1.163 # Hostname or IP address
DataDir=/var/lib/mysql-cluster/data # Directory for this data node's data files

# SQL nodes
[mysqld]
HostName=192.168.1.162

[mysqld]
HostName=192.168.1.211

Once the configuration files are in place, I started the cluster with these commands (NOTE: Make sure that the firewall was properly configured first):

mm0> ndb_mgmd --ndb-nodeid=1 -f /var/lib/mysql-cluster/config.ini
mm0> service mysql start
mm1> ndb_mgmd --ndb-nodeid=2 -f /var/lib/mysql-cluster/config.ini
mm1> service mysql start
lbindb1> ndbmtd
lbindb2> ndbmtd

To verify if my cluster is really running, I logged-in into one of the MGM nodes and ran ndb_mgm like this:

I was able to set it this up a few weeks back. Unfortunately, I haven’t had the chance to really test it with our ETL scripts… I was occupied with other responsibilities…

Thinking about it now, I may have to scrap the whole cluster and install a MySQL with InnoDB + lots of RAM! hmmm… Maybe I’ll benchmark it first…

Oh well… 🙂

References:

OK.. my MySQL NDB Cluster is up… now what?

I’ve been working on how to deploy our cluster for the past 2 days already. It’s nice to see that it’s running now given that I’ve been reading the MySQL manual for 2 days now…

 

 

I’ll create a detailed post on how I did it when I have more time.

Here are my preliminary notes so far:

  • only 2 files are needed, MySQL-Cluster-client-gpl-7.2.5-1.el6.x86_64.rpm & MySQL-Cluster-server-gpl-7.2.5-1.el6.x86_64.rpm
  • --force is required to install the server package in CentOS 6.2
  • make sure that IPs are static and firewalls are setup
  • total ndb nodes must be multiples of NoOfReplicas (1 or 2)
  • if mgm > 1, all mgms must be up first before you can issue commands (use –nowait-nodes to override)
  • for ndb nodes, ensure that the DataDir exists

I’m just savoring the fruits of my labor… for this is only the beginning… *sigh*

MySQL: Ubuntu Server 9.04, moving the data directory to a different location

I recently deployed a “custom” server, will be used primarily as a MySQL server, using software RAID 5 for storage reliability.

I’ll try to explain in the future how I deployed the software RAID…

Anyway, my RAID partition is mounted in /data, I’m planning to store all MySQL files in sub-directory inside it, /data/mysql/

After changing the relevant MySQL variables in /etc/mysql/my.cnf,

[mysqld]
port            = 3306
datadir         = /data/mysql
socket          = /var/run/mysqld/mysqld.sock
<<< … truncated data … >>>
innodb_data_home_dir = /data/mysql/
innodb_data_file_path = ibdata1:500M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_log_arch_dir = /data/mysql/
innodb_buffer_pool_size = 4000M

I attempted to start MySQL but all I have is this,

root@****:~# /etc/init.d/mysql start
 * Starting MySQL database server mysqld
   …fail!


I am more familiar with RHEL/CentOS, been using it in our servers for years now, so the first thing that comes to mind is SELinux. This is the first time that I’m going to deploy an Ubuntu Server, in short, I’m a newbie.

Note: Realization came later that Ubuntu’s server deployment doesn’t use SELinux, it’s using a counterpart called AppArmor.

Looking at /var/log/messages, I got these messages,

May 10 13:52:38 ****server kernel: [ 9495.640883] type=1503 audit(1241934758.732:11): operation=”inode_create” requested_mask=”a::” denied_mask=”a::” fsuid=0 name=”/data/mysql/****server.lower-test” pid=18791 profile=”/usr/sbin/mysqld”
May 10 13:52:38 ****server kernel: [ 9495.640944] type=1503 audit(1241934758.732:12): operation=”inode_create” requested_mask=”a::” denied_mask=”a::” fsuid=0 name=”/data/mysql/****server.lower-test” pid=18791 profile=”/usr/sbin/mysqld”

These messages are not from SELinux at all, something is preventing /usr/sbin/mysqld from writing in /data/mysql/

A little googling led me to this blog [neodon.blogspot.com] and this blog [brainwreckedtech.wordpress.com].

One of the entries there said that I have to edit AppArmor’s MySQL policy (aha!, so it’s AppArmor!)

root@****:~# vi /etc/apparmor.d/usr.sbin.mysqld

Inside it, I added /data/mysql/, the blue one (don’t ask me what the line means, I don’t have the exact idea, I only assumed it has something to do with read/write permissions).

/usr/sbin/mysqld {
  #include

  #include

  #include

  #include

  #include

  <<< … truncated data … >>>

  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /data/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid w,
  /var/run/mysqld/mysqld.sock w,
}

Then restarted AppArmor,

root@****:~# /etc/init.d/apparmor reload
 * Reloading AppArmor profiles …
   …done.


and now it’s working!

root@****:~# /etc/init.d/mysql start
 * Starting MySQL database server mysqld
   …done.

MySQL: how to deny remote client access to MySQL temporarily

Say, you’re going to do a “dump” and you don’t want remote clients to alter the current data…

Here’s a simple trick to deny access to remote clients temporarily.

1. open your MySQL configuration file, my.cnf (in CentOS / Red Hat, located in /etc/my.cnf)
2. add skip-networking flag under [mysqld], like this:

[mysqld]
skip-networking

3. restart your MySQL server (in CentOS / Red Hat, run service mysqld restart)
4. Do a simple test to verify.

Note that this will affect remote clients only, local clients can still connect via Unix sockets.

MySQL: 32-bit vs 64-bit OS

This is a classic mistake in deploying MySQL servers in stand-alone machines, deploying it in a >3GB memory 64-bit capable machine with a 32-bit linux OS. It’s quite an epidemic actually that even hosting sites are committing this “mistake”. It’s no surprise that even us suffered the same fate in our previous MySQL servers… (we have a valid reason though)

What’s wrong with 32-bit, you say?

Simply put, MySQL won’t be able to use the remaining memory at all. MySQL is a single process, 32-bit + single process = memory limit for each process is… sounds familiar?

Anyway, you can find more information from this blog [mysqlperformanceblog.com].

If you want to check if you have a 64-bit capable cpu, you can check the links below:
http://processorfinder.intel.com/
http://www.cpuid.com/

Just look for the AMD64 or EM64T flag.

MySQL: ooops, regexp and rlike operators won’t use indices

I constructed a database that will hold millions of records last week, data mining purposes. I was in the middle of inserting the needed records when I decided to execute some queries, just for fun. I just want to check if how long (or how short) the response time of the server will be.

mysql> select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
Empty set (0.16 sec)

Not bad, 0.16 seconds.

mysql> explain select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | sig | sig | 257 | NULL | 1 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.15 sec)

How about like,

mysql> select * from unbilled_transactions where sig like ‘%doesn’t_exist’;
Empty set (15.69 sec)

Ooops. What happened here?

mysql> explain select * from unbilled_transactions where sig like ‘%doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 18934700 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.20 sec)


It’s doing a sequential search! Oh no, some of our queries looked exactly like this…

Same behaviour with rlike,

mysql> explain select * from unbilled_transactions where sig rlike ‘doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19001584 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.19 sec)

and regexp,

mysql> explain select * from unbilled_transactions where sig regexp ‘doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19005533 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

But not if the string you’re looking for is at the beginning,

mysql> explain select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | sig | sig | 257 | NULL | 1 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.19 sec)

But not with regexp though,

mysql> explain select * from unbilled_transactions where sig regexp ‘^doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19044284 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

mysql> explain select * from unbilled_transactions where sig regexp ‘^d.*’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19044878 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

The only solution so far that I can think of is to use another indexed column…

mysql> explain select * from unbilled_transactions where sig regexp ‘doesn’t_exist.*’ and tcsd <> ”;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | tcsd | tcsd | 8 | NULL | 3 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.00 sec)


Using another index,

mysql> select * from unbilled_transactions where sig regexp ‘doesn’t_exist.*’ and tcsd <> ”;
Empty set (0.00 sec)

is a lot better than this,

mysql> select * from unbilled_transactions where sig regexp ‘doesn’t_exist%’;
Empty set (17.68 sec)