Downloads

Blog

MongoDB Administration Checklist for MySQL DBAs

June 1, 2016

Author

Alexander Rubin

MongoDB

MySQL

Share this Post:

In this blog, I discuss a MongoDB administration checklist designed to help MySQL DBAs.

If you are MySQL DBA, starting MongoDB administration is not always an easy transition. Although most of the concepts and even implementation are similar, the commands are different. The following table outlines the typical MySQL concepts and DBA tasks (on the left) to corresponding MongoDB ones (on the right). If you happen to be a MongoDB DBA and want to learn MySQL administration, you can use the same table looking from right to left.

I’ve also created a webinar, MongoDB administration for MySQL DBA, that explains the above concepts. You can download the slides to use as a reference.

Don’t forget about our upcoming event Community Open House for MongoDB in New York, June 30, 2016. There will be technical presentations and sessions from key members of the MongoDB open source community. This event is free of charge and open to all.

Architecture: Basic Concepts

Replication:

Sharding:

Day-to-day operations

MySQL: SELECT

select * from zips limit 1G<br>country_code: US<br>postal_code: 34050<br>place_name: FPO<br>admin_name1:<br>admin_code1: AA<br>admin_name2: Erie<br>admin_code2: 029<br>admin_name3:<br>admin_code3:<br>latitude: 41.03750000<br>longitude: -111.67890000<br>accuracy:<br>1 row in set (0.00 sec)<br>

1	select * from zips limit 1G<br>country_code: US<br>postal_code: 34050<br>place_name: FPO<br>admin_name1:<br>admin_code1: AA<br>admin_name2: Erie<br>admin_code2: 029<br>admin_name3:<br>admin_code3:<br>latitude: 41.03750000<br>longitude: -111.67890000<br>accuracy:<br>1 row in set (0.00 sec)<br>

MongoDB: FIND

MongoDB shell version: 3.0.8<br>connecting to: zips<br>> db.zips.find().limit(1).pretty()<br>{<br>"_id" : "01001",<br>"city" : "AGAWAM",<br>"loc" : [<br>-72.622739,<br>42.070206<br>],<br>"pop" : 15338,<br>"state" : "MA"<br>}<br>

1	MongoDB shell version: 3.0.8<br>connecting to: zips<br>> db.zips.find().limit(1).pretty()<br>{<br>"_id" : "01001",<br>"city" : "AGAWAM",<br>"loc" : [<br>-72.622739,<br>42.070206<br>],<br>"pop" : 15338,<br>"state" : "MA"<br>}<br>

MySQL: Schema

CREATE TABLE users(<br>id MEDIUMINT NOT NULL AUTO_INCREMENT,<br>user_id varchar(30),<br>age Number,<br>status char(1),<br>PRIMARY KEY (id)<br>);<br>

1	CREATE TABLE users(<br>id MEDIUMINT NOT NULL AUTO_INCREMENT,<br>user_id varchar(30),<br>age Number,<br>status char(1),<br>PRIMARY KEY (id)<br>);<br>

MongoDB: Flexible Schema

db.users.insert( {<br>          user_id: "abc123",<br>          age: 55,<br>          status: "A"} <br>)<br>

1	db.users.insert( {<br> user_id: "abc123",<br> age: 55,<br> status: "A"} <br>)<br>

MySQL: Config file

/etc/my.cnf<br>

1	/etc/my.cnf<br>

MongoDB:

/etc/mongod.conf<br><br># Where and how to store data.<br>storage:<br>  dbPath: /datawt<br>  journal:<br>    enabled: true<br>  engine: wiredTiger<br>...<br><br>/usr/bin/mongod -f /etc/mongod.conf<br>

1	/etc/mongod.conf<br><br># Where and how to store data.<br>storage:<br> dbPath: /datawt<br> journal:<br> enabled: true<br> engine: wiredTiger<br>...<br><br>/usr/bin/mongod -f /etc/mongod.conf<br>

MySQL: databases

Databases<br>mysql> show databases;<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>...<br><br>mysql> use zips<br>Database changed<br><br>Tables<br>mysql> show tables;<br>+----------------+<br>| Tables_in_zips |<br>+----------------+<br>| zips           |<br>+----------------+<br>

MongoDB: Databases

Databases<br>> show dbs;<br>admin   0.000GB<br>local   0.000GB<br>osm    13.528GB<br>test    0.000GB<br>zips    0.002GB<br><br>> use zips<br>switched to db zips<br><br>Collections<br>> show collections<br>zips<br>> show tables // same<br>zips<br><br>

1	Databases<br>> show dbs;<br>admin 0.000GB<br>local 0.000GB<br>osm 13.528GB<br>test 0.000GB<br>zips 0.002GB<br><br>> use zips<br>switched to db zips<br><br>Collections<br>> show collections<br>zips<br>> show tables // same<br>zips<br><br>

MySQL: Storage Engines

MyISAM<br>InnoDB<br>TokuDB<br>MyRocks<br>

1	MyISAM<br>InnoDB<br>TokuDB<br>MyRocks<br>

MongoDB: Storage Engines

MMAPv1:  memory mapped<br>WiredTiger: transactional+compression<br>TokuMX / PerconaFT <br>RocksDB<br>

1	MMAPv1: memory mapped<br>WiredTiger: transactional+compression<br>TokuMX / PerconaFT <br>RocksDB<br>

MySQL: Processlist

mysql> show processlistG<br>           Id: 137259<br>         User: root<br>         Host: localhost<br>           db: geonames<br>      Command: Query<br>         Time: 0<br>        State: init<br>         Info: show processlist<br>    Rows_sent: 0<br>Rows_examined: 0<br>1 row in set (0.00 sec)<br><br>

1	mysql> show processlistG<br> Id: 137259<br> User: root<br> Host: localhost<br> db: geonames<br> Command: Query<br> Time: 0<br> State: init<br> Info: show processlist<br> Rows_sent: 0<br>Rows_examined: 0<br>1 row in set (0.00 sec)<br><br>

MongoDB: CurrentOp()

> db.currentOp()<br>{<br>        "inprog" : [<br>                {<br>                        "desc" : "conn28",<br>                        "threadId" : "0x19b85260",<br>                        "connectionId" : 28,<br>                        "opid" : 27394208,<br>                        "active" : true,<br>                        "secs_running" : 3,<br>                        "microsecs_running" :            <br>                              NumberLong(3210539),<br>                        "op" : "query",<br>                        "ns" : "osm.points3",<br>                        "query" : {<br>                                "name" : "Durham"<br>                        },<br>                        "planSummary" : "COLLSCAN",<br>                        "client" : "127.0.0.1:58835",<br>                        "numYields" : 24905,<br>                        "locks" : {<br>                                "Global" : "r",<br>                                "Database" : "r",<br>                                "Collection" : "r"<br>                        },<br>                        "waitingForLock" : false,<br>  ...<br>}<br><br>

> db.currentOp() { "inprog" : [ { "desc" : "conn28", "threadId" : "0x19b85260", "connectionId" : 28, "opid" : 27394208, "active" : true, "secs_running" : 3, "microsecs_running" : NumberLong(3210539), "op" : "query", "ns" : "osm.points3", "query" : { "name" : "Durham" }, "planSummary" : "COLLSCAN", "client" : "127.0.0.1:58835", "numYields" : 24905, "locks" : { "Global" : "r", "Database" : "r", "Collection" : "r" }, "waitingForLock" : false, ... }

MySQL: Grants

mysql> grant all on *.* <br>to user@localhost <br>identified by 'pass'; <br>

1	mysql> grant all on . <br>to user@localhost <br>identified by 'pass'; <br>

MongoDB: createUser

> use products<br>db.createUser( {<br>          user: "accountUser",<br>          pwd: "password",<br>          roles: [ "readWrite", "dbAdmin" ]<br>})<br>

1	> use products<br>db.createUser( {<br> user: "accountUser",<br> pwd: "password",<br> roles: [ "readWrite", "dbAdmin" ]<br>})<br>

MySQL: Index

mysql> show keys from zipsG<br>**** 1. row ****<br>        Table: zips<br>   Non_unique: 0<br>     Key_name: PRIMARY<br> Seq_in_index: 1<br>  Column_name: id<br>    Collation: A<br>  Cardinality: 0<br>     Sub_part: NULL<br>       Packed: NULL<br>         Null: <br>   Index_type: BTREE<br>

1	mysql> show keys from zipsG<br>** 1. row **<br> Table: zips<br> Non_unique: 0<br> Key_name: PRIMARY<br> Seq_in_index: 1<br> Column_name: id<br> Collation: A<br> Cardinality: 0<br> Sub_part: NULL<br> Packed: NULL<br> Null: <br> Index_type: BTREE<br>

MongoDB: Index

> db.zips.getIndexes()<br>[<br>        {<br>                "v" : 1,<br>                "key" : {<br>                        "_id" : 1<br>                },<br>                "name" : "_id_",<br>                "ns" : "zips.zips"<br>        }<br>]<br>

1	> db.zips.getIndexes()<br>[<br> {<br> "v" : 1,<br> "key" : {<br> "_id" : 1<br> },<br> "name" : "_id_",<br> "ns" : "zips.zips"<br> }<br>]<br>

MySQL: Add Index

mysql> alter table zips <br>       add key (postal_code);<br>Query OK, 0 rows affected (0.10 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>

1	mysql> alter table zips <br> add key (postal_code);<br>Query OK, 0 rows affected (0.10 sec)<br>Records: 0 Duplicates: 0 Warnings: 0<br><br>

MongoDB: Create Index

> db.zips.createIndex({ state : 1 } )<br>{<br>        "createdCollectionAutomatically" : false,<br>        "numIndexesBefore" : 1,<br>        "numIndexesAfter" : 2,<br>        "ok" : 1<br>}<br><br>// Index can be sorted:<br><br>> db.zips.createIndex({ state : -1 } )<br>{<br>        "createdCollectionAutomatically" : false,<br>        "numIndexesBefore" : 2,<br>        "numIndexesAfter" : 3,<br>        "ok" : 1<br>}<br><br>

> db.zips.createIndex({ state : 1 } ) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } // Index can be sorted: > db.zips.createIndex({ state : -1 } ) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 }

MySQL: Explain

mysql> explain select * from zips <br>where place_name = 'Durham'G<br>**** 1. row ****<br>           id: 1<br>  select_type: SIMPLE<br>        table: zips<br>         type: ref<br>possible_keys: place_name<br>          key: place_name<br>      key_len: 183<br>          ref: const<br>         rows: 25<br>        Extra: Using index condition<br>1 row in set (0.00 sec)<br>

mysql> explain select * from zips where place_name = 'Durham'G **** 1. row **** id: 1 select_type: SIMPLE table: zips type: ref possible_keys: place_name key: place_name key_len: 183 ref: const rows: 25 Extra: Using index condition 1 row in set (0.00 sec)

MongoDB: Explain

> db.zips.find({"city": "DURHAM"}).explain()<br>{<br>        "queryPlanner" : {<br>                "plannerVersion" : 1,<br>                "namespace" : "zips.zips",<br>                "indexFilterSet" : false,<br>                "parsedQuery" : {<br>                        "city" : {<br>                             "$eq" : "DURHAM"<br>                        }<br>                },<br>                "winningPlan" : {<br>                        "stage" : "COLLSCAN",<br>                        "filter" : {<br>                            "city" : {<br>                                "$eq" : "DURHAM"<br>                                }<br>                        },<br>                        "direction" : "forward"<br>                },<br>                "rejectedPlans" : [ ]<br>        },<br>        "serverInfo" : { ... },<br>        "ok" : 1<br>}<br>

> db.zips.find({"city": "DURHAM"}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "zips.zips", "indexFilterSet" : false, "parsedQuery" : { "city" : { "$eq" : "DURHAM" } }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "city" : { "$eq" : "DURHAM" } }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { ... }, "ok" : 1 }

MySQL: Alter Table

mysql> alter table wikistats_innodb_n <br>       add url_md5 varbinary(16);<br><br>Query OK, 0 rows affected (37 min 10.03 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql> update wikistats_innodb_n <br>set url_md5 = unhex(md5(lower(url)));<br><br>Query OK, <br>85923501 rows affected (42 min 29.05 sec)<br>Rows matched: 85923511  Changed: 85923501 ...<br>

mysql> alter table wikistats_innodb_n add url_md5 varbinary(16); Query OK, 0 rows affected (37 min 10.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update wikistats_innodb_n set url_md5 = unhex(md5(lower(url))); Query OK, 85923501 rows affected (42 min 29.05 sec) Rows matched: 85923511 Changed: 85923501 ...

MongoDB: Flexible schema

No Alter statement.<br>Just insert the new document version ...<br>Different documents can have <br>different schema versions<br>

1	No Alter statement.<br>Just insert the new document version ...<br>Different documents can have <br>different schema versions<br>

MySQL: Slow Query Log

mysql> set global long_query_time = 0.1;<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql> set global slow_query_log = 1;<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql> show global variables <br>       like 'slow_query_log_file'G<br>**** 1. row ****<br>Variable_name: slow_query_log_file<br>        Value: /var/lib/mysql/slow.log<br>1 row in set (0.00 sec)<br>

mysql> set global long_query_time = 0.1; Query OK, 0 rows affected (0.02 sec) mysql> set global slow_query_log = 1; Query OK, 0 rows affected (0.02 sec) mysql> show global variables like 'slow_query_log_file'G **** 1. row **** Variable_name: slow_query_log_file Value: /var/lib/mysql/slow.log 1 row in set (0.00 sec)

MongoDB: Profiling

// db.setProfilingLevel(level, slowms)<br>// Level: 0 = no profiling, <br>//        1 = only slow ops<br>//        2 = all ops<br>// Slowms same as long_query_time<br>//               in milliseconds<br>> db.setProfilingLevel(2, 100);<br>{ "was" : 0, "slowms" : 100, "ok" : 1 }<br><br>> db.system.profile.find( <br>  { millis : <br>          { $gt : 100 } <br>  } ).pretty()<br>{<br>        "op" : "query",<br>        "ns" : "zips.zips",<br>        "query" : {<br>           "city" : "DURHAM"<br>        },<br>        "ntoreturn" : 0,<br>..<br><br>

// db.setProfilingLevel(level, slowms) // Level: 0 = no profiling, // 1 = only slow ops // 2 = all ops // Slowms same as long_query_time // in milliseconds > db.setProfilingLevel(2, 100); { "was" : 0, "slowms" : 100, "ok" : 1 } > db.system.profile.find( { millis : { $gt : 100 } } ).pretty() { "op" : "query", "ns" : "zips.zips", "query" : { "city" : "DURHAM" }, "ntoreturn" : 0, ..

MySQL: Percona Toolkit

$ pt-query-digest --limit 100 slow.log  <br>                  > slow.log.report.txt<br>

1	$ pt-query-digest --limit 100 slow.log <br> > slow.log.report.txt<br>

MongoDB: Mtools

$ mlogfilter mongo.log-20150915 <br>   --from 'Sep 14 06:00:00' --to 'Sep 14 23:59:59' <br>  | mlogvis  --line-max 100000 --out 'mongo.html'<br>

1	$ mlogfilter mongo.log-20150915 <br> --from 'Sep 14 06:00:00' --to 'Sep 14 23:59:59' <br> \| mlogvis --line-max 100000 --out 'mongo.html'<br>

MySQL 5.7: GIS

SELECT osm_id, name,<br>round(st_distance_sphere(shape, <br>      st_geomfromtext(<br>        'POINT (-78.9064543 35.9975194)', 1) <br>       ), 2) as dist, <br>st_astext(shape)<br>FROM points_new<br>WHERE <br>st_within(shape, <br>   create_envelope(@lat, @lon, 10))<br>and (other_tags <br>   like '%"amenity"=>"cafe"%' <br>or other_tags <br>   like '%"amenity"=>"restaurant"%')<br>and name is not null<br>ORDER BY dist asc LIMIT 10;<br>

SELECT osm_id, name, round(st_distance_sphere(shape, st_geomfromtext( 'POINT (-78.9064543 35.9975194)', 1) ), 2) as dist, st_astext(shape) FROM points_new WHERE st_within(shape, create_envelope(@lat, @lon, 10)) and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') and name is not null ORDER BY dist asc LIMIT 10;

MongoDB 3.2: GIS

db.runCommand( { <br>     geoNear: "points",  <br>     near: {<br>         type: "Point" ,  <br>         coordinates: <br>            [ -78.9064543, 35.9975194 ]<br>     },      <br>     spherical: true, <br>     query: { <br>        name: { <br>             $exists: true, $ne:null},  <br>             "other_tags": { $in: [     <br>                /.*amenity=>restaurant.*/,  <br>                /.*amenity=>cafe.*/ ] <br>            } <br>      }, <br>     "limit": 5, <br>     "maxDistance": 10000 <br>} )<br>

db.runCommand( { geoNear: "points", near: { type: "Point" , coordinates: [ -78.9064543, 35.9975194 ] }, spherical: true, query: { name: { $exists: true, $ne:null}, "other_tags": { $in: [ /.*amenity=>restaurant.*/, /.*amenity=>cafe.*/ ] } }, "limit": 5, "maxDistance": 10000 } )