Thursday, June 17, 2021

Mongo DB Queries ......

Quicks Hacks:

1.   db.getCollection('books').aggregate([//{$match:{date:{"$gte":new ISODate("2022-05-23")}}},
{$group:{_id: { $month: "$time" },count:{$sum:1}}}, // { _id: 10, count: 2 }
//_id: { $dateToString: {date: "$time",format: "%Y-%m"}},count:{$sum:1}}}, // { _id: "2012-10", count: 2 }
,{$sort:{count:-1}}])



Reference: 
[1]: https://www.bmc.com/blogs/mongodb-memory-usage-and-management/ 
This good one for checking the monogodb mem analyse: db.serverStatus().mem ,
[2]: https://www.mongodb.com/docs/v4.2/tutorial/analyze-query-plan/ 

@ Mongo DB Queries ......

UPDATE: db.subscriber.update(  { username:"1003" },   { $set:{ "balance":10.00 }   } )
Multiple Update:
db.subscriber.updateMany(  { username: {$regex: /sipp/}  },   { $set:{ "balance":10.00 } } )

CURSOR:
.limit( nPerPage )
.count()

FILTER:
db.subscriber.find({username:"1003"})
{duration: {$gt: 300}}

Pattern Match: {user: {$regex: /^sipp$/} }

SORTING:  1= ascending, -1 = descending
db.subscriber.find({balance:{$lt:0.04}}).sort( { balance: 1 } )
db.subscriber.find().sort( { "item.category": 1, "item.type": 1 } ) // Sort within sort 
SELECT:
s=db.subscriber;
s.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )

SELECT item, status from subscriber WHERE status = "A"
db.subscriber.find( { status: "A" }, { _id: 0 } ) // just removes _id
BETWEEN TWO DATES:
db.cdr.find({
             starttime:{
                     $gt:ISODate("2018-05-14T00:08:16.111Z"),
                     $lt:ISODate("2018-05-15T10:08:16.111Z")}
              }).pretty();
FUNCTION:
db.system.js.save( {
    _id : "myAddFunction" ,
    value : function (x, y){ return x + y; print "hello" }
 });
db.loadServerScripts();

myAddFunction(3, 5);
$where:
db.subscriber.find( { $where: function() {
   return (hex_md5(this.name) == "9b53e667f30cd329dca1ec9e6a83e994")

} } );

SetOnInsert:
db.tariff.update(
   { _id: 1 },
   { $set: { item: "apple" },  $setOnInsert: { dateModified: new Date() }},
   { upsert: true }
);

AGGREGATE (match): 
LInk2 - Just group explained
Link3 - equal commands for SQL 
Link3 - MongoDB gui expalin 

Sample 1:
db.articles.aggregate( [
  { $match: { $or: [ { score: { $gt: 70, $lt: 90 } }, { views: { $gte: 1000 } } ] } },
  { $group: { _id: null, count: { $sum: 1 } } }

] );
Output: { "_id" : null, "count" : 5 }
Eg:
db.cdr.aggregate([
   {   $match:  {             
         starttime:{
           $gt:ISODate("2018-04-28T00:00:00.000Z"),
           $lt:ISODate("2018-05-17T10:00:00.000Z")}}
   },{
   $group: {
       _id: null,
       debited_amt_sum: { $sum: "$debited_amt"      },
       duration_sum: {   $sum: "$duration"      },
       count: {$sum: NumberInt(1)}
     }
   }

]);


Aggregate length: 
aggregate().toArray().length; // to get size of result

TO Get Size of array count: (get_device_id_size_wise)
db.subscriber.aggregate([
{  $project: { username: 1,
   count: { $cond: { if: { $isArray: "$dlist" }, then: { $size: "$dlist" }, else: 0} }
     } }
   , {$sort:{count:-1}}
] )

TO RENAME DATA TYPE 
db.temp.find({name: {$exists:true}}).forEach( function(x) {
    db.temp.update({_id: x._id}, {$rename: {name: x.name.toString()}});
});

Add Update datatype long into to string Serial Number into db:
var counter =  4090
db.getCollection('temp').find({batchname:"batch1",serial:{$gt:0}}).sort({modified_date:1}).forEach(function(doc){ 
  result = db.temp.updateOne({ _id: doc._id }, { $set: { serial: counter+"" }}); 
  counter = counter + 1; 
})
db.getCollection('subscriber').find({did: {$ne : null}}).forEach(function(doc){ 
  print(doc.did)
  //result = db.subscriber.updateOne({ _id: doc._id }, { $set: { dlist: [doc.did] }}); 
})

eg: Move field to Array list of object [Link]
db.subscriber.find({pin:{$ne:""},vlist:{$ne:[]},group:"pin",username:"test1"}).forEach(
function( r ) {
    var newItems = [];  var pair = { pin: r.pin, pass: r.pass }; newItems.push( pair );
    r.vlist.forEach( function( item ){  newItems.push( item );  } );
    // printjson(typeof(r.vlist));    printjson(newItems);
    db.subscriber.update({ username: r.username }, { "$set": { pin:"", pass:"", "vlist": newItems } } );
});

Set with Condition:
{ "$set":{ "users.user": { $cond:[ { $eq:  [ "$user","$users.user"]},  "pin", "$users.user" ]   }} },

Slice: 
db.cdr.aggregate([
   {$match:  { callid :"1234561336" } },
   { $project: { rU: 1, users: { $slice: [ "$users" ,-3,2] } } }
  // {    "$set":{ "users.user": { $cond:[ { $eq:  [ "$user","$users.user"]},  "pin", "$users.user" ]   }} },
])
(or) Move to array to ROOT
{ $project: { rU: 1, users: { $slice: [ "$users" ,-2,1] }, rcvd: { $slice: [ "$users" ,-1,1] } } },


Find if the element is only Array:
 db.cdr.find({  "$where": "return Array.isArray(this.objuser)"})
{_id: 1, userobj: [ { user: "test", dur: 10} ] }.      // this matches
{_id: 1, userobj: { "test": { user: "test", dur: 10} } } // Does not matches
db.cdr.find({$where: "(this.fieldname.length == 16)"})


[ref]Modify the document using another field in mongo same collection:
var cursor = db.cdr.aggregate([   
{$match: { caller:"test"}},
    {  $addFields: { usersdata: [ "$objuser.test" ] ,cid: "$callid"}}
])
var requests = []; var count = 0; var i=0;   var countWrite = 0;
cursor.forEach(doc => {  //printjson(doc._id)
    requests.push( { 
            'updateMany': {
            'filter': { '_id': doc._id },
            'update': { '$set': { 'users': doc.usersdata } },
            'upsert' : true
        }
    });
    //printjson(requests[count])
    count ++; i++;
    if (requests.length === 100) { //Execute per 500 operations and re-init
       db.cdr.bulkWrite(requests,{ordered:true});
        countWrite += requests.length;
        requests = []; count =0;
    }
});
if(requests.length > 0) {
    countWrite += requests.length;
    db.cdr.bulkWrite(requests),{ordered: true};
}
print("totcount="+i+ "  countWrite="+countWrite);
{_id: 1, userobj: { "test": {  dur: 10} } } // This will
{_id: 1, users: [ { user: "test", dur: 10} ] }      // become like this
Note: Make async set {ordered:false} 


Alternate method:
//For mongoDB version >4.2 : merge into same doc supported from 4.4 onwards only;
db.doc.aggregate([{ $match: {'experience.0': { $exists: false } } },   
    {$project:{experience:["$experience.0"]}}, { $merge: { into: "doc", on: "_id" }
])
Lets assume your collection name is doc:
db.doc.aggregate([
    {$group: {_id:"$_id", experience:{$push:"$experience.0"}}},
    {$out: "doc"}
])
db.cdr.aggregate([{ $match: {'users': { $exists: true }  ,user: "Mujeeb_IN" }},
   {$limit:2},
   {$addFields:{adur: "$dur"}}, 
   {$out: "cdr"}
])

SEARCH TEXT IN FIELDS:
Create text indexes and use $text in find query
Ref: MongoDB Create Indexes and $text in find() query
Click to Manage Index.   To List: db.people.getIndexes()
How indexes works ->  Click here

Transactions in mongodb:https://hackernoon.com/mongodb-transactions-5654cdb8fd24

Do Aggregation efficiently:
=========================
[Ref]:https://stackoverflow.com/questions/21509045/mongodb-group-by-array-inner-elements
db.articles.aggregate([
  {$match: { class_date: { $gte: date } } },
  {$project: { _id: 0, class_artist: 1 } },
  {$unwind: "$class_artist" },
  {$group: { _id: "$class_artist", tags: { $sum: 1 } }},
  {$project: { _id: 0,class_artist: "$_id", tags: 1 } },
  {$sort: { tags: -1 } }
])
  1. Filter by date because you already set a var for the last 7 days
  2. Project only the field(s) we need { We need only one! }
  3. Unwind the array so we now have a record for every array element in every document
  4. Group on the Artist from the expanded documents
  5. Project into a document format you can use as group messed around with _id
  6. Sort the results in reverse order to see the top tagged first

Figure 3 shows a conceptual model for the Aggregation Framework pipeline. This is what's happening at each stage:

  • On the left-hand side/start of the pipeline is the original collection contents – each record (document) containing a number of shapes (keys), each with a particular color (value)
  • The $match stage filters out any documents that don't contain a red diamond
  • The $project stage adds a new “square” attribute with a value computed from the value (color) of the snowflake and triangle attributes
  • The $lookup stage (new in 3.2 - more details later) performs a left-outer join with another collection, with the star being the comparison key. This creates new documents which contain everything from the previous stage but augmented with data from any document from the second collection containing a matching colored star (i.e., the blue and yellow stars had matching “lookup” values, whereas the red star had none).
  • Finally, the $group stage groups the data by the color of the square and produces statistics (sum, average and standard deviation) for each group.

$lookup – Left Outer Equi-Joins

Figure 4 illustrates the syntax for performing the join:

  • leftCollection is the collection that the aggregation is being performed on and is the left collection in the join
  • from identifies the collection that it will be joined with – the right collection (rightCollection in this case)
  • localField specifies the key from the original/left collection – leftVal
  • foreignField specifies the key from the right collection – rightVal
  • as indicates that the data from the right collection should be embedded within the resulting documents as an array called embeddedData



eg: https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-2-of-3-worked-examples 

Saturday, January 11, 2020

Network performance tunning

Network performance tunning

Good Topic:
 Ref: https://blog.cloudflare.com/how-to-receive-a-million-packets/

HW Spec: They both have two 6-core 2GHz Xeon processors. With hyperthreading (HT) enabled that counts to 24 processors on each box. The boxes have a multi-queue 10G network card by Solarflare, with 11 receive queues configured. More on that later. 

Crash course to multi-queue NICs:
Historically, network cards had a single RX queue that was used to pass packets between hardware and kernel. This design had an obvious limitation - it was impossible to deliver more packets than a single CPU could handle.
To utilize multicore systems, NICs began to support multiple RX queues. The design is simple: each RX queue is pinned to a separate CPU, therefore, by delivering packets to all the RX queues a NIC can utilize all CPUs. But it raises a question: given a packet, how does the NIC decide to which RX queue to push it?
multiqueue nic
Round-robin balancing is not acceptable, as it might introduce reordering of packets within a single connection. An alternative is to use a hash from packet to decide the RX queue number. The hash is usually counted from a tuple (src IP, dst IP, src port, dst port). This guarantees that packets for a single flow will always end up on exactly the same RX queue, and reordering of packets within a single flow can't happen.
In our case, the hash could have been used like this:
RX_queue_number = hash('192.168.254.30', '192.168.254.1', 65400, 4321) % number_of_queues

Multi-queue hashing algorithms

The hash algorithm is configurable with ethtool. On our setup it is:
receiver$ ethtool -n eth2 rx-flow-hash udp4
UDP over IPV4 flows use these fields for computing Hash flow key:
IP SA
IP DA
This reads as: for IPv4 UDP packets, the NIC will hash (src IP, dst IP) addresses. i.e.:
RX_queue_number = hash('192.168.254.30', '192.168.254.1') % number_of_queues
This is pretty limited, as it ignores the port numbers. Many NICs allow customization of the hash. Again, using ethtool we can select the tuple (src IP, dst IP, src port, dst port) for hashing:
receiver$ ethtool -N eth2 rx-flow-hash udp4 sdfn
Cannot change RX network flow hashing options: Operation not supported
Unfortunately our NIC doesn't support it - we are constrained to (src IP, dst IP) hashing.
TIPS:(1) While we could have used the usual send syscall, it wouldn't be efficient. Context switches to the kernel have a cost and it is be better to avoid it. Fortunately a handy syscall was recently added to Linux: sendmmsg. It allows us to send many packets in one go.
TIPS:(2) Fortunately, there is a workaround recently added to Linux: the SO_REUSEPORT flag. When this flag is set on a socket descriptor, Linux will allow many processes to bind to the same port. In fact, any number of processes will be allowed to bind and the load will be spread across them.
With SO_REUSEPORT each of the processes will have a separate socket descriptor. Therefore each will own a dedicated UDP receive buffer. This avoids the contention issues previously encountered:
receiver$ taskset -c 1,2,3,4 ./udpreceiver1 0.0.0.0:4321 4 1
  1.114M pps  34.007MiB / 285.271Mb
  1.147M pps  34.990MiB / 293.518Mb
  1.126M pps  34.374MiB / 288.354Mb
This is more like it! The throughput is decent now!
More investigation will reveal further room for improvement. Even though we started four receiving threads, the load is not being spread evenly across them:
port uneven
Two threads received all the work and the other two got no packets at all. This is caused by a hashing collision, but this time it is at the SO_REUSEPORT layer.
-----------------------------------------------------------------------------------
== My Observation ==
Below shows how to check NIC is has how many RX-TX queues
[root@localhost ~]# cat /proc/interrupts | grep ens192
  57:   14481874    8881296     446331    2463896   PCI-MSI-edge      ens192-rxtx-0
  58:   11724321    5151965     722736    6279171   PCI-MSI-edge      ens192-rxtx-1
  59:       1339    4112623          0 1338260993   PCI-MSI-edge      ens192-rxtx-2
  60:  411242882  920999779          0    3695065   PCI-MSI-edge      ens192-rxtx-3
  61:          0          0          0          0   PCI-MSI-edge      ens192-event-4
[root@localhost ~]# 


(or) run below to show rx and tx queues
[root@localhost ~]# ls /sys/class/net/ens192/queues/
rx-0  rx-1  rx-2  rx-3  tx-0  tx-1  tx-2  tx-3
[root@localhost ~]# 
[ref]: You can see how many queues that you have available with: ethtool -S [interface]
If you have multiple queues enabled, they will show up. In addition you can watch traffic on the rx (tx) queues with the watch command:
watch -d -n 2 "ethool -S [interface] | grep rx | grep packets | column"
For Filtering Queues, use: tc qdisc show dev [interface]
If you have ADq or DCB queues they will show up here.
The documentation at https://www.kernel.org/doc/Documentation/networking/multiqueue.txt has a number of useful concepts and uses the tc command to manipulate the available multiqueue parameters. Without knowing your intentions, it's difficult to give a specific answer, but this information should get you pointed in the right direction.
-------------------------------------------------------------------------------------------------------------
TIPS:(3) RSS - Receive Side Scaling

In the previous article we mentioned that the NIC hashes packets in order to spread the load across many RX queues. This technique is called RSS - Receive Side Scaling. We can see it in action by observing the /proc/net/softnet_stat file with the softnet.sh script:
---------------------------------------------------------------------------------------------------------------