|Showing entries 1 to 30 of 74||Next 30 Older Entries|
I have been meaning to update some systems to MariaDB 10.0 and finally had a bit of time to get around to that. The documentation of specifics of what’s needed to go from MariaDB 5.5 to 10.0 can be found here and while it’s not very long it seems there’s little to actually do.
Having already upgraded some servers from MySQL 5.5 to 5.6 the process and appropriate configuration changes were very similar so all in all a rather non event.
One thing which is always a concern if systems can not be down for long is the time to do the upgrade. While you see many blog posts talking about taking a backup via mysqldump and then loading it all back this is not really an option on many systems I manage and a replacement[Read more...]
Astute readers of the release notes for MariaDB 10.0.12 will notice that there is a line that reads: performance_schema is now disabled by default.
We didn’t come to this decision by accident. Recently at the SkySQL company meeting in Budapest, we did have some time to break out into our usual working teams to talk about our daily operations. Team MariaDB had a debate about PERFORMANCE_SCHEMA and how it was left on by mistake in 10.0 GA as there was a decision to turn it off. Personally, I don’t like introducing such changes in a GA release, and there was no archive of such a discussion, so the next best thing to do was to ask the MariaDB developers and users via a post to both[Read more...]
I’ve just released the 1.0.1 version of the MySQL sys schema. It contains a few bug fixes (including a contribution from Joe Grasse, thanks Joe!), and a number of new helper procedures around viewing and altering configuration for consumers and instruments, contributed by the MySQL QA team, as they’ve started to incorporate more Performance Schema trace data in to their automated testing.
Next up is the 1.1.0 release, that also includes a bunch of new host summary views that were contributed by Arnaud Adant (of the MySQL Support team). I have a number of new things in development to add as well before then though.
Let me know if there are things you’d like to see as well, maybe I can find time to work on those too.
This question was asked at support by a customer to solve a difficult issue.
How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?
If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !
Here is the solution using the performance_schema in MySQL 5.6 :
SELECT host, SUM(essbben.count_star) AS total_statements, format_time(SUM(essbben.sum_timer_wait)) AS total_latency, format_time(SUM(essbben.sum_timer_wait) / SUM(count_star)) AS avg_latency FROM performance_schema.events_statements_summary_by_host_by_event_name essbben GROUP BY host ORDER BY SUM(sum_timer_wait) DESC;
Here is the result[Read more...]
A couple of weeks ago, shortly after Vadim wrote about Percona Cloud Tools and using Slow Query Log to capture the data, Mark Leith asked why don’t we just use Performance Schema instead? This is an interesting question and I think it deserves its own blog post to talk about.
First, I would say main reason for using Slow Query Log is compatibility. Basic Slow query log with microsecond query time precision is available starting in MySQL 5.1, while[Read more...]
MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes. Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job. Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.
So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the[Read more...]
I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.
First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:
mysql> SELECT * FROM performance_schema.setup_instruments -> WHERE NAME = 'wait/lock/metadata/sql/mdl'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | YES | YES | +----------------------------+---------+-------+
“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.
“TIMED” (referring to event timing) reports[Read more...]
I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.
In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.
In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:
INSTALL SONAME 'metadata_lock_info';
Then, you will have the table.
To see it in action:
mysql> create table t (id int) engine=myisam; mysql> begin; mysql> select * from t;
mysql> alter table t[Read more...]
If you’ve had any experience with these, you’ll know finding them isn’t always the most straight-forward task.
So I was glad to see metadata lock instrumentation added to MySQL 5.7.3 as part of performance_schema, which makes tracking these down a breeze! (Note this is only in 5.7.3 currently, and therefore is some time from being GA as of today)!
To use these, performance_schema must be enabled (i.e., performance_schema=1 in your config file).
But, also, the metadata_locks instrument is disabled by default, so even if you enable the[Read more...]
Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later). We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date. A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me. The event was streamed and recorded, and you can view the full presentation on YouTube. The slide deck can be found here.
The discussion around proposed[Read more...]
Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.
The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.
use performance_schema; update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest'); truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest; do sleep(60); select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from[Read more...]
Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.
So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.
Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn’t already in the host_cache-table, made a connection attempt to the mysql server[Read more...]
After a slight delay (travel and catching up with “real work”), I’ve now uploaded the talks that I gave at MySQL Connect and Oracle Open World.
They are available on my Presentations Page, and inline below for convenience. The “Introduction to MySQL Enterprise Monitor” talk was actually a full demo, but there are some screenshots of MEM 3.0 in there if you’re interested in seeing a high level picture of what it looks like now.
Thanks to all that attended my talks, I got a lot of good questions and feedback!Performance Schema and ps_helper [Read more...]
To only slightly misquote one of the greatest movies of all times:
You keep using that option. I do not think it means what you think it means.
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I’m writing this blog post to help others who hold similar misconceptions of what this option does.
Many, if not most, MySQL DBAs are familiar with “host blocked”[Read more...]
The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts. You can do this with the following query:
mysql> SELECT -> ip, -> host, -> host_validated, -> sum_connect_errors -> FROM performance_schema.host_cache\G *************************** 1. row *************************** ip: 192.168.2.4 host: TFARMER-MYSQL.wh.oracle.com host_validated: YES sum_connect_errors: 3 1 row in set (0.02 sec)
That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked. Due to Bug#69807,[Read more...]
In an earlier post, I commented on clients and utility programs which seem to no longer be useful, and opened (or referenced existing) public bug reports to deprecate and remove, where appropriate. That effort came actually was the product of a different initiative: I was looking for clients which might leverage the full spectrum of MySQL protocol commands in an effort to understand whether certain protocol commands are in use. I thought I would share my observations, in the hope that we might also get feedback from others regarding usage of these commands. And even though it’s no longer spring (I started this post in April), I finally found time to finish this post.
Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools. Mark Leith even provided a patch, but it’s never been incorporated. With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:
mysql> SELECT SUM(count_star) as pings -> FROM events_statements_summary_global_by_event_name -> WHERE event_name = 'statement/com/Ping'; +-------+ | pings | +-------+ | 12 | +-------+ 1 row in set (0.02 sec)
Not only does PERFORMANCE_SCHEMA provide capabilities which mirror the STATUS
Idle connections can cause problems both at the application side, increasing the risk of connection timeouts for applications where persistent connections are used, and the server side, where resources remain allocated to idle connections. Any application with persistent connections, such as a JDBC application using a connection pool, will have periods where connections are idle – but it’s good to know how much time is spent idle. Too much idle time might mean connections pools configured to allow too many connections to sit idle in a connection pool, or not properly doing connection pool maintenance.
PERFORMANCE_SCHEMA in MySQL 5.6 makes it trivial to measure absolute time spent waiting. This will show total, average[Read more...]
I’ve often wished that PROCESSLIST exposed when a connection was first established, and I find myself wishing for this information more now with MySQL 5.6. Improvements to PERFORMANCE_SCHEMA make it trivial to see how much time is being spent in various operations for a given connection – but it would make some analysis (“what percentage of connection time is spent doing X?”) easier.
That said, it is possible to approximate connection age with PERFORMANCE_SCHEMA in MySQL 5.6. I say “approximate” because results will vary based on what instrumentation exists, is enabled, and is collecting timing data. That’s because we’re just doing a SUM() on the SUM_TIMER_WAIT column for all instrumented waits. Here’s an example (FYI, I’m using[Read more...]
In a previous post, I described how to leverage PERFORMANCE_SCHEMA in MySQL 5.6 to identify connections which had not been properly closed by the client. One possible cause of connections being closed without explicit request from the client is when another process issues a KILL CONNECTION command:
mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Aborted_clients | 0 | +-----------------+-------+ 1 row in set (0.02 sec) mysql> KILL CONNECTION 3; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients'; +-----------------+-------+ | Variable_name | Value |[Read more...]
MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection. The manual describes potential causes as follows:
MySQL 5.6 introduced major advances to monitoring made via PERFORMANCE_SCHEMA, but also made a change in how it binds to the network by default. In MySQL 5.5, the –bind-address configuration option defaulted to “0.0.0.0″, meaning only IPv4. This changed to “*” in MySQL 5.6, accepting connections on both IPv6 and IPv4 interfaces. Somehow (I’ve not looked into it yet), my (unsupported) WindowsXP installation now refuses to bind to IPv4,[Read more...]
|Showing entries 1 to 30 of 74||Next 30 Older Entries|