FullText Indexing IPv6 addresses with MySQL 5.7

MySQL 5.7 supports generated fields. This is particularly useful for searching the string representation of numeric stored ip addresses:

CREATE TABLE catalog(
ip varbinary(16) not null,
hostname varchar(64) not null,
label varchar(64),
ip_ntoa varchar(64) generated always as (inet6_ntoa(ip)) STORED, -- generate and store fields with the address representation
fulltext key (hostname, ip_ntoa, label)
);

When inserting values

INSERT INTO catalog(ip,hostname,label) VALUES
(inet6_aton('127.0.0.1'), 'localhost', 'lo'),
(inet6_aton('192.168.0.1'), 'gimli', 'stage,ipv4'),
(inet6_aton('fdfe::5a55:caff:fefa:9089'), 'legolas', 'router,ipv6'),
(inet6_aton('fdfe::5a55:caff:fefa:9090'), 'boromir', 'router,ipv6')

you can search in OR mode with

SELECT hostname FROM catalog WHERE
  MATCH(ip_ntoa, hostname, label)
  AGAINST('9089 router');
-- returns every entry matching ANY needle
***1***
hostname: legolas
***2***
hostname: boromir

Or exact matches

SELECT hostname FROM catalog WHERE
  MATCH(ip_ntoa, hostname, label)
  AGAINST('+9089 +router' in boolean mode);
-- returns ONE entry matching ALL needles
***1***
hostname: legolas

Positioning MySQL replication with GTID

mysql-fabric is a set of tools to setup resiliend and scalable mysql infrastructures.

You can create fabric groups (a set of replicated servers) with automatic failover and various policies.

Shortly:

#mysqlfabric group create mycluster
#mysqlfabric group add mycluster db-1:3306
#mysqlfabric group add mycluster db-2:3306
#mysqlfabric group add mycluster db-3:3306

And now pick a master: fabric will configure replication on all the nodes

#mysqlfabric group promote mycluster --slave_id DB_1_ID

Now, unless db-1 is a blank page, you’re likely to get an error 🙁

Fabric is trying to replicate ALL the changes happened on db-1 since its creation (included “CREATE USER root …”) to all slaves.

The solution is to
1 – get the last transaction id used for configuration;
2- tell to the slaves to skip everything until then.

It is done via

-- stop replication first, and reset what have been done until now (hopefully nothing ;)
STOP SLAVE; 
RESET MASTER;
-- tell the slave to skip the first 10 transactions from the server with id 9f36...
 SET @@GLOBAL.GTID_PURGED = '9f367fff-d91e-11e4-8ffe-0242ac110017:1-10'; 
-- now restart the slave and check that everything is fine
START SLAVE; 

SHOW SLAVE STATUS \G

Speed up oracle import

While importing a DB in oracle, I found the following warnings in the log:

Thread 1 cannot allocate new log, sequence 1299
Checkpoint not complete
  Current log# 3 seq# 1298 mem# 0: /oradata/TESTDB/redo03.log
Thread 1 cannot allocate new log, sequence 1300
Checkpoint not complete
  Current log# 6 seq# 1299 mem# 0: /oradata/TESTDB/redo06.log

At the same time, iostat reported low values for disk utilization

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00    7,40  531,40     2,39    42,44   170,40     0,35    0,66   0,47  25,52
dm-13             0,00     0,00    0,00   23,00     0,00     8,23   733,05     0,42   18,14   0,42   0,96

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8,28    0,00    9,45    6,86    0,00   75,41

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00   16,80  999,80     5,55    94,42   201,40     5,89    5,79   0,53  53,50
dm-13             0,00     0,00    0,00   52,00     0,00    16,54   651,34     1,01   19,48   1,05   5,48

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6,19    0,00    1,83    1,73    0,00   90,25

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00    5,40  840,40     1,95    24,87    64,93     0,29    0,35   0,29  24,94
dm-13             0,00     0,00    0,00   49,00     0,00    16,48   688,74     0,79   15,76   0,38   1,86

A Checkpoint error means that oracle stops processing new entries because the redo logs were not committed to the database file.

The solution was just adding more redo-log files via sqlplus

ALTER DATABASE ADD LOGFILE  (
'/oradata/TESTDB/redo10.log',
'/oradata/TESTDB/redo11.log',
'/oradata/TESTDB/redo12.log',
'/oradata/TESTDB/redo13.log',
'/oradata/TESTDB/redo14.log'
) size 50M
;

MySQL Cluster: dimensioning redo logs

A rule of thumb of redo log dimensioning is: enough to store all incoming create/update transaction between three LCP. In MySQL Cluster jargon, the periodical snapshot of the database is name Local Checkpoint or LCP.

That is:

2 * (DataMemory / DiskSpeed) * Incoming Thruput

During some test importing the famous Employees Database on a two-node amazon cluster I ran into the following error:

ERROR 1297 (HY000) at line 38 in file: ‘xao’: Got temporary error 410 ‘REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)’ from NDBCLUSTER

It was strange, because the REDO log size was 512M, while the DB was only 130M. No, I definitely didn’t want to increase NoOfFragmentLogFiles (aka REDO log size): 512M was enough. Even decreasing TimeBetweenLocalCheckpoints was not useful – since I saturated AMZ machine I/O that was ~ 5MBps

So I started monitoring the REDO log occupation with the following command:

while sleep 5; do
mysql -e “select node_id, 100*used/total, used/1024/1024 from ndbinfo.logspaces where node_id=4;”;
done

I found that the logs – which are split in 4 logical chunks of 128M named “fragments” – were not uniformly used.
Each table was using always the same fragment, and the error was given while inserting a big table which exhausted “its” 128M chunk.

| node_id | 100*used/total | used/1024/1024 |
+---------+----------------+----------------+
|       4 |        96.7500 |   123.00000000 |
|       4 |         1.2500 |     2.00000000 |
|       4 |         1.2500 |     2.00000000 |
|       4 |         1.2500 |     2.00000000 |
+---------+----------------+----------------+

I asked to MySQL Guru Matthew Montgomery, which suggested the following reason:
“All transactions within a given LCP for a given fragment are stored within the same fragment log.”

The hints he provided follow:

Hint 1 :distribute REDO log usage, manually partition the table. This requires removing a foreign key to add the partition using ALTER TABLE:

CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
) ;
— We have to split the PARTITION statement from the CREATE one
— due to http://bugs.mysql.com/bug.php?id=67492
— Thx to Matthew “MontgoDB” Montgomery for the workaround!
ALTER TABLE salaries PARTITION BY KEY() PARTITIONS 8;

Edit: Thanks to this post, Matthew filed this bug and suggested the forementioned workaround!

Using a single statement you would have get this error from mysqld:

ERROR 1506 (HY000) at line 97: Foreign key clause is not yet supported in conjunction with partitioning

This is because the mysqld parser – which is InnoDB oriented – blocks this request because InnoDB doesn’t still support FK+Partitioning.

Obviously

Hint 2: use the max_rows directive and find a suitable value. In our case, the table should contain ~3M rows but we needed to overbook allocating space for 40M:

CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
) max_rows=40000000;

The 40M number was just heuristic: a script modified the “CREATE TABLE” statement until the inserting was successful 😉

After the change, fragment usage was doubled:

+---------+----------------+----------------+
| node_id | 100*used/total | used/1024/1024 |
+---------+----------------+----------------+
|       4 |        98.4000 |   126.00000000 |
|       4 |         1.2500 |     2.00000000 |
|       4 |         1.2500 |     2.00000000 |
|       4 |        99.2150 |   127.00000000 |
+---------+----------------+----------------+
+---------+----------------+----------------+
| node_id | 100*used/total | used/1024/1024 |
+---------+----------------+----------------+
|       4 |        57.0310 |    73.00000000 |
|       4 |         1.8750 |     3.00000000 |
|       4 |         1.8750 |     3.00000000 |
|       4 |        57.0310 |    73.00000000 |