As noted in an earlier post, MySQL Server 5.7 prefers and
enables SSL/TLS connections by default. That’s great and
useful progress towards secure connections, but we know that not
all SSL/TLS ciphers are created equal – some are older and more
vulnerable. Furthermore, some recent vulnerabilities rely
on the ability to negotiate less-secure ciphers during the
handshake. Monitoring which ciphers are used can help
identify connections using low-grade ciphers, but also to build
an appropriate restricted cipher list. Using
improvements to PERFORMANCE_SCHEMA
introduced in
5.7, you can now easily do this – and this post will show you
how.
The cipher used for each TLS connection is stored in a …
With the changes to performance_schema in MySQL 5.7 Development
Milestone Release it is now possible to analyze and profile the
execution of stored programs. This is highly useful if you
develop more complex stored procedures and try to find the
bottlenecks. The "old" performance_schema up to MySQL 5.6 only
reported a CALL statement with a runtime, but no information on
statements that were executed WITHIN the stored procedure. Now
let's try this in the latest MySQL 5.7.6 DMR release. After
creating some test table and a test stored procedure we need to
activate the events_statements_history_long consumer, which is
OFF by default:
mysql> UPDATE setup_consumers SET ENABLED="YES"
WHERE NAME = "events_statements_history_long";
Then let's call the stored procedure that we want to
inspect:
mysql> CALL …
The performance_schema is a powerful tool for analyzing MySQL
performance and behavior. One aspect of the performance_schema is
that the view of the data is "right now", and very little
historical information is present. You can see that there are 10
threads connected right now, but what about five minutes
ago?
ps_history
ps_history is a set of stored routines and events
for periodically collecting the data in the performance_schema
into another schema called ps_history. The ps_history schema
contains a copy of each performance_schema view as a real table,
and timestamp and server_id columns have been added to each
table. Periodically (by default every 30 seconds) the
performance_schema data is written into the history tables.
ps_history comes as one script (setup.sql) which will create the
ps_history schema, the tables within it, and …
The performance_schema is a powerful tool for analyzing MySQL
performance and behavior. One aspect of the performance_schema is
that the view of the data is "right now", and very little
historical information is present. You can see that there are 10
threads connected right now, but what about five minutes
ago?
ps_history
ps_history is a set of stored routines and events
for periodically collecting the data in the performance_schema
into another schema called ps_history. The ps_history schema
contains a copy of each performance_schema view as a real table,
and timestamp and server_id columns have been added to each
table. Periodically (by default every 30 seconds) the
performance_schema data is written into the history tables.
ps_history comes as one script (setup.sql) which will create the
ps_history schema, the tables within it, and …
For months when speaking with customers I have been positioning Percona Cloud Tools (PCT) as a valuable tool for the DBA/Developer/SysAdmin but only recently have I truly been able to harness the data and make a technical recommendation to a customer that I feel would have been very difficult to accomplish otherwise.
Let me provide some background: I was tasked with performing a Performance Audit for one of our customers (Performance Audits are extremely popular as they allow you to have a MySQL Expert confirm or reveal challenges within your MySQL environment and make your database run faster!) and as part of our …
[Read more]We have been using SHOW ENGINE INNODB MUTEX command for years. It shows us mutex and rw-lock information that could be useful during service troubleshooting in case of performance problems. As Morgan Tocker announced in his blog post the command will be removed from MySQL 5.7 and we have to use performance_schema to get that info.
The documentation of MySQL also says that most of the command output has been removed from 5.6 and that we can find similar info in performance_schema. It doesn’t show any examples of how to use performance_schema or what is the query we need to use from now on. It is also important to mention that 5.6 doesn’t show any warning about the feature being deprecated.
This is a short blog post to show how to configure performance_schema and get the info we need. Hoping it will end …
[Read more]Some time ago, Peter Zaitsev posted a blog titled “How well does your table fits in innodb buffer pool?” He used some special INFORMATION_SCHEMA tables developed for Percona Server 5.1 to report how much of each InnoDB table and index resides in your buffer pool.
As Peter pointed out, you can use this view into the buffer pool to watch a buffer pool warm up with pages as you run queries. You can also use it for capacity planning. If you expect some tables need to be fully loaded in the buffer pool to be used efficiently, but the buffer pool isn’t large enough to hold them, then it’s time to increase the size of the buffer pool.
The problem, however, was that system tables change from version to version. Specifically, the INNODB_BUFFER_POOL_PAGES_INDEX table no longer exists in Percona Server 5.6, and the …
[Read more]
Today I was doing some tests with XA transactions in MySQL
5.6.
The output of the XA RECOVER command to list transactions was
hard to read because of the representation of the data
column:
The good news is that 5.7 has transaction information in
performance_schema:
mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode[Read more]
-> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id | isolation_level | state | xid | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
| NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE |
| 421476507015704 | REPEATABLE READ | …
One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and
corresponding views in sys
schema
And as troubleshooting freak I have huge reason to greet this
feature.
Before version 5.7 we had very limited abilities to diagnose
memory issues in MySQL. We could use operating system tools, such
as vmstat, top, free, but they only showed what MySQL server uses
memory, but do not show how. In version 5.7 things
changed.
Lets examine what can we study about memory usage by MySQL
Server.
At first, this is total amount of memory, used by all internal MySQL
structures:
mysql> select * from …
When I talk about troubleshooting I like to repeat: "Don't grant
database access to everybody!" This can sound a bit weird having
one can give very limited read-only access.
But only if ignore the fact what even minimal privileges in MySQL
allows to change session variables, including those which control
server resources. My favorite example is "Kill MySQL server with
join_buffer_size". But before version 5.7 I could only recommend
this, but not demonstrate. Now, with help of memory summary
tables in Performance Schema, I can show how unprivileged user
can let your server to use great amount of swap.
At first lets create a user account with minimal privileges and
login.
$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor. Commands end with ; or
\g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution
…