|Showing entries 1 to 30 of 69||Next 30 Older Entries|
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...]
I’ve previously written about several problems which can benefit from additional visibility provided by PERFORMANCE_SCHEMA in MySQL 5.6, and it’s time to add to that list. A very common problem involves connections which are not properly closed – they simply idle until they reach wait_timeout (or interactive_timeout, depending on the client flags set), and the server terminates the connection. Who knows what the root cause is – perhaps the client terminated without cleaning up connections, or maybe there was just no load, or maybe the network cable was unplugged. It’s something application developers[Read more...]
I was testing out the latest MySQL 5.6 on Windows (having used the auto-installer) and happened to notice my RAM usage (via Windows Task Manager) was reporting quite a high value, when I had very modest ram/buffer settings (should have been around 40M, but instead it was around 400M).
After double/triple-checking my settings to make sure I didn’t overlook something obvious, I searched the bugs database, and ran across bug #68287:
Turns out, using the auto-installer set the value of table_definition_cache=1400, when the minimum value is 400. Reducing it to 400, and restarting MySQL immediately lowered the RAM usage, and is the “work-around”[Read more...]
While Domas may have rather effictively trolled the discussion with his post on howto configure table/user statistics (which gave me a good chuckle I do have to say), it’s at least incorrect for Percona Server as you have to enable the “userstat” server option :)
That being said, once enabled there are no extra configuration variables to think about. This is a huge advantage over configuring PERFORMANCE_SCHEMA - which has a total of THIRTY configuration options (31 if you include the global enable/disable option).
Some of these thirty odd configuration variables are only going to matter if[Read more...]
|Showing entries 1 to 30 of 69||Next 30 Older Entries|