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 …
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]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]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]This blog reports the results of Percona’s database security poll.
As Peter Zaitsev mentioned recently in his blog post on database support, the data breach costs can hit both your business reputation and your bottom line. Costs vary depending on the company size and market, but recent studies estimate direct costs ranging in average from $1.6M to 7.01M. Everyone agrees leaving rising security risks and costs unchecked is a recipe for disaster.
Reducing security-based outages doesn’t have a simple answer, but can be a combination of internal and external monitoring, support contracts, enhanced security …
[Read more]In this blog post, I’ll provide my thoughts about Consul for ProxySQL service discovery and automation.
I approached Consul recently while looking for a service discovery and configuration automation solution for ProxySQL. My colleague Nik Vyzas wrote a great post on this topic, and I suggest you read it. I wrote this article to share my first impressions of Consul (for whomever it might interest).
Consul is a complete service discovery solution. In this respect it differs from its alternative etcd, which only provides a foundation to build such solutions.
Consul consists of a single, small binary (the Linux binary is 24MB). You just download it, edit the configuration file and start the program. It doesn’t …
[Read more]Just as you use a database like MySQL to store your application data, MySQL must also store its meta data (schema names, table definitions etc) somewhere. Traditionally this meta data storage has been split between many different locations (.FRM, .PAR, .OPT, .TRN and .TRG files).…
I’ve uploaded the slides from my latest talks at OpenWorld and Percona Live, available below. These are mostly an updated version of previous talks, with some new info added here and there..
MySQL sys schema deep dive (Oracle OpenWorld 2016)
Performance schema and sys schema (Percona Live Amsterdam 2016)
The slides of my joint tutorial with Kenny Gryp from Percona on MySQL InnoDB Cluster and Group Replication are now online.
We got nice questions during the session and very good feedback.
MySQL Group Replicatio in a nutshell – MySQL InnoDB Cluster from Frédéric Descamps
First of all I want to say thanks to Thomas that sent me feedback on part1 (see his comment). And indeed all three cases were able to defeat the datamasking.
I rewrote then the rules to also take those cases in consideration:
ProxySQL> DELETE FROM mysql_query_rules where rule_id INSERT INTO mysql_query_rules
(rule_id,active,username,match_pattern,replace_pattern,apply)
VALUES (1,1,'devel','`cc_num`',"cc_num",0);
ProxySQL> INSERT INTO mysql_query_rules
(rule_id,active,username,match_pattern,replace_pattern,apply)
VALUES (2,1,'devel','^[sS][eE][lL][eE][cC][tT] (.*)cc_num([ ,\n])(.*)',
"SELECT \1CONCAT(LEFT(cc_num,2),REPEAT('X',10)) cc_num\2\3",1);
However the current recursive implementation of ProxySQL (using
flagIN & flagOUT) is not an option to face the case where a …