Overview

In the last few months, we have seen more and more discussion on the use of Galera solution for MySQL Clustering.

I have being one of those that had heavily test and implement Galera solution, actually with quite good results and I have also presented SOME of them at Oracle Connect.

On the other side I have be work with MySQL NDB for years (at least from 2007) at many customers site, from simple to complex setups.

So also if I cannot consider myself as mega expert, I think to have some good experience and insight on both platform.

The point here is that I was not happy in reading some articles comparing the two, not because the kind of tests, or results.

Not because I prefer this or that, but simply because, from my point of view, it does not make any sense to compare the two.

We can spend pages and pages in discussing the point here, but I want try to give a simple generalize idea of WHY it makes no sense in few lines.

NDB brief list

• NDB is not a simple storage engine and can work independently, MySQL is “just “ a client.
• NDB is mainly an in memory database and also if it support table on disk the cost of them not always make sense.
• NDB is fully synchronous, no action can be returned to client until transactions on all nodes are really accepted.
• NDB use horizontal partition to equally distribute data cross node, but none of them has the whole dataset (unless you use one node group only, which happens ONLY when you don’t know how to use it).
• NDB Replicate data for a specific factor, which is the number of replica, and that replication factor will not change with the increase of the nodes number.
• Clients retrieve data from NDB as whole, but internally data is retrieve by node, often use parallel execution. (Not going in the details here on the difference between select methods like match by key, range, IN option and so on).
• NDB scale by node group that means it really scale in the Dataset dimension it can manage and operation it could execute, and it really scale!

Galera brief list

• Galera is an additional layer working inside the MySQL context.
• Galera require InnoDB to work.
• Galera offer “virtually synchronous” replication.
• Galera replicate the full dataset across ALL nodes.
• Galera data replication overhead, increase with the number of nodes present in the cluster.
• Galera replicate data from one node to cluster on the commit, but apply them on each node by a FIFO queue (multi thread).
• Galera do not offers any parallelism between the nodes when retrieving the data; clients rely on the single node they access.

So why they cannot be compare?

It should be quite clear that the two, are very different from starting from the main conceptualization, given NDB is a cluster of many node groups with distribute dataset, while Galera is a very efficient (highly efficient) replication layer.

But just to avoid confusion:

1. NDB does data partition and data distribution with redundant factor.
2. Galera just replicate data all over.
3. NDB apply parallel execution to the incoming request, involving more node groups in data fetch.
4. Galera is not involved at all in the data fetch and clients need to connect to one node or more by themselves, means application require managing parallel request in case of need.
5. In NDB the more node groups you add the more you get in possible operation per second and data archived/retrieved. 
6. In Galera the more nodes you add, the more overhead you generate in the replication, so more data will require to be “locally” commit by the replication layer, until when the number of nodes and operations executed on them will compromise the performance for each node.

Conclusion

NDB Cluster is a real cluster solution, design to internally scale and to perform internally all the required operation to guarantee high availability and synchronous data distribution.

Galera is a very efficient solution to bypass the current inefficient mechanism MySQL has for the replication.

Galera allow to create a cluster of MySQL nodes, in virtually synchronous replication. This with almost zero complexity added on the standard MySQL management.

Never the less the obtaining platform is composed by separate nodes, which for the good or the bad is not a system of distributed data.

Given that, the scenario where we can use Galera or NDB are dramatically different, trying to compare them is like comparing a surfboard with a snowboard.

I love them both, and honestly I am expecting to see Galera deployment to dramatically increase in 2013, but I am still respecting my motto “use the right tool for the job”.

Let us try to make our life easier and avoid confusions.

Happy MySQL to all!!

Ho-ho-ho

{joscommentenable}

Today was another good day, but I had a problem with one presentation and keynote.

the "Big Data is a Big Scam: Most of the Time". There is something that is not convincing me in the architecture,

and is mainly base on the lack of numbers and match between requirements and implementation.

The presentation was interesting from some aspects, and the tile approach is probably a good one.

I have enjoy the general idea and I did found awesome the bravery of setting up a circular replication cross geographic regions.

Never the less I remain bewildered by the tiles approach numbers, probably because the presentation was missing of few additional information.

Just to be more clear I was trying to understand better the real number and how works.

As for example if each tile is compose by 1 MySQL node, and 2 data node, and given that this solution use Xtra large EC2 with 68GB ram. I can assume that,

we have approximately 65 GB data (given some overhead for buffers and so on) for the current Node Group on this tile.

From the description I have also understood that the number of tails implemented, is different by region.

Given that the number of Node Group is different and by direct consequence the memory available as well, and the MySQL SQL node as well.

This raise an immediate question, are we having the same data set replicated world wide?

Or are we having a different sets?

Also,

each Node Group can host ~69GB, the maximum number of data node in 7.2 is still 48 (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html), which means that the maximum size that can be served is of 1.3TB for the whole cluster.

Given during the presentation my understanding was that the data set is of 100TB, where are located, now or in the future, the remaining 98.7TB? Keeping in mind that I asks if table on disks where used and answer was NO.

Then I have another question-mark infront of me.

If the set of tiles in US is compose of 20 tiles, (so 20 MySQL SQL Nodes, 40 Data nodes and so on), and it set like this because it needs to sustain the traffic mainly it requires that setting for "calculation power" needs.

I assume (probably wrong) that this is due to the number of request per second coming in.

Given we have circular replication in the architecture, and given we have different numbers of tiles, and by consequence of MySQL nodes, HOW can a 8 MySQL SQL Node setup (say in Australia) sustain the traffic coming in from a 20 MySQL SQL nodes setup?

I am sure, we are missing something in the presentation in terms of real numbers.

Being a MySQL NDB lover, I would really like to have more information and details, given that what I get from the presentation was not enough for me.

{joscommentenable}

Today, I had the need to debug mysql, given that I download the code and I try to compile on my MacBook pro.

Surprise I got the following issue:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC - Success
-- Check size of pthread_t
-- Check size of pthread_t - done
-- Performing Test HAVE_PEERCRED
-- Performing Test HAVE_PEERCRED - Failed
-- GTEST_LIBRARIES:optimized;/Users/marcotusa/mysql_work/source/gtest-1.6.0/test;debug;/Users/marcotusa/mysql_work/source/gtest-1.6.0
-- Configuring incomplete, errors occurred!
tusamac:mysql-5.6.5-m8 marcotusa$ cmake . -G Xcode -DCMAKE_INSTALL_PREFIX:PATH=/Users/marcotusa/mysql_work/template/mysql-5.6.5

 -DCMAKE_USE_RELATIVE_PATHS=1 -DDEBUG_EXTNAME=1 -DDISABLE_SHARED=OFF -DENABLE_DOWNLOADS=1 -DWITHOUT_SERVER=0

 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_DEBUG=1 -DWITH_EMBEDDED_SERVER=0

 -DWITH_EXTRA_CHARSETS=all -DWITH_FAST_MUTEXES=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 

-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_LIBEDIT=1 -DWITH_LIBWRAP=1

-DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 

-- MySQL 5.6.5-m8
CMake Error at configure.cmake:196 (LIST):
  list sub-command REMOVE_DUPLICATES requires list to be present.
Call Stack (most recent call first):
  CMakeLists.txt:262 (INCLUDE)
 

Obviously on linux all works fine.

After having fight a little by myself, I start to look around and I have found that bug 65050 was talking about it.

But for version 5.5. and is marked as Critical.

Anyhow I review the patch describe there, and look in the file configure.cmake.

Obviously the lines were different but once identify the right position: line 196 in 5.6.6 m8.

Replacing the line

196

LIST(REMOVE_DUPLICATES CMAKE_REQUIRED_LIBRARIES)

with 

196
197
198

  IF(CMAKE_REQUIRED_LIBRARIES)
   LIST(REMOVE_DUPLICATES CMAKE_REQUIRED_LIBRARIES)
  ENDIF()

I run again the cmake and finally I got :

1
2
3
4
5
6
7
8
9
10
11

tusamac:mysql-5.6.5-m8 marcotusa$ cmake . -G Xcode -DCMAKE_INSTALL_PREFIX:PATH=/Users/marcotusa/mysql_work/template/mysql-5.6.5 

-DCMAKE_USE_RELATIVE_PATHS=1 -DDEBUG_EXTNAME=1 -DDISABLE_SHARED=OFF -DENABLE_DOWNLOADS=1

 -DWITHOUT_SERVER=0 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 

-DWITH_DEBUG=1 -DWITH_EMBEDDED_SERVER=0 -DWITH_EXTRA_CHARSETS=all -DWITH_FAST_MUTEXES=1

 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_LIBEDIT=1

 -DWITH_LIBWRAP=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_UNIT_TESTS=1
-- MySQL 5.6.5-m8
-- C warning options: -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wdeclaration-after-statement
-- C++ warning options: -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wno-unused-parameter -Woverloaded-virtual
-- Successfully downloaded http://googletest.googlecode.com/files/gtest-1.5.0.tar.gz to /Users/marcotusa/mysql_work/source/mysql-5.6.5-m8/source_downloads
-- GTEST_LIBRARIES:gtest
-- Configuring done
-- Generating done
-- Build files have been written to: /Users/marcotusa/mysql_work/source/mysql-5.6.5-m8
tusamac:mysql-5.6.5-m8 marcotusa$ 
 

Easy and clean.

Thank you to : Mike McQuaid for identifying the issue and submit the patch

And shame on MySQL/Oracle for having Mac OS/X so out of the radar.

{joscommentenable}

Portable table space part III or We can do it (with partition)

First of all, I have to say that as usual I did the three investigation, because I had fun.

MySQL is still fun for me, and also if I am not touching the magic topic of the moment like SSD,

replication and so on, I enjoy what I was doing, and I see how I can save my customer data, with this.

Second, I thanks again the people who helps me in understanding better MySQL, in this case Mattias Jonsson, who points me in right direction.

The Solution

The solution is a little bit cumbersome, but it works.

I have assume that:

• a customer have a data-set which contains a partitioned table
• we can have short period of read only data

To use the DISCARD tablespace on a partition the process is:

1. - LOCK the table with partition and the final table where I will put the data
2. - EXCHANGE the table with the partition
3. - Flush the table
4. - save the files
5. - EXCHANGE AGAIN table with partition
6. - reload the record IF ANY from the table I have move back to the partition (delta insert during the first exercise)
7. - Flush again
8. - save files

To recover do the usual procedure but against the table then EXCHANGE it with the partition.

It is obvious that more time pass more DATA will differ from the saved tablespace and the live data set, so longer it will take to reload the data.

Anyhow below the full process in detail, have fun.

Process details

INITIAL data set

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

(root@localhost) [test_tablespace1]>select * FROM partD;
Empty SET (0.00 sec)
(root@localhost) [test_tablespace1]>show CREATE TABLE tbtest_part \G
*************************** 1. row ***************************
TABLE: tbtest_part
CREATE TABLE: CREATE TABLE 'tbtest_part' (
'a' int(11) NOT NULL DEFAULT '0',
'b' char(3) DEFAULT 'AAA',
'dat' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
'part' int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (part)
(PARTITION partA VALUES IN (1) ENGINE = InnoDB,
PARTITION partB VALUES IN (2) ENGINE = InnoDB,
PARTITION partC VALUES IN (3) ENGINE = InnoDB,
PARTITION partD VALUES IN (4) ENGINE = InnoDB) */
1 row IN SET (0.02 sec)
(root@localhost) [test_tablespace1]>select COUNT(*) FROM tbtest_part WHERE part=4;
+----------+
| COUNT(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.95 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>create TABLE parD SELECT * FROM tbtest_part LIMIT 0;
 

I have to exchange the PARTITION and then use the delta to fill the difference.

lock tables tbtest_part write, partD write;   then run the  ALTER TABLE tbtest_part EXCHANGE PARTITION partD with table partD;

1
2
3
4
5
6
7

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (0.32 sec)
(root@localhost) [test_tablespace1]>unlock TABLES;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test_tablespace1]>FLUSH TABLE partD WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 

Time to save the files in the backup directory

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 1407388
drwx------ 2 mysql mysql 4096 2012-05-07 17:16 ./
drwxr-xr-x 5 mysql mysql 4096 2012-05-07 11:41 ../
-rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql 523 2012-05-07 17:16 partD.cfg
-rw-rw---- 1 mysql mysql 8636 2012-05-07 11:41 partD.frm
-rw-rw---- 1 mysql mysql 96468992 2012-05-07 11:45 partD.ibd
-rw-r----- 1 mysql mysql 476 2012-05-05 19:06 tbtest_100MB.cfg
-rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_100MB.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.* /home/mysql/backup/

Let assume that during a period of time N the table will get additional 600 records

such that when we bring it back we have to reinsert them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.46 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.94 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM partD;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961464 |
+----------+
1 row IN SET (0.94 sec)
 

Good all data recover, now we must do something to preserver our set, we must save again the partD table to preserve the delta

we miss during the EXCHANGE.

We must also rename the table's files.

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.ibd /home/mysql/backup/parDdelta.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.frm /home/mysql/backup/parDdelta.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.cfg /home/mysql/backup/parDdelta.cfg

Assume that we have a disaster and data is lost, we will simulate truncating the partition

1
2
3
4
5
6
7
8
9
10

(root@localhost) [test_tablespace1]>alter TABLE tbtest_part TRUNCATE partition partD;
Query OK, 0 rows affected (0.27 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row IN SET (0.00 sec)

Help my data is lost!!!

And assume I still have insert until I put the table on reed only (hoping I can doit at elast for short time):

1
2
3
4
5
6
7

(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row IN SET (0.00 sec)

Now we have to:

1. re-attach the old dataset to a new table and exchange with the partitioned table.
2. save new data in a mew table
3. re-attach the tablespace to partionD and reload the data;
4. reload the delta between crash and new inserts

let's go:

1) Do the different steps to have the original data back

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

(root@localhost) [test_tablespace1]>create TABLE partD SELECT * FROM partD_temp LIMIT 0;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE partD DISCARD tablespace;
Query OK, 0 rows affected (0.03 sec)

Copy old TABLE space FROM my BACKUP area TO DATA directory

(root@localhost) [test_tablespace1]>ALTER TABLE partD IMPORT tablespace;
Query OK, 0 rows affected (30.94 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.43 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.93 sec)

Finally save the DELTA of the data in a different table to do not overwrite it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

(root@localhost) [test_tablespace1]> RENAME TABLE partD TO partD_AFTER_EMERGENCY;
Query OK, 0 rows affected (0.08 sec)
2) reload DATA of the partD delta (the 600 entries we saved before)
(root@localhost) [test_tablespace1]>create TABLE parDdelta SELECT * FROM tbtest_part LIMIT 0;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta DISCARD tablespace;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta IMPORT tablespace;
Query OK, 0 rows affected (2.95 sec)
3) reload the DATA FROM the reattached tablespace
(root@localhost) [test_tablespace1]>select count(*) FROM parDdelta;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM parDdelta;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0

4) reload the missed delta after the EMERGENCY

1
2
3
4
5
6
7
8
9
10

(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD_AFTER_EMERGENCY;
Query OK, 200 rows affected (0.06 sec)
Records: 200 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961664 |
+----------+
1 row IN SET (0.94 sec)

Done the whole dataset is rebuild.

Conclusion

It is possible to have per PARTITION tablespace backup using DISCARD/IMPORT and EXCHANGE.

It is also possible to recover any delta that the application will insert in the meanwhile.

It is obvious that during th process the dataset will be inconsistent, so the best thing to do is to avoid inserts.

Copy the specific tablespace will be much easier and faster, then reloading from a full backup, if the issue is limited to a single tablespace or partition.

What I have done here manually must be automize using a script, that will simplify the process and make it much faster.

I hope you enjoy the whole as I did during the tests.

{joscommentenable}