We have just released MySQL 8.0.20. And it has some interesting replication enhancements. In particular one big and exciting feature: binary log compression. Here is the list of things in this release:
… Tweet Share
To celebrate the publishing of my new book MySQL 8 Query Performance Tuning, the Apress team invited me (thanks Jonathan and Liz) to write a post for the Apress blog. I decided to write about my top six best practices:
Yes, my first best practice is to be wary of best practices. Read why I added that and the other best practices at MySQL Performance Tuning Best Practices.
The post Apress Blog: …
[Read more]
MySQL introduced optimizer hints in version 5.7 and greatly
extended the feature in MySQL 8. One thing that has been missing
though is the ability to specify index hints using the syntax of
optimizer hints. This has been improved of in MySQL 8.0.20 with
the introduction of index-level optimizer hints for the
FORCE and IGNORE versions of the index
hints. This blog will look at the new index hint syntax.
Warning
Do not add index hints – neither using the old or new style –
unless you really need them. When you add index hints, you limit
the options of the optimizer which can prevent the optimizer
obtaining the optimal query plan as new optimizer improvements
are implemented or the data changes.
On …
Usually one would expect that ALTER TABLE with ALGORITHM=COPY will be slower than the default ALGORITHM=INPLACE. In this blog post we describe the case when this is not so.
One of the reasons for such behavior is the lesser known limitation of ALTER TABLE (with default ALGORITHM=INPLACE) that avoids REDO operations. As a result, all dirty pages of the altered table/tablespace have to be flushed before the ALTER TABLE completion.
Some history
A long time ago, all “ALTER TABLE” (DDLs) operations in MySQL were implemented by creating a new table with the new structure, then copying the content of the original table to the new table, and finally renaming the table. During this operation the table was locked to prevent data inconsistency.
Then, for InnoDB tables, the new algorithms were introduced, which do not involve the full table copy and some operations do not apply the table level lock – first the online …
[Read more]Usually one would expect that ALTER TABLE with ALGORITHM=COPY will be slower than the default ALGORITHM=INPLACE. In this blog post we describe the case when this is not so.
One of the reasons for such behavior is the lesser known limitation of ALTER TABLE (with default ALGORITHM=INPLACE) that avoids REDO operations. As a result, all dirty pages of the altered table/tablespace have to be flushed before the ALTER TABLE completion.
Some history
A long time ago, all “ALTER TABLE” (DDLs) operations in MySQL were implemented by creating a new table with the new structure, then copying the content of the original table to the new table, and finally renaming the table. During this operation the table was locked to prevent data inconsistency.
Then, for InnoDB tables, the new algorithms were introduced, which do not involve the full table copy and some operations do not apply the table level lock – first the online …
[Read more]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]
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:-
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 :