2

MariaDB has an extra_port for administrative access that helps in cases the thread pool or main port is locked up.

MariaDB's documentation says it's use case is primarily administrative access and it can be used to make sure monitoring systems always have access.

To me "always have access" sounds like something you want for replication but it's not mentioned and I could not find anyone writing about using the extra port for replication.

The paragraph When to Use the Thread Pool in the documentation says:

Thread pools are most efficient in situations where queries are relatively short and the load is CPU-bound

Replication runs forever (fingers crossed) and is IO-bound. This sounds like another indication to use the extra port (although replication is not a query).

My Questions:
Can you explain me why it's not a good idea to use the extra port for replication?
If it actually is a good idea, why is it not widespread (as indicated by not finding anything substantial when googling for "mariadb extra_port replication")?
Do replication connections get some special treatment and/or priority?

wedi
  • 121
  • 3

3 Answers3

1

Good question.

The default REPLICATION CLIENT / BINLOG_MONITOR has no elevated privileges like CONNECTION ADMIN or SUPER that allow them to bypass the max_connections limit.

The extra_port connections use one thread per connection rather than pooling. max_extra_connections applies to the extra_port, defaulting to one, so if you use it for replication, increasing the max_extra_connections to allow for a few other tasks is prudent if you use them.

The reason thread pools where suited for short connections is the overhead of creating new threads. As pointed out in the question, this isn't important for replication.

Granting a CONNECTION ADMIN to a replication user is quite beyond what it needs.

So using an extra port to preserve the replication ability if it happens to disconnect, and so that users cannot consume connections beyond the ability of the replica to connect, is actually a reasonable idea (even if not commonly considered).

danblack
  • 8,258
  • 2
  • 12
  • 28
0

Replication do not need some special ports and can use the common 3306. An extra port is a port that can be used by root as back door if there is no access through the common port.

Say if you have max_connections = 100 and your app is already used all that 100 connections, only one extra connection can be used by user with ALL PRIVILEGES granted. If someone is already connected as DBA you have no chance to connect to the server. But you still can connect via extra_port cause it has no max_connections limit. And that is the reason why you should not use it for any other tasks except an emergency access. With no max_connections set to the reasonable value you can easily exhaust your host's RAM that can hang your DB or even a host in a pretty bad way.

Kondybas
  • 4,800
  • 19
  • 16
0

Thread pool is smart enough to handle one long query, too, and to recognize network-bound so it excludes this thread out from calculations. Thus, I would say, no, there is no reason to use extra-port in a creative way.

Vladislav Vaintroub
  • 676
  • 6
  • 8