The latest from the MySQL community

I did more CTE blogging: check it out here and here !

I’ve been wanting an easy way to play around with MySQL Cluster lately, the latest 7.5 release is now based on the MySQL 5.7 branch, so it also has the sys schema packaged, adding more areas for me to add sys integration that is more cluster specific – especially given all of the new tables that were added within ndbinfo.

There’s a couple of examples of docker images that wrap older MySQL Cluster packages out there, but:

• Nothing up to date, I wanted to use MySQL Cluster 7.5.4
• Nothing that uses docker networks, rather than the old link style
• Nothing that helps to orchestrate starting the containers, I don’t want anything fancy, just to start a Cluster of a certain shape (n of …

Here is the third in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release. In the first post we had explored the new SQL syntax, and in the second we had applied it to generating series.…

Disk space issues are common, and they’re often difficult to solve quickly. One way to recover some space is by enabling InnoDB compression.

First, of course, you want to make sure you’ve covered alternative solutions. Can you archive data? Do partitioning/sharding? These generally involve application changes and can take longer.

You may need to first do conversion to InnoDB.

While compression is available for MyISAM via myisampack, and this can be useful for some use cases (for example, if you are rotating out tables on a monthly basis), it makes the tables read-only, so generally you will want to first convert MyISAM tables to InnoDB.

Things to watch for in the schema: After working on functional and performance issues with full-text indexes after conversion to InnoDB, I wouldn’t recommend it. Application changes are also required to rewrite queries. You can consider outsourcing these to a tool …

InnoDBOnline buffer pool resize:

• Configure innodb_buffer_pool_size offline (at startup) or online, while the server is running 

• The operation is performed in chunks

• Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option      

• Buffer pool size always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

• Default innodb_buffer_pool_chunk_size is 128M

Reference:


https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html



Online RENAME INDEX operation:

• ALTER TABLE RENAME INDEX requires only meta-data change.

Reference:

…

Prepare the RPM repository server

Install the software to create a RPM repository server (on an Ubuntu web server):

shell> sudo apt-get install createrepo

Create the directory structures and initialize the repositories:

shell> sudo mkdir -p /var/www/html/repos/centos/7/os/x86_64
shell> sudo createrepo /var/www/html/repos/centos/7/os/x86_64/

Copy the MySQL Enterprise RPM packages to the repository:

shell> sudo cd /var/www/html/repos/centos/7/os/x86_64/
shell> sudo unzip -v /download/V790254-01.zip   # MySQL Enterprise Server 5.7.16
Archive:  /download/V790254-01.zip
   Length  Method      Size Cmpr    Date    Time   CRC-32   Name
---------  ------  -------- ---- ---------- ----- --------  ----
 23979568  Stored  23979568   0% 2016-09-29 16:56 ae6693b1  mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm
 45327096  Stored  45327096   0% 2016-09-29 16:56 bb83c965 …

If you have already Red Hat Enterprise Linux 7 (RHEL 7) or CentOS 7 in use you probably found out, that it is not that easy to install MySQL Enterprise Server because there are already pre-installed MariaDB 5.5 libraries:

shell> yum localinstall mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-server-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-common-5.7.16-1.1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-client-5.7.16-1.1.el7.x86_64
Marking mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-libs-5.7.16-1.1.el7.x86_64
Marking mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-server-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-server-5.7.16-1.1.el7.x86_64
Marking …

This tutorial shows how to install an Ubuntu 16.10 (Yakkety Yak) server (with Apache2, BIND, Dovecot) for the installation of ISPConfig 3.1, and how to install ISPConfig. ISPConfig 3 is a web hosting control panel that allows you to configure the following services through a web browser: Apache or nginx web server, Postfix mail server, Courier or Dovecot IMAP/POP3 server, MySQL, BIND or MyDNS nameserver, PureFTPd, SpamAssassin, ClamAV, and many more. This setup covers the installation of Apache (instead of nginx), BIND (instead of MyDNS), and Dovecot (instead of Courier).

The Madrid MySQL Users Group has its next meeting on Tuesday, 22nd November 2016.  Giuseppe Maxia will be giving a presentation MySQL document store: SQL and NoSQL united and I’ll be providing a brief summary of the new MySQL 8.0 and MariaDB 10.2 beta versions which were announced recently. There will also be an opportunity to … Continue reading MMUG16: MySQL document store: SQL and NoSQL united

The post MMUG16: MySQL document store: SQL and NoSQL united first appeared on Simon J Mudd's Blog.

In this blog post, we’ll look at how replication triggers a Performance Schema issue on Percona XtraDB Cluster.

During an upgrade to Percona XtraDB Cluster 5.6, I faced an issue that I wanted to share. In this environment, we set up three Percona XtraDB Cluster nodes (mostly configured as default), copied from a production server. We configured one of the members of the cluster as the slave of the production server.

During the testing process, we found that a full table scan query was taking four times less in the nodes where replication was not configured. After reviewing mostly everything related to the query, we decided to use perf.

We executed:

perf record -a -g -F99 -p $(pidof mysqld) -- sleep 60

And the query in another terminal a couple of times. …