Starting with the 8.0 optimizer labs release the MySQL server now supports descending indexes. As I will detail in this post, this new feature can be used to eliminate the need for sorting results, and lead to performance improvements in a number of queries.…
Oct
12
2016
Oct
11
2016
In this blog post, I’ll look at how to use the loose_ option prefix in my.cnf in MySQL.
mysqld throws errors at startup – and refuses to start up – if a non-existent options are defined in the my.cnf file.
For example:
2016-10-05 15:56:07 23864 [ERROR] /usr/sbin/mysqld: unknown variable 'bogus_option=1'
The MySQL manual has a solution: use
the loose_ prefix option in my.cnf file.
In the prior example, if we specify
loose_bogus_option=1 in the my.cnf file, instead of
bogus_option=1, mysqld starts successfully and
ignores that option.
This is useful in three situations:
- Using …
Oct
11
2016
This blog discusses the gradual end of MyISAM in MySQL.
The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.
(Check your MySQL installation, you may still have MyISAM tables).
MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.
In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:
- After …
Oct
11
2016
PS_history is a tool which collects historical snapshots of the
PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values
over time, for example, it is possible to look at the 95 th
percentile response time for a query over time.
PS_history is stored procedure and event based, and thus it
resides entirely inside of the database with no external
dependencies. It uses a clever technique to capture all of the
P_S data in one consistent snapshot. This ensures that all of the
sys_history views (bundled now with PS_history) have a consistent
set of data.
By default, as long as the event_schedule is enabled, PS_history
will collect data every 30 seconds. If a snapshot takes 30
seconds, there will be a 30 second delay before the next snapshot
starts. This value can be changed by calling the
`ps_history`.`set_collection_interval`(N) where N is the number
of seconds between samples.
The `sys_history` schema is …
Oct
11
2016
PS_history is a tool which collects historical snapshots of the
PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values
over time, for example, it is possible to look at the 95 th
percentile response time for a query over time.
PS_history is stored procedure and event based, and thus it
resides entirely inside of the database with no external
dependencies. It uses a clever technique to capture all of the
P_S data in one consistent snapshot. This ensures that all of the
sys_history views (bundled now with PS_history) have a consistent
set of data.
By default, as long as the event_schedule is enabled, PS_history
will collect data every 30 seconds. If a snapshot takes 30
seconds, there will be a 30 second delay before the next snapshot
starts. This value can be changed by calling the
`ps_history`.`set_collection_interval`(N) where N is the number
of seconds between samples.
The `sys_history` schema is …
Oct
11
2016
The MySQL Connector/Python Team is pleased to announce the newest MySQL Connector/Python 2.1.4, the second GA version of 2.1 release series of the pure Python database driver for MySQL.
This release includes a number of improvements for usability, stability and security.
Changes in MySQL Connector/Python 2.1.4 GA Security Notes
The linked OpenSSL library for Connector/Python Commercial has
been updated to version 1.0.1q. Issues fixed in the new OpenSSL
version are described at
http://www.openssl.org/news/vulnerabilities.html. This change
does not affect Oracle-produced MySQL Community builds of
Connector/Python, which use the yaSSL library instead.
The change also does not affect connections made using any pure
Python implementation of Connector/Python, for which the version
of OpenSSL used is whatever is installed on the system.
Bugs fixed
- Connector/Python failed to establish connections …
Oct
11
2016
To focus on just one point for OTN appreciation day on October 11 2016 and to the benefit of all users of MySQL is to consider the extremely convenient and rich value of information available in the MySQL Performance Schema to understand what SQL queries are running in a MySQL instance now. The MySQL Performance Schema in MySQL 5.6 is enabled by default, (performance_schema=on).
The following one off SQL statement will enable the
instrumentation of SQL statements in the most detailed level of
assessment.
The following query will show you the longest running queries in
your database at this present time.
This ease of accessing what is running in a MySQL instance replaces many different and creative techniques …
[Read more]
Oct
11
2016
Hi all MySQL & Friends fans,
I’ve the pleasure to announce you that the CfP for MySQL & Friends Devroom for Fosdem 2017 is now open.
FOSDEM 2017 edition will be held February 4 and 5 in Brussels like every year since 2000.
The MySQL & Friends Devroom is back again on Saturday from 9.00AM.
What is FOSDEM? It stands for the “Free and Open Source Software Developers’ European Meeting.” It’s a free event that offers open-source communities a place to meet, share ideas and collaborate.
CfP is open until December 6th (St Nicolas)!
The submission can be done using https://fosdem.org/submit.
If you don’t have an account yet, …
[Read more]
Oct
11
2016
Graphs are important, as they’re your window onto your monitored systems. ClusterControl comes with a predefined set of graphs for you to analyze, these are built on top of the metric sampling done by the controller. Those are designed to give you, at first glance, as much information as possible about the state of your database cluster. You might have your own set of metrics you’d like to monitor though. Therefore ClusterControl allows you to customize the graphs available in the cluster overview section and in the Nodes -> DB Performance tab. Multiple metrics can be overlaid on the same graph.
Overview tab
Let’s take a look at the cluster overview - it shows the most important information aggregated under different tabs.
You can see there graphs like “Cluster Load” and “Galera - Flow Ctrl” along with couple of others. If this is not enough for you, you can click on “Dash Settings” and then pick …
[Read more]
Oct
11
2016
About one month ago, the MySQL team at Oracle released MySQL 8.0, with a large list of changes. One of the most interesting features in the new release is also one that does not show up much, also because the team has gone to great length to keep most of its implementation hidden: the data dictionary.
What makes the data dictionary so interesting, despite its scarce visibility, is the effect that it has on performance. Up to MySQL 5.7, searching the information_schema was an onerous operation, potentially crippling the system. In MySQL 8.0, the same operations are …
[Read more]