MongoDB Munin Plugin

A plugin is available that provide metrics for:

• B-Tree stats
• Current connections
• Memory usage
• Database operations (inserts, updates, queries etc.)

The plugin can be installed on each node where MongoDB. For requirements and instructions to installing the MongoDB Munin Plugin.

Check your setup

After installing the plugin and making the configuration changes, force the server to update the information to check that your setup is correct using the following:

sudo -u munin /usr/share/munin/munin-update

If everything is set up correctly, you will get a chart like this:

Plugins

• mongo_ops : operations/second
• mongo_mem : mapped, virtual and resident memory usage
• mongo_btree : btree access/misses/etc...
• mongo_conn : current connections
• mongo_lock : write lock info
• mongo_docs : number of documents (inserted, updated...)

Requirements

• MongoDB 2.4+
• python/pymongo

Installation (ubuntu)

Install pip:

sudo apt-get install build-essential python-dev python-setuptools
sudo easy_install pip
sudo pip install --upgrade virtualenv 

Install pymongo:

sudo pip install pymongo

Install plugins:

git clone https://github.com/comerford/mongo-munin.git /tmp/mongo-munin
sudo cp /tmp/mongo-munin/mongo_* /usr/share/munin/plugins
sudo ln -sf /usr/share/munin/plugins/mongo_btree /etc/munin/plugins/mongo_btree
sudo ln -sf /usr/share/munin/plugins/mongo_conn /etc/munin/plugins/mongo_conn
sudo ln -sf /usr/share/munin/plugins/mongo_lock /etc/munin/plugins/mongo_lock
sudo ln -sf /usr/share/munin/plugins/mongo_mem /etc/munin/plugins/mongo_mem
sudo ln -sf /usr/share/munin/plugins/mongo_ops /etc/munin/plugins/mongo_ops
sudo ln -sf /usr/share/munin/plugins/mongo_docs /etc/munin/plugins/mongo_docs
sudo chmod +x /usr/share/munin/plugins/mongo_*
sudo service munin-node restart

Check if plugins are running:

munin-node-configure | grep "mongo_"

Test plugin output:

munin-run mongo_ops

Configuration

How to configure custom db connection?

munin-node can set env value in below file: /etc/munin/plugin-conf.d/munin-node

[mongo_*]
env.MONGO_DB_URI mongodb://user:password@host:port/dbname

How to Install MongoDB on Ubuntu 14.04 LTS

MongoDB is a NoSQL database intended for storing large amounts of data in document-oriented storage with dynamic schemas. NoSQL refers to a database with a data model other than the tabular format used in relational databases such as MySQL, PostgreSQL, and Microsoft SQL. MongoDB features include: full index support, replication, high availability, and auto-sharding.

Pre-Flight Check

• These instructions are intended for installing MongoDB on a single Ubuntu 14.04 LTS node.
• I’ll be working from a Liquid Web Core Managed Ubuntu 14.04 LTS server, and I’ll be logged in as a non-root user, but with sudo access. For information on giving a user sudo access visit our page on How to Add a User and Grant Root Privileges on Ubuntu 14.04

Step #1: Setup a the Package Database

First we’ll import the MongoDB public key used by the package management system:

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv EA312927

Create a list file for MongoDB.

Create the /etc/apt/sources.list.d/mongodb-org-3.2.list list file using the command appropriate for your version of Ubuntu:

Ubuntu 12.04

echo "deb http://repo.mongodb.org/apt/ubuntu precise/mongodb-org/3.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-3.2.list

Ubuntu 14.04

echo "deb http://repo.mongodb.org/apt/ubuntu trusty/mongodb-org/3.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-3.2.list

Now reload the package database:

sudo apt-get update

Step #2: Install Latest Stable Version MongoDB

At this point, installing MongoDB is as simple as running just one command:

sudo apt-get install -y mongodb-org

If you’d like MongoDB to auto-update with apt-get than you’re done with the installation. But, it’s possible to ‘pin’ the version of MongoDB you just installed to prevent apt-get from auto-updating.

echo "mongodb-org hold" | sudo dpkg --set-selections
echo "mongodb-org-server hold" | sudo dpkg --set-selections
echo "mongodb-org-shell hold" | sudo dpkg --set-selections
echo "mongodb-org-mongos hold" | sudo dpkg --set-selections
echo "mongodb-org-tools hold" | sudo dpkg --set-selections

Step #3: Get MongoDB Running

Start-Up MongoDB:

sudo service mongod start

Check MongoDB Service Status:

sudo service mongod status

Summary List of Status Statistics (Continuous):

mongostat

Summary List of Status Statistics (5 Rows, Summarized Every 2 Seconds):

mongostat --rowcount 5 2

Enter the MongoDB Command Line:

mongo

By default, running this command will look for a MongoDB server listening on port 27017 on the localhost interface. 

If you’d like to connect to a MongoDB server running on a different port, then use the –port option. For example, if you wanted to connect to a local MongoDB server listening on port 22222, then you’d issue the following command:

mongo --port 22222

Shutdown MongoDB:

sudo service mongod stop

Restart MongoDB:

sudo service mongod restart

Thanks,

Import a large sql dump file to a MySQL database from Terminal

Today I had to import a very large SQL dump file (16 Gb) to a MySQL database using osx terminal. If you are using linux or window command line it is the same. The process is the following:

1. Open a terminal (or shell in Linux or command prompt in window with administrative) privilleges
2. If you are in Windows set character set to unicode. Linux is using UTF-8 by default.

   chcp 65001

3. Connect to a mysql instance using command line

   $PATH_TO_MYSQL/mysql -h 192.168.1.1 --port=3306 -u root -p

   if you are in localhost you do not need host and port

   $PATH_TO_MYSQL/mysql -u root -p

4. You are now in mysql shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files

   set global net_buffer_length=1000000;

5. Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

   set global max_allowed_packet=1000000000;

6. Disable foreign key checking to avoid delays,errors and unwanted behaviour

   SET foreign_key_checks = 0;
   SET UNIQUE_CHECKS = 0;
   SET AUTOCOMMIT = 0;
   

7. Import your sql dump file

   source /Users/madman/Web/dbdump151225.sql

You are done! Remember to enable foreign key checks when procedure is complete!

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

If you are in Linux you can create a Bash script which will do the dirty job and write to stdout start and end time of import:

#!/bin/sh 
# store start date to a variable
imeron=`date`
echo "Import starting..."
dumpfile="/Users/madman/Webs/mobigate.sql" 
ddl="set names utf8; "
ddl="$ddl set global net_buffer_length=1000000;"
ddl="$ddl set global max_allowed_packet=1000000000; "
ddl="$ddl SET foreign_key_checks = 0; "
ddl="$ddl SET UNIQUE_CHECKS = 0; "
ddl="$ddl SET AUTOCOMMIT = 0; "
# if your dump file does not create a database, select one
ddl="$ddl USE mobigate; "
ddl="$ddl source $dumpfile; "
ddl="$ddl SET foreign_key_checks = 1; "
ddl="$ddl SET UNIQUE_CHECKS = 1; "
ddl="$ddl SET AUTOCOMMIT = 1; "
ddl="$ddl COMMIT ; "
time mysql -h 127.0.0.1 -u root -psa -e "$ddl" 
# store end date to a variable
imeron2=`date`
echo "End import:$imeron2"