Fork of Nagios custom dashboard

I was looking for something to create custom dashboards in Nagios and came across this. The approach taken here limited each user to a single dashboard but also the method for getting the logged in user didn’t work in my environment. So I decided to fork it…

My version is here.

It basically contains the following changes…

  • Multiple custom dashboards presented to the Nagios user for selection.
  • Removal of dashboard add / edit pages (didn’t work for me).

For further detail see the README file in the project. Thanks to the original author; Franjo Stipanovic (sorry, couldn’t find a url for credit other than Nagios Exchange).

 


MariaDB: subquery causes table scan

I got asked today to look at some slow queries on a MariaDB 10 instance. Here are the anonymized results of the investigation I did into this and how I solved the issue…

There were a bunch of queries, expected to run very fast, that were taking 10-15 seconds to execute. The queries were all similar to this…

DELETE from my_user_table
WHERE u_id IN (SELECT u_id 
		FROM other_table WHERE id = 9999);

I found that the appropriate columns were indexed. Next step was to EXPLAIN the query…

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: my_user_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4675
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: other_table
         type: unique_subquery
possible_keys: PRIMARY,other_index
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

We can see from the output here that a table scan is performed on the first table and the subquery is executed against the second table for every row. The key here is DEPENDENT SUBQUERY. For some reason MariaDB has decided the subquery is dependent on the outer query. We know this isn’t the case but what can we do about it? The solution is turns out is quite simple…

DELETE u 
FROM my_user_table AS u 
WHERE u.u_id IN (SELECT p.u_id
		FROM other_table o 
		WHERE o.id = 9999);

Yep, just use aliases! The EXPLAIN for the modified query is much improved…

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ref
possible_keys: PRIMARY,other_index
          key: other_index
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: my_user_table
         type: ref
possible_keys: idx_t
          key: idx_t
      key_len: 4
          ref: p.u_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)

Here we can see the join order has changed and MariaDB has recognised that the subquery can be changed to a constant. This results in speedier queries because far fewer rows need to read. I’d hazard a guess here that the absence of aliases causes MariaDB to get a little confused about what is dependent on what. Thus causing it to choose a less than optimum plan.


emo: Launch an elasticsearch cluster

I’m getting a bit more into elasticsearch and I’ve started up a github project to contain some of work. This project will be similar to mmo: Python library for MongoDB and can be found at emo. The project will again be in python and will basically be a bunch of methods for exploring and managing an elasticsearch cluster. There’s not much there at the moment. Just a bash script to launch a test elasticsearch cluster. Here’s how you could use it…

First you need to make the functions available in your shell…

. emo_elasticsearch_cluster.sh

This will make the following functions available…

emo_check_cluster_nodes_are_talking  emo_install_kibana
emo_check_es_path                    emo_launch_es_node
emo_check_processes                  emo_launch_nodes
emo_create_admin_user                emo_remove_all_plugins
emo_create_drectories                emo_set_mem_limits
emo_destroy_es_cluster               emo_setup_cluster
emo_install_es_plugins               emond

You don’t need to know the details of most of these. There are some variables in the script you might want to set before starting.

To launch a test cluster simply do the following…

emo_setup_cluster

This will start a process to launch a simple 3 node elasticsearch cluster. We also download and install a few plugins and kibana. There is an option to disable this in the script if you would prefer not to do this. Assuming everything is working you’ll see output similar to this…

lasticsearch is in your path. Continuing...
Created directories for the ES cluster.
Set jvm memory limits
Fired up first elasticsearch node.
Fired up second elasticsearch node.
Fired up third elasticsearch node.
OK: All expected elasticsearch processes are running
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
No JSON object could be decoded
The Elasticsearch cluster nodes are not yet communicating. Sleeping...
OK: Elasticsearch cluster nodes are all talking.
-> Installing license...

To clean everything up just run…

emo_destroy_es_cluster

Note this removes all data directories. It also kills all java processes on the machine which isn’t ideal. I’ll make an update to this soon so only elasticsearch processes are killed. There’s lot more improvements I’ll need to make. Let me know if you have any suggestions.


Delete all but the most recent files in Bash

I’ve been reviewing a few things I do and decided I need to be a bit smarter about managing backups. I currently purge by date only. Which is fine if everything is working and checked regularly. I wouldn’t want to return from a two week holiday to find my backups had been failing, nobody checked it, but the purge job was running happily.

Here’s what I came up to try and solve the problem…

cd /path/to/backup/location && f="backup_pattern*.sql.gz" && [ $(find ${f} -type f | wc -l) -gt 14 ] && find ${f} -type f -mtime +14 -delete 2>/dev/null

Breaking this down…

cd /path/to/backup/location – cd to backup location.
f=”backup_pattern*.sql.gz” – set pattern to match backups in variable.
[ $(find ${f} -type f | wc -l) -gt 14 ] – Return true if more than 14 backups are found. Otherwise false and the command will exit at this point.
find ${f} -type f -mtime +14 -delete 2>/dev/null – Delete files that are older than 14 days and throw away error output to /dev/null

This approach makes use of the && (AND) operator to make its magic work. There’s a lot of good discussion on the web about tackling this problem.


Update on pymmo and demo app

Just a quick update on my pymmo project I started over on github. As I stated earlier this year I want to get deeper into Python and would be writing tools for MongoDB (and potentially other databases).

It doesn’t do a whole lot yet but I hope to make regular small improvements. Using the MongoDB shell for some stuff isn’t really ideal. I’m not keen on looking at large JSON documents to get little bits of information. This tool is an attempt to rectify some of that. I’m not 100% sure where I’m going with this project but I imagine it will be some type of DBA tool for MongoDB.

To list the help for the tool…

python mm.py --help

Output will look something like this…

usage: mm.py [-h] [--summary] [--repl]

MongoDB Manager

optional arguments:
  -h, --help  show this help message and exit
  --summary   Show a summary of the MongoDB Cluster Topology
  --repl      Show a summary of the replicaset state

It’s still very simple so there’s only two options. It also currently uses a default connection on the localhost. More improvements will come.

We connect initially to a mongos server so if you’re using a standalone shard setup this tool won’t work for you.

We can display the structure of our cluster….

MongoDB Cluster Summary

We can also display the status of replication…

MongoDB Replication Status

That’s it for now! If you have any suggestions, for what you’d like to see in this tool, let me know in the comments.