0

Since last 2 year, we were using NDB Cluster for live database of our app, which is now have size of 7-10 GB, running on 6 Dedicated servers (6 data node, 2 api node, 1 mgm node) to have get maximun throughput with the single replica and multi threaded data nodes.

We are facing following problems.

  1. ALTER tables

We can't alter tables on live database,

algorithm=inplace don't work in ndb for alters.

Thus for these operations we must stop application. what could be possible solution for this because development is life time & ongoing, and looking for long-term solution so without app downtime we can perform some operations.

i know pt-online-schema-change and some other tools but they don't work with ndb.

  1. Scaling

When data size increases we can add node using rolling restart, but even after adding node, we'll need to reorganize partition to make data equal on all data node, which causes table lock for long hours.

  1. Is using multiple single threaded NDB is better or multi-threaded NDBMTD

  2. Is it possible for muliple replica system and have online alters without downtime?

What could be the solution of above two problems we are facing?

or can you guide what should be the architecture for High Avaialibity, Max Throughput and some times online Maintenance with lesser downtime.

  • What is the error message you are facing when you use algorithm=inplace? Note: if the table has a defined max row MAX_ROWS, this algorithm will not work. – Ahmad Abuhasna Apr 10 '18 at 8:06
  • it say error like this is not supported for ndb engine – dev.meghraj Apr 10 '18 at 8:07
  • Is your table has been created using MySQL 5.5 or older and your table has date or timestamp on it? if yes, this is a known limitation. you can use a workaround (rebuild using algorithm copy). but I suggest to test it in dev environment first. – Ahmad Abuhasna Apr 10 '18 at 8:35
  • 1
    7 - 10 GB is not huge! Even 10 times that isn't enormous. If you want a MySQL solution, then why not use Percona cluster or MariaDB cluster? You could also (my recommendation) look at PostgreSQL solutions. – Vérace Apr 10 '18 at 8:43
  • Hi, @dev.meghraj would you please provide full alter command with the error message since the provided information will make us guessing about the situation. – Ahmad Abuhasna Apr 10 '18 at 10:09
0

The above is a correct analysis, an ALTER TABLE using algorithm=inplace will have a write lock on the table in the MySQL server that performs the operation.

BUT, in all the other MySQL servers in the cluster it is ok to continue reading and writing the table. The reason is that the MySQL server code is designed in that way, but NDB can actually handle writes when performing ALTER TABLE that uses the inplace algorithm.

In my book MySQL 7.5 inside and out I recommend to use a special MySQL server for ALTER TABLE operations, but obviously the above method will work as well.

  • That's great.. let me try that out Any throughts or experience with.other part of questions – dev.meghraj May 1 '18 at 15:10
  • It is better to use multi-threaded NDB data nodes than to use multiple single-threaded data nodes. – Mikael Ronström May 1 '18 at 21:07
  • It is possible to perform online ALTERs without downtime even with multiple replicas and writes are always ok (except the special case mentioned above) – Mikael Ronström May 1 '18 at 21:08
-1

With ALTER tables on NDB we noticed the funny thing. You can work with the data if you run the queries on the different SQL node from the one which is running actual ALTER TABLE command. Maybe not all the possible variants of ALTER TABLE but enough for our purposes.

So we were doing the following on one SQL node:

iptables -I INPUT -p tcp --dport 3306 -j REJECT
service mysqld restart
mysql -e "ALTER TABLE table1 <do something>" db1
iptables -D INPUT -p tcp --dport 3306 -j REJECT

2nd step is to drop all the active connections and unfinished transactions so that node is free from user queries.

And all the applications continued to work on the other SQL node.

  • Hi, are you sure blocking cluster will fix the issue? if he gets the following error "ERROR 1846 (0A000)" changing IP tables rules will not fix the issue. – Ahmad Abuhasna Apr 10 '18 at 10:07
  • means at app side, if mysql connection fails (due to blocking we did) app sholud connect to 2nd server.. – dev.meghraj Apr 11 '18 at 6:40
  • Why mysql locks on one api node and not on other node? – dev.meghraj Apr 11 '18 at 6:41
  • Now I do not remember why. Just I know that we used such method several times to ALTER tables online. – Mindaugas Riauba Apr 11 '18 at 10:37
  • That's also not seems good solution..as per seems blocking firewall etc. – dev.meghraj Apr 12 '18 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.