Planet MySQL

Lesson 09: Managing Users and Privileges in MySQL

Notes/errata/updates for Chapter 9:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 9 includes pages 297 – 350.

In the fourth paragraph of this chapter, starting with “Most applications don’t need superuser privileges for day-to-day activities” they give you some reasons why you want to create users without the SUPER privilege. There are better reasons than the book gives, which are at the MySQL Manual page for the SUPER privilege.

In the section “Creating and Using New Users” (p. 300) they say “There’s no limit on password length, but we recommend using eight or fewer characters because this avoids problems with system libraries on some platforms.” You should ignore this, this book was written in 2006 and modern system libraries can handle more than 8 characters in a password. Also ignore it when they say the same thing in the section “Understanding and Changing Passwords” (p. 324).

In the section “Creating a New Remote User” at the very end (p. 214), it talks about using % as a host wildcard character. I want to point out that if there are no ACL’s set for a given host, MySQL will reject ALL connections from that host – even “telnet host 3306” will fail. So if you avoid using %, you are slightly more secure.

In the “Anonymous Users” section (p. 315), one fact that is not mentioned is that for all users, including the anonymous user, any database named “test” or that starts with “test_” can be accessed and manipulated. So an anonymous user can create tables in the “test” database (or even “test_application”) and fill it full of data, causing a denial of service when the disk eventually fills up. This fact is mentioned later in the chapter in the “Default User Configuration” section under “Linux and Mac OS X”, but it should be known earlier.

The “mysqlaccess” utility described in the section of that name (p. 320) is usually not used. These days, folks prefer the pt-show-grants tool. Here is a blog post with some examples of pt-show-grants.

In the section on “Removing Users” (p. 324), it says that if all the privileges are revoked, and a user only has GRANT USAGE, “This means the user can still connect, but has no privileges when she does.” This is untrue, as mentioned before, everyone can access and manipulate databases starting with “test”.

The section “Managing Privileges with SQL” is deprecated (p. 339-346, up to and including “Activating Privileges”). It used to be, back when this was written, that few people used the GRANT statements and more people directly manipulated the tables. These days, it’s the other way around, and due to problems like SQL injection, there are safeguards in place – for example, if you change the host of a user with an ALTER TABLE on the mysql.user table, the user will have all privileges dropped. Just about the only thing direct querying is used for, is to find who has the Super_priv variable set to ‘Y’ in the user table.

Supplemental material: I have a video presentation on security which includes ACLs and there are accompanying PDF slides.

Topics covered:
Creating and dropping local and remote users
Different MySQL privileges
SUPER privilege
GRANT and REVOKE syntax
Hosts and wildcards
Anonymous and default users
Checking privileges
Password management
Basic user security
Resource limit controls

Reference/Quick Links for MySQL Marinate

Top Performance Metrics to Monitor on MySQL (Connections & Buffer Pool Usage)

As a DBA, your top priority is to keep your databases and dependent applications running smoothly at all times. To this end, your best weapon is judicious monitoring of key performance metrics. In a perfect world, you’d want to be up-to-date regarding every aspect of your database’s activity – i.e. how many events occurred, how big they were, precisely when they happened and how long they took.

There certainly is no shortage of tools that can monitor resource consumption, provide instantaneous status snapshots, and generate wait analysis and graphs. The challenge is that some metrics can be expensive to measure, and, perhaps even more importantly, they can require a lot of work to analyze.

The purpose of Part-2 of the blog series is to narrow down the field to those performance metrics that provide the most value for the effort as well as present some tangible ways to capture and study them. It is by tracking the most useful metrics and reviewing them in the most informative way(s) that you will strike a balance between paranoid over-monitoring and firefighting unforeseen crises.

This topic is divided into 2 parts. In part 1, we:

  • examined the benefits of performance monitoring,
  • outlined the main performance metric categories,
  • listed the monitoring tools provided by MySQL, i.e.:
    • server variables
    • the Performance Schema
    • the sys Schema
  • learned how to monitor:
    • transaction throughput
    • query execution performance

This blog will focus on monitoring Database Connections and Buffer Pool metrics.

Connections

Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. Connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full. In this connection thread model, there are as many threads as there are clients currently connected.

It’s important to monitor your client connections because, once the database server runs out of available connections, new client connections are refused!

The MySQL connection limit defaults to 151, but it can be changed using the SET statement, so it’s best to not assume anything. The connection limit is stored in the @@max_connections variable:

SELECT @@max_connections; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+

The connection limit can be set at any time like so:

SET GLOBAL max_connections = 200;

To permanently set the connection limit, so that it persists once the server is restarted, add a line like this to your my.cnf configuration file:

max_connections = 200

Don’t be afraid to increase the number of max_connections significantly. According to MySQL docs, production servers should be able to handle connections in the high hundreds or thousands! Just bear in mind that there are some caveats when the server must handle large number of connections. For instance, thread creation and disposal become expensive when there are a lot of them. In addition, each thread requires server and kernel resources, such as stack space. Therefore, in order to accommodate many simultaneous connections, the stack size per thread must be kept small. This can lead to a situation where the stack size is either too small or the server consumes large amounts of memory.

The takeaway here is that your database server should have adequate amounts of processing power and memory to accommodate a large user base.

MySQL provides a few good metrics for monitoring your connections:

Variable What it represents Why you should monitor it Threads_connected This variable indicates the total number of clients that have currently open connections to the server. It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start. Threads_running The number of threads that are not sleeping. It’s good for isolating which connected threads are actively processing queries at any given time, as opposed to connections that are open but are currently idle. Connections The number of connection attempts (successful or not) to the MySQL server. It can give you a good idea of how many people and applications are accessing the database. Over time, these numbers reveal busiest times and average usage numbers. Connection_errors_internal The number of connections refused due to internal server errors, such as failure to start a new thread or an out-of-memory condition. Although MySQL exposes several metrics on connection errors, Connection_errors_internal is probably the most useful, because it is incremented only when the error comes from the server itself. Internal errors can indicate an out-of-memory condition or an inability to start a new thread.

We can use the MySQL show status command to show MySQL variables and status information. Here are a few examples:

SHOW GLOBAL STATUS LIKE '%Threads_connected%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 2 | +-------------------+-------+ SHOW GLOBAL STATUS LIKE '%Threads_running%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_running | 1 | +-----------------+-------+ SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 20 | +---------------+-------+ Aborted Client and Connections

Every time a client is unable to connect, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for the following reasons:

  • A client attempts to access a database but has no privileges for it.
  • A client uses an incorrect password.
  • A connection packet does not contain the right information.
  • It takes more than connect_timeout seconds to obtain a connect packet.

If these kinds of things happen, it might indicate that someone is trying to break into your server! If the general query log is enabled, messages for these types of problems are logged to it.

If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log.

Here’s how to view the number of aborted clients and connections:

mysql> SHOW GLOBAL STATUS LIKE 'Aborted_c%'; +-----------------------------------+------------+ | Variable_name | Value | +-----------------------------------+------------+ | Aborted_clients | 3 | | Aborted_connects | 8 | +-----------------------------------+------------+ Connection Errors

MySQL does an outstanding job of breaking down metrics on connection errors into different status variables:

SHOW GLOBAL STATUS LIKE 'Connection_errors%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Connection_errors_accept | 0 | +-----------------------------------+-------+ | Connection_errors_internal | 0 | +-----------------------------------+-------+ | Connection_errors_max_connection | 0 | +-----------------------------------+-------+ | Connection_errors_peer_address | 0 | +-----------------------------------+-------+ | Connection_errors_select | 0 | +-----------------------------------+-------+ | Connection_errors_tcpwrap | 0 | +-----------------------------------+-------+

Once all available connections are in use, attempting to connect to MySQL will cause it to return a “Too many connections” error and increment the Connection_errors_max_connections variable. Your best bet in preventing this scenario is to monitor the number of open connections and make sure that it remains safely below the configured max_connections limit.

Fine-grained connection metrics such as Connection_errors_max_connections and Connection_errors_internal can be instrumental in pinpointing the source of the problem. The following statement fetches the value of Connection_errors_internal:

SHOW GLOBAL STATUS LIKE 'Connection_errors_internal'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Connection_errors_internal | 2 | +----------------------------+-------+

Here are a couple of Monyog screens that monitor Current Connections and the Connection History, respectively:

Monyog monitor – current connection

Monyog monitor – Connection History

Buffer Pool Usage

MySQL’s default storage engine, InnoDB, uses a special storage area called the buffer pool to cache data for tables and indexes. Buffer pool metrics are categorized as resource metrics. As such, their main value is in the investigation, rather than the detection, of performance issues.

Configuring the Buffer Pool

You can configure various aspects of the InnoDB buffer pool to improve performance.

The buffer pool defaults to a relatively small 128MB. Ideally, you should increase the size of the buffer pool to as large a value as is practical, while leaving enough memory for other processes on the server to run without excessive paging. That typically amounts to about 80 percent of physical memory on a dedicated database server. The idea is that, the larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.

Please note:

  • InnoDB’s memory overhead can increase the memory footprint by about 10 percent beyond the allotted buffer pool size.
  • Once physical memory is exhausted, your system will resort to paging and performance will suffer significantly. Hence, if database performance starts to degrade while disk I/O is rising, it might be time to expand the buffer pool.

Buffer-pool resizing operations are performed in chunks, and the size of the buffer pool must be set to a multiple of the chunk size times the number of instances:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

The chunk size defaults to 128MB but is configurable as of MySQL 5.7.5. The value of both parameters can be checked as follows:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size"; SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

If querying the innodb_buffer_pool_chunk_size returns no results, the parameter is not tunable in your version of MySQL and can be assumed to be 128MB.

To set the buffer pool size and number of instances at server startup, invoke mysqld.exe with the following parameters:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

As of MySQL 5.7.5, you can also resize the buffer pool on-the-fly via a SET command specifying the desired size in bytes. For instance, with two buffer pool instances, you could set each to 4GB by setting the total size to 8GB:

SET GLOBAL innodb_buffer_pool_size=8589934592; Important InnoDB Buffer Pool Metrics

InnoDB Standard Monitor output, which can be accessed using “SHOW ENGINE INNODB STATUS”, provides a number of metrics pertaining to operation of the InnoDB buffer pool, under the BUFFER POOL AND MEMORY section. Here is some typical content:

---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2198863872 Dictionary memory allocated 776332 Buffer pool size 131072 Free buffers 124908 Database pages 5720 Old database pages 2071 Modified db pages 910 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4, not young 0 0.10 youngs/s, 0.00 non-youngs/s Pages read 197, created 5523, written 5060 0.00 reads/s, 190.89 creates/s, 244.94 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5720, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] A Word about the InnoDB Buffer Pool LRU Algorithm

In order to better understand what the above metrics mean, we should briefly review how the InnoDB Buffer Pool LRU Algorithm works.

InnoDB manages the buffer pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a new page to the pool, InnoDB evicts the least recently used page and adds the new page to the middle of the list. This “midpoint insertion strategy” treats the list as two sublists:

  • At the head, a sublist of “new” (or “young”) pages that were accessed recently.
  • At the tail, a sublist of “old” pages that were accessed less recently.

This algorithm keeps pages that are heavily used by queries in the new sublist. The old sublist contains less-used pages, which are candidates for eviction.

With that in mind, here are some of the more important fields in the InnoDB Standard Monitor output:

  • Old database pages: The number of pages in the old sublist of the buffer pool.
  • Pages made young, not young: The number of old pages that were moved to the head of the buffer pool (the new sublist), and the number of pages that have remained in the old sublist without being made new.
  • youngs/s non-youngs/s: The number of accesses to old pages that have resulted in making them young or not. This metric differs from that of the previous item in two ways. First, it relates only to old pages. Second, it is based on number of accesses to pages and not the number of pages. (There can be multiple accesses to a given page, all of which are counted.)
  • young-making rate: Hits that cause blocks to move to the head of the buffer pool.
  • not: Hits that do not cause blocks to move to the head of the buffer pool.

The young-making rate and not rate will not normally add up to the overall buffer pool hit rate.

Some MySQL Performance Tuning Tips

If you see very low youngs/s values when you do not have large scans going on, that may be an indication that you have to either reduce the delay time for a block to be moved from the old to the new sublist, or increase the percentage of the buffer pool used for the old sublist.

If you do not see a lot of non-youngs/s when you are doing large table scans (and lots of youngs/s), try tuning your delay value to be larger.

The innodb_old_blocks_time global variable specifies how long in milliseconds (ms) a page inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. If the value is 0, a page inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, pages remain in the old sublist until an access occurs at least that many milliseconds after the first access. For example, a value of 1000 causes pages to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

The following statement sets the innodb_old_blocks_time to zero:

SET GLOBAL innodb_old_blocks_time = 0;

The innodb_old_blocks_pct global variable specifies the approximate percentage of the buffer pool that InnoDB uses for the old block sublist. Increasing the old sublist percentage makes it larger, so blocks in that sublist take longer to move to the tail and be evicted. This increases the likelihood that they will be accessed again and be made young. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool).

When scanning small tables that do fit into memory, there is less overhead for moving pages around within the buffer pool, so you can leave innodb_old_blocks_pct at its default value, or even higher, such as innodb_old_blocks_pct=50.

There are many other global status variables you can examine in addition to innodb_old_blocks_time and innodb_old_blocks_pct:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; Variable_name Value ----------------------------------------------------------- Innodb_buffer_pool_dump_status, not started Innodb_buffer_pool_dump_status not started Innodb_buffer_pool_load_status not started Innodb_buffer_pool_pages_data 460 Innodb_buffer_pool_bytes_data 7536640 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_bytes_dirty 0 Innodb_buffer_pool_pages_flushed 1 Innodb_buffer_pool_pages_free 7730 Innodb_buffer_pool_pages_misc 2 Innodb_buffer_pool_pages_total 8192 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_ahead 0 Innodb_buffer_pool_read_ahead_evicted 0 Innodb_buffer_pool_read_requests 15397 Innodb_buffer_pool_reads 461 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 1

Of these, some metrics will be more useful to you than others. Standouts include:

  • metrics tracking the total size of the buffer pool
  • how much is in use
  • how effectively the buffer pool is serving reads

The metrics Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are integral to gauging buffer pool utilization. Innodb_buffer_pool_read_requests are number of request to read a row from the buffer pool and Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk to fetch required data pages. Reading from memory is generally much faster than reading from disk, so keep an eye out for increasing Innodb_buffer_pool_reads numbers!

Buffer pool efficiency can be calculated using the formula:

innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001

Here’s an example:

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_rea%'; Variable_name Value ----------------------------------------------- Innodb_buffer_pool_read_requests | 2905072850 | Innodb_buffer_pool_reads | 1073291394 |

Calculating the Innodb buffer pool efficiency:

(107329139 / 2905072850 * 100) = 37

In this particular case, the Innodb is doing more disk reads, so the Innodb buffer pool is not large enough.

Buffer pool utilization is another useful metric to check. The utilization metric is not available as such but can be easily calculated as follows:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) ---------------------------------------------------------------- Innodb_buffer_pool_pages_total

Here’s an example:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%'; Variable_name Value ----------------------------------------------- Innodb_buffer_pool_pages_data 460 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 1 Innodb_buffer_pool_pages_free 7730 Innodb_buffer_pool_pages_misc 2 Innodb_buffer_pool_pages_total 8192

Plugging the numbers into our formula:

(8192 – 7730) / 8192 = 0.056396484375

We can convert that into a percentage by multiplying by 100:

0.056396484375 * 100 = 5.64% (Quite low!)

The fact that your database is serving a large number of reads from disk while the buffer pool is near empty is not in itself cause for celebration; it may be that your cache has recently been cleared and is still in the process of refilling. However, should this condition continues for an extended amount of time, it’s very likely that there is plenty of memory to accommodate your dataset.

High buffer pool utilization is not necessarily a bad thing either, as long as old data is being aged out of the cache according to the LRU policy.

Only when read operations are overpowering the buffer pool, should you start to seriously think about scaling up your cache.

Converting Buffer Pool Metrics to Bytes

Most buffer pool metrics are reported as a count of memory pages, which is not all that useful. Luckily, page counts can be converted to bytes, which makes it a lot easier to determine the actual size of your buffer pool. For instance, this simple formula gives us the total size of buffer pool in bytes:

Innodb_buffer_pool_pages_total * innodb_page_size

The innodb_page_size can be retrieved using a SHOW VARIABLES query:

SHOW VARIABLES LIKE "innodb_page_size"

Monyog offers the most useful Buffer Pool metrics at a glance:

Conclusion

In this final installment of 2-part blog series, we learned how to capture and study MySQL metrics that provide the most value for the effort. The salient points covered include:

  • Client connection requests on the network interfaces that the server listens to are handled by the Connection manager.
  • It’s important to monitor your client connections because, once the database server runs out of available connections, new client connections are refused!
  • Every time a client is unable to connect, the server increments the Aborted_connects status variable.
  • Fine-grained connection metrics such as Connection_errors_max_connections and Connection_errors_internal can be instrumental in pinpointing the source of connection problems.
  • MySQL’s default storage engine, InnoDB, uses a special storage area called the buffer pool to cache data for tables and indexes.
  • Buffer pool metrics are categorized as resource metrics.
  • You can configure various aspects of the InnoDB buffer pool to improve performance.
  • InnoDB Standard Monitor output provides a number of metrics pertaining to operation of the InnoDB buffer pool.
  • The least recently used (LRU) algorithm employs a “midpoint insertion strategy” that treats the pages as “old” and “new” sublists.
  • The LRU algorithm can be tuned using the innodb_old_blocks_time and innodb_old_blocks_pct global variables.

By tracking the most useful metrics and reviewing them in the most informative way(s), we strike a balance between over-monitoring and firefighting unforeseen crises.

Monyog is a MySQL monitoring tool that improves the database performance of your MySQL powered systems. Download your free trial.

The post Top Performance Metrics to Monitor on MySQL (Connections & Buffer Pool Usage) appeared first on Monyog Blog.

MariaDB Server 10.2.10 now available

MariaDB Server 10.2.10 now available dbart Wed, 11/01/2017 - 11:04

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.10. See the release notes and changelog for details and visit mariadb.com/downloads to download.

Download MariaDB Server 10.2.10

Release Notes Changelog What is MariaDB 10.2?

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.10. See the release notes and changelog for details.

Login or Register to post comments

Scotland PHP, World PHP, and PyTexas -- Support your local conferences

November is the beginning of the end of the 2017 year conference cycle. But there is still time for you to get involved!! Scotland PHP is November 4th and features top presenters in Edinburgh. Last year this show was a happy surprise to find such a vibrant local PHP community and this year will be bigger.



















November 15th-17th is PHP World in Washington, D.C. -- a show that had been extremely popular since it started with the movers and shakers of the PHP world.


And PyTexas has moved to Austin and is November 18th and 19th! This show has moved from the campus of Texas A&M into UT territory and will attract developers from the bubbling Texas developer community.
 
If there is a conference in your area it is a big bonus for you to support it. Not only can you get exposed to new ideas, network professionally, and visit vendors, you also show to those who are not from your area or lack local knowledge that there is a a community in your area. A community worth visiting, a community with a talent pool, and a community that needs paying attention to. So what if you do not know anyone there, or are not sure what exactly all the talks are about (I try to go to one session every conference on something I know NOTHING about to help broaden my perspective). You will run into others with like interests. And if you run into those with diametrically opposite interests, they can still teach you a thing or two. Two of the three events above (Scotland PHP and PyTexas(are smaller, regional events that tell vendors like Oracle/MySQL what is going on in that area by the attendees. All three are great events and, if local, worth spending a hard earned weekend day (or two) to attend and a reasonably price. PHP World may be your regional event but it has a larger, global audience that provides a lot of information for the price. The three sets of organizers of these shows do an amazing amount of work on your behalf. The presenters have fascinating things to show you. The vendors will have solutions to your problems. And you are part of a bigger community that needs you to help represent them to the world.
 
If you have not been to a conference this year then please try to make these shows.


And if you can not make it to show this year, you need to be planning for 2018. This is an investment in yourself that you need to make.

MySQL Dashboard Improvements in Percona Monitoring and Management 1.4.0

In this blog post, I’ll walk through some of the improvements to the Percona Monitoring and Management (PMM) MySQL dashboard in release 1.4.0.

As the part of Percona Monitoring and Management development, we’re constantly looking for better ways to visualize information and help you to spot and resolve problems faster. We’ve made some updates to the MySQL dashboard in the 1.4.0 release. You can see those improvements in action in our Percona Monitoring and Management Demo Site: check out the MySQL Overview and MySQL InnoDB Metrics dashboards.

MySQL Client Thread Activity

One of the best ways to characterize a MySQL workload is to look at the number of MySQL server-client connections (Threads Connected). You should compare this number to how many of those threads are actually doing something on the server side (Threads Running), rather than just sitting idle waiting for a client to send the next request.

MySQL can handle thousands of connected threads quite well. However, many threads (hundred) running concurrently often increases query latency. Increased internal contention can make the situation much worse.

The problem with those metrics is that they are extremely volatile – one second you might have a lot of threads connected and running, and then none. This is especially true when some stalls on the MySQL level (or higher) causes pile-ups.

To provide better insight, we now show Peak Threads Connected and Peak Threads Running to help easily spot such potential pile-ups, as well as Avg Threads Running. These stats allow you look at a high number of threads connected and running to see if it there are just minor spikes (which tend to happen in many systems on a regular basis), or something more prolonged that warrants deeper investigation.

To simplify it even further: Threads Running spiking for a few seconds is OK, but spikes persisting for 5-10 seconds or more are often signs of problems that are impacting users (or problems about to happen).

InnoDB Logging Performance

Since I wrote a blog post about Choosing MySQL InnoDB Log File Size, I thought it would be great to check out how long the log file space would last (instead of just looking at how much log space is written per hour). Knowing how long the innodb_log_buffer_size lasts is also helpful for tuning this variable, in general.

This graph shows you how much data is written to the InnoDB Log Files, which helps to understand your disk bandwidth consumption. It also tells you how long it will take to go through your combined Redo Log Space and InnoDB Log Buffer Size (at this rate).

As I wrote in the blog post, there are a lot of considerations for choosing the InnoDB log file size, but having enough log space to accommodate all the changes for an hour is a good rule of thumb. As we can see, this system is close to full at around 50 minutes.

When it comes to innodb_log_buffer_size, even if InnoDB is not configured to flush the log at every transaction commit, it is going to be flushed every second by default. This means 10-15 seconds is usually good enough to accommodate the spikes. This system has it set at about 40 seconds (which is more than enough).

InnoDB Read-Ahead

This graph helps you understand how InnoDB Read-Ahead is working out, and is a pretty advanced graph.

In general, Innodb Read-Ahead is not very well understood. I think in most cases it is hard to tell if it is helping or hurting the current workload in its current configuration.

The for Read-Ahead in any system (not just InnoDB) is to pre-fetch data before it is really needed (in order to reduce latency and improve performance). The risk, however, is pre-fetching data that isn’t needed. This is wasteful.

InnoDB has two Read-Ahead options: Linear Read-Ahead (designed to speed up workloads that have physically sequential data access) and Random Read-Ahead (designed to help workloads that tend to access the data in the same vicinity but not in a linear order).

Due to potential overhead, only Linear Read-Ahead is enabled by default. You need to enable Random Read-Ahead separately if you want to determine its impact on your workload

Back to the graph in question: we show a number of pages pre-fetched by Linear and Random Read-Aheads to confirm if these are even in use with your workload. We show Number of Pages Fetched but Never Accessed (evicted without access) – shown as both the number of pages and as a percent of pages. If Fetched but Never Accessed is more than 30% or so, Read-Ahead might be producing more waste instead of helping your workload. It might need tuning.

We also show the portion of IO requests that InnoDB Read-Ahead served, which can help you understand the portion of resources spent on InnoDB Read-Ahead

Due to the timing of how InnoDB increments counters, the percentages of IO used for Read-Ahead and pages evicted without access shows up better on larger scale graphs.

Conclusion

I hope you find these graphs helpful. We’ll continue making Percona Monitoring and Management more helpful for troubleshooting database systems and getting better performance!

MySQL 5.7 InnoDB Temporary Tablespace – but why?

So, recently we had a runaway query eat up all sorts of temporary table space on our machines. Several machines had several terabytes in their ibtmp1 file after this happened. So I set out to find out more about why the InnoDB temporary tablespace is used, why it is better than using regular files, which was what was used prior to MySQL 5.7, and how to make sure that runaway queries do not end up filling up disk space.

Unfortunately, the manual does not go into why ibtmp1 is better than one file per temporary query, which disappears once the query ends. There are a few sections to look into:

Temporary Table Undo Logs – has one paragraph that states that these are the undo logs for temporary tablespaces. Given that these are undo logs, my guess is that this makes MySQL more crash-safe. But that is just a guess.

There is also InnoDB Temporary Tablespace which is two paragraphs, with some more details, but again, no reasoning why.

And finally, the documentation for the innodb_temp_data_file_path system variable sheds a bit of light on the subject – It explains “Metadata about active InnoDB temporary tables is located in INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO.”

There is a manual page on Innodb temp table info table as well, which shows both compressed and uncompressed tables – uncompressed tables live in the ibtmp tablespaces, and compressed temporary tables live in the .ibd tablespace of the compressed table in question – as shown in the “PER_TABLE_SPACE” and “IS_COMPRESSED” fields.

Sadly, the table does not give useful information such as which process ID or user is generating the data. And of course it is only the active temporary space usage at the time – if you have a large temporary tablespace but no active queries using the tablespace, INNODB_TEMP_TABLE_INFO is empty.

I can imagine a scenario with more than one long-running query using a lot of space in the temporary tablespace. But I do not see how the INNODB_TEMP_TABLE_INFO would help me determine any useful information as to which query I should kill. I guess it is useful to see if there is an active query currently using temporary tablespace, but when you have a large file with nothing in it, it is just that much more depressing.

# mysql INFORMATION_SCHEMA -p -e “SELECT * FROM INNODB_TEMP_TABLE_INFO”
Enter password:
# ls -rlth /var/lib/mysql/ibtmp1
-rw-r—– 1 mysql mysql 2.3T Oct 31 10:50 /var/lib/mysql/ibtmp1

How to Stop or Throttle SST Operation on a Galera Cluster

State Snapshot Transfer (SST) is one of the two ways used by Galera to perform initial syncing when a node is joining a cluster, until the node is declared as synced and part of the “primary component”. Depending on the dataset size and workload, SST could be lightning fast, or an expensive operation which will bring your database service down on its knees.

SST can be performed using 3 different methods:

  • mysqldump
  • rsync (or rsync_wan)
  • xtrabackup (or xtrabackup-v2, mariabackup)

Most of the time, xtrabackup-v2 and mariabackup are the preferred options. We rarely see people running on rsync or mysqldump in production clusters.

The Problem

When SST is initiated, there are several processes triggered on the joiner node, which are executed by the "mysql" user:

$ ps -fu mysql UID PID PPID C STIME TTY TIME CMD mysql 117814 129515 0 13:06 ? 00:00:00 /bin/bash -ue /usr//bin/wsrep_sst_xtrabackup-v2 --role donor --address 192.168.55.173:4444/xtrabackup_sst//1 --socket /var/lib/mysql/mysql.sock --datadir mysql 120036 117814 15 13:06 ? 00:00:06 innobackupex --no-version-check --tmpdir=/tmp/tmp.pMmzIlZJwa --user=backupuser --password=x xxxxxxxxxxxxxx --socket=/var/lib/mysql/mysql.sock --galera-inf mysql 120037 117814 19 13:06 ? 00:00:07 socat -u stdio TCP:192.168.55.173:4444 mysql 129515 1 1 Oct27 ? 01:11:46 /usr/sbin/mysqld --wsrep_start_position=7ce0e31f-aa46-11e7-abda-56d6a5318485:4949331

While on the donor node:

mysql 43733 1 14 Oct16 ? 03:28:47 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=7ce0e31f-aa46-11e7-abda-56d6a5318485:272891 mysql 87092 43733 0 14:53 ? 00:00:00 /bin/bash -ue /usr//bin/wsrep_sst_xtrabackup-v2 --role donor --address 192.168.55.172:4444/xtrabackup_sst//1 --socket /var/lib/mysql/mysql.sock --datadir /var/lib/mysql/ --gtid 7ce0e31f-aa46-11e7-abda-56d6a5318485:2883115 --gtid-domain-id 0 mysql 88826 87092 30 14:53 ? 00:00:05 innobackupex --no-version-check --tmpdir=/tmp/tmp.LDdWzbHkkW --user=backupuser --password=x xxxxxxxxxxxxxx --socket=/var/lib/mysql/mysql.sock --galera-info --stream=xbstream /tmp/tmp.oXDumYf392 mysql 88827 87092 30 14:53 ? 00:00:05 socat -u stdio TCP:192.168.55.172:4444

SST against a large dataset (hundreds of GBytes) is no fun. Depending on the hardware, network and workload, it may take hours to complete. Server resources may be saturated during the operation. Despite throttling is supported in SST (only for xtrabackup and mariabackup) using --rlimit and --use-memory options, we are still exposed to a degraded cluster when you are running out of majority active nodes. For example, if you are unlucky enough to find yourself with only one out of three nodes running. Therefore, you are advised to perform SST during quiet hours. You can, however, avoid SST by taking some manual steps, as described in this blog post.

Stopping an SST

Stopping an SST needs to be done on both the donor and the joiner nodes. The joiner triggers SST after determining how big the gap is when comparing the local Galera seqno with cluster's seqno. It executes the wsrep_sst_{wsrep_sst_method} command. This will be picked by the chosen donor, which will start streaming out data to the joiner. A donor node has no capabilities of refusing to serve snapshot transfer, once selected by Galera group communication, or by the value defined in wsrep_sst_donor variable. Once the syncing has started and you want to revert the decision, there is no single command to stop the operation.

The basic principle when stopping an SST is to:

  • Make the joiner look dead from a Galera group communication point-of-view (shutdown, fence, block, reset, unplug cable, blacklist, etc)
  • Kill the SST processes on the donor

One would think that killing the innobackupex process (kill -9 {innobackupex PID}) on the donor would be enough, but that is not the case. If you kill the SST processes on donor (or joiner) without fencing off the joiner, Galera still can see the joiner as active and will mark the SST process as incomplete, thus respawning a new set of processes to continue or start over again. You will be back to square one. This is the expected behaviour of /usr/bin/wsrep_sst_{method} script to safeguard SST operation which is vulnerable to timeouts (e.g., if it is long-running and resource intensive).

Let's look at an example. We have a crashed joiner node that we would like to rejoin the cluster. We would start by running the following command on the joiner:

$ systemctl start mysql # or service mysql start

A minute later, we found out that the operation is too heavy at that particular moment, and decided to postpone it later during low traffic hours. The most straightforward way to stop an xtrabackup-based SST method is by simply shutting down the joiner node, and kill the SST-related processes on the donor node. Alternatively, you can also block the incoming ports on the joiner by running the following iptables command on the joiner:

$ iptables -A INPUT -p tcp --dport 4444 -j DROP $ iptables -A INPUT -p tcp --dport 4567:4568 -j DROP

Then on the donor, retrieve the PID of SST processes (list out the processes owned by "mysql" user):

$ ps -u mysql PID TTY TIME CMD 117814 ? 00:00:00 wsrep_sst_xtrab 120036 ? 00:00:06 innobackupex 120037 ? 00:00:07 socat 129515 ? 01:11:47 mysqld

Finally, kill them all except the mysqld process (you must be extremely careful to NOT kill the mysqld process on the donor!):

$ kill -9 117814 120036 120037

Then, on the donor MySQL error log, you should notice the following line appearing after ~100 seconds:

2017-10-30 13:24:08 139722424837888 [Warning] WSREP: Could not find peer: 42b85e82-bd32-11e7-87ae-eff2b8dd2ea0 2017-10-30 13:24:08 139722424837888 [Warning] WSREP: 1.0 (192.168.55.172): State transfer to -1.-1 (left the group) failed: -32 (Broken pipe)

At this point, the donor should return to the "synced" state as reported by wsrep_local_state_comment and the SST process is completely stopped. The donor is back to its operational state and is able to serve clients in full capacity.

For the cleanup process on the joiner, you can simply flush the iptables chain:

$ iptables -F

Or simply remove the rules with -D flag:

$ iptables -D INPUT -p tcp --dport 4444 -j DROP $ iptables -D INPUT -p tcp --dport 4567:4568 -j DROP Related resources  How to Avoid SST when adding a new node to Galera Cluster for MySQL or MariaDB  Using the Galera Replication Window advisor to avoid SST  Updated - How to Bootstrap MySQL or MariaDB Galera Cluster

The similar approach can be used with other SST methods like rsync, mariabackup and mysqldump.

Throttling an SST (xtrabackup method only)

Depending on how busy the donor is, it's a good approach to throttle the SST process so it won't impact the donor significantly. We've seen a number of cases where, during catastrophic failures, users were desperate to bring back a failed cluster as a single bootstrapped node, and let the rest of the members catch up later. This attempt reduces the downtime from the application side, however, it creates additional burden on this “one-node cluster”, while the remaining members are still down or recovering.

Xtrabackup can be throttled with --throttle=<rate of IO/sec> to simply limit the number of IO operation if you are afraid that it will saturate your disks, but this option is only applicable when running xtrabackup as a backup process, not as an SST operator. Similar options are available with rlimit (rate limit) and can be combined with --use-memory to limit the RAM usage. By setting up values under [sst] directive inside the MySQL configuration file, we can ensure that the SST operation won't put too much load on the donor, even though it can take longer to complete. On the donor node, set the following:

[sst] rlimit=128k inno-apply-opts="--use-memory=200M"

More details on the Percona Xtrabackup SST documentation page.

However, there is a catch. The process could be so slow that it will never catch up with the transaction logs that InnoDB is writing, so SST might never complete. Generally, this situation is very uncommon, unless if you really have a very write-intensive workload or you allocate very limited resources to SST.

Conclusions

SST is critical but heavy, and could potentially be a long-running operation depending on the dataset size and network throughput between the nodes. Regardless of the consequences, there are still possibilities to stop the operation so we can have a better recovery plan at a better time.

Tags:  galera sst MySQL MariaDB troubleshooting

MariaDB Server 10.0.33 now available

MariaDB Server 10.0.33 now available dbart Mon, 10/30/2017 - 14:12

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.0.33. See the release notes and changelog for details and visit mariadb.com/downloads to download.

Download MariaDB Server 10.0.33

Release Notes Changelog What is MariaDB 10.0?

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.0.33. See the release notes and changelog for details.

Login or Register to post comments

Monyog MySQL Monitor v8.3.0: Introducing Trend Graph Analysis

Monyog MySQL Monitor v8.3.0 is a feature-rich release which adds a large number of user requests for quick access to relevant monitoring information, for ‘cross-plotting’ multiple servers in a unified chart and more.  Additionally, it adds a number of non-critical bug fixes.

Changes as compared to Monyog MySQL Monitor v8.2.0 include: Features:
  • Added option to set a distinct email distribution list for warning alerts and critical alerts.
  • Trend Graph Analysis: Added option to group a single metric (which one you find most important) from different servers into one unified chart. This allows you to visually analyse a metric across servers at various points in time.

Choose the monitor group >> Click on the trend graph icon next to the metric as shown below.

Select the trend graph corresponding to the required metric

Monitor single metric across servers

  • Added option for disk monitoring of the system where Monyog is installed. In case the free space on the system where Monyog is installed goes below a defined threshold value, Monyog will raise an alert.
  • Added a REGEX-based filter to exclude unwanted long_running queries in Sniffer. Monyog will ignore queries satisfying the expression and neither kill nor send an alert for such.

REGEX-based filter to exclude unwanted long_running queries

  • Added option to filter queries based on poor indexes, missing indexes, errors and warnings. This feature is available for PERFORMANCE SCHEMA based sniffer in Query Analyzer and “Show details” page in Dashboard. When you choose a filter, Monyog will show queries based on the criteria set.

Filter queries based on poor indexes, missing indexes, errors and warnings

  • Redesigned Server selector GUI.
  • Redesigned Query Analyzer GUI:  a more intuitive view of the top 5 queries based on total time has been added.

A more intuitive view of the top 5 queries based on total time has been added

  • Added Y-axis unit for charts, along with the option to define the units and unit-factors.
Bug Fixes:
  • Fixed bogus “UNIQUE constraint failed..” -errors in MONyog.log.
  • In rare cases real-time page hanged on switching between real-time sessions.
  • In Option to plot values based on data collection in History/Trend analysis(For uptime counters) only “Group By” -option was available for those counters.
  • Monyog failed to recognise Aurora instances for OS monitoring. The four required fields for OS monitoring were not displaying.
  • Email addresses with “+” in monitor level notification settings was not accepted.
  • Added “DB” as a column under ‘Manage Columns’ in Dashboard’s ‘show details’ page in ‘Processlist’ mode.
  • Monyog showed the total time and average latency as “0” when the execution time of the query was less than a second for table based slow query log.
  • Fixed an issue with Custom SQL Object (CSO) – Monyog logged entries like “not an error” in MONyog.log.
  • Server name with ‘&’ in it was not getting deleted.
  • In some rare cases, Monyog failed to generate alert if the network connection on the machine where MySQL is running goes down.
  • Some monitors in the linux monitor group gave NaN while doing History/Trend Analysis.

You can download a 14-day free trial of Monyog here.
Purchase Monyog: https://www.webyog.com/product/monyogpricing

The post Monyog MySQL Monitor v8.3.0: Introducing Trend Graph Analysis appeared first on Monyog Blog.

Percona XtraDB Cluster 5.7.19-29.22-3 is now available

Percona announces the release of Percona XtraDB Cluster 5.7.19-29.22-3 on October 27, 2017. Binaries are available from the downloads section or our software repositories.

NOTE: You can also run Docker containers from the images in the Docker Hub repository.

Percona XtraDB Cluster 5.7.19-29.22-3 is now the current release, based on the following:

All Percona software is open-source and free.

Fixed Bugs
  • Added access checks for DDL commands to make sure they do not get replicated if they failed without proper permissions. Previously, when a user tried to perform certain DDL actions that failed locally due to lack of privileges, the command could still be replicated to other nodes, because access checks were performed after replication.This vulnerability is identified as CVE-2017-15365.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Percona XtraDB Cluster 5.6.37-26.21-3 is Now Available

Percona announces the release of Percona XtraDB Cluster 5.6.37-26.21-3 on October 27, 2017. Binaries are available from the downloads section or our software repositories.

Percona XtraDB Cluster 5.6.37-26.21-3 is now the current release, based on the following:

All Percona software is open-source and free.

Fixed Bugs
  • Added access checks for DDL commands to make sure they do not get replicated if they failed without proper permissions. Previously, when a user tried to perform certain DDL actions that failed locally due to lack of privileges, the command could still be replicated to other nodes, because access checks were performed after replication.This vulnerability is identified as CVE-2017-15365.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Announcing Galera Cluster for MySQL 5.5.58, 5.6.38, 5.7.20 with Galera 3.22.

Codership is pleased to announce a new release of Galera Cluster for MySQL consisting of MySQL-wsrep 5.5.58, 5.6.38, 5.7.20 and new Galera 3.22 library, wsrep API version 25.

 

NEW FEATURES AND NOTABLE FIXES IN THE GALERA REPLICATION LIBRARY SINCE LAST BINARY RELEASE BY CODERSHIP (3.21):

 

New features and notable fixes in Galera replication since last binary release

* Reporting last committed write set fixed to respect commit ordering (MW-341)

* GComm socket level error handling improved to avoid backend thread exit
in case of unexpected input from ASIO IO service (GAL-518)

* Race condition fixed in GComm message sending codepath (GAL-520)

* Fix for EVS protocol stall due to exhausted send window setting. This
bug could stall cluster messaging until the next keepalive was sent by
some node, causing intermittent pauses in write set replication. (GAL-521)

* Code changes to avoid name collisions with FreeBSD system headers (GAL-523)

Read the full release notes (how to install, repository structure) 

 

 

NOTABLE BUG FIXES IN MYSQL-WSREP:

 

Version MySQL-wsrep 5.7.20 and Galera 3.22, wsrep API version 25.

* Preserve –wsrep-recover log for future reference when starting the server.
The preserved log is stored in a file under MySQL data directory,
either in wsrep_recovery.ok or wsrep_recovery.fail depending on recovery
success. (MW-318)

* Avoid returning outdated values for wsrep_ready status variable (MW-384)

* A bug which caused stored procedure with an error handler to commit
a statement even in case of certification error was fixed. (MW-388)

* Crash during LOAD DATA for partition engine was fixed (MW-394)

* Fixed a crash caused by a dangling reference to wsrep status variables
array. (MW-399)

* Fixes to processing of foreign key cascades. (MW-402)

* ACL checks are now enforced before replication for all DDL operations
(MW-416)

* ALTER EVENT statement failure on slave was fixed (MW-417)

Read the full release notes  (known issues, how to install, repository structure) 

 

 

Version MySQL-wsrep 5.6.38 and Galera 3.22, wsrep API version 25

* Preserve –wsrep-recover log for future reference when starting the server.
The preserved log is stored in a file under MySQL data directory,
either in wsrep_recovery.ok or wsrep_recovery.fail depending on recovery
success. (MW-318)

* Avoid returning outdated values for wsrep_ready status variable (MW-384)

* A bug which caused stored procedure with an error handler to commit
a statement even in case of certification error was fixed. (MW-388)

* Crash during LOAD DATA for partition engine was fixed (MW-394)

* Fixed a crash caused by a dangling reference to wsrep status variables
array. (MW-399)

* Fixes to processing of foreign key cascades. (MW-402)

* ACL checks are now enforced before replication for all DDL operations
(MW-416)

Read the full release notes (known issues, how to install, repository structure)

 

 

Version MySQL-wsrep 5.5.58 and Galera 3.22, wsrep API version 25

Notable bug fixes in MySQL-wsrep:

* Avoid returning outdated values for wsrep_ready status variable (MW-384)

* Crash during LOAD DATA for partition engine was fixed (MW-394)

* Fixes to processing of foreign key cascades. (MW-402)

* ACL checks are now enforced before replication for all DDL operations
(MW-416)

Read the full release notes (known issues, how to install, repository structure)

 

This Week in Data with Colin Charles 12: Open Source Summit Europe and Open Source Entrepreneur Network

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

This week was exciting from a Percona exposure standpoint. We were at Open Source Summit Europe. I gave two talks and participated in a panel, as the co-located event for the Open Source Entrepreneur Network happened on the last day as well. We had a booth, and it was great to hang out and talk with my colleagues Dorothée Wuest and Dimitri Vanoverbeke as well as all the attendees that popped by.

Releases Link List Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Lesson 08: Doing More with MySQL

Notes/errata/updates for Chapter 8:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 8 includes pages 277 – 296.

Note: if trying to load the “academics.csv” file on Linux, you may want to set the owner and group to mysql first:
sudo chown mysql:mysql /tmp/academics.csv

In the section on “Creating Tables with Queries”, p. 286, it says “The LIKE syntax allows you to create a new table with exactly the same structure as another, including keys.” However, it only preserves UNIQUE, PRIMARY and regular indexes. It does not preserve FOREIGN keys.

In the section on “Replacing Data”, p 292 – 293, it mentions the REPLACE command, but not the INSERT…ON DUPLICATE KEY UPDATE syntax. You can read about that at the MySQL manual page.

Finally, for more information on EXPLAIN, check out this video presentation on how to use and interpret EXPLAIN. There are also PDF slides.

Topics covered:
Importing and Exporting Data
Replacing Data
CREATE TABLE…LIKE
EXPLAIN

Reference/Quick Links for MySQL Marinate

Efficient JSON Replication in MySQL 8.0

MySQL is not only a relational database, but can also be used as a schemaless/NOSQL document store, or a mix of both. This is realized by the JSON datatype, and is useful for any data that is hard to fit in the ”tabular” format of a traditional table.…

MySQL Enterprise Monitor 4.0.1 has been released

We are pleased to announce that MySQL Enterprise Monitor 4.0.1 is now available for download on the My Oracle Support (MOS) web site. It will also be available for download via the Oracle Software Delivery Cloud in a few days. MySQL Enterprise Monitor is the best-in-class tool for monitoring and management of your MySQL assets and is included with your MySQL Enterprise Edition and MySQL Enterprise Carrier Grade subscriptions.

You can find more information on the contents of this release in the change log.

Highlights of MySQL Enterprise Monitor 4.0 include:

  • Modern look and feel: a redesigned user interface delivers a vastly improved overall user experience. The visible changes--the layout, the icons, the and the overall aesthetics--provide a more natural and intuitive experience. Views dynamically change and adjust to your current context and the assets you've selected, everything from individual MySQL instances or hosts to your custom Groups, to your complex replication and clustered topologies. Additional enhancements include a much more responsive UI and a greater capacity to scale, allowing you to more effectively manage thousands of MySQL related assets.
  • MySQL Cluster monitoring: we now auto-discover your MySQL Cluster installations and give you visibility into the performance, availability, and health of each MySQL instance and NDB process, as well as the health of the MySQL Cluster instance as a single logical system. The Overview dashboard displays detailed instrumentation available for each MySQL Cluster and the Topology dashboard displays the current configuration of your MySQL Cluster enabling you to quickly see the status of the MySQL Cluster instance as a whole and each individual process. The Topology dashboard allows you to easily see how your MySQL Cluster installations are currently functioning.
  • A User Statistics report: which provides an easy way to monitor MySQL resource utilization broken down by user.

You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.

You will also find the binaries on the Oracle Software Delivery Cloud soon.  Type "MySQL Enterprise Monitor 4.0.0" in the search box, or enter a license name to find Enterprise Monitor along with other MySQL products: "MySQL Enterprise Edition" or "MySQL Cluster Carrier Edition".  Then select your platform.

Please open a bug or a ticket on My Oracle Support to report problems, request features, or give us general feedback about how this release meets your needs.

If you are not a MySQL Enterprise customer and want to try the Monitor and Query Analyzer using our 30-day free customer trial, go to http://www.mysql.com/trials, or contact Sales at http://www.mysql.com/about/contact.

Thanks and Happy Monitoring!

- The MySQL Enterprise Tools Development Team

Useful URLs

The Perfect Server - Ubuntu 17.10 (Artful Aardvark) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1

This tutorial shows how to install an Ubuntu 17.10 (Artful Aardvark) server (with Apache2, BIND, Dovecot) for the installation of ISPConfig 3.1, and how to install ISPConfig. ISPConfig 3 is a web hosting control panel that allows you to configure the following services through a web browser: Apache or nginx web server, Postfix mail server, Courier or Dovecot IMAP/POP3 server, MySQL, BIND or MyDNS nameserver, PureFTPd, SpamAssassin, ClamAV, and many more. This setup covers the installation of Apache (instead of nginx), BIND (instead of MyDNS), and Dovecot (instead of Courier).

Joint Webinar with Severalnines: How to manage Galera Cluster using ClusterControl

Since its creation, Galera Cluster has established itself as the most popular high availability solution for MySQL and MariaDB users worldwide.

ClusterControl is the go-to automation and management system for Galera Cluster users.

And together, we’re going to walk you through all the different aspects that make Galera Cluster such a popular high availability solution for MySQL and MariaDB and how to best manage it with ClusterControl.

We’ll hear about the latest features of Galera Cluster directly from Codership, the creators of Galera Cluster. And we’ll look at how to automate everything from deployment, monitoring (how about ASCII-art graphs?), backups, failover, recovery, rolling upgrades and scaling using the ClusterControl CLI (for a change, we also have a GUI of course).

AGENDA

Introduction
About Codership, the makers of Galera Cluster
About Severalnines, the makers of ClusterControl
What’s new with Galera Cluster
Core feature set overview
What’s coming up
ClusterControl for Galera Cluster
Deployment
Monitoring
Management
Scaling
Live Demo
Q&A

 

Join EMEA timezone webinar Tue November 14, 10 AM CET

 

Join USA timezone webinar Tue November 14, 9 AM PST

 

Presenting:

Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard.

Seppo Jaakola, Codership CEO

Pages