Ideas, releases, practical guides, and perspectives from the people building with MySQL.
This release has been superseded by 5.7.23-31.31.2 after a critical regression was found. Please update to the latest release.
Percona is glad to announce the release of Percona XtraDB Cluster 5.7.23-31.31 on September 26, 2018. Binaries are available from the downloads section or from our software repositories.
Percona XtraDB Cluster 5.7.23-31.31 is now the current release, based on the following:
[Read more]Thanks for joining us this week for our webinar on monitoring MySQL, MariaDB, PostgreSQL and MongoDB with freely available community tools and more specifically one: ClusterControl Community Edition. The replay and slides are now available to watch on our website.
Monitoring is essential for operations teams to ensure that databases are up and running. However, as databases are increasingly being deployed in distributed topologies based on replication or clustering, what does it mean to our monitoring infrastructure? Is it ok to monitor individual components of a database cluster, or do we need a more holistic systems approach? Can we rely on SELECT 1 as health check when determining whether a …
[Read more]In a DBA’s day to day activities, we are doing Archive operation on our transnational database servers to improve your queries and control the Disk space. The archive is a most expensive operation since its involved a huge number of Read and Write will be performed. So its mandatory to run the archive queries in …
The post Archive MySQL Data In Chunks Using Stored Procedure appeared first on SQLgossip.
In a DBA’s day to day activities, we are doing Archive operation on our transnational database servers to improve your queries and control the Disk space. The archive is a most expensive operation since its involved a huge number of Read and Write will be performed. So its mandatory to run the archive queries in chunks. The archive is depended on business use. Many of us need a copy of the data on an archive database to refer later. To perform the archive we can just simply run the delete query with the limit. But we need to run the query again and again until the matched rows count is 0. We can create a procedure to do this in a while loop. I have created one such procedure to archive many tables.
Image Source: Brent Ozar Unlimited
Why Archive is an expensive operation?
Generally how we are arching the data is *delete from table_name where column_name <= some_value;* If you …
[Read more]This post is a followup to my previous article https://www.percona.com/blog/2018/08/29/scaling-io-bound-workloads-mysql-cloud/
In this instance, I want to show the data in different dimensions, primarily to answer questions around how throughput scales with increasing IOPS.
A recap: for the test I use Amazon instances and Amazon gp2 and io1 volumes. In addition to the original post, I also tested two gpl2 volumes combined in software RAID0. I did this for the following reason: Amazon cap the single gp2 volume throughput to 160MB/sec, and as we will see from the charts, this limits InnoDB performance.
Also, a reminder from the previous post: we can increase gp2 IOPS by increasing volume size (to the top limit 10000 IOPS), and for io1 we can increase IOPS by paying per additional IOPS.
Scaling with InnoDB …
[Read more]A query plan uses loose index scan if “Using index for group-by” appears in the “Extra” column of the EXPLAIN output. In some plans though, “Using index for group-by (scanning)” appears. What does “(scanning)” mean and how is it different from the regular loose index scan?…
Continuent Clustering support true distributed multimaster clustering. In this topology, there are cross-site replicator services for each remote site. In a 3-site configuration, there are a total of 9 replication streams to manage.
Continuent Clustering also offers a graphical administration tool called the Tungsten Dashboard to help with your management burden. The GUI makes the deployment much easier to visualize and administer.
For our example, we will have a Composite Multimaster dataservice called global with three active, writable member clusters (one per site), east, west and north.
Dashboard Summary View
In the summary, collapsed view, the composite service and all member clusters are listed with associated information and controls. Note that the Type for the composite dataservice global is CompMM …
[Read more]When I worked at Borland on InterBase (one of the first MVCC relational databases), the saxophone-playing founder of Borland, Philippe Kahn, would talk about the ‘forehead install’ at nearly every meeting that I attended. Installs should be easy, he would say. As easy as hitting your forehead to the space bar. In fact, Kahn claimed that installing software with another product “could be equivalent to a heart transplant”.
Although MySQL installs are not complex, there is one tool that can make installs and test driving new software more palatable – Docker.
There are two advantages of testing MySQL 8.0 with Docker: (1) installing and starting MySQL 8.0 is even simplier with Docker and (2) changing my.cnf values with the SET PERSIST option can also help you navigate the Docker environment.
Installing Docker, Starting MySQL, and Connecting to MySQL
First, you grab Docker: …
[Read more]The MariaDB Foundation is pleased to announce the availability of MariaDB 10.2.18, the latest stable release in the MariaDB 10.2 series, as well as MariaDB Connector/Node.js 2.0.0, the second alpha release of the new 100% JavaScript non-blocking MariaDB client for Node.js, compatible with Node.js 6+. See the release notes and changelogs for details. Download MariaDB […]
The post MariaDB 10.2.18 and MariaDB Connector/Node.js 2.0.0 now available appeared first on MariaDB.org.
Lately, I worked on several queries which started returning wrong results after upgrading MySQL Server to version 5.7 The reason for the failure was derived merge optimization which is one of the default
optimizer_switch
options. Issues were solved, though at the price of performance, when we turned it
OFF
. But, more importantly, we could not predict if any other query would start returning incorrect data, to allow us to fix the application before it was too late. Therefore I tried to find reasons why
derived_merge
can fail. Analyzing the problem
In the first run, we turned SQL Mode
ONLY_FULL_GROUP_BY
on, and this removed most of the problematic queries. That said, few of the queries that were successfully working with
ONLY_FULL_GROUP_BY
were affected.
A quick search in the …
[Read more]