Below I will show you how to build the solution + manage your capabilities & compared against having MySQL 8.0 Server’s usage. This post will also demonstrate using MySQL’s Pluggable Password Store and the API-CLI for all password and cluster commands. These are MySQL 8.0 Features in the MySQL-Shell 8.0, of course. Understanding & Building the Solution The… Read More »
10 Older Entries »
A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing.
Below is the SQL query and its explain plan. ( MySQL 5.7 )
select username, role from user_roles where username= '9977223389' ORDER BY role_id DESC LIMIT 1;
Execution plan and table structure
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_roles partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 1 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) show create table user_roles\G *************************** 1. row *************************** Table: …[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.
- Service – Group Replication Cluster
- Cluster Nodes – 3
- GR mode – Single Primary …
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.
Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method . Through this blog I am going to compare the both methods .
Remember you can use the parallel data loading utility only via MySQL Shell .
Internal Work Flow :
This section describes the …[Read more]
There is an interesting feature in MySQL 5.7+: the global variable super_read_only. MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database. With super_read_only, those users cannot write anymore. But this feature comes with a surprise.
TL&DR: changing super_read_only to ON also sets read_only to ON and setting
Recently, One of our client reached our Remote DBA team with a requirement to reduce the size of the table as it is having many text columns with huge number of records. At preliminary check , I have validated the table size and its row format, as it was in compressed format already.
Later I checked on other possibilities to compress the text columns further, At that time, then I came across per-column compression feature in Percona MySQL server (From 5.7.17-11) which features individual column compression and we were using …[Read more]
In this blog, we will see how to do a flashback recovery or rolling back the data in MariaDB, MySQL and Percona.
As we know the saying “All humans make mistakes”, following that in Database environment the data modified accidentally can bring havoc to any organisations.
Recover the lost data
- The data can be recovered from the latest full backup or incremental backup when data size is huge it could take hours to restore it.
- From backup of Binlogs.
- Data can also be recovered from delayed slaves, this case would be helpful when the mistake is found immediately, within the period of delay.
We can use anyone of the above ways or other that can help to recover the lost data, but what really matters is, What is the …[Read more]
“Hey, what’s going on with my applications? I installed a newer version of MySQL. I have queries that perfectly run with the older version and now I have a lot of errors.”
This is a question some customers have asked me after upgrading MySQL. In this article, we’ll see what one of the most frequent causes of this issue is, and how to solve it.
We are talking about this error:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.web_log.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Have you ever seen it?
As the first thing let me introduce the concept of SQL_MODE.
MySQL can work using different SQL modes that affect the syntax of the queries and validation checks. Based on the configured value of the …[Read more]
When you write stored procedures in MySQL, you sometimes need to
generate queries on the fly, for example as you process the
result of another query. This is supported using prepared statements. This blog explores how
you can take advantage of the
sys schema to simplify
the use of dynamic queries.
Executing a query using the sys.execute_prepared_stmt() procedure.
sys schema includes several stored procedures
and functions as well as views to make the database
administrator’s life easier. One of these is the
10 Older Entries »