Nice mysql/mariadb cluster status query

I'm trying to write a nice bit of SQL to give me a quick readout on cluster performance. I have a start, just wondering if anyone has anything better, or would like to improve on what I have:

 select 'Node:' Variable, concat(ucase(@@hostname),' in ',(select variable_value from information_schema.global_status where variable_name='wsrep_cluster_size'),' node cluster') as val  UNION   select 'Connections', concat(variable_value,' used / ',@@max_connections,' max') from information_schema.global_status where variable_name='max_used_connections'   UNION   select 'Connections', concat(variable_value,' used / ',@@max_connections,' max') from information_schema.global_status where variable_name='max_used_connections'  UNION   select 'Memory used (Mb)',round(variable_value/1024/1024,1) from information_schema.global_status where variable_name='Memory_used'  UNION   select 'Statuses', ucase(group_concat(variable_value SEPARATOR ' ')) from information_schema.global_status where variable_name in ('wsrep_evs_state','wsrep_connected','wsrep_local_state_comment','wsrep_cluster_status')  UNION   (select replace(lcase(variable_name),'wsrep_','') as variable,ucase(replace(variable_value,':1306','')) as val from information_schema.global_status where variable_name in ('wsrep_flow_control_sent','wsrep_cert_deps_distance','wsrep_flow_control_paused','wsrep_local_recv_queue_avg','wsrep_local_recv_queue_avg','wsrep_incoming_addresses') order by variable) 

and sample output is:

 +----------------------+----------------------------------+  | Variable             | val                              |  +----------------------+----------------------------------+  | Node:                | NODEBOB in 3 node cluster        |  | Connections          | 5 used / 500 max                 |  | Memory used (Mb)     | 272.5                            |  | Statuses             | SYNCED OPERATIONAL PRIMARY ON    |  | local_recv_queue_avg | 0.000000                         |  | flow_control_paused  | 0.000000                         |  | flow_control_sent    | 0                                |  | cert_deps_distance   | 35.865174                        |  | incoming_addresses   | 10.1.1.122,10.1.1.126,10.1.1.120 |  +----------------------+----------------------------------+ 

I want to know, at a glance, that its up, all the nodes are attached, and there are no impending performance issues. (I'm using mariaDB 10.1)

Replay

Category: mariadb Time: 2016-07-28 Views: 0

Related post

  • Why would mysql "show global status" query be taking 15 minutes? 2013-01-10

    I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s. Any idea how to determine the cause of this? --------------Solutions------------- That sounds very unusual, but not surprising. All it takes to run S

  • MariaDB / MySQL galera cluster nodes won't join 2013-11-16

    I'm setting up a MariaDB Galera cluster and I can't seem to get the nodes to join each other. They both start up without errors but never seem to join, the status is always disconnected: MariaDB [(none)]> show status like 'wsrep%'; +-----------------

  • Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with intervall) 2016-07-17

    I search for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs). In MongoDB world there is the Closed Source Ops Manager and the Open Source mtools. I search something similar. In official

  • Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS 2012-02-27

    I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds. We have an integration with the UPS Worldship

  • MariaDB Cluster vs Percona Cluster for MySQL 2013-10-11

    What are the advantages and and disadvantages between the two? I've only been able to find information on these two implementations without any specifics on clusters. I'm currently implementing a Percona Cluster but my only concern currently is with

  • How to monitor MariaDB Galera cluster status from command line 2015-03-17

    I know to check the Galera cluster status, the command that should be used after login to the mysql client is: SHOW STATUS LIKE 'wsrep%'; but, how to get the output directly from the command line? The command below result in error: [[email protected] ~]

  • MariaDB Cluster - data consistency for updates and auto_increment 2014-05-07

    I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and have some questions. And I not use transactions, f

  • Can't reset MySQL (MariaDB) root password 2015-08-02

    Today, I wanted to create a database in PMA. It said: "Cannot log in to the MySQL server". I tried via a terminal, same problem, and it is because my password is wrong. And I can't understand why. I tried the usual method to reset the root passw

  • How do I correct replicaiton on the primary master after reconfiguring replication in a MySQL-MMM cluster? 2010-11-27

    I have a MySQL-MMM cluster with three database servers (two masters and one slave). Recently replication was broken by someone directly inserting to the slave database servers. After I discovered this I reestablished replication from the db1 system t

  • MySQL 5.5.9 Query Cache not working when schemas have hyphens ("-") in their name 2011-03-12

    I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU. I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the Qcache never hit? UPDATE 2: I have found this is lim

  • mysql replication: show 'bad' query 2011-07-11

    Is there a way to get MySQL to display the query that caused replication to stop? I have a DB with hundreds of identical tables, each of which has a trigger to update a related table. One of these is (apparently) not configured properly. I can't use

  • MySQL - MariaDB 2013-06-20

    Would it be possible to change (dump) mysql in favor of mariadb in future versions and tutorials? --------------Solutions------------- I don't think the tutorials will suggest Maria/Percona over MySQL - but you can easily switch yourself... Personall

  • MySQL/MariaDB stops running. How do I find out why? 2014-12-01

    Today I've seen MySQL/MariaDB stop running on a CentOS VPS. When I check the status I get: # service mysql status ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists So I remove that file and then restart the service. But then

  • What's the advantage of thread pool in MySQL/MariaDB? 2014-12-25

    MariaDB thread pool MySQL thread pool I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages: 1. limit too many connections, too many connection could lead to too many

  • indexable JSON datatype in MySQL/MariaDB 2015-04-18

    In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent? I'm specifically interested in the MariaDB Galera cluster, but am open to any MySQL-family answers.

  • Question on "Installing A Web, Email & MySQL Database Cluster On Debian 6.0 With ISP3 2015-05-28

    I have a firewall forwarding the port to my mail server. Do I still forward it to the "Master" server or do I forward to the "Slave" server. "Installing A Web, Email & MySQL Database Cluster On Debian 6.0 With ISPConfig 3"

  • Ubuntu mysql reinstall service status not working 2015-07-13

    I tried to reinstall my mysql server on ubuntu 14. After reinstall all seems to run fun until the service mysql status/start/stop command When the server is started the mysql status is stop/waiting but it runs (I did added (sudo update-rc.d mysql def

  • Problem with mysql (mariadb) crash 2011-04-23

    An hour ago I noticed that the mysql (mariadb 5.2.5) server is not running on my debian VPS. So I immediately ran "/etc/init.d/mysql restart" and it started just fine. Here's what the daemon log says about mariadb when it had crashed Apr 23 11:5

  • Web, Email And MySQL Database Cluster (Mirror) On Debian 6 2013-07-12

    hi folks, I build up a Web, Email And MySQL Database Cluster (Mirror) On Debian 6 according to the tutorial from Falko, I have 2 load balancers and 2 nodes, I'm balancing http and smtp with HAproxy, everything seems to be ok just some little things d

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.477 (s). 13 q(s)