Tag Archives: MySQL 5.6

Practical P_S: How idle are your connections?

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 and maximum idle times by account:
Continue reading Practical P_S: How idle are your connections?

Practical P_S: How old are your connections?

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 the format_time() function from Mark Leith’s awesome ps_helper scripts to convert from picoseconds to something meaningful to me):
Continue reading Practical P_S: How old are your connections?

Practical P_S: From which hosts are connections being attempted?

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:

It goes on to make the following statement:

If these kinds of things happen, it might indicate that someone is trying to break into your server! Messages for these types of problems are logged to the general query log if it is enabled. Continue reading Practical P_S: From which hosts are connections being attempted?

Practical P_S: Extending PROCESSLIST

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 “”, 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, which caused surprising problems for certain tools that seem to internally map “localhost” to IPv4-specific, where connections fail.  In working through this problem, I found myself wishing that PROCESSLIST output included information about which mechanism or interface was being used by each connection.  Fortunately, we can leverage PERFORMANCE_SCHEMA to extend PROCESSLIST in meaningful ways – this post aims to demonstrate how to do this by adding information about the interface as an example.

Continue reading Practical P_S: Extending PROCESSLIST

Practical P_S: Finding which accounts fail to properly close connections

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 – particularly those using persistent connections managed by a pool – run into frequently.

If you are a DBA rather than a developer, though, your only real clue that something is wrong may be a perpetually increasing Aborted_clients status variable counter. The manual has a page dedicated to solving such (and related) connection problems, and it references tools such as the general query log and error log.  The Aborted_clients status variable is useful to answer the question, “how many connections have been closed without an explicit quit request from the client?”  And prior to 5.6, that’s about as much information as you could expect to get:

mysql> show global status like 'aborted_clients';
| Variable_name   | Value |
| Aborted_clients | 5     |
1 row in set (0.00 sec)

With PERFORMANCE_SCHEMA in 5.6, we can isolate the problem to specific accounts, and we can calculate the percentage of client connections which were terminated without an explicit quit command from the client.  You can do that with the following query:

    performance_schema.events_statements_summary_by_account_by_event_name ess
    performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST)
    ess.EVENT_NAME = 'statement/com/Quit'

The easiest way to test this is to make a handful of connections, issue SET @@session.wait_timeout = 1 – here’s the result of the above query after doing so:

| USER | HOST      | not_closed | pct_not_closed |
| root | localhost |          4 |        44.4444 |
| ODBC | localhost |          1 |       100.0000 |
2 rows in set (0.00 sec)

Knowing which accounts are failing to properly close connections can help quickly spotlight where further investigation should be focused.  And with MySQL 5.6, DBAs can get that information without resorting to the general query log or via application logs.

mysql_upgrade is now version-specific by default

You’ve just completed an upgrade from MySQL 5.5 to 5.6.  You followed the upgrade instructions in the manual, and ran mysql_upgrade.  But when you start MySQL 5.6, you still see the following error messages like the following in the server error log:

2013-03-26 16:45:51 5040 [ERROR] Column count of mysql.events_waits_current is w
rong. Expected 19, found 16. Created with MySQL 50520, now running 50610. Please
use mysql_upgrade to fix this error.
2013-03-26 16:45:51 5040 [ERROR] Column count of mysql.events_waits_history is w
rong. Expected 19, found 16. Created with MySQL 50520, now running 50610. Please
use mysql_upgrade to fix this error.

What went wrong?

Continue reading mysql_upgrade is now version-specific by default

Finding the source of problematic queries

Many MySQL users are familiar with using slow query logs and tools such as mysqldumpslow to identify poor-performing SQL commands, and MySQL 5.6 introduces new powerful tools in PERFORMANCE_SCHEMA.  Both allow you to identify the date/time and the user account from which the command was issued, which is helpful – but if you’re using MySQL Enterprise Monitor (MEM), you can immediately identify the actual line of code responsible for the SQL command in question.  This happens to be one of my favorite and powerful features of MEM, but it’s frequently overlooked by new and experienced MEM users alike, so I’m writing the post to highlight it.

MySQL Enterprise Monitor, of course, is a commercial product that’s part of the MySQL Enterprise subscription.  But it’s freely-available under 30-day trial terms for evaluation from Oracle Software Delivery Cloud – if you aren’t a commercial customer, consider downloading MEM to see what it can do for you.  And if you are a MySQL Enterprise subscriber who hasn’t deployed MEM, or haven’t yet explored some of the more advanced features, now’s the time to do so.

Continue reading Finding the source of problematic queries

How to tell whether MySQL Server uses yaSSL or OpenSSL

Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL.  yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead.  Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request.  It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for have_ssl (I’m sure it made sense at the time, right?).  Continue reading How to tell whether MySQL Server uses yaSSL or OpenSSL