tag:blogger.com,1999:blog-15009691319312402222024-03-05T23:51:46.882-08:00Scale Hacking: Cloud Computing, Software and System PerformanceNews, Personal view and perspective of the software performance field, cloud computing and industry based on my experienceMoshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comBlogger221125tag:blogger.com,1999:blog-1500969131931240222.post-39262385647726280122022-12-28T00:03:00.002-08:002022-12-28T00:03:10.435-08:00How to Create an AWS MySQL RDS Read Replica<p><span style="background-color: #f7f7f8; color: #374151; font-family: arial; white-space: pre-wrap;"><b>Why?</b></span></p><p><span style="background-color: #f7f7f8; color: #374151; font-family: arial; white-space: pre-wrap;">Read Replica provides you extra read-only power, so you can direct your heavy load SELECT (OLAP) queries to the read replica, while the main RDS cluster can serve your critical OLTP transactions.</span></p><p><span style="background-color: #f7f7f8; color: #374151; font-family: arial; white-space: pre-wrap;"><b>How?</b></span></p><p><span style="background-color: #f7f7f8; color: #374151; font-family: arial; white-space: pre-wrap;">To create a MySQL read replica in Amazon Web Services (AWS) using Amazon Relational Database Service (RDS), you can follow these steps:</span></p><ol style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; counter-reset: item 0; display: flex; flex-direction: column; list-style-image: initial; list-style-position: initial; margin: 1.25em 0px; padding: 0px 0px 0px 1rem; white-space: pre-wrap;"><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">Sign in to the AWS Management Console and navigate to the RDS dashboard.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">In the navigation pane, click on the "Instances" menu item.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">Select the instance that you want to use as the source for the read replica.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">From the "Actions" dropdown menu, select "Create read replica".</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">In the "Create Read Replica" dialogue box that appears, you can specify the following options:</span></li></ol><ul style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; display: flex; flex-direction: column; list-style-image: initial; list-style-position: initial; margin: 1.25em 0px; padding: 0px 0px 0px 1rem; white-space: pre-wrap;"><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; display: block; margin: 0px; padding-left: 0.375em; position: relative;"><span style="font-family: arial;">The instance identifier for the read replica. This should be unique within your AWS account.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; display: block; margin: 0px; padding-left: 0.375em; position: relative;"><span style="font-family: arial;">The instance class for the read replica. You can choose from a range of instance sizes based on your performance and capacity needs.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; display: block; margin: 0px; padding-left: 0.375em; position: relative;"><span style="font-family: arial;">The storage type and size for the read replica. You can choose between magnetic and SSD storage, and specify the size in gigabytes.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; display: block; margin: 0px; padding-left: 0.375em; position: relative;"><span style="font-family: arial;">The availability zone for the read replica. This determines the geographical region where the read replica will be located.</span></li><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; display: block; margin: 0px; padding-left: 0.375em; position: relative;"><span style="font-family: arial;">The security group and VPC for the read replica. You can use the same security group and VPC as the source instance, or choose different ones.</span></li></ul><ol start="6" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; counter-reset: item 0; display: flex; flex-direction: column; list-style-image: initial; list-style-position: initial; margin: 1.25em 0px; padding: 0px 0px 0px 1rem; white-space: pre-wrap;"><li style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 0px; padding-left: 0.375em;"><span style="font-family: arial;">Click "Create read replica" to create the read replica. It will be created as a new RDS instance, and will be automatically replicating data from the source instance.</span></li></ol><p style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; margin: 1.25em 0px; white-space: pre-wrap;"><span style="font-family: arial;">You can monitor the progress of the read replica creation process by checking the "Status" column on the RDS dashboard. When the status of the read replica changes to "available", it is ready to use.</span></p><p style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; margin: 1.25em 0px 0px; white-space: pre-wrap;"><span style="font-family: arial;">Keep in mind that read replicas are designed to be used for read-only workloads, and you cannot write to them directly. Any writes to the source instance will be replicated to the read replica, but you cannot write directly to the read replica.</span></p><p style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: #f7f7f8; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; margin: 1.25em 0px 0px; white-space: pre-wrap;"><span style="font-family: arial;">Keep Performing,
<a href="https://www.linkedin.com/in/moshekaplan/" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-55593179582740900882022-12-03T12:40:00.001-08:002022-12-03T12:40:07.210-08:00Monitoring Dashboard Principles for AWS Serverless System<p><span style="font-family: arial;">A monitoring dashboard for a serverless system would typically include information about the health and performance of the system, such as the number of requests being handled, the average response time, and any errors or issues that are occurring. The specific metrics and information included on a monitoring dashboard can vary depending on the specific system and the needs of the users. Some common metrics that might be included on a monitoring dashboard for a serverless system include:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Request count: The number of requests being handled by the system.</span></li><li><span style="font-family: arial;">Error rate: The percentage of requests that are resulting in errors.</span></li><li><span style="font-family: arial;">Latency: The average time it takes for a request to be processed.</span></li><li><span style="font-family: arial;">CPU and memory usage: The amount of CPU and memory resources being used by the system.</span></li><li><span style="font-family: arial;">Throughput: The amount of data being processed by the system.</span></li><li><span style="font-family: arial;">Scalability: Information about how the system is scaling up or down in response to changes in workload.</span></li></ol><p></p><p><span style="font-family: arial;">These are just some examples of the types of information that might be included on a monitoring dashboard for a serverless system. The exact metrics and information displayed on the dashboard will depend on the specific needs of the system and the users. </span></p><p><span style="font-family: arial;">Keep Performing,</span></p><p><span style="font-family: arial;"><a href="https://linkedin.com/in/moshekaplan" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-63254922858132172872022-11-06T02:39:00.001-08:002022-11-06T02:39:15.306-08:00Why you get 503 with your AWS Lambda@Edge?<p><span style="font-family: arial;"><b> The short answer:</b> Lambda@Edge is limited to a 5 seconds timeout</span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhUfvLumloPqFCyVdXw6Dqo7GAxpvwhz9WOIDyKrU-Me8REE_IPQ63nkMUYtS1iWhwoAnHTybPjFHNcWaMSEOajIdMpww25Ab8xd4bQx0W8Xg2Jdul2xK5n4Wf4jSTgIwV7ALFagtmX6bkVkhzVjDAhBuj0XyvQK8Xasb0IyZ1BwddCiKZbAwaX93IK" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: arial;"><img alt="" data-original-height="94" data-original-width="817" height="37" src="https://blogger.googleusercontent.com/img/a/AVvXsEhUfvLumloPqFCyVdXw6Dqo7GAxpvwhz9WOIDyKrU-Me8REE_IPQ63nkMUYtS1iWhwoAnHTybPjFHNcWaMSEOajIdMpww25Ab8xd4bQx0W8Xg2Jdul2xK5n4Wf4jSTgIwV7ALFagtmX6bkVkhzVjDAhBuj0XyvQK8Xasb0IyZ1BwddCiKZbAwaX93IK" width="320" /></span></a></div><p></p><p><span style="font-family: arial;"><b>The reason: </b>If you configured your lambda to a larger timeout (let's say 58 sec), when you deploy it to Lambda@edge, AWS would automatically adjust the number to a lower number (for us it was 1 second).</span></p><p><span style="font-family: arial;"><b>The solution is simple: </b>You should not configure your Lambda functions that will be deployed to Lambda@Edge for more than 5 seconds.</span></p><p><span style="font-family: arial;"><b>How Trace it?</b></span></p><p><span style="font-family: arial;">1. You get a 503 when you try to login a Cognito secured site for example with "The Lambda function associated with the CloudFront distribution is invalid or doesn't have the required permissions"</span></p><p><span style="font-family: arial;"></span></p><div class="separator" style="clear: both; text-align: center;"><span style="font-family: arial;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiPmsXi4PW5-atPClgaz-If3v9uGQcMmRVO8TUnlMAtA8sUlHgPM14ksrALMQAXESCukyHvqIApoU71Uf2n0S0juNnDS4s2Y7FZ5uFmGp4lBG2cwP3aUFNvW5_T0xgNLg3k41iMqwCjFKGcHNfQKYQHylRFEGFNBrC6hBVgScACE3p6z-cfWjlBH4I2" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="368" data-original-width="1015" height="116" src="https://blogger.googleusercontent.com/img/a/AVvXsEiPmsXi4PW5-atPClgaz-If3v9uGQcMmRVO8TUnlMAtA8sUlHgPM14ksrALMQAXESCukyHvqIApoU71Uf2n0S0juNnDS4s2Y7FZ5uFmGp4lBG2cwP3aUFNvW5_T0xgNLg3k41iMqwCjFKGcHNfQKYQHylRFEGFNBrC6hBVgScACE3p6z-cfWjlBH4I2" width="320" /></a></span></div><span style="font-family: arial;"><br />2. When you go to the CloudWatch to examine the logs, you find out, that some function end after 1000 ms.</span><p></p><p><span style="font-family: arial;">Keep Performing,<br /></span><a href="https://www.linkedin.com/in/moshekaplan/" style="font-family: arial;" target="_blank">Moshe Kaplan</a></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><br /></span></p><p><br /></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-68082825645585898342022-10-17T12:21:00.003-07:002022-11-02T01:20:20.587-07:00How to Enable AWS OpenSearch Slow Logs?<p><span style="font-family: arial;">The first step to solving a data performance issue is tracing the slow queries.</span></p><p><span style="font-family: arial;">OpenSearch support two types of queries:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Indexing: write queries</span></li><li><span style="font-family: arial;">Searching: read queries</span></li></ol><div><span style="font-family: arial;">You will be able to find out what is your performance bottleneck in the AWS cluster health dashboard. If you will find out that indexing is your bottleneck, you may enable the indexing slow log according to the <a href="https://opensearch.org/docs/latest/opensearch/logs/#slow-logs" target="_blank">AWS docs</a>.</span></div><div><span style="font-family: arial;">If you find out that your bottleneck is the search queries, you may use the following:</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Get The list of index:</span></div><div><span style="font-family: courier; font-size: x-small;">curl -X PUT "https://<span style="color: #38761d;">YOUR-CLUSTER-NAME</span>.eu-west-1.es.amazonaws.com/_cat/indices?pretty"</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Enable the slow log on the relevant indexes:</span></div><div><span><div style="font-family: arial;"><br /></div><div><span style="font-family: courier; font-size: x-small;">curl -X PUT "https://<span style="color: #38761d;">YOUR-CLUSTER-NAME</span>.eu-west-1.es.amazonaws.com/<span style="color: #38761d;">YOUR-INDEX-NAME</span>/_settings?pretty" -H 'Content-Type: application/json' -d'</span></div><div><span style="font-family: courier; font-size: x-small;">{</span></div><div><span style="font-family: courier; font-size: x-small;"> "search": {</span></div><div><span style="font-family: courier; font-size: x-small;"> "slowlog": {</span></div><div><span style="font-family: courier; font-size: x-small;"> "threshold": {</span></div><div><span style="font-family: courier; font-size: x-small;"> "query": {</span></div><div><span style="font-family: courier; font-size: x-small;"> "warn": "15s",</span></div><div><span style="font-family: courier; font-size: x-small;"> "trace": "750ms",</span></div><div><span style="font-family: courier; font-size: x-small;"> "debug": "3s",</span></div><div><span style="font-family: courier; font-size: x-small;"> "info": "10s"</span></div><div><span style="font-family: courier; font-size: x-small;"> }</span></div><div><span style="font-family: courier; font-size: x-small;"> },</span></div><div><span style="font-family: courier; font-size: x-small;"> "level": "TRACE"</span></div><div><span style="font-family: courier; font-size: x-small;"> }</span></div><div><span style="font-family: courier; font-size: x-small;"> }</span></div><div><span style="font-family: courier; font-size: x-small;">}'</span></div></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Where query defines the thresholds, and the level the minimal threshold that will be logged to the file.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">After enabling the slow query log, you may be able to find the link to the log itself in the AWS cluster logs tab.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Bottom Line</b></span></div><div><span style="font-family: arial;">After analyzing the slow log, you may be able to get some light on your performance spots.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Keep Performing,</span></div><div><span style="font-family: arial;"><a href="https://www.linkedin.com/in/moshekaplan/">Moshe Kaplan</a></span></div><p></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-56612348407306908552022-09-06T05:22:00.004-07:002022-09-06T05:23:23.821-07:00The Data Analyst Guide: How to Connect Your MongoDB Database<p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Install MongoDB Compass</span></li><li><span style="font-family: arial;">Connect to your MongoDB Cluster</span></li><li><span style="font-family: arial;">Run an aggregation query</span></li></ol><div><span style="font-family: arial;">You can find the detailed process in our video</span></div><div class="separator" style="clear: both; text-align: center;"><span style="font-family: arial;"><iframe allowfullscreen="" class="BLOG_video_class" height="292" src="https://www.youtube.com/embed/tDgNbfP7DLY" width="458" youtube-src-id="tDgNbfP7DLY"></iframe></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Keep Performing,</span></div><div><a href="https://www.linkedin.com/in/moshekaplan/" target="_blank"><span style="font-family: arial;">Moshe Kaplan</span></a></div><p></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-77718677649148118202022-08-31T02:40:00.007-07:002022-08-31T02:42:23.822-07:00How did We Manage to Integrate Two Large Data based Services? or How to Query Large Number of Records from the MySQL database?<p><span style="font-family: arial;">A common case in enterprise systems and micro-services-based architecture is the need to sync data between 2 systems or services.</span></p><p><span style="font-family: arial;">For example, a billing system in a telco may need updated status regarding the customers' plan.</span></p><h4 style="text-align: left;"><span style="font-family: arial;">Common Solutions:</span></h4><p><span style="font-family: arial;"><b>1. A Low latency Service</b></span></p><p><span style="font-family: arial;">A common approach might be having a scaleable, low latency service that will be able to respond to status queries within 1ms. This can be achieved using golang based service and a Redis backend for example.</span></p><p><span style="font-family: arial;"><b>2. A PubSub architecture</b></span></p><p><span style="font-family: arial;">In this approach, we will update the billing system w/ recent updates and will need to assume the billing database is updated with the latest updates. This is a very efficient method, yet it is prone to discrepancies and data drifting.</span></p><p><span style="font-family: arial;"><b>3. Batch Queries</b></span></p><p><span style="font-family: arial;">The last method that we'll discuss in this post is having a batch query reg, "hot" subscribers, and getting back the results to the billing system. It might be less sophisticated than the other approaches, yet it is simple and less prone to load data drifting.</span></p><h4 style="text-align: left;"><span style="font-family: arial;">The Integration Patttern</span></h4><p><span style="font-family: arial;">The billing system would like to retrieve the status of up to 30K subscribers, in order to enrich the CDR (call data records) files.</span></p><p><span style="font-family: arial;">This approach may be chosen, in order to minimize the number of calls between the two systems and to avoid data drifting.</span></p><p><span style="font-family: arial;">As the number of subscribers is too large, to include in a single query, a naive solution, might be to query all the subscribers from the database and filter them at the application level.</span></p><p><span style="font-family: arial;">A better solution might be using the <a href="https://www.mysqltutorial.org/mysql-temporary-table/" target="_blank">TEMPORARY TABLE mechanism</a> to extract only the needed subscribers from the table</span></p><h4 style="text-align: left;"><span style="font-family: arial;">Step by Step Solution</span></h4><p><span style="font-family: arial;">1. Insert the subscriber ids we get from the billing to a temporary table</span></p><p><span style="font-family: courier; font-size: x-small;">CREATE TEMPORARY TABLE temp_billing_sps_subs_id (</span></p><p><span style="font-family: courier; font-size: x-small;"> subscriber_id bigint</span></p><p><span style="font-family: courier; font-size: x-small;">);</span></p><p><span style="font-family: arial;">2. Add a JOIN to the current query w/ the temp table</span></p><p><span style="font-family: arial;">3. Get back up to 30K records instead of 2M.</span></p><p><span style="font-family: arial;">Few things to think about:</span></p><p><span style="font-family: arial;">1. We may need to add a new index to match the updated queries</span></p><p><span style="font-family: arial;">2. We may need to add permissions in production to create these temporary tables.</span></p><h4 style="text-align: left;"><span style="font-family: arial;">Bottom Line</span></h4><p><span style="font-family: arial;">It may improve query response time (fewer data to fetch from disk and return to app server) and app query time (less time to scan the 2M records and filter them).</span></p><div><span style="font-family: arial;">Keep Performing,</span></div><div><span style="font-family: arial;"><a href="https://www.linkedin.com/in/moshekaplan/" target="_blank">Moshe Kaplan</a></span></div><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-68535391699104764042022-05-09T05:52:00.004-07:002022-05-09T05:52:57.855-07:00Duplicate Your MongoDB ATLAS ReplicaSet/Cluster<p><span style="font-family: arial;">Well, does a MongoDB ReplicaSet/Cluster duplication for backup or test purposes sound like an obvious task?</span></p><p><span style="font-family: arial;">Think again :-)</span></p><p><span style="font-family: arial;">There is no magic button or anything like that, but there are a few tools that can be used to complete this task:</span></p><p><b><span style="font-family: arial;">Backup and Restore</span></b></p><p><span style="font-family: arial;">Use mongodump and mongorestore to backup your current instance and restore to a new instance. To save bandwidth and minimize time, make sure you use an instance in the cloud vendor region matching your MongoDB ATLAS.</span></p><p><span style="font-family: arial;"><b>Mirroring</b></span></p><p><span style="font-family: arial;">Use the <a href="https://www.mongodb.com/docs/atlas/import/mongomirror/">mongomirror</a> to migrate the data to the new cluster. Please notice the mongomirror do not copy config and permissions.</span></p><p><span style="font-family: arial;"><b>Bottom Line</b></span></p><p><span style="font-family: arial;">I believe the backup/restore method might be best in most cases, but you can have it your way</span></p><p><span style="font-family: arial;">Keep Performing </span></p><p><span style="font-family: arial;"><a href="https://www.linkedin.com/in/moshekaplan/" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-20177580068730013342022-01-12T06:52:00.002-08:002022-01-12T06:52:24.800-08:00Creating Webhooks on a MySQL Table?<p><span style="font-family: arial;">If you need to create a webhook on a MySQL table, having a field that marks the last modification date of any record, can be a great start</span></p><p><span style="font-family: courier;">ALTER TABLE
my_table<br /></span><span style="font-family: courier;">ADD COLUMN modified DATETIME ON UPDATE CURRENT_TIMESTAMP,<br /></span><span style="font-family: courier;">ADD INDEX IX_modified (modified);</span></p><p><span style="font-family: arial;">Keep Performing,<br /></span><span style="font-family: arial;"><a href="https://www.linkedin.com/in/moshekaplan/" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-57005182217106066282022-01-01T15:30:00.002-08:002022-01-01T15:30:24.036-08:00How to Prepare Your MySQL Slave to be Promoted to Master?<p><span style="font-family: arial;"> Just a few steps, but make sure you do them on peacetime, as during downtime every minute counts.</span></p><p><span style="font-family: arial;">1. <a href="https://snapshooter.com/learn/mysql/enable-and-use-binary-log-mysql" target="_blank">Enable bin log</a>, you will need them when you will want to add new slaves to the promoted master.</span></p><p><span style="font-family: courier;">log_bin = /var/log/mysql/mysql-bin.log<br /></span><span style="font-family: courier;">expire_logs_days = 10<br /></span><span style="font-family: courier;">max_binlog_size = 100M<br /></span><span style="font-family: courier;">binlog_format = mixed</span></p><p><span style="font-family: arial;">2. <a href="https://serverfault.com/questions/36660/how-can-i-copy-mysql-users-table-from-one-server-to-another" target="_blank">Copy Master nodes users and permissions from the Master to the Slave</a></span></p><p><span style="font-family: courier;">MASTER> mysqldump -uUSER -p -t mysql user > /tmp/dump.sql<br />SLAVE> mysql -uUSER -p -D mysql < /tmp/dump.sql<br />SLAVE> mysql -uUSER -p -e "FLUSH PRIVILEGES;"</span></p><p><span style="font-family: arial;">P.S Consider using Galera as a Multi-Master solution to avoid the need to promote a node manually. However, in some cases, such as a delayed slave or hidden one, that might be the right solution.</span></p><p><span style="font-family: arial;">Keep Performing,<br /><a href="https://www.linkedin.com/in/moshekaplan/" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-63151987245421822482021-12-19T14:03:00.004-08:002021-12-21T16:23:15.592-08:00MongoDB DataOps: Fork, Lookup , mongostat, Custom Replication and Taking Care of Failed System<h4 style="text-align: left;"><a href="https://docs.mongodb.com/manual/tutorial/manage-mongodb-processes/" target="_blank"><b><span style="font-family: arial;">Why fork is used?</span></b></a></h4><p><span style="font-family: arial;">fork is the right way to make sure your MongoDB runs as a daemon. In this case, fork should be defined as true in the configuration file. If you want to run it as an interactive process. just set it to false.</span></p><h4 style="text-align: left;"><span style="font-family: arial;"><a href="https://docs.mongodb.com/datalake/reference/pipeline/lookup-stage/" target="_blank">How to Perform Lookup?</a></span></h4><p><span style="font-family: courier;">db.classes.aggregate( [<br />{ $lookup: {<br /> from: "members",<br /> localField: "enrollmentlist",<br /> foreignField: "name",<br /> as: "enrollee_info"<br />}}<br />])</span></p><h4 style="text-align: left;"><a href="https://docs.mongodb.com/database-tools/mongostat/#std-program-mongostat" target="_blank"><span style="font-family: arial;">mongostat and mongotop</span></a></h4><p><span style="font-family: arial;">These two great tools are provided as part of the MongoDB installation and can be used to detect the queries types and collections that most heavily affect the MongoDB performance.</span></p><h4 style="text-align: left;"><span style="font-family: arial;">Simple Backup and Restore</span></h4><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: arial;">mongodump --out /tmp/dump/<br />mongorestore mongodb://<a data-saferedirecturl="https://www.google.com/url?q=http://127.0.0.1&source=gmail&ust=1640169467063000&usg=AOvVaw2UmTIQMQHM5nQIC97R05qb" href="http://127.0.0.1/" style="color: #1155cc;" target="_blank">127.0.0.1</a> /tmp/dump/</span></span></div><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: arial;"><br /></span></span></div><h4 style="background-color: white; color: #222222; text-align: left;"><span style="border-collapse: collapse;"><span style="font-family: arial;">How to Reset Your MongoDB Instance?</span></span></h4><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: arial;">Stop the service, remove all content from the /var/lib/mongodb directory, and start the service again:</span></span></div><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: courier;">sudo service mongod stop</span></span></div><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: courier;">sudo rm -rf /var/lib/mongodb</span></span></div><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: courier;">sudo service mongod start</span></span></div><div style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: arial;"><br /></span></span></div><h4 style="background-color: white; color: #222222; text-align: left;"><span style="font-family: arial;"><a href="https://docs.mongodb.com/manual/tutorial/configure-replica-set-secondary-sync-target/" target="_blank">Setting Custom Replication Source</a></span></h4><div dir="ltr" style="background-color: white; color: #222222;"><span style="border-collapse: collapse;"><span style="font-family: arial;">MongoDB replication automatically balances between replication from the Primary (most updated values) and secondaries w/ a low networking latency to minimize WAN network usage and optimize network resources.</span></span></div><div dir="ltr" style="background-color: white; color: #222222;"><span style="font-family: arial;">However, your can control this behavior and configure specific source rs.syncFrom() command. Please notice that this command result is temporary and will reset to default once mongod is restarted, connection closed between nodes or 30 sec timeout is reached.</span></div><div dir="ltr" style="background-color: white; color: #222222; font-size: small;"><span style="font-family: arial;"><br /></span></div><h4 style="background-color: white; color: #222222; text-align: left;"><span style="font-family: arial;">Initiate Your Replicaset:</span></h4><div dir="ltr" style="background-color: white; color: #222222;"><span style="font-family: arial;">1. Set the replicaset name in the mongod.conf file</span></div><div dir="ltr" style="background-color: white; color: #222222;"><span style="font-family: arial;">2. Change the bindIP to 0.0.0.0 in the config file</span></div><div dir="ltr" style="background-color: white; color: #222222;"><span style="font-family: arial;">3. Run on the first node the rs.initiate command</span></div><div dir="ltr" style="background-color: white;"><span><div dir="ltr"><span style="color: #222222; font-family: courier;">rs.initiate(</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> {</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> _id: "rs",</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> version: 1,</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> members: [</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> { _id: 0, host : "172.30.0.158:27017" },</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> { _id: 1, host : "172.30.0.151:27017" },</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> { _id: 2, host : "172.30.0.219:27017" }</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> ]</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;"> }</span></div><div dir="ltr"><span style="color: #222222; font-family: courier;">)</span></div><h4 style="font-family: arial; text-align: left;"><span style="color: #222222;">Search for Delta in the Oplog</span></h4><div dir="ltr"><span style="color: #222222; font-family: courier;">db.oplog.rs.find({"ts":{$gt : Timestamp(1640074543, 1)}})</span></div></span></div><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-80426648476646146492021-12-19T13:53:00.000-08:002021-12-19T13:53:41.632-08:00Upgrade from MySQL 5.6 to 8.0<p>Verify your system configuration to select the right package and method for installation</p><p><span style="font-family: courier;">uname -r<br /></span><span style="font-family: courier;">> 2.6.32-696.3.2.el6.x86_64 ==> 64bit</span></p><p><span style="font-family: courier;">uname -r<br /></span><span style="font-family: courier;">> Description: CentOS release 6.9 (Final) => CentOS 6.9</span></p><p><b>Upgrade to the latest 5.6</b></p><p><a href="https://downloads.mysql.com/archives/community/?version=5.6.51" target="_blank">Look for the latest MySQL 5.6 minor version</a> and get the package:</p><p><span style="font-family: courier;">wget https://downloads.mysql.com/archives/get/p/23/file/MySQL-5.6.51-1.el6.x86_64.rpm-bundle.tar</span></p><div>Remove current package</div><div><span style="font-family: courier;">sudo service mysqld stop</span></div><div><span style="font-family: courier;">sudo yum -y remove mysql-community-common-5.6.36-2.el6.x86_64</span></div><div><br /></div><div><span style="font-family: courier;">tar -xvf MySQL-5.6.51-1.el6.x86_64.rpm-bundle.tar</span></div><div><span style="font-family: courier;">sudo rpm -i MySQL-client-5.6.51-1.el6.x86_64.rpm</span></div><div><span style="font-family: courier;">sudo rpm -i MySQL-server-5.6.51-1.el6.x86_64.rpm</span></div><div><span style="font-family: courier;">sudo service mysql start</span></div><div><span style="font-family: courier;">sudo mysql_upgrade</span></div><div><br /></div><div><b>Upgrade to the latest 5.7</b></div><div><a href="https://dev.mysql.com/downloads/mysql/5.7.html" target="_blank">Look for latest MySQL 5.7 minor version</a></div><div><span style="font-family: courier;">wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-1.el6.x86_64.rpm-bundle.tar</span></div><div><div><span style="font-family: courier;">sudo service mysql stop</span></div><div><span style="font-family: courier;">sudo yum -y remove MySQL-server-5.6.51-1.el6.x86_64 MySQL-client-5.6.51-1.el6.x86_64</span></div></div><div><div><span style="font-family: courier;">tar -xvf </span><span style="font-family: courier;">mysql-5.7.35-1.el6.x86_64.rpm-bundle.tar</span></div><div><span style="font-family: courier;">sudo rpm -i mysql-community-libs-5.7.35-1.el6.x86_64.rpm mysql-community-common-5.7.35-1.el6.x86_64.rpm mysql-community-client-5.7.35-1.el6.x86_64.rpm mysql-community-server-5.7.35-1.el6.x86_64.rpm</span></div><div><span style="font-family: courier;">sudo service mysqld start</span></div><div><span style="font-family: courier;">sudo mysql_upgrade</span></div><div><br /></div></div><div><div><b>Upgrade to the latest 8.0</b></div><div>Look for latest MySQL 8.0 minor version</div><div><span style="font-family: courier;">wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.26-1.el6.x86_64.rpm-bundle.tar</span></div><div><br /></div><div><div><span style="font-family: courier;">sudo service mysqld stop</span></div><div><span style="font-family: courier;">sudo yum -y remove mysql-community-libs-5.7.35-1.el6.x86_64 mysql-community-common-5.7.35-1.el6.x86_64 mysql-community-client-5.7.35-1.el6.x86_64 mysql-community-server-5.7.35-1.el6.x86_64</span></div></div><div><div><span style="font-family: courier;">tar -xvf </span><span style="font-family: courier;">mysql-8.0.26-1.el6.x86_64.rpm-bundle.tar</span></div><div><span style="font-family: courier;">sudo rpm -i mysql-community-client-plugins-8.0.26-1.el6.x86_64.rpm mysql-community-common-8.0.26-1.el6.x86_64.rpm mysql-community-libs-8.0.26-1.el6.x86_64.rpm mysql-community-client-8.0.26-1.el6.x86_64.rpm mysql-community-server-8.0.26-1.el6.x86_64.rpm</span></div><div><span style="font-family: courier;">sudo service mysqld start</span></div><div><br /></div></div></div><div><br /></div><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-70435481956404361502021-12-19T04:02:00.005-08:002021-12-19T04:02:37.679-08:00Comparing SQL to MongoDB Query Syntax<p><span style="font-family: arial;">#MongoDB Aggregation Pipeline</span></p><p><span style="font-family: courier;">db.documents.aggregate([<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$match": {"name": "/^asdasd/"}},<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$group": {"_id": "$name", {"cnt": {$sum:1}}},<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$match": {"cnt": {$gt: 3}}},<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$lookup": {from: "meta", localField:"name", foreignField:"name", as: "meta"}}<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$match": {"meta": {$exists: True}}},<br /></span><span style="font-family: courier; white-space: pre;"> </span><span style="font-family: courier;">{"$out": "debug_collection"}<br /></span><span style="font-family: courier;">]);</span></p><p><span style="font-family: arial;">#SQL Syntax</span></p><p><span style="font-family: courier;">SELECT *<br />FROM documents</span></p><p><span style="font-family: courier;">SELECT *<br />FROM documents<br />WHERE name like 'asdasd%'</span></p><p><span style="font-family: courier;">SELECT name, COUNT(*) AS cnt<br />FROM documents<br />WHERE name like 'asdasd%'<br />GROUP BY name</span></p><p><span style="font-family: courier;">SELECT name, COUNT(*)<br />FROM documents<br />WHERE name like 'asdasd%'<br />GROUP BY name<br />HAVING COUNT(*) > 3</span></p><p><span style="font-family: courier;">SELECT doc.*, meta.*<br />FROM meta INNER JOIN (<br />SELECT name, COUNT(*)<br />FROM documents<br />WHERE name like 'asdasd%'<br />GROUP BY name<br />HAVING COUNT(*) > 3) AS doc ON doc.name = meta.name</span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-5371734058219912462021-12-12T04:30:00.010-08:002021-12-19T05:14:17.831-08:00Your First MongoDB, C# and .NET Core App/My C# and MongoDB Cheat Sheet<p><br /></p><p><a href="https://dotnet.microsoft.com/download">Download .NET core SDK</a></p><h3 style="text-align: left;"><span style="font-family: arial;"><a href="https://code.visualstudio.com/docs/languages/dotnet" target="_blank"><span>Setup you project</span></a> and <a href="https://docs.microsoft.com/en-us/dotnet/core/tutorials/with-visual-studio?pivots=dotnet-6-0" target="_blank">your console project</a></span></h3><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Select a folder</span></li><li><span style="font-family: arial;">In the command line: </span><span style="font-family: courier;"><br />dotnet new console -o console<br /></span><span style="font-family: courier;">dotnet add console package MongoDB.Driver<br />cd console<br />code console</span></li><li><span style="font-family: arial;">vsoode will be opened</span></li></ol><div><span style="font-family: arial;"><a href="https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mongo-app?view=aspnetcore-6.0&tabs=visual-studio-code" target="_blank">Your First Basic Program</a></span></div><div><br /></div><div><span style="font-family: arial;">Create a program.cs file:</span></div><div><div><span style="font-family: courier;">namespace HelloWorld</span></div><div><span style="font-family: courier;">{</span></div><div><span style="font-family: courier;"> class Program</span></div><div><span style="font-family: courier;"> {</span></div><div><span style="font-family: courier;"> static void Main(string[] args)</span></div><div><span style="font-family: courier;"> {</span></div><div><span style="font-family: courier;"> Console.WriteLine("Hello World!");</span></div><div><span style="font-family: courier;"> }</span></div><div><span style="font-family: courier;"> }</span></div><div><span style="font-family: courier;">}</span></div></div><div><br /></div><div><span style="font-family: arial;">Run the code:</span></div><div><span style="font-family: courier;">dotnet run</span></div><div><br /></div><div><br /></div><div><span style="font-family: arial;">Now add a document and write it</span></div><div><span style="font-family: arial;"><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #af00db;">using</span> <span style="color: #267f99;">MongoDB</span>.<span style="color: #267f99;">Driver</span>;</div><div><span style="color: #af00db;">using</span> <span style="color: #267f99;">MongoDB</span>.<span style="color: #267f99;">Bson</span>;</div><div><span style="color: #af00db;">using</span> <span style="color: #267f99;">MongoDB</span>.<span style="color: #267f99;">Bson</span>.<span style="color: #267f99;">Serialization</span>.<span style="color: #267f99;">Attributes</span>;</div><br /></div></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><span style="color: blue;">namespace</span> <span style="color: #267f99;">MongoData</span></div></span></div><div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div>{</div><div> <span style="color: blue;">class</span> <span style="color: #267f99;">Program</span></div><div> {</div><div> <span style="color: blue;">static</span> <span style="color: blue;">void</span> <span style="color: #795e26;">Main</span>(<span style="color: blue;">string</span>[] <span style="color: #001080;">args</span>)</div><div> {</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #a31515;">"Hello World!"</span>);</div><div> <span style="color: blue;">var</span> <span style="color: #001080;">mongoClient</span> = <span style="color: blue;">new</span> <span style="color: #267f99;">MongoClient</span>(<span style="color: #a31515;">"MONGODB_URL"</span>);</div><div> <span style="color: blue;">var</span> <span style="color: #001080;">mongoDatabase</span> = <span style="color: #001080;">mongoClient</span>.<span style="color: #795e26;">GetDatabase</span>(<span style="color: #a31515;">"trial_db"</span>);</div><div> <span style="color: blue;">var</span> <span style="color: #001080;">collection</span> = <span style="color: #001080;">mongoDatabase</span>.<span style="color: #795e26;">GetCollection</span><<span style="color: #267f99;">TrialDoc</span>>(<span style="color: #a31515;">"trial_cl"</span>);</div><br /><div> <span style="color: #267f99;">TrialDoc</span> <span style="color: #001080;">trialDoc</span> = <span style="color: blue;">new</span> <span style="color: #267f99;">TrialDoc</span>();</div><div> <span style="color: #001080;">trialDoc</span>.<span style="color: #001080;">BookName</span> = <span style="color: #a31515;">"123"</span>;</div><div> <span style="color: #001080;">collection</span>.<span style="color: #795e26;">InsertOne</span>(<span style="color: #001080;">trialDoc</span>);</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #a31515;">"Done!"</span>);</div><div> }</div><div> }</div><br /><div> </div><div> <span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #267f99;">TrialDoc</span></div><div> {</div><div> [<span style="color: #267f99;">BsonId</span>]</div><div> [<span style="color: #267f99;">BsonRepresentation</span>(<span style="color: #001080;">BsonType</span>.<span style="color: #001080;">ObjectId</span>)]</div><div> <span style="color: blue;">public</span> <span style="color: blue;">string</span>? <span style="color: #001080;">Id</span> { <span style="color: blue;">get</span>; <span style="color: blue;">set</span>; }</div><br /><div> [<span style="color: #267f99;">BsonElement</span>(<span style="color: #a31515;">"Name"</span>)]</div><div> <span style="color: blue;">public</span> <span style="color: blue;">string</span> <span style="color: #001080;">BookName</span> { <span style="color: blue;">get</span>; <span style="color: blue;">set</span>; } = <span style="color: blue;">null</span>!;</div><br /><div> <span style="color: blue;">public</span> <span style="color: blue;">int </span><span style="color: #001080;">Price</span> { <span style="color: blue;">get</span>; <span style="color: blue;">set</span>; }</div><br /><div> <span style="color: blue;">public</span> <span style="color: blue;">string</span> <span style="color: #001080;">Category</span> { <span style="color: blue;">get</span>; <span style="color: blue;">set</span>; } = <span style="color: blue;">null</span>!;</div><br /><div> <span style="color: blue;">public</span> <span style="color: blue;">string</span> <span style="color: #001080;">Author</span> { <span style="color: blue;">get</span>; <span style="color: blue;">set</span>; } = <span style="color: blue;">null</span>!;</div><div> }</div><div>}</div><br /></div></div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><br /></div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div style="font-family: "Times New Roman"; font-size: medium; white-space: normal;"><span style="font-family: arial;">Add Read Operation: Find All</span></div></div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div style="line-height: 19px;"><div> <span style="color: blue;">var</span> <span style="color: #001080;">list</span> = <span style="color: #001080;">collection</span>.<span style="color: #795e26;">Find</span>(<span style="color: #001080;">_</span> => <span style="color: blue;">true</span>).<span style="color: #795e26;">ToList</span>();</div><div> <span style="color: #af00db;">foreach</span> (<span style="color: blue;">var</span> <span style="color: #001080;">item</span> <span style="color: #af00db;">in</span> <span style="color: #001080;">list</span>) {</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #001080;">item</span>.<span style="color: #001080;">BookName</span>);</div><div> }</div><div style="line-height: 19px;"><br /></div><span style="font-family: arial; font-size: medium; white-space: normal;">Add Read Operation: Specific Record</span><br /><div> <span style="color: blue;">var</span> <span style="color: #001080;">single</span> = <span style="color: #001080;">collection</span>.<span style="color: #795e26;">Find</span>(<span style="color: #001080;">x</span> => <span style="color: #001080;">x</span>.<span style="color: #001080;">BookName</span> == <span style="color: #a31515;">"123"</span>.<span style="color: #795e26;">ToString</span>()).<span style="color: #795e26;">FirstOrDefault</span>();</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #001080;">single</span>.<span style="color: #001080;">Id</span>);</div><div></div></div></div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><span face="SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace" style="background-color: #f2f2f2; color: #171717;"><br /></span></div><div style="background-color: white; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><span style="font-family: arial; font-size: medium; white-space: normal;">Update the record</span><br /></div><div style="background-color: white; line-height: 19px;"><div style="line-height: 19px;"><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"> <span style="color: #001080;">collection</span>.<span style="color: #795e26;">ReplaceOne</span>(<span style="color: #001080;">x</span> => <span style="color: #001080;">x</span>.<span style="color: #001080;">Id</span> == <span style="color: #001080;">single</span>.<span style="color: #001080;">Id</span>, <span style="color: blue;">new</span> <span style="color: #267f99;">TrialDoc</span>() {<span style="color: #001080;">Id</span> = <span style="color: #001080;">single</span>.<span style="color: #001080;">Id</span>, <span style="color: #001080;">BookName</span> = <span style="color: #a31515;">"456"</span>}, <span style="color: blue;">new</span> <span style="color: #267f99;">ReplaceOptions</span> {<span style="color: #001080;">IsUpsert</span> = <span style="color: blue;">true</span>});</div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"><br /></div><div style="font-size: 14px; white-space: pre;"><a href="https://stackoverflow.com/questions/36565911/findandmodify-missing-in-mongodb-c-sharp-driver/36570428"><span style="font-family: arial;">Smarter update using findAndModify</span></a></div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"><pre class="lang-js s-code-block" style="background-color: var(--highlight-bg); border-radius: 5px; border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: 1.30769; margin-bottom: calc(var(--s-prose-spacing) + 0.4em); margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: 12px; vertical-align: baseline; width: auto;"><pre class="default s-code-block" style="background-color: var(--highlight-bg); border-radius: 5px; border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: 1.30769; margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: 12px; vertical-align: baseline; width: auto;"><code class="hljs language-swift" style="background-color: transparent; border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> _client <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> new <span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">MongoClient</span>();
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> _database <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> _client.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">GetDatabase</span>(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"users"</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> counters <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> _database.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">GetCollection</span><<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BsonDocument</span>>(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"counters"</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> counterQuery <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> <span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Builders</span><<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BsonDocument</span>>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Filter</span>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Eq</span>(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"_id"</span>, <span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"eventId"</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> findAndModifyResult <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> counters.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">FindOneAndUpdate</span>(counterQuery,
<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Builders</span><<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BsonDocument</span>>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Update</span>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Set</span>(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"web"</span>, <span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"testweb"</span>));</code></pre></pre></div><div style="line-height: 19px;"><span style="font-family: arial;"><a href="https://www.mongodb.com/community/forums/t/how-to-write-this-aggregate-query-in-c/6940/2" target="_blank">Aggregation Framework</a></span></div><div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div> <span style="color: #267f99;">FilterDefinition</span><<span style="color: #267f99;">TrialDoc</span>> <span style="color: #001080;">filter</span> = </div><div> <span style="color: #001080;">Builders</span><<span style="color: #267f99;">TrialDoc</span>>.<span style="color: #001080;">Filter</span>.<span style="color: #795e26;">Eq</span>(<span style="color: #001080;">x</span> => <span style="color: #001080;">x</span>.<span style="color: #001080;">BookName</span>, <span style="color: #a31515;">"123"</span>);</div><div> <span style="color: #267f99;">ProjectionDefinition</span><<span style="color: #267f99;">TrialDoc</span>> <span style="color: #001080;">project</span> = <span style="color: #001080;">Builders</span><<span style="color: #001080;">TrialDoc</span>></div><div> .<span style="color: #001080;">Projection</span>.<span style="color: #795e26;">Include</span>(<span style="color: #001080;">x</span> => <span style="color: #001080;">x</span>.<span style="color: #001080;">BookName</span>);</div><br /><div> <span style="color: blue;">var</span> <span style="color: #001080;">results</span> = <span style="color: #001080;">collection</span>.<span style="color: #795e26;">Find</span>(<span style="color: #001080;">filter</span>).<span style="color: #795e26;">Project</span>(<span style="color: #001080;">project</span>).<span style="color: #795e26;">ToList</span>();</div><div> <span style="color: #af00db;">foreach</span> (<span style="color: blue;">var</span> <span style="color: #001080;">item</span> <span style="color: #af00db;">in</span> <span style="color: #001080;">results</span>) {</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #001080;">item</span>);</div><div> }</div><div></div></div><span style="font-family: Consolas, Courier New, monospace;"><span style="font-size: 14px; white-space: pre;">
</span></span></div><div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="font-family: arial; font-size: medium; white-space: normal;"><a href="https://stackoverflow.com/questions/41356544/full-text-search-in-mongodb-in-net/41357957" target="_blank">Full Text Search</a></span></div><div><span style="font-family: arial; font-size: medium; white-space: normal;">Don't forget to create a full text search index first</span></div><div> <span style="color: blue;">var</span> <span style="color: #001080;">results_list</span> = <span style="color: #001080;">collection</span>.<span style="color: #795e26;">Find</span>(<span style="color: #001080;">Builders</span><<span style="color: #267f99;">TrialDoc</span>>.<span style="color: #001080;">Filter</span>.<span style="color: #795e26;">Text</span>(<span style="color: #a31515;">"123"</span>)).<span style="color: #795e26;">ToList</span>();</div><div> <span style="color: #af00db;">foreach</span> (<span style="color: blue;">var</span> <span style="color: #001080;">item</span> <span style="color: #af00db;">in</span> <span style="color: #001080;">results_list</span>) {</div><div> <span style="color: #001080;">Console</span>.<span style="color: #795e26;">WriteLine</span>(<span style="color: #001080;">item</span>);</div><div> }</div><br /></div></div><div><span style="font-family: Consolas, Courier New, monospace;"><span style="font-size: 14px; white-space: pre;"><br /></span></span></div><div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><span style="font-family: arial; font-size: medium; white-space: normal;"><a href="https://stackoverflow.com/questions/33986155/how-do-i-use-a-geospatial-query-in-the-2-1-mongodb-c-sharp-driver" target="_blank">GeoSpatial Stuff</a></span><br /></div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><br /></div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;"><br /></div><div style="line-height: 19px;"><span style="font-family: courier;"><span style="font-size: 14px; white-space: pre;">using MongoDB.Driver.GeoJsonObjectModel;
</span><span style="background-color: transparent;"><br /></span></span></div><div style="line-height: 19px;"><span style="background-color: transparent;"><span style="font-family: courier;">#Add to document<br /></span></span><span style="font-family: courier;">public GeoJson2DCoordinates Location { get; set; } = null!;<br />trialDoc.Location = new GeoJson2DCoordinates(31.11, 19.12);</span></div><div style="line-height: 19px;"><pre class="lang-cs s-code-block" style="background-color: var(--highlight-bg); border-radius: 5px; border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: Consolas, "Courier New", monospace; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: 1.30769; margin-bottom: calc(var(--s-prose-spacing) + 0.4em); margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: 12px; vertical-align: baseline; white-space: pre; width: auto;"><pre class="default s-code-block" style="background-color: var(--highlight-bg); border-radius: 5px; border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: 1.30769; margin-bottom: calc(var(--s-prose-spacing) + 0.4em); margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: 12px; vertical-align: baseline; width: auto;"><code class="hljs language-swift" style="background-color: transparent; border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">var</span> keys <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> <span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Builders</span><<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BsonDocument</span>>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">IndexKeys</span>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Geo2DSphere</span>(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"Location"</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">await</span> collection.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Indexes</span>.<span class="hljs-type" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">CreateOneAsync</span>(keys);</code></pre></pre></div></div><div style="font-family: Consolas, "Courier New", monospace; font-size: 14px; white-space: pre;"></div></div></div><p></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-23609809071540385872021-11-29T03:09:00.000-08:002021-11-29T03:09:00.678-08:00How to Setup Your Development Environment for a MongoDB and .Net based Project<p>1. <a href="https://top-performance.blogspot.com/2021/11/how-to-setup-free-mongodb-atlas.html" target="_blank">Get your MongoDB Atlas Replicaset </a></p><p>2. Make sure you can access the MongoDB Atlas Replicaset using port 27017/TCP</p><p>3. Setup your desktop with the following as described in the embedded Loom:</p><p> - <a href="https://docs.mongodb.com/compass/current/install/" target="_blank">Install MongoDB Compass on the desktop</a> </p><div class="separator" style="clear: both; text-align: center;"><iframe allowfullscreen="" class="BLOG_video_class" height="266" src="https://www.youtube.com/embed/Cqs3hssata4" width="320" youtube-src-id="Cqs3hssata4"></iframe></div><p> - <a href="https://code.visualstudio.com/docs/languages/dotnet" target="_blank">Install a coding environment with recent VS Code ready for .NET Core</a></p><div class="separator" style="clear: both; text-align: center;"><iframe allowfullscreen="" class="BLOG_video_class" height="266" src="https://www.youtube.com/embed/Kh4yhbGaFWE" width="320" youtube-src-id="Kh4yhbGaFWE"></iframe></div><p>Now when everything is ready, you start coding your project and enjoy the combined power of .NET Core and MongoDB</p><p>Keep Performing,<br /><a href="https://www.linkedin.com/in/moshekaplan" target="_blank">Moshe Kaplan</a></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-39454746202908779592021-11-29T01:59:00.000-08:002021-11-29T01:59:05.064-08:00How to Setup a Free MongoDB Atlas Replicaset<p> Having a MongoDB Relicaset was always simple, but with MongoDB Atlas, it is simple than ever.</p><p>In this two sessions video we'll learn:</p><p>1. How to open a MongoDB Atlas account, create a project and a new cluster (Replicaset)</p><div class="separator" style="clear: both; text-align: center;"><iframe allowfullscreen="" class="BLOG_video_class" height="266" src="https://www.youtube.com/embed/SaCppL1RCgc" width="320" youtube-src-id="SaCppL1RCgc"></iframe></div><p>2. How to verify you can connect your Replicaset</p><div class="separator" style="clear: both; text-align: center;"><iframe allowfullscreen="" class="BLOG_video_class" height="266" src="https://www.youtube.com/embed/gqqqkiYnvNE" width="320" youtube-src-id="gqqqkiYnvNE"></iframe></div><br /><p><br /></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-46404245972986036222021-11-21T15:58:00.002-08:002021-11-29T01:39:19.302-08:00How to Install MongoDB Communitry Edition 5.0.4 on Ubuntu 18.04<p>Well it is super simple, and you can actually try the <a href="https://docs.mongodb.com/manual/tutorial/install-mongodb-on-ubuntu/">MongoDB guide</a> or my embedded video:</p><div class="separator" style="clear: both; text-align: center;"><iframe allowfullscreen="" class="BLOG_video_class" height="266" src="https://www.youtube.com/embed/KBoaEJd-2yY" width="320" youtube-src-id="KBoaEJd-2yY"></iframe></div><p>I used for this task a t2.medium AWS instance with 2 burstable cores, 4GB RAM and 8GB gp2 SSD disks.</p><p>Keep Performing,</p><p><a href="https://www.linkedin.com/in/moshekaplan" target="_blank">Moshe Kaplan</a></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-21569215424247342412021-09-22T07:42:00.008-07:002021-09-22T07:47:39.796-07:00An Open Source Data Masking Solution for MySQL<p><span style="font-family: arial;">Today we'll disucss the need for data masking due to privacy regulations such as GDPR that becone more and more common in the industry.</span></p><p><span style="font-family: arial;">In order to deploy such a solution we'll utlize two great products:</span></p><p><span style="font-family: arial;">1. <a href="https://www.percona.com/doc/percona-server/8.0/security/data-masking.html" target="_blank">Percona Server 8.0.17</a> that has recently introduced the data masking plugin (that is compatible w/ MySQL Enterprise one. This plugin exposes multiple functions that translate sensitive strings such as SSN and emails to masked strings.</span></p><p><span style="font-family: arial;">2. <a href="https://medium.com/@dotronglong/proxysql-data-anonymization-e82a26b07f19" target="_blank">ProxySQL</a> a proxy server that supports modifying SQL queries on the fly. For example replacing </span><span style="font-family: courier;">SELECT ssn FROM users;</span><span style="font-family: arial;"> with </span><span style="font-family: courier;">SELECT mask_ssn(ssn) FROM users;</span></p><p><span style="font-family: arial;">The Percona Server will serve as our MySQL solution (you can use it as a slave instance if you need it for analyst purposes only). while the ProxySQL will serve as a Proxy that modifies SQL queries to utilize the Percona server data masking functions. You may also need to limit users access from the network to the Percona server.</span></p><p><span style="font-family: arial;"><b>Bottom Line</b></span></p><p><span style="font-family: arial;">New times bring new products that can serve us to create novel solutions</span></p><p><span style="font-family: arial;">Keep Performing,</span></p><p><span style="font-family: arial;"><a href="https://www.linkedin.com/in/moshekaplan" target="_blank">Moshe Kaplan</a></span></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-65266531646244836762021-06-09T01:53:00.004-07:002021-06-09T01:53:36.066-07:00MongoDB Monitoring Using Prometheus<p><span style="font-family: arial;">If you environment is bound to Prometheus it is best we focus on this platform as a baseline.</span></p><div dir="ltr" gmail_original="1"><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">We will need multiple aspects:</span></div><div><span style="font-family: arial;"><br /></span></div><div><b><span style="font-family: arial;">System Counters</span></b></div><div><span style="font-family: arial;">Main metrics needed:</span></div><div><span style="font-family: arial;">1. Machines CPU</span></div><div><span style="font-family: arial;">2. Read and Write IOPS to disks</span></div><div><span style="font-family: arial;">3. Memory utilization</span></div><div><span style="font-family: arial;">4. Disk utilization</span></div><div><span style="font-family: arial;">5. Incoming and outgoing network traffic</span></div><div><span style="font-family: arial;"><br /></span></div><div><b><span style="font-family: arial;">MongoDB Counters</span></b></div><div><span style="font-family: arial;">For this task it is best to use the <a href="https://github.com/percona/mongodb_exporter">MongoDB exporter</a></span></div><div><span style="font-family: arial;">There are also 3 Grafana dashboards provided that we can use to monitor the performance</span></div><div><span style="font-family: arial;">MongoDB Overview;<br />MongoDB ReplSet;<br />MongoDB WiredTiger;<br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">These will be able to provide us key indicators such as locks, replicaset status, </span></div><div><span style="font-family: arial;"><br /></span></div><div><b><span style="font-family: arial;">MongoDB Slow Queries</span></b></div><div><span style="font-family: arial;">It will also be wise to enable the MongoDB slow query w/ an initial limit of 100ms to gain some overview of the cluster slow queries:</span></div><div><span style="font-family: arial;">db.setProfilingLevel(1,100)</span></div></div><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-26631002242312300752021-02-10T04:24:00.002-08:002021-02-10T04:24:34.928-08:00Why 4 Nodes MongoDB Replicaset is not a Good Idea<p><span style="font-family: arial;"><b>Customer Question:</b></span></p><p><span style="font-family: arial;">We have a 4 nodes replicaset w/ leading nodes each with different priority. From time to time, when we suffer from network issues, all the replicaset goes down.</span></p><p><span style="font-family: arial;"><b>Why is this Happens?</b></span></p><p><span style="font-family: arial;">Since two of the nodes are a single site and the two other on two other sites, when the first site goes offline, no site can create a majority. Therefore, you must remove one of the nodes in the first site to avoid these downtimes.</span></p><p><span style="font-family: arial;">Moreover, the MongoDB selects the primary node by priority. If the primary is unstable, everytime it will go offline, a secondary will be chosen w/ a possible few secondds replicaset downtime. When the high priority node goes back online, it will be selected again to primary (and causing another possible downtime).</span></p><p><span style="font-family: arial;">Therefore, if there is no good reason, avoid specifing variable priorities.</span></p><p><span style="font-family: arial;"><b>How to fix It?</b></span></p><p><span style="font-family: arial;">1. Perform the task during off peak hours</span></p><p><span style="font-family: arial;">2. Remove the arbiter node by <a href="https://docs.mongodb.com/manual/tutorial/remove-replica-set-member/">rs.remove() </a></span></p><p><span style="font-family: arial;">3. Verify replicaset is okay by running rs.status()</span></p><p><span style="font-family: arial;">4. <a href="https://docs.mongodb.com/manual/tutorial/adjust-replica-set-member-priority/">Modify the remaining nodes configuration</a>:</span></p><p><span style="font-family: arial;">cfg = rs.conf()</span></p><p><span style="font-family: arial;">cfg.members[0].priority = 1</span></p><p><span style="font-family: arial;">cfg.members[1].priority = 1</span></p><p><span style="font-family: arial;">cfg.members[2].priority = 1</span></p><p><span style="font-family: arial;">rs.reconfig(cfg)</span></p><p><span style="font-family: arial;">5. Verify again.</span></p><p><span style="font-family: arial;"><b>Bottom Line</b></span></p><p><span style="font-family: arial;">Keep it Simple :-)</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;">Keep Performing,</span></p><p><a href="https://www.linkedin.com/in/moshekaplan/"><span style="font-family: arial;">Moshe Kaplan</span></a></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-56184548834746010482020-11-16T11:13:00.002-08:002020-11-18T15:53:30.035-08:00MongoDB Review Checklist and Tools<p><span style="font-family: arial;"><b>Tools</b></span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Analyze the mongostat results: <a href="https://docs.mongodb.com/manual/reference/program/mongostat/">https://docs.mongodb.com/manual/reference/program/mongostat/</a></span></li><li><span style="font-family: arial;">And mongotop to see query highlight <a href="https://docs.mongodb.com/manual/reference/program/mongotop/">https://docs.mongodb.com/manual/reference/program/mongotop/</a></span></li><li><span style="font-family: arial;">Enable slow queries: db.setProfilingLevel(1,50)</span></li><li><span style="font-family: arial;">Dex: to analyze slow queries: <a href="http://dex.mongolab.com/">http://dex.mongolab.com/</a></span></li><li><span style="font-family: arial;">mtools to visualize the query usage <a href="https://github.com/rueckstiess/mtools">https://github.com/rueckstiess/mtools</a></span></li><li><span style="font-family: arial;">System metrics: top and iostat</span></li></ol><p></p><p><b><span style="font-family: arial;">Checklist</span></b></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Usage and location of the Replica Set instances</span></li><li><span style="font-family: arial;">Check system resources usage: CPU and disk IOPS</span></li><li><span style="font-family: arial;">Check network latency, bandwidth and Application level Read Preference and Write Concern</span></li><li><span style="font-family: arial;">Check mongo instances usage (mongostat)</span></li><li><span style="font-family: arial;">Check top slow queries (mongotop, slow queries)</span></li><li><span style="font-family: arial;">Check queries and command usage using </span></li><li><span style="font-family: arial;">Check backup strategy</span></li><li><span style="font-family: arial;">Check storage engine and MongoDB version</span></li><li><span style="font-family: arial;">Check monitoring tools</span></li></ol><p></p><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-70888089011969578582020-06-07T00:53:00.005-07:002020-06-07T00:53:32.502-07:00Enable Slow Queries Analysis on Postgres RDS (pg_stat_statements)<span style="font-family: Arial, Helvetica, sans-serif;">Analyzing slow queries on Postgres can be done by enabling slow queries, but it is even more fun with the builtin extension: pg_stat_statements</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">1. Enable tracking by changing parameters group values (if you did not create a custom parameter group <a href="https://pganalyze.com/docs/install/amazon_rds/01_configure_rds_instance">follow these instrcutions</a> and don't forget to reboot the instance to take effect):</span><br />
<span style="font-family: Courier New, Courier, monospace;">pg_stat_statements.track<span style="white-space: pre;"> </span>ALL</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">2. Connect to Postgres</span><br />
<span style="font-family: Courier New, Courier, monospace;">psql -U ADMIN_USER -h RDS_PATH.rds.amazonaws.com -d postgres</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">3. Create</span><span style="font-family: Arial, Helvetica, sans-serif;"> the extension</span><br />
<span style="font-family: Courier New, Courier, monospace;">CREATE EXTENSION pg_stat_statements;</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">4. Verify the extension was created:</span><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT * </span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM pg_available_extensions </span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE </span><br />
<span style="font-family: Courier New, Courier, monospace;"> name = 'pg_stat_statements' and </span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;"> installed_version is not null;</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">5. Analyze the queries usage:</span><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Keep Performing,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://www.linkedin.com/in/moshekaplan/">Moshe Kaplan</a></span><div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-30305789589927541292020-02-17T01:00:00.003-08:002020-02-17T01:00:51.920-08:00Kafka Cluster Hardware Best Practices<div gmail_original="1">
<b><span style="font-family: Arial, Helvetica, sans-serif;">Reg. Kafka case studies</span></b></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Some of the largest Kafla installations in Israel are Adtech (Kenshoo, Taboola...) and Cyber (Verint...) with >40TB or 75M messages per day </span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Although the literature claims that Kafka async writes favours magnetic disks (or at least minimizes their benefit [1]) <b>The practice shows that using attached storage 1TB SSD is the best (Kenshoo for example),</b> and it <b>confirmed by confluent</b> that IO can bound performance if disks are slow (our case) [3]</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Micron Benchmark shows x4 to x20 performance when using SSD/NVMe [4]</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">You may avoid using NAS/SAN/external storage machine [2]</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">You should <b>avoid placing OS and logs on the Kafka disks</b> [2]</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">One last thing, verify you are using 10Gb NICs [5]</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Keep Performing,</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://www.linkedin.com/in/moshekaplan/">Moshe Kaplan</a></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">[1] <a href="https://blog.cloudera.com/deploying-apache-kafka-a-practical-faq/" target="_blank">https://blog.cloudera.com/<wbr></wbr>deploying-apache-kafka-a-<wbr></wbr>practical-faq/</a> </span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Using SSDs instead of spinning disks has not been shown to provide a significant performance improvement for Kafka, for two main reasons:<br /><br />Kafka writes to disk are asynchronous. That is, other than at startup/shutdown, no Kafka operation waits for a disk sync to complete; disk syncs are always in the background. That’s why replicating to at least three replicas is critical—because a single replica will lose the data that has not been sync’d to disk, if it crashes.<br />Each Kafka Partition is stored as a sequential write ahead log. Thus, disk reads and writes in Kafka are sequential, with very few random seeks. Sequential reads and writes are heavily optimized by modern operating systems.</span></div>
<div>
</div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">[2] <a href="https://docs.confluent.io/current/kafka/deployment.html" target="_blank">https://docs.confluent.io/<wbr></wbr>current/kafka/deployment.html</a> <wbr></wbr> </span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">[3] <a href="https://docs.confluent.io/current/streams/sizing.html" target="_blank">https://docs.confluent.io/<wbr></wbr>current/streams/sizing.html</a> </span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">[4] <a href="https://www.micron.com/about/blog/2017/october/improve-apache-kafka-performance-with-flash-storage" target="_blank">https://www.micron.com/about/<wbr></wbr>blog/2017/october/improve-<wbr></wbr>apache-kafka-performance-with-<wbr></wbr>flash-storage</a> </span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">[5] <a href="https://docs.cloudera.com/HDPDocuments/HDF3/HDF-3.1.1/bk_planning-your-deployment/content/ch_hardware-sizing.html" target="_blank">https://docs.cloudera.com/<wbr></wbr>HDPDocuments/HDF3/HDF-3.1.1/<wbr></wbr>bk_planning-your-deployment/<wbr></wbr>content/ch_hardware-sizing.<wbr></wbr>html</a> </span></div>
<div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-83329912161813130822019-11-10T04:11:00.000-08:002019-11-10T04:11:08.519-08:00Poor TokuDB Performance due to Unreasonable Statisitcs<div gmail_original="1">
<b><span style="font-family: Arial, Helvetica, sans-serif;">The details behind TokuDB Statistics </span></b></div>
<div gmail_original="1">
<a href="https://dba.stackexchange.com/questions/135180/percona-5-7-tokudb-poor-query-performance-wrong-non-clustered-index-chosen"><span style="font-family: Arial, Helvetica, sans-serif;">https://dba.stackexchange.com/questions/135180/percona-5-7-tokudb-poor-query-performance-wrong-non-clustered-index-chosen</span></a></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">All the details about <b>TokuDB Background ANALYZE TABLE</b></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://www.percona.com/doc/percona-server/LATEST/tokudb/tokudb_background_analyze_table.html">https://www.percona.com/doc/percona-server/LATEST/tokudb/tokudb_background_analyze_table.html</a></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Upgrade to 5.6.38: to enjoy</b> TokuDB: ANALYZE TABLE Is No Longer a Blocking Operation - Percona Database Performance Blog</span></div>
<br />
<div gmail_original="1">
</div>
<br />
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/">https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/</a><a href="https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/"><br /></a></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Keep Performing,</span></div>
<div gmail_original="1">
<span style="font-family: Arial, Helvetica, sans-serif;">Moshe Kaplan</span></div>
<div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-44920601360182204852019-01-09T02:59:00.001-08:002019-01-09T03:07:42.327-08:00Disaster Recovery Plan (DRP) for MySQL/MariaDB Galera Cluster<h3>
<span style="font-family: "arial" , "helvetica" , sans-serif;">When S#!t Hits the Fan...</span></h3>
<span style="font-family: "arial" , "helvetica" , sans-serif;">That is a good reason to prepare for failure to minimize data loss and downtime</span><br />
<h3>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Cluster Design Documentation</span></h3>
<span style="font-family: "arial" , "helvetica" , sans-serif;">First document your cluster, and verify you have:</span><br />
<ol>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Odd number of instances (>=3) on at least 3 independent location</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">At least a daily backup using XtraBackup is saved remotely</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Enabled Monitoring is enabled (warning you from low disk space or under performing instances). </span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Enabled slow queries monitoring to make sure query performance is monitored and you take care of slow queries to maximize UX</span></li>
</ol>
<div>
<h3 style="font-family: arial, helvetica, sans-serif;">
Data Recovery Plan (DRP)</h3>
<h4 style="font-family: arial, helvetica, sans-serif;">
DR Cases</h4>
<div>
<ol style="font-family: arial, helvetica, sans-serif;">
<li>Data was deleted/modified accidentally. This case will require either:</li>
<ol>
<li>Accept the data loss</li>
<li>Get back to daily backup and lose any data collected since last backup (T1+T2).</li>
<li>Recover the database on a new node, and cherry picking the changes on the current cluster (T1)</li>
</ol>
<li>Single node was crushed<br />Galera support an automatic recovery of a node w/o significant work. Recovery can be accelerated by recovering the node from the daily backup (T1)</li>
<li>All Cluster is not working</li>
<ol>
<li>Requires recovery of a single node from daily backup (T1)</li>
<li>Setup the cluster (T2)</li>
</ol>
</ol>
<div>
<h4 style="font-family: arial, helvetica, sans-serif;">
Technical Procedures</h4>
<div style="font-family: arial, helvetica, sans-serif;">
<b>T1: Restore a node from daily backup</b></div>
<div>
<ol>
<li style="font-family: arial, helvetica, sans-serif;">Bring back the files from the the remote backup to /mnt/backup/</li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Uncompress the files</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo tar xvfz $file_name</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Shutdown the MySQL</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo service stop mysqld</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Copy the files to your data folder (/var/lib/mysql)</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo rm -rf /var/lib/mysql/*<br />sudo innobackupex --copy-back /mnt/datadrive/mysqlbackup/</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Verify the folder permissions</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo chown -R mysql:mysql /var/lib/mysql</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Restart the MySQL and verify everything is working.</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo service mysql start</span></li>
</ol>
</div>
<div style="font-family: arial, helvetica, sans-serif;">
<b>T2: <a href="http://galeracluster.com/documentation-webpages/startingcluster.html">Setup a cluster</a></b></div>
<div>
<ol>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Verify Galera was defined in my.cnf and define </span><br /><span style="font-family: "courier new" , "courier" , monospace;">[mysqld]<br />wsrep_cluster_address=gcomm://10.10.10.10<br />wsrep_provider=/usr/lib64/libgalera_smm.so</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Start the first node:</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo service mysql start --wsrep-new-cluster</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Verify the cluster size</span><br /><span style="font-family: "courier new" , "courier" , monospace;">mysql> SHOW STATUS LIKE 'wsrep_cluster_size';<br />+--------------------+-------+<br />| Variable_name | Value |<br />+--------------------+-------+<br />| wsrep_cluster_size | 1 |<br />+--------------------+-------+</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Repeat the process on the other nodes, this time just w/ a simple MySQL restart</span><br /><span style="font-family: "courier new" , "courier" , monospace;">sudo service mysql start</span></li>
</ol>
</div>
<div style="font-family: arial, helvetica, sans-serif;">
<b>T3: Add/Remove a node</b></div>
<div style="font-family: arial, helvetica, sans-serif;">
<ol>
<li>Restore the node from the nightly backup (T1)</li>
<li>Perform step 4 in setup a cluster (T2)</li>
</ol>
</div>
</div>
</div>
</div>
<div>
<span id="docs-internal-guid-cacb242f-7fff-b532-3c1c-8d1ae9467a84">
</span>
<h3 style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;">
<span id="docs-internal-guid-cacb242f-7fff-b532-3c1c-8d1ae9467a84">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Bottom Line</span></span></h3>
<span id="docs-internal-guid-cacb242f-7fff-b532-3c1c-8d1ae9467a84">
<div>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Being ready for the worst, can help you mitigate it with minimal data loss and minimal downtime</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Keep Performing,</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><a href="https://www.linkedin.com/in/moshekaplan/">Moshe Kaplan</a></span></div>
</span></div>
<div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.comtag:blogger.com,1999:blog-1500969131931240222.post-35324472854536498302018-11-18T02:55:00.000-08:002018-11-18T02:55:20.501-08:0010 Things You Should Know about MongoDB Sharding<br />
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">MongoDB Sharding is a great way to scale out your writes without changes to application code. However, it requires careful design to enjoy all the scale and performance benefits:</span></div>
<ol>
<li><span style="font-family: Arial, Helvetica, sans-serif;">You cannot rename a sharded collection. Think twice before any action!</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">MongoDB collection cannot be resharded: To reshard a collection, you will have to copy the collection into a new sharded collection (or do it twice if you cannot rename the collection name in the application level). You will need a script like this:</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">use app;</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">db.c_to_shard.find().forEach(function(doc){<br /> db.c_to_shard_new.insert(doc);<br /> db.c_to_shard.remove({"_id":doc._id});<br />});</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">When you run this script, make sure you do it on the cluster (or on a server with minimal latency to the cluster) to accelerate copy.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">If collection is already large, you will need to split the script and run multiple processes to fasten the copy.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Avoid creating <a href="https://stackoverflow.com/questions/13757712/unique-indexes-in-mongodb-if-we-use-sharding">a unique index</a> on the sharded collection. Sharded collection cannot utilize unique index..</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Be careful to avoid selecting a field that does not exist in all the documents. Otherwise, you will find tons of "missing documents".</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Follow the previous rule when selecting range based sharding.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Choose the sharding key wise. If you do not sure 100% your keys will be distributed evenly, use a <a href="https://stackoverflow.com/questions/18575094/cant-shard-a-collection-on-mongodb">hashed key</a> to ensure it</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">db.c_to_shard.ensureIndex({user_id: "hashed"})<br />sh.shardCollection("app.c_to_shard", {"user_id": "hashed"})</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Verify your shards and chunks are evenly distributed using </span><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">getShardDistribution()</span><span style="font-family: Arial, Helvetica, sans-serif;">. Verify it once sharded is done, and as data starts to get into the collections. If something is wrong, the smaller the collection, the easier the fix.</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">db.c_to_shard.getShardDistribution() </span><span style="font-family: Arial, Helvetica, sans-serif;">or do the same with the <a href="https://github.com/comerford/mongodb-scripts/blob/master/AllChunkInfo.js">following script</a> that will </span><span style="font-family: Arial, Helvetica, sans-serif;">provide you a detailed information on each shard and chunk.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">If all chunks reside on a single shard, verify the balancer status, and start if needed:</span><br /><span style="font-family: Courier New, Courier, monospace;">sh.getBalancerState()<br />sh.startBalancer()</span></li>
</ol>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Bottom Line</b></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">MongoDB sharding is a great way to scale out your data store write thoughput. However, you should select your sharding key wise, otherwise, it might require a significant effort to fix stuff.</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Keep Performing,</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://www.linkedin.com/in/moshekaplan/">Moshe Kaplan</a></span></div>
<div class="blogger-post-footer">For consulting contact me now mokplan@gmail.com</div>Moshe Kaplanhttp://www.blogger.com/profile/15653295778603874226noreply@blogger.com