I have over the last few years been fortunate to have two books
published through Apress, Pro MySQL NDB Cluster which I wrote together with
Mikiya Okuno and MySQL Connector/Python Revealed. With the release
of MySQL 8 around a year ago, I started to think of how many
changes there has been in the last few MySQL versions. Since
MySQL 5.6 was released as GA in early 2013, some of the major
features related to performance tuning includes the Performance
Schema which was greatly changed in 5.6, histograms,
EXPLAIN ANALYZE
, hash …
In this fourth post in our MySQL Use Case Blog Series we look at a customer of ours who was able to grow their Saas business from tens of customers initially to thousands of enterprise customers once they achieved continuous MySQL operations with Continuent Tungsten.
This particular customer, based in California, develops and sells automation software for account-based marketing as well as other marketing services and products such as SEO and content marketing. They provide solutions tailored for large enterprises and fast-growing, small businesses alike covering all industry types from technology all the way to higher education.
So how did this fast growing marketing automation SaaS provider scale from tens of customers to thousands of enterprise customers using Tungsten Clustering (currently with 600+ MySQL instances)?
The Challenge
SaaS and other web applications are inherently 24/7/365 operations, thus they …
[Read more]This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of 3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.
We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.
In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.
Going forward will look into the detailed analysis made to tackle the issue of OOM.
DB Environment:-
- Service – Group Replication Cluster
- Cluster Nodes – 3
- GR mode – Single Primary …
Recently one of our client approach Mydbops with Query slowness on a MySQL environment . They deployed the new code for generate the huge reports for the year end analytics data . After the deployment the queries were extremely slow and they struggled lot , then they approached us for the solution. After the analysis, their OLAP database as expected it was IO bound with 100% disk IOPS utilised during the report generation. So, the queries were starving for the Disk IO slows the process .
Problem statement :
- Reports are majorly focused on two larger log tables ( emp_Report_model , emp_details ) .
- The report generator (procedure) is using the count(*) statement to stimulate the aggregated data on each call. It is required for their business purpose .
- Count(*) is terribly slow in MySQL ( Using MySQL 5.7 ) as it …
In a MySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL master and slave nodes to ensure your setup is healthy. The monitoring program or script must alert the high availability framework in case any of the health checks fails, enabling the high availability framework to take corrective actions in order to ensure service availability.
MySQL Master Server Health Checks
We recommended that your MySQL master monitoring program or scripts runs at frequent intervals. Assuming that the monitoring script is running on the same server as your …
[Read more]As a part of Mydbops Consulting we have a below problem statement from one of our client.
“We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.“
OS : Debian 9 (Stretch)
CPU : 40
RAM : 220G (Usable)
Disk : 3T SSD with 80K sustained IOPS.
MySQL : 5.6.43-84.3-log Percona Server (GPL)
Datasize : 2.2TB
Below is the graph on CPU utilisation from Grafana.
Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.
Explain Plan:
…[Read more]
The MySQL NDB Cluster team works on fundamental redesigns of core parts of NDB architecture. One of these changes is the partial checkpoint algorithm. You can now take full advantage of it when building much larger clusters: NDB 8.0 can use 16 TB data memory per data node for in-memory tables.…
Tweet Share
Queries effect database performance. That’s not a typo: “effect” not “affect”. The difference is not a word game but an important way to think about database performance.
Many things can affect performance. For example, if the system runs out of memory then starts swapping which causes high disk IO latency, that will negatively affect performance. But external factors like that notwithstanding (i.e. when hardware and MySQL are normal and stable), it’s important to understand that queries effect performance.
Queries effect database performance. That’s not a typo: “effect” not “affect”. The difference is not a word game but an important way to think about database performance. Many things can affect performance. For example, if the system runs out of memory then starts swapping which causes high disk IO latency, that will negatively affect performance. But external factors like that notwithstanding (i.e. when hardware and MySQL are normal and stable), it’s important to understand that queries effect performance.
Monitoring your MySQL database performance in real-time helps you immediately identify problems and other factors that could be causing issues now or in the future. It’s also a good way to determine which components of the database can be enhanced or optimized to increase your efficiency and performance. This is usually done through monitoring software and tools either built-in to the database management software or installed from third-party providers.
Prometheus is an open-source software application used for event monitoring and alerting. It can be used along with a visualization tool like Grafana to easily create and edit dashboards, query, visualize, alert on, and understand your metrics. ScaleGrid provides full admin access to your MySQL deployments – this makes it …
[Read more]