MongoDB Performance Tuning

Find a list of "out of the box" command line tools , used to gather MongoDB database and server data. This information can be used to identify bottlenecks for troubleshooting performance issues.This list only begins to touch upon sources of MongoDB data. MongoDB database sizes are growing ,requiring new strategies for maintaining MongoDB performance. Benchmarking query response times and other indicators serves as a basis to database performance. Predictability is the key to scalability A performance strategy should include best practises for deploying MongoDB servers. 1) mongostat - is like the vmstat tool. The default columns returned are : insert... Read more →

Transparent Huge Pages - Detected startup warnings on a database backing the system

Question: I'm getting these messages as system alerts through the MongoDB Ops Manager. 2020-02-07T21:34:21.241+0000 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'. 2020-02-07T21:34:21.241+0000 I CONTROL [initandlisten] ** We suggest setting it to 'never' What do they mean? and how can I stop these warning messages occuring? Answer: The Transparent Hugepages Linux kernel feature aims to boost performance by by "optimising" the processor's memory mapping. By using larger memory page sizes , the aim is to decrease the Translation Lookaside Buffer(TLB) lookups . By default this feature is enabled in most Linux distributions. MongoDB recommends to Disable Transparent Huge Pages Follow... Read more →

MariaDB RLIKE condition

Question: I'm having a problem using RLIKE and searching for a hyphen '-' , within a string.Also some other problems related to identifying strings with both character and numeric , while excluding numeric only or letters only strings. These are some examples of the strings: Example 1 : Digit followed by letter – 12B, 110A , 78B, 2150A, 287H, 941C Example 2: Numbers or numbers/letter with hyphen separator eg 61-63, 98-99, 1-33,2-14,1A-2A,169-173 Example 3: Numbers with forward slash separator e.g 1B/32,142/8 ,336C/2 Could you give me some RLIKE examples for each of the above scenarios Answer: The following are simple... Read more →

MongoDB Replica Set High Availability Notes

MongoDB offers a strong solution for High Availability via the Replica Set High Availability setup. DBAs work with HA and become familiar with the behaviour during setup and management, there is also a requirement to communicate with application teams some of the HA behaviour. Developers can use this information to design their applications In the examples below - we are using a 3 node cluster set. A 3 node cluster replica set has a fault tolerance supporting unavailability of 1 node while still having a Primary Node and the ability to still write to the Primary. If 2 nodes become... Read more →

mongo : command not found

Question: I've installed the mongo as part of a replicaset . The server is part of a 3 - node replicaset and I can manage through MongoDB Operations Manager. But when i get onto the command line and type "mongo" , I'm getting the -bash: mongo: command not found. How can I fix this issue - as I'd like to be able to have the command line option Answer: Assuming everything is running as expected - the most likely reason is that the relevant mongo binaries are not in the /usr/bin folder. I've had this problem a few times and... Read more →

How to list MongoDB connections - client ip and port

Question: How can I list client ip , port and connection ID of connections to MongoDB? I want to connect through the MongoDB command line and extract this information as I'm testing some HA failover scenarios. Answer: There's a few different ways to extract the MongoDB connection details - for example you can use netstat. But from the command line - this syntax will report the details required db.currentOp(true).inprog.forEach(function(d){if(d.client)print(d.client, d.connectionId)}) Example output - clientip:port clientid: 127.0.0.1:55552 4668364 10.120.xxx.xxx:60942 4668363 10.120.xxx.xxx:60924 4668357 Read More on MongoDB How to read MongoDB system log file MongoDB cheatsheet Read more →

bash: /home/myuser/.ssh/authorized_keys: No such file or directory

Question: When I type in cat ~/.ssh/authorized_keys to a RHEL 7 server I get the following message: cat: /root/.ssh/authorized_keys: No such file or directory I'm going through the process of setting up a SSH to create a connection between BART and client Potgresql servers - to backup Postgresql databases. How can I fix this issue? Answer:You need to create the ssh directory for the first time and create the authorized keys file. Create the .ssh directory. First check if actually already exists mkdir ~/.ssh Set the right permissions for the new .ssh directory. Very important step chmod 700 ~/.ssh Create... Read more →

How to load csv data with quotes and ENCLOSED BY into MariaDB table

Question : I have a csv file , with 1 million rows and would like to import into a MariaDB table.I'm importing successfully but having a problem with a row where the "," is within a double quote. Example row: GenerationX,Rufus,Johnny,"Chat hype, Power crazy" i.e instead of getting 4 columns , I'm getting 5 , because the comma within the 4th column is splitting the data Could you give me an example of how to load the data and maintain the data integrity ? Answer: When loading data into a MariaDB table the optional : ENCLOSED BY can help you.... Read more →

MariaDB /tmp file filling with MAD file

Question: I've received an alert from a RHEL server with MariaDB installed. The alert refers to a MAD file filling the /tmp drive . What is going on? and how can I fix the mysql MAD file filling the /tmp drive? Answer: This is a common problem when under certain circumstances - CREATE & ALTER statements executed on InnoDB tables create a temporary table in mysql's tmpdir. To find out the current value check my.cnf or use SHOW VARIABLES LIKE "TMPDIR During the data load phase MariaDB creates a temporary table in /tmp - which consequently fills the /tmp The... Read more →

MariaDB Cheatsheet

--connect to a MariaDB through command line mysql --user=myuser --password=mypw --port=myportnumber --host=myservername --MariaDB port number used SHOW GLOBAL VARIABLES LIKE 'PORT'; --backup mysqldump -u myun -p database_name > /mariadb/backups/mydb171117.sql Note: you'll be prompted for the password --list indexes SHOW INDEX FROM mytable; --drop index DROP idx1 ON MYTABLE --list tables show tables; --list views SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW'; --list databases show databases; --drop database drop database db1; --show columns show columns FROM MYTABLE; --get index information SHOW INDEX FROM MYTABLE; mysqlshow -k db_name tbl_name --error log file SHOW VARIABLES LIKE 'log_error' tail xxxxx.err tail -f... Read more →