Friday, 12 April 2013

Mongo db stress test using different writeConcern

The aim is to determine how mongo performs using different writeConcern, which control the acknowledgment/quality of the write operations with various options, such as:
WriteConern options are documented here:

Test results:  Total 200k documents are injected into 'user' collection using 4, 8 and 16 parallel threads

UNACKNOWLEDGED option is the fastest of all but not safe, on other hand FSYNC_SAFE is the most safe option but is the slowest of all..

Protecting MongoDB Server with Authentication

MongoDB supports a simple authentication model that allows the administrator to restrict access to databases on a per user basis.
MongoDB supports individual access control records on each database that are stored in a special system.users collection. For normal users to have access to two databases (e.g., db1 and db2), their credentials and rights must be added to both databases.
Note: Before you enable authentication on your server, make sure you have added your admin users to the admin database; if you enable authentication before adding the admin users, you will not be able to perform any actions on your database.

Adding an Admin User

Adding the admin user is as simple as changing to the admin database and using the addUser() command:
$ mongo
MongoDB shell version: 2.2.3
connecting to: test
> use admin
switched to db admin
> show collections
> db.addUser("admin","xxxxx")
        "user" : "admin",
        "readOnly" : false,
        "pwd" : "9f3121efccbe3fef09a799d5e63077c2",
        "_id" : ObjectId("5148475c7719ce8dd603e52d")
> exit

Note: Any users added to the special admin database will have the same access rights on all databases

Enabling Authentication
Now you need to alter your server's configuration to enable authentication. Do so by stopping your server and adding --auth to the startup parameters.
$ grep auth /etc/mongod-rs0-1.conf
auth = true
$ sudo mongod --dbpath /mnt/mongodb/data/rs0-1-db --shutdown
killing process with pid: 26501
[1]+  Done                    sudo nohup mongod --config /etc/mongod-rs0-1.conf
Restart mongoDB:
$ sudo nohup mongod --config /etc/mongod-rs0-1.conf &
[1] 26982
[akhan2@eugenius ~]$ nohup: appending output to `nohup.out'

Authentication is done using db.auth():

> db.auth("admin","xxxxx");

Adding a Read-Only User

The addUser() function includes an additional parameter that allows you to create a user that has only read-only permissions. The MongoDB client will throw an exception if a process authenticated as the newly created user attempts to do anything that would result in a change to the contents of the database. The following example gives a user access to the database for status monitoring or reporting purposes:
First connect to mongo db as 'admin' user:
> use admin
switched to db admin
> db.auth("admin","123");
Grant Read-Only permission to user 'userA' on test db:
> use test
switched to db test
> db.addUser("readuser","xxxxx",true);
        "user" : "userA",
        "readOnly" : true,
        "pwd" : "e982ddebe2d61f96d42e78cd2033a69f",
        "_id" : ObjectId("51484a69ce8d584df56af233")

Test 'userA' authentication:

$ mongo
MongoDB shell version: 2.2.3
connecting to: test
> use test
switched to db test
> db.auth("userA","xxxx");
> db.akhan.find();
{ "_id" : ObjectId("514846197876222c62329f27"), "Ad" : 999919 }
{ "_id" : ObjectId("514846197876222c62329f28"), "Ad" : 999920 }

Deleting a User

To remove a user from a database, simply use the normal remove() function for a collection. The following example removes the user just added; note that you have to authenticate against the admin database before you can remove the user:
> use test
switched to db test
> db.system.users.remove({username:"userA"})

Thursday, 4 April 2013

Minimize MySQL replication lag

Replication lag occurs in many MySQL deployments. This often happens due to slow insert/update/delete query that cause the slave to fall behind. However, it could also happen due to high volume of writes to the master database. One of my clients (who use MySQL 5.1) reported replication lag on a slave host, there was no stuck/long query running to cause slave to fall behind. And MySQL process-list show that it's reading/applying events from relay log (i.e. from local copy of master binary logs):

| 2 | system user | | NULL | Connect | 779612 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 4215   | Reading event from the relay log | NULL |
And the replication latency continue to grow:  "Seconds_Behind_Master: 4234"
When we look at MySQL dashboard to check on database activity graph (see below), its doing only avg 170 inserts/second. 

MySQL config for replication slave hosts can be configured to behave differently, the benefit is to help assist with replication catch-up. In this scenario we changed the value of  innodb_flush_log_at_trx_commit  to 2 from default 1. After this change, significant increase in db activity was observed, replication lag started to reduce sharply, and replication caught up with MySQL database under 5 mins, see below db activity graph: