Starting out with ProxySQL - monitoring

[Kévin Lemonnier]

Hi,

I just installed ProxySQL and I'm wondering how to monitor it. I thought a good start would be to make sure SELECT * FROM mysql_servers on the admin port returns 3 ONLINE servers,

but testing that out I noticed that if I stop a server, that table doesn't get updated. Worse, the site doesn't work anymore.

I tried following most of http://severalnines.com/blog/how-proxysql-adds-failover-and-query-control-your-mysql-replication-setup to do my install,

but I'm using a master / master configuration, no cluster control so I didn't do the last part about telling ProxySQL which groups are read or write.

Instead, as a write server, I added HAProxy which has the first server in normal and the second server as backup, that way I should have automatic FailOver.

Then the two servers are added directly for read, not using HAProxy.

So my question is what could I have missed ? Looks like ProxySQL doesn't notice when I stop one of the servers, clearly I the write isn't the problem since HAProxy

does see it and starts routing to the backup server, so it must be a problem in the read group. Is defining the read and write groups what enables monitoring in ProxySQL ?

I added a monitoring user and pass, that user can monitoring the replication (we actually use it in our monitoring for this so I know it works).

And as a more broad question, what do you use to monitor that ProxySQL is working fine ?

Thanks a lot !

[Kévin Lemonnier]

Looks like my problem is the replication lag for some reason ..

When I stop a server, the other is considered in lag, even if it's not :

+-----------+------------+----------+-------------------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

| hostgroup | srv_host   | srv_port | status                  | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |

+-----------+------------+----------+-------------------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

| 0         | 172.16.0.1 | 3307     | SHUNNED_REPLICATION_LAG | 0        | 0        | 6      | 2       | 18919   | 10038282        | 43283598        | 301        |

| 1         | 172.16.0.5 | 3306     | SHUNNED_REPLICATION_LAG | 0        | 0        | 9      | 22      | 459654  | 217392876       | 164952074       | 289        |

| 1         | 172.16.0.6 | 3306     | SHUNNED                 | 0        | 0        | 7      | 0       | 477345  | 220385359       | 158207723       | 327        |

+-----------+------------+----------+-------------------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

[Kévin Lemonnier]

Well I removed the replication lag check for now, that "solves" it but that's really not ideal.

[René Cannaò]

Hi Kévin,

this email is just to acknowledge I read this message, and I will provide a more exhaustive reply later on.

Two important notes:

* table mysql_servers is a configuration table : if a server becomes unavailable, mysql_servers is not updated (it is a configuration), unless the monitor module can't ping it (MySQL Ping)

* quickly reading the output below, my bet is that you haven't configured the Monitor username/password :
https://github.com/sysown/proxysql/blob/master/doc/monitor.md#variables

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[aaron.i...@gmail.com]

Hi René,

Does the Monitor password handle encrypted passwords too or must they be in plain text?

Thanks,

Aaron

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

I am late in replying this email threads, sorry.

Kévin: as the node get shunned due to replication lag, I assume the monitor user works correctly.

For configuration in master-master, please have a look at https://github.com/sysown/proxysql/issues/722 , and a feature request: https://github.com/sysown/proxysql/issues/730 .

Aaron: the Monitor module must have passwords in clear text.

The MySQL module doesn't need the passwords in clear text because from sha1(sha1(plainpass)) can derive sha1(plainpass) when a client authenticate, and use this information to connect with the backend.

Only sha1(sha1(plainpass)) is not enough to connect to the backend, so the Monitor module can't use hashed password.

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.